1

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 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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
wruckie
  • 1,717
  • 1
  • 23
  • 34
  • 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 Mar 02 '17 at 01:16
  • @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 Mar 02 '17 at 13:35
  • 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 Mar 02 '17 at 04:04
  • 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 Mar 02 '17 at 13:54
  • @BilalAyub How do you know what class a custom attribute is in taking into account the instructions I used to create it? – wruckie Mar 02 '17 at 20:49

1 Answers1

1

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.

James Truxon
  • 46
  • 5
  • apologies -- i forgot to include the punchline: I believe the steps to try out are: 1.) restart SQL server, retry the query, and 2.) test the query from an instance of SQL server that cannot possibly have the Active Directory column information cached. – James Truxon Jun 14 '17 at 16:33
  • While I cannot guarantee 100% that stopping and restarting services, etc will do the trick, I can say that my problem was most likely caching because the code that failed then is working now. It looked like time fixed my caching issue. – wruckie Jun 21 '17 at 23:35
  • followup -- I saw this error after restarting our production server. Researching ADSI schema caching online, I found [this link](https://blogs.technet.microsoft.com/fieldcoding/2014/11/12/the-adsi-schema-cache-revealed/), which gave me the pointer to run a "`dir /s *.sch`" on the commandline, which returned a few cache files of the form **[domain]**.sch. Running "`del /s [domain].sch`" killed the cache, I restarted SQL server (not sure if this was necessary), and the cache was automatically recreated from the server on the next query that used ADSI. – James Truxon Jul 03 '17 at 16:15
  • the link that @James Truxon gave is now dead. I stopped and restarted the SQL services via the SQL Server Configuration Manager and my query now works. – wruckie Apr 10 '20 at 04:10