Joining on objectSID or using objectSID in a WHERE Clause

This one drove me crazy for the better part of a day.
When SQL Server shows you an objectSID it looks something like this:

0x010500000000000515000000317B347F75771D5BB254B44007080000

If you take that string and type something like:

SELECT * FROM viewADEmployees WHERE objectSID = 0x010500000000000515000000317B347F75771D5BB254B44007080000

It works just fine.

But if you take that string and stuff it in a VARCHAR (or any other type you think might be appropriate) you get an empty result set.

So I thought, duh, I need to cast it to the same type as objectSID. It only took a little while to figure out that objectSID is of type VARBINARY. It turns out, however, that simply casting the string to VARBINARY does NOT work! In fact I could not find any CAST or CONVERT that did the trick. I performed more casts than the entire fishing population on opening day of trout season.

Google was of no help either. I was on my own on this one.

I finally stepped back reasoned it out:

1. objectSID is VARBINARY
2. I need to convert my comparison value to VARBINARY
3. I should create a user function that takes a string representation of a series of hex byte values
4. each two-character pair of hex digits reprsents a single binary byte
5. I need to build up a VARBINARY value by successively converting the pairs to integers and then adding them to my VARBINARY return value

This is my resulting function which took a suprisingly short period of time to conceive, write and debug :

CREATE FUNCTION dbo.fncFormatSID (@vchFromSID VARCHAR(300))
RETURNS VARBINARY(85)
AS
BEGIN
DECLARE @binOut VARBINARY(85);
DECLARE @intLen INT;
DECLARE @intIndex INT;
DECLARE @intLow INT;
DECLARE @intHigh INT;
DECLARE @vchLow VARCHAR(1);
DECLARE @vchHigh VARCHAR(1);

SELECT @intLen = LEN(@vchFromSID)
SELECT @intIndex = 2

WHILE (@intIndex < @intLen)
BEGIN

SELECT @vchHigh = SUBSTRING(@vchFromSID, @intIndex, 1)
SELECT @vchLow = SUBSTRING(@vchFromSID, @intIndex + 1, 1)

IF(@vchLow >= '0' AND @vchLow <= '9')
BEGIN
SELECT @intLow = ASCII(@vchLow) - ASCII("0");
END

IF(@vchHigh >= '0' AND @vchHigh <= '9')
BEGIN
SELECT @intHigh = ASCII(@vchHigh) - ASCII("0");
END

IF(@vchLow >= 'A' AND @vchLow <= 'F')
BEGIN
SELECT @intLow = ASCII(@vchLow) - ASCII("A") + 10;
END

IF(@vchHigh >= 'A' AND @vchHigh <= 'F')
BEGIN
SELECT @intHigh = ASCII(@vchHigh) - ASCII("A") + 10;
END

IF @binOut IS NULL
BEGIN
SELECT @binOut = CONVERT(VARBINARY(1), (@intHigh * 16) + @intLow) ;
END
ELSE BEGIN
SELECT @binOut = @binOut + CONVERT(VARBINARY(1), (@intHigh * 16) + @intLow) ;
END

SELECT @intIndex = @intIndex + 3

END /* While */


RETURN(@binOut);

END

Accessing Active Directory From SQL Server

Figured out how to query active directory from SQL Server. Its pretty well documented but the first step is to create what is know as a 'Linked Server'. This involves executing one line of code in Query Analyzer:

sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

Then you can execute stuff like this:

SELECT * from openquery(ADSI, 'SELECT cn,objectsid FROM ''LDAP://atlas/CN=Users,DC=doublestarinc,DC=com''')

I'm not sure if its possible to perform a join directly with this. To save time I created a view using something similar to the above SELELCT statement. Then using that view we can all join to our heart's content.