March 9, 2015 at 4:44 pm
I am trying to setup querying Active directory from sql for the first time.
We are running on windows server 2012 and using sql 11.0.2100.60. Have tried the following
sql is on sever dev
AD is on sever DO
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
'ADSDSOObject', 'adsdatasource'
GO
EXEC sp_addlinkedsrvlogin 'ADSI', 'false', NULL, 'flench', '******' ---- sql authentication
SELECT *
FROM OPENQUERY( ADSI,
'SELECT name
FROM ''LDAP:// xxxxx.internal''
WHERE objectCategory=''Person'' AND
objectClass = ''contact''')
I get the following error when I try and query
Msg 7321, Level 16, State 2, Line 2
An error occurred while preparing the query "SELECT name
FROM 'LDAP:// xxxx.internal'
WHERE objectCategory='Person' AND
objectClass = 'contact'" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
Any help would be much appreciated
March 9, 2015 at 5:23 pm
There are a couple of 'gotchas' that I found with this process. There is a limit to how many rows the query will return and if the result set exceeds that number, you can get unhelpful messages. Add some criteria to the 'WHERE' to get the result set down to under 1000 rows and see if that makes a difference. If it does, you may have to execute the query multiple times with different criteria to get all the data you want.
Also, in AD, some things are kept in multi-value columns (like group membership, if I recall correctly) and those can't be extracted with this kind of query.
Having said that, it's been a while since i've done this, so the provider may be more capable now and your solution may lie elsewhere.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply