Gorgo.Live.ToString()

Mariusz, Gorzoch tech Blog

Archive for July 2010

SQL and Active Directory

leave a comment »

Lately I was asked by one of my customer to build a solution for him with will take user information from his SQL database and for each of them, check if user has a login in Active Directory and if he do then store this information in database. On the beginning I was thinking to build a console app for that but on the end I found much easer way with use of pure SQL 🙂

To achieve that you just need to add special linked server and then open queries to it with use of LDAP protocol. Bellow you can find some example how this can done:

Creating linked server.

To create linked server, simply start management studio and past this string :

EXEC sp_addlinkedserver ‘ADSI’, ‘Active Directory Services 2.5’, ‘ADSDSOObject’, ‘adsdatasource’

This will create a linked server called ‘ADSI’. When it is created go to the setting of it and make sure that security tab contain proper information. Security here is quite important as it define credentials with SQL will use to connect to DC. If chosen user do not have proper right, then we can get quite cryptic error message.

Create query

when you set-up linked server then you are ready to start building your queries. Important think to remember here is to know that if you run some queries against LDAP then you result will be always limited to 1000 rows. This is set-up like that by default and of course you can try to change that but believe this is not trivial (just google for that and you will see). Anyway the simplest LDAP query looks like this:

SELECT samAccountName, cn
    FROM OPENQUERY (ADSI, ‘SELECT cn, mail, department, title, samAccountName FROM ”LDAP://DC=europe,DC=company,DC=net” WHERE objectCategory = ”Person” AND objectClass = ”user” ‘)

This will return max 1000 users from your AD. When you write you query please pay attention to “LDAP….xxxx” string. This string need to be surround by double ‘ (not “). If you want to do you will get syntax error without any additional information.

If this is working then we can go for more advance example, which actually is updating database with data from AD. Here is the SP I’ve built for that:

set nocount on
declare @IdPrac int
declare @Employee varchar(200)
declare hr_cursor cursor for
select idPrac, replace(LastName,”,’ ‘)+’, ‘+FirstName as Employee from tbEmployee
where
(
    (dom_log is null or dom_log=”)
    and txStatus in (select nrStatus from tbPracownicyStatus where isInactive=0)
)
open hr_cursor

if (object_id(‘tempdb..#adTab’) is not null)
begin
    drop table #adTab
end

create table #adTab (
    login varchar(100),
    name varchar(500)
)

declare @query varchar(8000)
declare @login varchar(20)

Fetch next from hr_cursor into @IdPrac, @Employee
while(@@FETCH_STATUS=0)
begin

    set @query = ‘SELECT samAccountName, cn
    FROM OPENQUERY (ADSI, ”SELECT cn, mail, department, title, samAccountName FROM ””LDAP://DC=europe,DC=company,DC=net”” WHERE objectCategory = ””Person””
        AND objectClass = ””user”” AND displayName = ””’+ @Employee + ””’ ”)’

    insert into #adTab(login,name)

    exec(@query)

    set @login= null
    select @login = login from #adTab
    if (@login<>” and not @login is null)
    begin

        print ‘Updating employee "’+@Employee+’" login to "’+@login+’"’
        update tbEmployee set dom_log = @login where idPrac=@IdPrac

    end

    truncate table #adTab

fetch NEXT from hr_cursor into @IdPrac, @Pracownik
end

close hr_cursor
deallocate hr_cursor

drop table #adTab

This function let you get user by user from HR database, check if user exist in Active Directory and if he exist then update his login with data from AD.

have fun

Advertisements

Written by Mariusz Gorzoch

27 July 2010 at 22:20

Posted in Bez kategorii