Yes you can directly query AD using TSQL queries. And it's REALLY easy. Why you ask – well, LDAP can be painfully slow if you have to trawl through groups eg if you are storing the results in a table or outputting them to screen or suchlike. I've found this method to be pretty reliable and reasonable with regards to performance. First things first… on a SQL server, you need to create a linked server to AD as follows: EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource' That's all the configuration you have to do. You are good to go! Below is an example of the TSQL syntax. Note that the DC is the domain in which the AD resides – in this instance it has three parts MyDomainName,DC=MyTopLevelDomain,DC=MyDomainCountryCode eg DC=microsoft,DC=com,DC=au. In this example, I am returning the username of all users who are a member of the AD group called GAMyADGroupName. This example checks if a particular user is a member of a particular AD group: SELECT *
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
FROM OPENQUERY(ADSI,
'SELECT sAMAccountName
FROM ''LDAP://DC=csr,DC=com,DC=au''
WHERE objectCategory = ''Person''
AND objectClass = ''user''
AND memberOf = ''CN=GAMyADGroupName,OU=Other,OU=Global Groups,OU=Security Groups,DC=MyDomainName,DC=com,DC=au''
AND sAMAccountName = ''MyUserName''')
Thursday, 21 February 2008
Using TSQL Queries for AD Authentication
Subscribe to:
Post Comments (Atom)
Posts
4 comments:
How would you list all of the groups a given user is a member of?
Not as easy to do unfortunately.
Using the MemberOf property, you can find out if the user is a group or you can find all users in a group but you can't list all groups the user is in. Essentially you can't put MemberOf in the SELECT clause, it can only be used in the WHERE clause. This is because it returns an array object not a value.
Also, you can't use wildcards in the MemberOf.
So how can you get around these limitations?
I havne't had any requirement to do this so I haven't spent a whole lot of time researching it. So the only useful suggestion I can give at this point is to use nested queries. Not pretty but it's the best I can guess at without spending a lot more time on research.
It's easier to query the groups, and then run a separate query for each group. If you're doing ASP.NET, the groups an account is a member of is available in the identity object.
declare @GroupCN nvarchar(128),
@GroupObjectGUID nvarchar(128),
@GroupObjectSID nvarchar(128),
@sql nvarchar(4000)
create table #Groups(
cn nvarchar(128),
objectGUID nvarchar(128),
objectSID nvarchar(128)
)
create table #users(
UserCN nvarchar(128),
UserObjectGUID nvarchar(128),
UserObjectSID nvarchar(128),
UserPhysicalDeliveryGroupName nvarchar(128),
GroupCN nvarchar(128),
GroupObjectGUID nvarchar(128),
GroupObjectSID nvarchar(128)
)
insert into #Groups
select
cn,
upper(substring(master.dbo.fn_varbintohexstr(objectGUID), 3,
len(master.dbo.fn_varbintohexstr(objectGUID)) - 2)) as objectGUID,
upper(substring(master.dbo.fn_varbintohexstr(objectSID), 3,
len(master.dbo.fn_varbintohexstr(objectGUID)) - 2)) as objectSID
from openquery(ADSI,
'
SELECT
cn,
objectGUID,
objectSID
FROM ''LDAP://cn=users,DC=mydomain,DC=com''
WHERE
objectClass = ''group''
')
--debug
--select * from #Groups
declare MyCursor cursor for
select
cn,
objectGUID,
objectSID
from #Groups
open MyCursor
fetch next from MyCursor into
@GroupCN,
@GroupObjectGUID,
@GroupObjectSID
while(@@fetch_status = 0)
begin
-- print @GroupCN
set @sql =
'
select
cn as UserCN,
upper(substring(master.dbo.fn_varbintohexstr(objectGUID), 3,
len(master.dbo.fn_varbintohexstr(objectGUID)) - 2)) as UserObjectGUID,
upper(substring(master.dbo.fn_varbintohexstr(objectSID), 3,
len(master.dbo.fn_varbintohexstr(objectSID)) - 2)) as UserObjectSID,
physicalDeliveryGroupName as UserPhysicalGroupDeliveryName,
''' + @GroupCN + ''' as GroupCN ,
''' + @GroupObjectGUID + ''' as GroupObjectGUID ,
''' + @GroupObjectSID + ''' as GroupObjectSID
from openquery(ADSI,
''
SELECT
cn,
objectGUID,
objectSID,
physicalDeliveryGroupName
FROM ''''LDAP://cn=users,DC=mydomain,DC=com''''
WHERE
memberOf=''''CN=' + @GroupCN + ',CN=Users,DC=mydomain,DC=com''''
'')
'
-- debug
-- select @sql
insert into #users
exec sp_executesql @sql
fetch next from MyCursor into
@GroupCN,
@GroupObjectGUID,
@GroupObjectSID
end
close MyCursor
deallocate MyCursor
-- do some work here
drop table #Groups
drop table #users
Yep there's no question it can be done using ASP.Net... I'm just trying to do it purely in SQL. Your approach was along the same lines I was thinking... it sure ain't pretty though! That's no slight on your code - the technology is getting in the way.
What would be great would be access to use wildcards in the MemberOf property... then it would be a cinch.
Post a Comment