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]
GO
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'
GO

USE OPE
GO

CREATE  VIEW ADView
AS
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'' ))  ')
GO

USE OPE
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
Advertisements

, , ,

  1. #1 by Sumesh on May 4, 2012 - 8:33 PM

    Kollalo bhai

  2. #2 by GET MORE INFORMATION on May 17, 2013 - 12:34 PM

    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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: