SQL Server и ActiveDirectory

Создание подключения к AD

Либо по-простому:
--A little easier way to perform this :-) --Create the linked server EXEC SP_ADDLINKEDSERVER 'ADSI', 'Active Directory Services 2.5','ADSDSOObject', 'adsdatasource' --Create the login account --(change SQL_ACCOUNT_NAME, --NT_ACCOUNT_NAME, PASSSWORD) EXEC SP_ADDLINKEDSRVLOGIN 'ADSI', FALSE, 'SQL_ACCOUNT_NAME','NT_ACCOUNT_NAME', 'PASSWORD' --Execute your query SELECT samAccountName, givenName [FIRST Name], sn [LAST Name],legacyExchangeDN FROM OPENQUERY(ADSI,'SELECT samAccountName, givenName, sn, legacyExchangeDN
   FROM ''LDAP://DC=zm,DC=COM''
   WHERE objectClass=''Person''
   AND objectClass = ''User''') WHERE givenName IS NOT NULL ---------------------------------------------------------------------------------   --из других источников SP_ADDLINKEDSERVER 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource' GO --удалить можно так --if exists(select 1 from master..sysservers where srvname = 'ADSI') exec sp_dropserver 'ADSI', 'droplogins' --CREATE VIEW viewADUsers AS --SELECT * FROM OpenQuery( ADSI,'
;(objectClass=*);givenname,sn, mail, adspath')
IF OBJECT_ID(N'[dbo].vw_ADUsers') IS NOT NULL DROP VIEW [dbo].vw_ADUsers GO -- Create the 'vw_ADUsers' view CREATE VIEW [dbo].vw_ADUsers AS SELECT      samaccountname,
     givenName,
     [description] FROM OPENQUERY( ADSI,
   'SELECT samaccountname, givenName,description
    FROM ''LDAP://OU=WCC Users,DC=wcc,DC=net''
   WHERE objectCategory = ''Person'' AND
                objectClass = ''User''') GO SELECT * FROM [dbo].vw_ADUsers

либо по полной в 2008:
/****** Object:  LinkedServer [ADSI]    Script Date: 11/16/2009 09:33:13 ******/ EXEC master.dbo.SP_ADDLINKEDSERVER @server = N'ADSI', @srvproduct=N'ActiveDirectory2SQL', @provider=N'ADsDSOObject', @datasrc=N'addatasource'  /* For security reasons the linked server remote logins password is changed with ######## */ EXEC master.dbo.SP_ADDLINKEDSRVLOGIN @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'RNTC\rpbd',@rmtpassword='########' GO EXEC master.dbo.SP_SERVEROPTION @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.SP_SERVEROPTION @server=N'ADSI', @optname=N'data access', @optvalue=N'true' GO EXEC master.dbo.SP_SERVEROPTION @server=N'ADSI', @optname=N'dist', @optvalue=N'false' GO EXEC master.dbo.SP_SERVEROPTION @server=N'ADSI', @optname=N'pub', @optvalue=N'false' GO EXEC master.dbo.SP_SERVEROPTION @server=N'ADSI', @optname=N'rpc', @optvalue=N'false' GO EXEC master.dbo.SP_SERVEROPTION @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false' GO EXEC master.dbo.SP_SERVEROPTION @server=N'ADSI', @optname=N'sub', @optvalue=N'false' GO EXEC master.dbo.SP_SERVEROPTION @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0' GO EXEC master.dbo.SP_SERVEROPTION @server=N'ADSI', @optname=N'collation name', @optvalue=null
GO EXEC master.dbo.SP_SERVEROPTION @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false' GO EXEC master.dbo.SP_SERVEROPTION @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0' GO EXEC master.dbo.SP_SERVEROPTION @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true' GO EXEC master.dbo.SP_SERVEROPTION @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true' GO

