在MS Sql 查询Windows AD 相关信息....

在MS Sql 查询AD 相关信息

 

关于AD 相关的特性质

 

1. LDAP 的特性质说明

LDAP Attribute

Example

CN - Common Name

CN=Guy Thomas.  Actually, this LDAP attribute is made up from givenName joined to SN.

description

What you see in Active Directory Users and Computers.  Not to be confused with displayName on the Users property sheet.

displayName

displayName = Guy Thomas.  If you script this property, be sure you understand which field you are configuring.  DisplayName can be confused with CN or description.

DN - also distinguishedName

DN is simply the most important LDAP attribute.
CN=Jay Jamieson, OU= Newport,DC=cp,DC=com

givenName

Firstname also called Christian name

homeDrive

Home Folder : connect.  Tricky to configure

name

name = Guy Thomas.  Exactly the same as CN.

objectCategory

Defines the Active Directory Schema category. For example, objectCategory = Person

objectClass

objectClass = User.  Also used for Computer, organizationalUnit, even container.  Important top level container.

physicalDeliveryOfficeName

Office! on the user's General property sheet

profilePath

Roaming profile path: connect.  Trick to set up

sAMAccountName

sAMAccountName = guyt.  Old NT 4.0 logon name, must be unique in the domain.  Can be confused with CN.

SN

SN = Thomas. This would be referred to as last name or surname.

userAccountControl

Used to disable an account.  A value of 514 disables the account, while 512 makes the account ready for logon.

initials

Initials

description

Description

telephoneNumber

Telephone Number

otherTelephone

Telephone: Other

mail

E-Mail

wwwHomePage

Web Page

url

Web Page: Other

userPrincipalName

UserLogon Name

logonHours

Logon Hours

logonWorkstation

Log On To

lockoutTime

Account is locked out time

lockoutDuration

Account is locked out lockoutTime and lockoutDuration If the lockoutTime attribute is not zero, the lockoutDuration attribute is added to lockoutTime and compared to the current date and time to determine if the account is locked out

pwdLastSet

User must change password at next logon

accountExpires

Account Expires

streetAddress

Street

postOfficeBox

P.O.Box

I

City

st

State/Province

postalCode

Zip/Postal Code

Top of Form

c

Bottom of Form

Bottom of Form

 

Country/Region

countryCode

Country/Region

co

Country/Region

memberOf

Member of

primaryGroupID

Set Primary Group

primaryGroupToken

Tied to primaryGroupToken of the primary group.

title

Title

department

Department

company

Company

manager

Manager:Name

directReports

Direct Reports

profilePath

Profile Path

scriptPath

Logon Script

homeDirectory

Local Path

homeDrive

Home Folder: Connect

homePhone

Home

otherHomePhone

Home: Other

pager

pager

otherPager

otherPager

mobile

Mobile

otherMobile

Mobile: Other

facsimileTelephoneNumber

Fax

otherFacsimileTelephoneNumber

Fax: Other

ipPhone

IP phone

otherIpPhone

IP phone: Other

info

Notes

 

 

2.关于帐号状态的一些参数值

特性质：  UserAccountControl

Num

Account_Status

512

Account: Enabled

514

Account: ACCOUNTDISABLE

528

Account: Enabled - LOCKOUT

530

Account: ACCOUNTDISABLE - LOCKOUT

544

Account: Enabled - PASSWD_NOTREQD

546

Account: ACCOUNTDISABLE - PASSWD_NOTREQD

560

Account: Enabled - PASSWD_NOTREQD - LOCKOUT

640

Account: Enabled - ENCRYPTED_TEXT_PWD_ALLOWED

2048

Account: INTERDOMAIN_TRUST_ACCOUNT

2080

Account: INTERDOMAIN_TRUST_ACCOUNT - PASSWD_NOTREQD

4096

Account: WORKSTATION_TRUST_ACCOUNT

8192

Account: SERVER_TRUST_ACCOUNT

66048

Account: Enabled - DONT_EXPIRE_PASSWORD

66050

Account: ACCOUNTDISABLE - DONT_EXPIRE_PASSWORD

66064

Account: Enabled - DONT_EXPIRE_PASSWORD - LOCKOUT

66066

Account: ACCOUNTDISABLE - DONT_EXPIRE_PASSWORD - LOCKOUT

66080

Account: Enabled - DONT_EXPIRE_PASSWORD - PASSWD_NOTREQD

66082

Account: ACCOUNTDISABLE - DONT_EXPIRE_PASSWORD - PASSWD_NOTREQD

66176

Account: Enabled - DONT_EXPIRE_PASSWORD - ENCRYPTED_TEXT_PWD_ALLOWED

131584

Account: Enabled - MNS_LOGON_ACCOUNT

131586

Account: ACCOUNTDISABLE - MNS_LOGON_ACCOUNT

131600

Account: Enabled - MNS_LOGON_ACCOUNT - LOCKOUT

197120

Account: Enabled - MNS_LOGON_ACCOUNT - DONT_EXPIRE_PASSWORD

532480

Account: SERVER_TRUST_ACCOUNT - TRUSTED_FOR_DELEGATION (Domain Controller)

1049088

Account: Enabled - NOT_DELEGATED

1049090

Account: ACCOUNTDISABLE - NOT_DELEGATED

2097664

Account: Enabled - USE_DES_KEY_ONLY

2687488

Account: Enabled - DONT_EXPIRE_PASSWORD - TRUSTED_FOR_DELEGATION -USE_DES_KEY_ONLY

4194816

Account: Enabled - DONT_REQ_PREAUTH

 

 

3. 在SQL 是查 LDAP 的信息

 

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource' 

 

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'domain/user_name',@rmtpassword='123456'

 

SELECT *

FROM OPENQUERY( ADSI,

'select AdsPath,objectCategory,objectSid

,homeDirectory,userAccountControl

,SAMAccountName,Name,userPrincipalName,mail, givenName,sn,cn,

telephoneNumber,displayName,department,lockoutTime,pwdLastSet,badPwdCount,lastLogon

 from ''LDAP://your_DC''

 WHERE objectCategory=''user''

'

)

 

 

注：LDAP://your_DC  也可以用  LDAP://IP