在MS Sql 查询Windows AD 相关信息....
在MS Sql 查询AD 相关信息
关于AD 相关的特性质
1. LDAP 的特性质说明
LDAP Attribute |
Example |
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. |
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 |
|
|
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 |
c 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