Querying Active Directory with T-SQL

Question

Have tried:

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

EXEC master.dbo.sp_addlinkedsrvlogin 
        @rmtsrvname=N'ADSI',
        @useself=N'False',
        @locallogin=NULL,
        @rmtuser=N'your_domain\domain_user',
        @rmtpassword='********'

SELECT * 
FROM OPENQUERY (ADSI, 'SELECT *
                       FROM ''LDAP://DC=your_domain,DC=com''')

Getting this error:

Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT * FROM 'LDAP://DC=your_domain,DC=com'" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".

I have already confirmed mine and the SQL Server Service domain accounts have read access to AD, and that the "Allow inprocess" is enabled on the ADsDSOObject provider is selected.

Any thoughts would be appreciated.

If you navigate to your linked server is SSMS you can right click and test connection. Does that work? — David Rushton
I think the issue is SELECT * returns data that is not compatible with SQL Server. Trying limited the SELECT clause in the OPENQUERY to just the fields you are using. There are some handy tips here. — David Rushton

C-COOP C-COOP · Accepted Answer · 2016-12-08T17:16:11

Every example I could find had: LDAP://DC=your_domain,DC=com as syntax. Well for our server (and maybe other's) it is: LDAP://DC=your_domain,DC=internal

As suggested by someone, I used the Softerra LDAP browser (free) and opened the server, clicked on the top node and found distinguished name entry listed as: DC=your_domain,DC=internal

Once I made that change, I could see the AD data.

Querying Active Directory with T-SQL

1 Answers