SQLServer uses ADSI to perform Distributed Query of activedorecloud objects.
SQLServer uses ADSI to perform Distributed Query of activedorecloud objects.
Step 1: Creating a Linked Server.
EXEC sp_addmediaserver 'adsi', 'active Directory Services 100', 'adsdsoobject', 'adsdatasource'
Step 2: Creating a SQL Server Authenticated Login
EXEC sp_add1_srvlogin @ rmtsrvname = N 'adsi', @ locallogin = NULL, @ useself = N 'false', @ rmtuser = N 'domain \ account', @ rmtpassword = N 'Password'
For SQL Server authorized logon, you can use sp_addjavassrvlogin system stored procedure configuration to connect to the appropriate logon/password for the Directory Service.
See here: http://blogs.msdn.com/euanga/archive/2007/03/22/faq-how-do-i-query-active-directory-from-sql-server.aspx
If SQLServer uses Windows-authorized logon, you only need to use self- ing to access AD by using SQL Server Security delegation. Simply put, you can directly run the Step 3 Statement.
Step 3: Querying the Directory Service.
The Code is as follows:
-- Query for a list of User entries in an OU using the SQL query dialect
Select convert (varchar (50), [Name]) as FullName,
Convert (varchar (50), Title) as Title,
Convert (varchar (50), TelephoneNumber) as PhoneNumber
From openquery (ADSI,
'Select Name, Title, TelephoneNumber
From ''ldap: // OU = Directors, OU = Atlanta, OU = Intellinet, DC = vizability, DC = intellinet, DC = com''
Where objectClass = ''user ''')
-- Query for a list of Group entries in an OU using the SQL query dialect
Select convert (varchar (50), [Name]) as GroupName,
Convert (varchar (50), [Description]) GroupDescription
From openquery (ADSI,
'Select Name, Description
From ''ldap: // OU = VizAbility Groups, DC = vizability, DC = intellinet, DC = com''
Where objectClass = ''group ''')
Reference:
Http://msdn2.microsoft.com/en-us/library/aa772380.aspx
Http://www.atlantamdf.com/presentations/AtlantaMDF_111201_examples.txt
Note: by default, 1000 objects are queried. How can this problem be solved?
Method 1: cycle through letters. See the following:
The Code is as follows:
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 @ brief STR varchar (255)
DECLARE @ nAsciiValue smallint
DECLARE @ sChar char (1)
SELECT @ nAsciiValue = 65
WHILE @ nAsciiValue <91
BEGIN
SELECT @ sChar = CHAR (@ nAsciiValue)
EXEC master .. xp_sprintf @ brief str output, 'select employeeId, SAMAccountName, Mail from openquery (ADSI, ''select Mail, SAMAccountName, employeeID FROM ''' LDAP: // dc = central, dc = mydomain, dc = int ''' WHERE objectCategory = ''' Person ''' AND SAMAccountName = ''' % s *'''''') ', @ sChar
INSERT # tmpADUsers
EXEC (@ brief Str)
SELECT @ nAsciiValue = @ nAsciiValue + 1
END
Drop table # tmpADUsers
The above method is derived from:
My recommended method: I found it at Microsoft.How to Use NTDSUtil to modify the maxPageSize limit for the server
Modifying policy settings
Source:
How to use SQL to query the Active Directory object Syntax:
