ADSI - Query Custom User Attribute through SQL Server Is Failing

Question

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:

Did I create my custom attributes incorrectly?
Did I make them inaccessible by not giving them the right X.500 OID?
Is the OpenQuery choking on them because they are integers and not strings?

Figure 1: my custom attributes showing is ADS MMC:

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

How long from when you created the custom elements until you queried for them? Is this a propagation thing? Do you need to run repadmin cmd on a specific server and possibly a gpupdate on the sql server? Are these the first custom elements you have queried for using that query? — Sql Surfer
If you have created these customer attributes in "User" class than it should be accessible with ADSI linked server, otherwise applying server patch will be helpful. — Bilal Ayub
@SqlSurfer I was trying it within the hour, but only after I was able to alter it in all those other ways. Just for giggles, I ran it again but it still failed. — wruckie
Yeah to some extend it was comment though it gives you an idea where to look for answer, your query explicitly look for user class so you need to make sure attributes are in that class. As it is environment specific question so mostly I can give you comment which will lead you to solution. — Bilal Ayub
@BilalAyub How do you know what class a custom attribute is in taking into account the instructions I used to create it? — wruckie

James Truxon James Truxon · Accepted Answer · 2017-06-14T16:31:47

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.

ADSI - Query Custom User Attribute through SQL Server Is Failing

1 Answers