SQL Server 2005 – Get LDAP (Lightweight Directory Access Protocol) Informtion

Steps to access LDAP information in SQL server. 

1. SQL Commands for Creating linked Server  

EXEC master.dbo.sp_addlinkedserver @server = N’ADSI’,

@srvproduct=N’Directory Services’,

@provider=N’ADsDSOObject’

EXEC master.dbo.sp_addlinkedsrvlogin  @rmtsrvname=N’ADSI’,

@useself=N’False’,

@locallogin=NULL,

@rmtuser=N’ Domain\UserName’,

@rmtpassword=’########’

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_serveroption @server=N’ADSI’,

@optname=N’collation compatible’,

@optvalue=N’false’

GO

EXEC master.dbo.sp_serveroption @server=N’ADSI’,  @optname=N’data access’, @optvalue=N’true’

GO

EXEC master.dbo.sp_serveroption @server=N’ADSI’, @optname=N’dist’, @optvalue=N’false’

GO

EXEC master.dbo.sp_serveroption @server=N’ADSI’, @optname=N’pub’, @optvalue=N’false’

GO

EXEC master.dbo.sp_serveroption @server=N’ADSI’, @optname=N’rpc’, @optvalue=N’false’

GO

EXEC master.dbo.sp_serveroption @server=N’ADSI’, @optname=N’rpc out’, @optvalue=N’false’

GO

EXEC master.dbo.sp_serveroption @server=N’ADSI’, @optname=N’sub’, @optvalue=N’false’

GO

EXEC master.dbo.sp_serveroption @server=N’ADSI’, @optname=N’connect timeout’, @optvalue=N’0′

GO

EXEC master.dbo.sp_serveroption @server=N’ADSI’, @optname=N’collation name’, @optvalue=null

GO

EXEC master.dbo.sp_serveroption @server=N’ADSI’, @optname=N’lazy schema validation’, @optvalue=N’false’

GO

EXEC master.dbo.sp_serveroption @server=N’ADSI’, @optname=N’query timeout’, @optvalue=N’0′

GO

EXEC master.dbo.sp_serveroption @server=N’ADSI’, @optname=N’use remote collation’, @optvalue=N’true’

2. SQL for Accessing LDAP information

SELECT *

FROM OPENQUERY(ADSI,

‘SELECT CN,displayname,mail,userPrincipalName

FROM ”LDAP://DC=synapse,DC=com”

WHERE objectCategory = ”Person”

and mail=”abc.xyz@globallogic.com”’)

 

Regards

Bhagwan Singh Jatav

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

One Comment on “SQL Server 2005 – Get LDAP (Lightweight Directory Access Protocol) Informtion”

Leave a Reply

Your email address will not be published. Required fields are marked *