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

WCF, RLS and SSL

Wow, working in IT is simple as long as you have an acronym lookup, but this is what I have been working on (banging my head against) fo rthe last week, sorting out deploying a new security architecture.

My god is it complex. I am writing extensive notes at the moment and when I have finished they deployment I will will release a few of my common solutions to some of the problems I have faced.

As much as Bill and teh boys think that they have integrated security down pat, let me assure you that the documentation to make it all work is a mass of methods and turtorials and other problems all piled high and shuffled to make one cohesive solution.

Also I have been working towards achieving my TOGAF Architecture Exam, and again more acronyms. I am sure somebody somewhere thinks that there much easier for remembering things, but honestly lets face it.
If you work it out

WCF (3 Bytes)
Translating that (1 Byte)
Translation to (Windows Communication Foundation) (32 bytes)

Total Storage Require (36 bytes)

So just by removing the acronyms from the process would save 12 bytes for my title......... but I gues it would make typing it all so much more longwinded.

Anyway enough from my senile rumblings..........

"Be good out there"

Skev