Sql-server – Querying AD from SQL Server

active-directorysql-server-2012

I've read tons of questions and answers about my error but none of them helped me ( from PINAL website, microsoft links and everything ). questions from 9 years ago to today.

I created a Linked server with proper user/pass to read info from our AD.

but it doesn't matter what user I use ( we tried everything, even with the most powerfull user we have in the network that can make it rain if it wants ) and I still get the same error:

Msg 7399, Level 16, State 1, Line 16
The OLE DB provider "ADsDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation.

i tried to run SSMS with the user with no success. I set the secutiry tab to run with the specific user that can read the AD.

these are the queries i'm trying :
SELECT top 100 * FROM OpenQuery (
ADSI, 'SELECT displayname FROM 
''LDAP://myDomain.local/OU=Usuarios'''
) 


SELECT TOP 100 * FROM OPENQUERY
(ADSI,'SELECT displayname FROM 
''LDAP://myDomain.local/OU=USUARIOS,DC=MyDomain,DC=LOCAL'''
)

SELECT * FROM OpenQuery (
ADSI,
'SELECT *
FROM ''LDAP://myDomain.local/DC=MyDomain,DC=local''
WHERE objectClass = ''User''
')

I opened management studio with this powerfull user and it still doesnt work. what can be the error here?

I'm querying the AD server remotely from my machine from SSMS.

Best Answer

You might be better off and find it easier to query Active Directory with a CLR stored procedure or CLR function. See this walkthrough on how to do so, as this is how I've done it in the past. Also someone else made one already you can look into (but be careful installing CLR assemblies from outside sources).