CODEWITHSUNDEEP
c#sql-serveropenqueryadsi
wruckie
wruckie

Reputation: 1803

ADSI - Query Custom User Attribute through SQL Server Is Failing

I created 4 custom attributes in my Active Directory. I followed the instructions as found here - https://social.technet.microsoft.com/wiki/contents/articles/20319.how-to-create-a-custom-attribute-in-active-directory.aspx

I can see the attributes in Active Directory Schema MMC (Figure 1), AD Users and Computer's properties (Figure 2) and can select and update these 4 attributes with C# and the PrincipalContext and UserPrincipal (extended) classes.

I have been using this ADSI query for months... 

SELECT * 
FROM OpenQuery (ADSI,  
                'SELECT SAMAccountName, pwdLastSet, lockoutTime, employeeID, displayName, givenname, sn, 
                        scriptpath, distinguishedName, telephoneNumber, mail, mobile,        
                        facsimileTelephoneNumber ,LastLogon, badPasswordTime, accountExpires, 
                        userAccountControl, manager
                 FROM ''LDAP://example.com/DC=example,DC=com'' 
                 WHERE objectClass =  ''User'' 
                   AND ''userAccountControl:1.2.840.113556.1.4.803:''<> 2') AS tblADSI_CBS
 WHERE 
     samaccountname not like '%$'

But as soon as i add fBCArenaID to the query, I get this error message

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "ADSDSOObject" for linked server "ADSI" reported an error. The provider did not give any information about the error.

Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "ADSDSOObject" for linked server "ADSI".

Questions:

Figure 1: my custom attributes showing is ADS MMC:

Figure 1 - My Custom Attributes showing is ADS MMC

Figure 2: my custom attributes showing in AD Users and Computers:

Figure 2 - My Custom Attributes showing in AD Users and Computers

Upvotes: 1

Views: 2965

Answers (1)

James Truxon
James Truxon

Reputation: 46

I encountered an identical issue -- added a custom field ('PreferredFirstName', in my case) to AD, attempted to query it through ADSI, encountered error 7350. I ran across this discussion, which made mention of schema caching in sql server, so I spun up a new SQL instance, added the ADSI linked server to that fresh instance, and the query worked immediately / returned my custom attribute just fine.

Upvotes: 1

Related Questions