SELECT * -- , description,   FROM      OPENQUERY(ADSI, 'select cn,sn, givenname, ou, mail,displayname, samaccountname, distinguishedname, name,
 l, title, physicaldeliveryofficename, telephonenumber, department
 , company, streetaddress, userprincipalname,   manager
 , mailnickname, homephone, pager, mobile, postalcode
 from     ''LDAP://avrora/ DC = rntc, DC = ru''
 where     objectCategory = ''Person''  
order by sn')

Перебрать по буквам с A - Z (Т.к. есть ограничение на 1000 записей)

CREATE TABLE #tmpADUsers
 (  employeeId VARCHAR(10) NULL,
  SAMAccountName VARCHAR(255) NOT NULL,
  email  VARCHAR(255) NULL) GO /* AD is limited to send 1000 records in one batch. In an ADO interface you can define this batch size, not in OPENQUERY.
Because of this limitation, we just loop through the alphabet.
*/ DECLARE @cmdstr VARCHAR(255) DECLARE @nAsciiValue SMALLINT DECLARE @sChar CHAR(1) SELECT @nAsciiValue = 65 WHILE @nAsciiValue < 91  BEGIN   SELECT @sChar=  CHAR(@nAsciiValue)   EXEC master..XP_SPRINTF @cmdstr OUTPUT, 'SELECT employeeId, SAMAccountName, Mail FROM OPENQUERY( ADSI, ''SELECT Mail, SAMAccountName, employeeID FROM ''''LDAP://avrora/ DC = rntc, DC = ru''''WHERE objectCategory = ''''Person'''' AND SAMAccountName = ''''%s*'''''' )', @sChar

  INSERT #tmpADUsers
  EXEC( @cmdstr )   SELECT @nAsciiValue = @nAsciiValue + 1  END SELECT* FROM #tmpADUsers --DROP TABLE #tmpADUsers

Или так

SELECT users.sAMAccountName AS userName, objectsid,name,RTRIM(LTRIM(isnull(givenName,'')+' ' +isnull(sn,'')))AS fullname FROM OPENQUERY( ADSI, 'SELECT name, sn,givenName,sAMAccountName,objectsid FROM ''LDAP://DC=madomaine,DC=com'' WHERE objectCategory = ''Person'' AND objectClass= ''user'' AND ( NAME = ''A*'' OR NAME = ''B*'' OR NAME = ''C*'' OR NAME = ''D*'' OR NAME = ''E*'' OR NAME = ''F*'' OR NAME = ''G*'')')AS users UNION SELECT users.sAMAccountName AS userName, objectsid,name,RTRIM(LTRIM(isnull(givenName,'')+' ' +isnull(sn,'')))AS fullname FROM OPENQUERY( ADSI, 'SELECT name, sn,givenName,sAMAccountName,objectsid FROM ''LDAP://DC=madomaine,DC=com'' WHERE objectCategory = ''Person'' AND objectClass= ''user'' AND ( NAME = ''H*'' OR NAME = ''I*'' OR NAME = ''J*'' OR NAME = ''K*'' OR NAME = ''L*'' OR NAME = ''M*'' OR NAME = ''N*'')')AS users UNION SELECT users.sAMAccountName AS userName, objectsid,name,RTRIM(LTRIM(isnull(givenName,'')+' ' +isnull(sn,'')))AS fullname FROM OPENQUERY( ADSI, 'SELECT name, sn,givenName,sAMAccountName,objectsid FROM ''LDAP://DC=madomaine,DC=com'' WHERE objectCategory = ''Person'' AND objectClass= ''user'' AND ( NAME = ''O*'' OR NAME = ''P*'' OR NAME = ''Q*'' OR NAME = ''R*'' OR NAME = ''S*'' OR NAME = ''T*'' OR NAME = ''U*'')')AS users UNION SELECT users.sAMAccountName AS userName, objectsid,name,RTRIM(LTRIM(isnull(givenName,'')+' ' +isnull(sn,'')))AS fullname FROM OPENQUERY( ADSI, 'SELECT name, sn,givenName,sAMAccountName,objectsid FROM ''LDAP://DC=madomaine,DC=com'' WHERE objectCategory = ''Person'' AND objectClass= ''user'' AND ( NAME = ''V*'' OR NAME = ''W*'' OR NAME = ''X*'' OR NAME = ''Y*'' OR NAME = ''Z*'' )') AS users

Нельзя получить поля из АД через SQL

1. DESCRIPTION
2. POSTOFFICEBOX
3. memberOf