Step 1:creating a linked Server.
EXEC sp_addlinkedserver ' ADSI ', ' Active Directory Services 2.5 ', ' adsdsoobject ', ' Adsdatasource '
Step 2:creating A SQL Server authenticated Login
EXEC sp_addlinkedsrvlogin @rmtsrvname = n ' ADSI ', @locallogin = NULL, @useself = N ' False ', @rmtuser = N ' domain\account ', @ Rmtpassword = N ' Password '
For SQL Server authorized logins, you can use the sp_addlinkedsrvlogin system stored procedure to configure the appropriate login/password to connect to the directory service.
Reference here: http://blogs.msdn.com/euanga/archive/2007/03/22/faq-how-do-i-query-active-directory-from-sql-server.aspx
If SQL Server uses Windows to authorize logins, the only self mapping is sufficient to access the ad by using the SQL Server security delegate. The simple point is to run the third-step statement directly.
Step 3:querying the Directory Service.
Copy Code code as follows:
--Query for a-list of User entries in an OU using the SQL Query dialect
Select CONVERT (varchar, [Name]) as FullName,
CONVERT (varchar, title) as title,
CONVERT (varchar (), 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, [Name]) as GroupName,
CONVERT (varchar (m), [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
Description: But this default query comes with 1000 objects. What do we do?
Method one, through letters to circulate. See below:
Copy Code code as follows:
CREATE TABLE #tmpADUsers
(employeeId varchar) NULL,
sAMAccountName varchar (255) Not NULL,
Email varchar (255) NULL)
Go
/**//* AD is limited to send 1000 records in one batch. In a ADO interface 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, E Mployeeid from ' ldap://dc=central,dc=mydomain,dc=int ' "WHERE objectcategory = ' person '" ' and sAMAccountName = ' "'%s* '", @sChar
INSERT #tmpADUsers
EXEC (@cmdstr)
SELECT @nAsciiValue = @nAsciiValue + 1
End
DROP TABLE #tmpADUsers
The above methods originate from: Http://www.sqlservercentral.com/Forums/Topic231658-54-1.aspx
The method I recommend: A search in Microsoft. How to modify restrictions for a server through Ntdsutil maxpagesize
1. |
Click Start, and then click Run. |
2. |
In the Open text box, type ntdsutil, and then press ENTER. To view Help on any time, type ? at the command prompt. |
modifying policy settings
1. |
At the Ntdsutil.exe command prompt, type LDAP policies, and then press ENTER. |
2. |
At the LDAP policy command prompt, type Set setting to variable, and then press ENTER. For example, type Set maxpoolthreads to 8.
This setting changes if your add another processor to your server. |
3. |
Can use the show Values command to verify your changes.
To save the changes, use Commit changes. |
4. |
When you finish, type Q, and then press ENTER. |
5. |
To quit Ntdsutil.exe, at the command prompt, type q, and then press ENTER. |
Source of information:
Http://support.microsoft.com/kb/315071/en-us
http://support.microsoft.com/?scid=kb%3Bzh-cn%3B299410&x=16&y=10
How to use SQL to query the Active Directory object syntax: http://www.microsoft.com/china/technet/community/columns/scripts/sg0505.mspx
