Jan 14

I recently found myself needing a function to turn SDDL format SIDs into varbinary using only SQL.

I couldn’t find any examples of going in that direction so for the benefit of anyone who finds themselves stuck in that position (your poor sod) here is the Function I hacked together. I’m sure there are better ways and I kept the table variable in there as it helped me check what I had done so far.

create function dbo.ConvertSSDLToVarBinary (@SIDSSDL nvarchar(max))
Returns varbinary(max)
Begin

declare @SplitTable TABLE
(
Id int identity(1,1) primary key,
Data nvarchar(max),
HexString nvarchar(max)
)

Declare @SplitOn nvarchar(1)
Set @SplitOn =’-‘
Declare @Cnt int
Set @Cnt = 1
Declare @tmpString nvarchar(max)

While (Charindex(@SplitOn,@SIDSSDL)>0)
Begin
Set @tmpString = ltrim(rtrim(Substring(@SIDSSDL,1,Charindex(@SplitOn,@SIDSSDL)-1)));
IF @Cnt > 1 –Lose the S
BEGIN
IF @Cnt = 2 — First is a single digit 1 for 1 byte
BEGIN
Insert Into @SplitTable (data,HexString)
Select @tmpString,
CONVERT(NVARCHAR(MAX),CONVERT(VarBinary(1),CONVERT(INT,@tmpString),0),2)
END
ELSE IF @Cnt = 3 –48 bit BIGENDIAN, 6 for 6 bytes
BEGIN
Insert Into @SplitTable (data,HexString)
Select @tmpString,
CONVERT(NVARCHAR(MAX),CONVERT(VarBinary(6),CONVERT(BIGINT,@tmpString),0),2)
END
ELSE
BEGIN — 32bit LittleEndian so reverse, 4 for 4 bytes
Insert Into @SplitTable (data,HexString)
Select @tmpString,
CONVERT(NVARCHAR(MAX),CONVERT(VARBINARY,REVERSE(CONVERT(VarBinary(4),CONVERT(BIGINT,@tmpString),3))),2)

END
END
Set @SIDSSDL = Substring(@SIDSSDL,Charindex(@SplitOn,@SIDSSDL)+1,len(@SIDSSDL))
Set @Cnt = @Cnt + 1
End

IF Len(@SIDSSDL) <> 0
BEGIN
Insert Into @SplitTable (data,HexString)
Select Data = ltrim(rtrim(@SIDSSDL)),
CONVERT(NVARCHAR(MAX),CONVERT(VARBINARY,REVERSE(CONVERT(VarBinary(4),CONVERT(BIGINT,@SIDSSDL),3))),2)
END

— We now have a table with all of the converted items
— Now need to construct from the table the string for the hex including the special item that
— tracks the number of dashes -2

declare @NumberOfDashesMinus2 int
set @NumberOfDashesMinus2 = @Cnt -3 –Our count starts at 1

set @Cnt = 1
declare @FullHexString nvarchar(Max)
set @FullHexString = ‘0x’
declare @CurrentString nvarchar(Max)
select @CurrentString = HexString from @SplitTable where ID = @Cnt

while @CurrentString IS NOT NULL
BEGIN
set @FullHexString = @FullHexString + @CurrentString
IF @Cnt = 1
BEGIN
set @FullHexString = @FullHexString + CONVERT(NVARCHAR(MAX),CONVERT(VarBinary(1),CONVERT(INT,@NumberOfDashesMinus2),0),2)
END
select @CurrentString = NULL
set @Cnt = @Cnt + 1
select @CurrentString = HexString from @SplitTable where ID = @Cnt
END

return convert(VarBinary(max),@FullHexString,1)

End
GO

Jan 16

One of the reasons why I decided to start actually blogging on my domain was to document my learning of the innards of my Mac and IPhone. Having had a Mac for a few years now I have used it for organising my music, photographs, e-mails etc. I have never tried to dig under the covers too far, mainly due to never having had to. Inspired by the desire to satisfy my geek tendencies I thought I should teach myself a little more about what is going on under the surface.

Therefore, despite the devastation it wreaks upon my Amazon recommendations list (“No I am not interested in buying any of those 150 books on IT! Where did you hide the literature I want to read? Oh well actually maybe I do want…”), I ordered myself a copy of Sams Teach Yourself iPhone Application Development.

Sams Teach Yourself iPhone Application Development Book Cover

Sams Teach Yourself iPhone Application Development

So today I sat down to read the book and start to see how the whole shebang works. As with the best types of these books it begins with a series of chapters to get you set-up correctly and give you a “led by the hand” walkthrough of the tools you are going to need, things to you need to set-up and things that are best left to worry about later because you haven’t a snowballs chance in hell of understanding that particular weirdness.

So far so good. I have my iPhone all set up for developing on. I have the XCode development environment and iPhone SDK installed. I’m feeling smug. Having done development in the Microsoft world, things are not too scary. I can see the analogies between the two worlds. You say pohtahtoh etc. Things are going ok.

Then I hit a brick wall.

I’m stuck.

I don’t know what to do.

The instructions tell me to do something but I can find no way to do it. The instructions have failed me. This is not some moral dilemma. The instructions are not asking me to betray some strongly held tenet of belief. They want me to do something simple and as far as I can see all the other kids are indeed doing it.

The book taunts me with its request. It wants me to press #. Yes it wants a hash. A US pound. A musical sharp symbol. That’s what it needs for me to continue and I’m failing it. I can’t do it. I have no idea how. This is 3rd year French all over again.

Now I’ve typed # many times. Damn it I play music, I’ve written a lot of C# code in my time. I’ve created a lot of temporary tables. I know how to press the hash key. Except it isn’t there. There is no hash anywhere on my Mac’s keyboard.

My Mac's Keyboard and not a # in sight

My Mac's Keyboard and not a # in sight

Now I don’t have a defective keyboard. It turns out that in their infinite wisdom Apple decided that those of receiving UK Macs would not need to worry about the #. We can have our £ sign on the 3 key where they put the # in the US. I mean why would we want it?

So after some mashing of keys I finally managed to get the hash to appear on screen.

You press the “Alt” key and 3 together and the hash appears.

Now this might perhaps have been more swiftly accomplished if they HAD PUT THE HASH ON THE KEY. They seemed to manage to put a Euro symbol € next to the @ above the 2 key quite happily. I should be careful though. I believe “Alt” and 5 opens the portal to Narnia and I’ve had enough snow for a while.

Get Adobe Flash player