Active Directory to MSSQL

  1. Create a database (here it is OPE), with required columns.
  2.  Open a ‘New Query’  window in  Microsoft SQL Server Management Studio Express and put the below SQL Code there.
  3. Find and Replace(Ctrl+H) all occurance of DC=epillars and DC=local with the appropriate values from the Active Directory.
  4. Execute(F5) the Query.
  5. The Query will create a Linked Server ADSI(Server Objects->Linked Servers->ADSI) and a view  ADView(Databases->OPE->Views->dbo.ADView) which contains users on active directory. Then the Users table in OPE database will be updated with the active directory users details.
USE [master]
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'


SELECT     CAST(LOWER(SUBSTRING(master.dbo.fn_varbintohexstr(objectGUID), 3, 8) + '-' + SUBSTRING(master.dbo.fn_varbintohexstr(objectGUID), 11, 4)
                      + '-' + SUBSTRING(master.dbo.fn_varbintohexstr(objectGUID), 15, 4) + '-' + SUBSTRING(master.dbo.fn_varbintohexstr(objectGUID), 16, 4)
                      + + '-' + RIGHT(master.dbo.fn_varbintohexstr(objectGUID), 12)) AS uniqueidentifier) AS GUID, sAMAccountName AS Name, NULL AS Password,
                      cn AS FullName, userPrincipalName AS Email, CAST ((SELECT GUID FROM Roles WHERE (Name = 'default')) AS uniqueidentifier) AS RoleGUID, 'True' AS IsActive, 'False' AS IsAdmin, '1' AS UserType, 'False' AS ResetPwd,
                      '' AS OrderNo, GETDATE() AS PasswordCreateTime
FROM         OPENQUERY(ADSI,'SELECT  userPrincipalName ,cn, sAMAccountName , objectGUID FROM ''LDAP://DC=epillars,DC=local'' WHERE objectCategory=''user''AND NOT((MemberOf=''CN=Administrators,CN=Builtin,DC=epillars,DC=local'' OR MemberOf=''CN=Guests,CN=Builtin,DC=epillars,DC=local'' OR MemberOf=''CN=IIS_WPG,CN=Users,DC=epillars,DC=local'' ))  ')

insert into Users (GUID,Name,FullName,Email,RoleGUID,IsActive,IsAdmin,UserType,ResetPwd,PasswordCreateTime) select a.GUID,a.Name,a.FullName,a.Email,a.RoleGUID,a.IsActive,a.IsAdmin,a.UserType,a.ResetPwd,a.PasswordCreateTime from ADView a  where not exists (select 0 from Users b where b.Name=a.Name)select * from Users

Author: Shyju Kanaprath

In our daily life we learn lots of new things. As the time goes we forgets those and we may reach a situation where "Things You Probably Already Know, But Have Forgotten". I’m Shyju Kanaprath, the blogger of "Tech.. Logs.." and I blog here to avoid those kind of situations, at least for myself :) . This blog keep me in track on such times..

2 thoughts on “Active Directory to MSSQL”

  1. I rarely leave a response, but i did some searching and wound up here Active Directory to MSSQL | Tech.
    . Logs… And I actually do have some questions for you if
    you do not mind. Could it be only me or does it look like a few of the comments look like coming from
    brain dead people? 😛 And, if you are posting on other online sites,
    I would like to follow anything fresh you have to post. Could you make a
    list of every one of all your community pages like your twitter
    feed, Facebook page or linkedin profile?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s