pdanes (8/22/2013)
opc.three (8/21/2013)
... consider a Linked Server configured to connect to and issue queries against AD. Getting a user's group memberships, or a group's members list, is trivial using a Linked Server.Despite my deciding to deal with this situation in a different way, the Linked Server approach sounds interesting. As I mentioned in my first response, I did try this method, and created a Linked Server connection to our local domain controller mirror. However, all my attempts to execute queries against this connection came up dry. Do you know what privilege or such I would need to make this work?
It can be a regular Domain User, but for some reason I have never had luck getting SQL Server to pass along the credentials of the logged in user so I have to explicitly set the creds it uses. Here is the boilerplate script I use to setup an AD Linked Server and a basic test query. In the script change the domain creds and in the query set your DC path, e.g. if you domain was level4.level3.level2.level1 the FROM in the test query would be correct if you were looking for members of group GroupName stored in OU OUName
USE DB
GO
IF EXISTS ( SELECT srv.name
FROM sys.servers srv
WHERE srv.server_id != 0
AND srv.name = N'ADSI' )
EXEC master.dbo.sp_dropserver
@server = N'ADSI',
@droplogins = 'droplogins'
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'ADSI',
@provider = N'ADSDSOObject',
@srvproduct = N'ADSDSOObject'
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'ADSI',
@useself = N'False',
@locallogin = NULL,
-- THIS IS WHERE YOU PUT IN THE DOMAIN USER CREDS THAT WILL AUTH TO AD
@rmtuser = N'DOMAIN\USER',
@rmtpassword = N'PASSWORD'
-- THIS IS WHERE YOU PUT IN THE DOMAIN USER CREDS THAT WILL AUTH TO AD
;
GO
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'collation compatible',
@optvalue = N'false'
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'data access',
@optvalue = N'true'
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'dist',
@optvalue = N'false'
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'pub',
@optvalue = N'false'
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'rpc',
@optvalue = N'true'
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'rpc out',
@optvalue = N'true'
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'sub',
@optvalue = N'false'
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'connect timeout',
@optvalue = N'0'
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'collation name',
@optvalue = NULL
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'lazy schema validation',
@optvalue = N'false'
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'query timeout',
@optvalue = N'0'
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'use remote collation',
@optvalue = N'true'
EXEC master.dbo.sp_serveroption
@server = N'ADSI',
@optname = N'remote proc transaction promotion',
@optvalue = N'true'
GO
/*
---------------------------------------------------------------------------------------------------------
-- test it out
SELECT *
FROM OPENQUERY(ADSI,
'SELECT sAMAccountName, sn
FROM ''LDAP://DC=level4,DC=level3,DC=level2,DC=level1''
WHERE memberOf=''cn=GroupName,OU=OUName,DC=level4,DC=level3,DC=level2,DC=level1''')
ORDER BY sn;
*/
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato