--	Treat Active Directory as a linked server and then you can query against it.
--	This work is applied against Master as it effects the entire instance.
--
USE [master]
GO
--
--	Clean up if the linked server already exists.
-- ==============================================================================
--		Replace N’AdvWrksDC’ with the name of your Domain Controller
--		throughout this script
-- ==============================================================================
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0
	AND srv.name = N'AdvWrksDC') EXEC master.dbo.sp_dropserver
		@server=N'AdvWrksDC', @droplogins='droplogins'
GO
--
--	'show advanced options' lets us get at the 'Ad Hoc Distributed Queries' option
--
SP_CONFIGURE 'show advanced options',1
GO
reconfigure with override
GO
--
--	'Ad Hoc Distributed Queries' option - By default, SQL Server does not allow
--	ad hoc distributed queries using OPENROWSET and OPENDATASOURCE. When this is
--	set to 1, SQL Server allows ad hoc access.
--
SP_CONFIGURE 'Ad Hoc Distributed Queries',1
GO
RECONFIGURE WITH OVERRIDE
GO
--
--	Use the sp_addlinkedserver stored procedure, @server is the name of your
--	domain controller
--
EXEC master.dbo.sp_addlinkedserver @server = N'AdvWrksDC', @srvproduct=
	N'Active Directory Service Interfaces’, @provider=N'ADSDSOObject',
	@datasrc=N'adsdatasource'
--
-- ==============================================================================
--		You need a domain login which has access to Active Directory
--		information @rmtuser and @rmtpassword identify this user
-- ==============================================================================
--
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AdvWrksDC', @useself=N'False',
	@locallogin=NULL, @rmtuser=N'adventure-works\Administrator',
	@rmtpassword='******'
GO
--	Treat Active Directory as a linked server and then you can query against it.
--	This work is applied against Master as it effects the entire instance.
--
USE [master]
GO
--
--	Clean up if the linked server already exists.
-- ==============================================================================
--		Replace N’AdvWrksDC’ with the name of your Domain Controller
--		throughout this script
-- ==============================================================================
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0
	AND srv.name = N'AdvWrksDC') EXEC master.dbo.sp_dropserver
		@server=N'AdvWrksDC', @droplogins='droplogins'
GO
--
--	'show advanced options' lets us get at the 'Ad Hoc Distributed Queries' option
--
SP_CONFIGURE 'show advanced options',1
GO
reconfigure with override
GO
--
--	'Ad Hoc Distributed Queries' option - By default, SQL Server does not allow
--	ad hoc distributed queries using OPENROWSET and OPENDATASOURCE. When this is
--	set to 1, SQL Server allows ad hoc access.
--
SP_CONFIGURE 'Ad Hoc Distributed Queries',1
GO
RECONFIGURE WITH OVERRIDE
GO
--
--	Use the sp_addlinkedserver stored procedure, @server is the name of your
--	domain controller
--
EXEC master.dbo.sp_addlinkedserver @server = N'AdvWrksDC', @srvproduct=
	N'Active Directory Service Interfaces’, @provider=N'ADSDSOObject',
	@datasrc=N'adsdatasource'
--
-- ==============================================================================
--		You need a domain login which has access to Active Directory
--		information @rmtuser and @rmtpassword identify this user
-- ==============================================================================
--
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AdvWrksDC', @useself=N'False',
	@locallogin=NULL, @rmtuser=N'adventure-works\Administrator',
	@rmtpassword='******'
GO
--
--	'collation compatible' false indicates that AD might not have the collation
--	as SQLServer Instance
--
EXEC master.dbo.sp_serveroption @server=N'AdvWrksDC',
	@optname=N'collation compatible’, @optvalue=N'false'
GO
--
--	'data access' true Enables a linked server for distributed query access
EXEC master.dbo.sp_serveroption @server=N'AdvWrksDC', @optname=N'data access',
	@optvalue=N'true'
GO
--
--	'dist' false indicates this linked server is not a Distributor
EXEC master.dbo.sp_serveroption @server=N'AdvWrksDC', @optname=N'dist',
	@optvalue=N'false'
GO
--
--	'pub' false indicates this linked server is not a Publisher.
EXEC master.dbo.sp_serveroption @server=N'AdvWrksDC', @optname=N'pub',
	@optvalue=N'false'
GO
--
--	'rpc' false disables RPC from the given server.
EXEC master.dbo.sp_serveroption @server=N'AdvWrksDC', @optname=N'rpc',
	@optvalue=N'false'
GO
--
--	'rpc out' false disables RPC to the given server.
EXEC master.dbo.sp_serveroption @server=N'AdvWrksDC', @optname=N'rpc out',
	@optvalue=N'false'
GO
--
--	'sub' false indicates that this linked server is not a Subscriber
EXEC master.dbo.sp_serveroption @server=N'AdvWrksDC', @optname=N'sub',
	@optvalue=N'false'
GO
--
--	'connect timeout' = 0 indicates to use the default for a connection timeout
EXEC master.dbo.sp_serveroption @server=N'AdvWrksDC', @optname=N'connect timeout',
	@optvalue=N'0'
GO
--
--	'collation name' null Specifies the name of the collation used by the remote
--	data source if use remote collation is true and the data source is not a SQL
--	Server data source. The name must be one of the collations supported by SQL
--	Server.
EXEC master.dbo.sp_serveroption @server=N'AdvWrksDC', @optname=N'collation name',
	@optvalue=null
GO
--
--	'lazy schema validation' false Determines whether the schema of remote tables
-- 	will be checked.
EXEC master.dbo.sp_serveroption @server=N'AdvWrksDC',
	@optname=N'lazy schema validation’, @optvalue=N'false'
GO
--
--	'query timeout' = 0 indicates to use the default for a queryeout
EXEC master.dbo.sp_serveroption @server=N'AdvWrksDC', @optname=N'query timeout',
	@optvalue=N'0'
GO
--
--	'use remote collation' true indicates the collation of the remove server will
--	be used
EXEC master.dbo.sp_serveroption @server=N'AdvWrksDC', @optname=N'use remote collation’, @optvalue=N'true'
GO
--
--	'remote proc transaction promotion' true Use this option to protect the actions
--	of a server-to-server procedure through a Microsoft Distributed Transaction
--	Coordinator (MS DTC) transaction. When this option is TRUE (or ON) calling a
--	remote stored procedure starts a distributed transaction and enlists the
--	transaction with MS DTC.
EXEC master.dbo.sp_serveroption @server=N'AdvWrksDC',
	@optname=N'remote proc transaction promotion', @optvalue=N'true'
GO