从SQL Server 2005查询Active Directory

How can I query Active Directory from SQL Server 2005?

如何从SQL Server 2005查询Active Directory?

4 个解决方案

#1

Pretty general question but here are some pointers.

相当普遍的问题,但这里有一些指示。

You need a linked server creating on the SQL Server that points to ADSI (Active Directory Service Interface) something like this will do it.

您需要在SQL Server上创建指向ADSI(Active Directory服务接口)的链接服务器才能执行此操作。

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

Then you can use the following sort of query.

然后,您可以使用以下类型的查询。


SELECT *
FROM OPENQUERY(ADSI, 'SELECT sAMAccountName
FROM ''LDAP://DC=MyDC,DC=com,DC=uk''
WHERE objectCategory = ''Person''
AND objectClass = ''user'')

You'll need to set the LDAP:// line appropriately (ask your AD admin for the details) and be aware that distributed adhoc queries using OpenQuery are disabled by default in SQL Server. Once you have the above though it should be pretty easy to google for any particular variations.

您需要适当地设置LDAP://行(请向您的AD管理员询问详细信息)并注意在SQL Server中默认禁用使用OpenQuery的分布式adhoc查询。一旦你有了上述内容,谷歌应该很容易找到任何特定的变化。

#2

Yes.

Linked server:

EXEC master.dbo.sp_addlinkedserver
    @server = N'ADSI', 
    @srvproduct=N'Active Directory Services',
    @provider=N'ADsDSOObject', 
    @datasrc=N'Servername.domain.com'

Query:

select * from openquery
(
ADSI,'SELECT name 
FROM ''LDAP://Servername.domain.com''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
')

There are lots of examples if you search linked server and LDPA on Google. I say this because LDAP can be quite complicated to work with.

如果您在Google上搜索链接服务器和LDPA,有很多示例。我这样说是因为LDAP可能非常复杂。

#3

Just a note; to remove the link use

只是一张纸条;删除链接使用

exec sp_dropserver 'ADSI';

#4

In order to overcome the maximum limit of 1000 records returned at a time from the Active Directory queries, you can use the function which I wrote below.

为了克服Active Directory查询中一次返回的1000条记录的最大限制,您可以使用我在下面编写的函数。

CREATE FUNCTION [dbo].[tf_GetAllUsersFromActiveDirectory]
()
RETURNS 
     @USERS TABLE 
        (   
              sAMAccountName    VARCHAR(25)             PRIMARY KEY CLUSTERED     
            , givenName VARCHAR(200)
            , SN VARCHAR(200)
            , userAccountControl VARBINARY(8)
            , mail VARCHAR(200)
        )
AS
BEGIN

INSERT INTO @Users
SELECT  sAMAccountName,givenName, sn, userAccountControl,mail FROM OpenQuery(ADSI, '<LDAP://YourDomain.com:389>;(&(objectClass=User)(|(sAMAccountName=A*)(sAMAccountName=B*)(sAMAccountName=C*)(sAMAccountName=D*)) );sAMAccountName,givenName, sn, mail,userAccountControl;subtree')
UNION ALL
SELECT  sAMAccountName,givenName, sn, userAccountControl,mail FROM OpenQuery(ADSI, '<LDAP://YourDomain.com:389>;(&(objectClass=User)(|(sAMAccountName=E*)(sAMAccountName=F*)(sAMAccountName=G*)(sAMAccountName=H*)) );sAMAccountName,givenName, sn, mail,userAccountControl;subtree')
UNION ALL 
SELECT  sAMAccountName,givenName, sn, userAccountControl,mail FROM OpenQuery(ADSI, '<LDAP://YourDomain.com:389>;(&(objectClass=User)(|(sAMAccountName=I*)(sAMAccountName=J*)(sAMAccountName=K*)(sAMAccountName=L*)) );sAMAccountName,givenName, sn, mail,userAccountControl;subtree')
UNION ALL
SELECT  sAMAccountName,givenName, sn, userAccountControl,mail FROM OpenQuery(ADSI, '<LDAP://YourDomain.com:389>;(&(objectClass=User)(|(sAMAccountName=M*)(sAMAccountName=N*)(sAMAccountName=O*)(sAMAccountName=P*)) );sAMAccountName,givenName, sn, mail,userAccountControl;subtree')
UNION ALL 
SELECT  sAMAccountName,givenName, sn, userAccountControl,mail FROM OpenQuery(ADSI, '<LDAP://YourDomain.com:389>;(&(objectClass=User)(|(sAMAccountName=Q*)(sAMAccountName=R*)(sAMAccountName=S*)(sAMAccountName=T*)) );sAMAccountName,givenName, sn, mail,userAccountControl;subtree')
UNION ALL
SELECT  sAMAccountName,givenName, sn, userAccountControl,mail FROM OpenQuery(ADSI, '<LDAP://YourDomain.com:389>;(&(objectClass=User)(|(sAMAccountName=U*)(sAMAccountName=V*)(sAMAccountName=W*)(sAMAccountName=X*)) );sAMAccountName,givenName, sn, mail,userAccountControl;subtree')
UNION ALL 
SELECT  sAMAccountName,givenName, sn, userAccountControl,mail FROM OpenQuery(ADSI, '<LDAP://YourDomain.com:389>;(&(objectClass=User)(|(sAMAccountName=Y*)(sAMAccountName=Z*)) );sAMAccountName,givenName, sn, mail,userAccountControl;subtree')

RETURN
END
GO

#1