Query AD from sql server 2008 Description field gives error

active-directoryadsildapsql-server-2008

I am trying to query the Active Directory with the following SQL query, which works perfectly.

SELECT * FROM OpenQuery(ADSI, 
                         'SELECT title, displayName, sAMAccountName, givenName, 
                                 telephoneNumber, facsimileTelephoneNumber, sn,  
                                 mail, physicalDeliveryOfficeName
                          FROM ''LDAP://DC=mydomain,DC=org'' 
                          WHERE sAMAccountName = ''myUser''')

When I add the description attribute I get the following error:

Msg 7346, Level 16, State 2, Line 6
Cannot get the data of the row
from the OLE DB provider "ADSDSOObject" for linked server "ADSI".
Could not convert the data value due to reasons other than sign
mismatch or overflow.

Best Answer

The SQL/ADO query capabilities into Active Directory are very limited - I would try to avoid using them if ever possible.

The reason here is that the description attribute in Active Directory is multi-valued - it could potentially contain multiple values (something that's impossible in a relational database model).

Therefore, the SQL/ADO query provider cannot read any of those multi-valued attributes from LDAP - there's no way or option or checkbox to enable that - it's just not possible.

Related Solutions

Sql-server – How to check if a column exists in a SQL Server table

SQL Server 2005 onwards:

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'columnName'
          AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
    -- Column Exists
END

Martin Smith's version is shorter:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    -- Column Exists
END

Query AD from sql server 2005

The main problem is that the double quotes around the LDAP query need to be doubled single quotes.

The LDAP query can include as server name or IP and/or a LDAP specification.

Some queries that work for me:

SELECT * FROM OPENQUERY
(ADSI,'SELECT NAME FROM ''LDAP://*ldap.server.name*''')

SELECT * FROM OPENQUERY
(ADSI, 'SELECT name, sAMAccountName, distinguishedName 
FROM ''LDAP://DC=*mycompany*, DC=*mytld*''
WHERE objectCategory = ''Person'' AND objectClass = ''user''')

SELECT * FROM OPENQUERY
(ADSI,'SELECT name, sAMAccountName, distinguishedName
FROM ''LDAP://*ldap.server.name*/OU=ITDept, OU=users, OU=DC, OU=Corporate, DC=*mycompany*, DC=*mytld*''
WHERE objectCategory = ''Person'' AND objectClass = ''user''')

Once again... there are no double quotes in the above... multiple single quotes.

ADSI needs to be registered/linked with

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

if you have permissions issues you can set the account used under the security property tab on the ADSI linked server.

Best Answer

Related Solutions

Sql-server – How to check if a column exists in a SQL Server table

Query AD from sql server 2005

Related Topic