Active Directory, LDAP and Open Query

Right folks, you'll probably be here because your trying to do one of those thing sthat we are told is very simple, well I am here to tell you that it's a right royal pain in the ass.

Once you set up the Linked Server Querying the Active Directory makes it so useful, and stops a hell of a lot of duplication, but setting iit up can cause a lot of head scratching.

So this is the script that I use for setting ours up with (SQL2005) I don't guarantee that this will work for you but its what I get the most consistent results with.

(this bit that alot of the sites out there misss when they tell you how to set up your ad linked server, and unless your a linked server wizard you won't even realise that its turned off. With out distributed Ad Hoc Queries turned on you'll be beating your head against a wall.)

Use Master
Go
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'Ad Hoc Distributed Queries','1';
RECONFIGURE With OVERRIDE;
go


/****** Object: LinkedServer [AD] Script Date: 05/15/2008 07:39:38 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'AD', @srvproduct=N'Active Directory Services 2.5', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AD',@useself=N'False',@locallogin=NULL,@rmtuser=N'DOMAIN\DOMAINUSER',@rmtpassword=DomainPassword
GO

(The user for this just needs to have domain user rights, so that it can query the AD)

EXEC master.dbo.sp_serveroption @server=N'AD', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AD', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AD', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AD', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AD', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AD', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AD', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AD', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'AD', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'AD', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AD', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'AD', @optname=N'use remote collation', @optvalue=N'true'

When you've done this you should be able to run the following query

Select distinguishedName from OPENQUERY (AD,'SELECT distinguishedName FROM ''LDAP://MYDCSERVER/DC=MYDOMAIN,DC=NET,DC=UK'' WHERE objectClass=''USER''')

And get some results.

And whilst your here, your're going to want to at some point query the object by the SID value from the windowsIdentity so heres that query

Select distinguishedName from OPENQUERY (AD,'SELECT distinguishedName FROM ''LDAP://MYDCSERVER/DC=MYDOMAIN,DC=NET,DC=UK'' WHERE objectSID= ''S-1-5-21-1645522239-1801674531-682003330-3730''')

And for those of you who are glutons for punishment.

If you want to a SID query using the binary value of the SID, then what you do is convert it from a SID to A HEX String and then seperate the binary values using \\.

But hey go look up Joe Kaplan and Ryan Dunn here.

http://directoryprogramming.net/default.aspx

They now a hell of a lot more about it than I do.

Well that's it for now........

Alixx

Security, Security, Securiy

Well I am now on day 30 of my intrepid travellings into Row Level Security, SSPI and Kerberos.

When I finally get all the kinks worked out I think that it will be an incredibly slick way of dealing with multiple customers in multipel databases....... but at the moment it is making me bleeed out of my eyes.

Luckily enough however the show season for me has started in earnest now and I have had chance to beat people with my sword, so that makes me feelk much better.

Just as an aside this year makes it the seventh year that I have been a Member of the Institute of Analysts and Programmers.

Anyway that's it for now catch you all later.


Alixx