T-SQL ile Active Directorydeki kullanıcı bilgilerini sorgulama

Active Directorydeki (aktif dizin = AD) kullanıcı bilgilerini sorgulama

lokal portal yapılarında belli kullanıcı bilgilerini AD ye yazmak ve portale giriş yapan kullanıcının

AD kullanıcı adından diğer bilgilerine erişmek oldukça pratik bir yoldur.

bunun için active directorye .net kodları ile erişebiliriz ancak olayı sql tarafından yaparak

sql tarafındaki  prosedürlerimizdende kullanabilmek istersek bunun için linked server ve openquery yapılarını

kullanacağız..

-- öncelikle AD sorgusu yapabilecek şekilde bir linked server ekliyoruz...

sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

-- sonra AD ldap sorgusu yapmaya yetkili bir kullanıcı adı ve şifre girirek login ekliyoruz

EXEC sp_addlinkedsrvlogin @rmtsrvname = N'ADSI', @locallogin = NULL , @useself = N'False',@rmtuser = N'domain\user1', @rmtpassword = N'şifre'

--- daha sonra openQuery  kullanabilmek varsayılan olarak kapalı olduğu için bunu açmak için

-- aşağıdaki gibi konfigurasyonda bazı değişiklikler yapıyoruz...

sp_configure 'show advanced options', 1

reconfigure with override

sp_configure 'Ad Hoc Distributed Queries', 1

reconfigure

varsayalım ki  inovasyon.org  diye bir domain  adımız var AD kısmında..

aşağıdaki gibi bir sorgu ile AD den belli bilgileri çekebiliriz..

SELECT * FROM OPENQUERY (ADSI,

'SELECT SAMAccountname,name,title,mail,userAccountControl

,telephonenumber,company,department FROM ''LDAP://DC=inovasyon,DC=org''

WHERE objectCategory = ''Person'' AND objectClass = ''user'' AND ( userAccountControl = 512)  ')

where  1=1

--( userAccountControl = 512  or userAccountControl = 66048)

 --and  company is not null  and telephonenumber is null

--  NOT (company is null and telephonenumber is null)

-- ( userAccountControl = 512  or userAccountControl = 66048) and  company is not null and telephonenumber is not null

--and name like '%ÜNAL%'

--and charindex(' ',name)>0

GO

buradaki  userAccountControl  değeri 512 ise kullanıcı enable=aktif oluyor. disable edilmişleri getirmemek için kullanılabilen bir filtre.

yukarıdaki sorguda bir çok filtreleme ile istediğimiz kullanıcı bazındada bilgileri filtreleyip getirebildiğimiz görülmekte...

bu select cümlesini eğer bir view içine koyarsak  artık daha pratik bir şekilde sorgularımızı yapabiliriz...

create view vwADsorgula

as

 SELECT * FROM OPENQUERY (ADSI,

'SELECT SAMAccountname,name,title,mail,userAccountControl

,telephonenumber,company,department FROM ''LDAP://DC=inovasyon,DC=org''

WHERE objectCategory = ''Person'' AND objectClass = ''user'' AND ( userAccountControl = 512  OR userAccountControl = 66048)  ')

where  1=1

GO

kendi uygulamanızda tutacağınız kullanıcı tablosunu belli aralıklarla çalışacak bir job (görev) ile AD den kolayca güncelletebilirsiniz artık.

evet önce bir stored procedure yazalım sonrada bunu bir job ile tarihlendirebiliriz...

aşağıdaki sp  kendi kullanıcı tablomuzda olmayan kullanıcları (ADye yeni eklenenleri yada username-i değişenleri) ekler

olanlarında bilgilerini günceller...

create proc  [dbo].[spkullaniciguncelle]

 as

 begin

 

 ---olmayanı ekle

 insert into tblPersonel

 (username,unvan,isim,birim,email,sirket)

 select SAMAccountname,title,name,department,mail,company

  from Genel.dbo.vwADSorgu t1

  where userAccountControl=512 and company is not null and mail is not null

  and not exists(select username from  tblPersonel  where username=t1.SamAccountname)

 

 

  --olanı güncelle

   update tblPersonel

    set username=SAMAccountname

    ,unvan=title

    ,isim=name

    ,birim=department

    ,email=mail

    ,sirket=company

    from vwADSorgu t1

    where tblPersonel.username=t1.Samaccountname

  

 end