How to add active directory linked server

 How to add Active Directory-linked server

This is an example of how to set up a linked server to Active Directory.

USE [master]
GO

/****** Object:  LinkedServer [ADSI] ******/
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI'
 ,@srvproduct = N'Active Directory Services 2.5'
 ,@provider = N'ADSDSOObject'
 ,@datasrc = N'adsdatasource'
GO

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'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'remote proc transaction promotion'
 ,@optvalue = N'true'
GO

Example query to retrieve user details from Active Directory.

SELECT *
FROM openquery(adsi, '
select employeeID
 ,sAMAccountName
 ,sAMAccountType
 ,distinguishedName
 ,displayName
 ,cn
 ,givenName
 ,middlename
 ,initials
 ,title
 ,department
 ,mail
 ,streetAddress
 ,c
 ,co
 ,st
 ,l
 ,postalCode
 ,homePhone
 ,mobile
 ,telephoneNumber
 ,company
 ,manager
 ,info
 ,homeDrive
 ,homeDirectory
 ,whenChanged
 ,whenCreated
 ,wWWHomePage
 ,internationalISDNNumber
 ,accountExpires
 ,comment
 ,assistant
 ,countrycode
 ,lastlogon
 ,legacyExchangeDN
 ,userPrincipalName
 ,logoncount
 ,physicalDeliveryOfficeName
 ,ou
 ,primaryGroupID
 ,scriptPath
 ,textEncodedORAddress
 ,facsimileTelephoneNumber
 ,modifyTimeStamp
 ,objectCategory
 ,uSNChanged
 ,uSNCreated
from ''LDAP://ad.MyAdServer.com''
where objectCategory = ''Person''
      and objectClass = ''user''
') AS tab