Recently, I am creating intranet portal for a company that I am working for. Active directory has a lot of build-in fields for users like first name, last name, email, phone. But it still not enough for a bussiness needs. So, I had to create additional table for extended information in ms sql server database. Now how do you connect extended information and information from active directory?
Active directory has objectGuid field. You can use this field to connect your table and active directory information. .NET has two ways to access active directory data using old ODBC method or System.DirectoryServices namespace. However manual connection of table from sql server and active directory information is too much of work. It is much more easier to use linked server feature of MS SQL Server.
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'
And then you can query linked server like this:
SELECT * FROM OpenQuery(ADSI, 'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://DC=whaever,DC=domain,DC=org'' where objectClass = ''User''')
You might see following error:
The OLE DB provider "ADSDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation.
Then you need to go to properties of newly created linked server and adjust security settings (Server Objects > Linked Servers > ADSI).