Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Getting SID from Active Directory - SID transformation problems Expand / Collapse
Author
Message
Posted Friday, June 19, 2009 4:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 17, 2010 7:13 AM
Points: 15, Visits: 41
Hi all,

here is the issue:
we had an old xp that gets SID from AD, using a domain user name (i.e. DOM\user), lately it started with performance issues until one day the system couldn't be accessed (i provided a temporary solution), i figured to skip the xp, that get's SID, due we don't have source code for it (it's an inherited system) so i'm trying to do the same using only T-SQL so i needed to get SID from the authenticated user, after some testings found out that using USER_SID() function, return the same SID that an openquery to LDAP does
(0x01050000000000051500000003A42F702A46973F9BDCFDF668040000
0x01050000000000051500000003A42F702A46973F9BDCFDF668040000)
i created two functions to perform conversion to SID S-1-5-21-1882170371-1066878506-4143832219-1128 (checked over the internet and used this article to guide myself http://www.mombu.com/microsoft/windows-server-active-directory/t-convert-objectsid-to-string-136938.html) here is the code that i came up with:


/****** Object: UserDefinedFunction [dbo].[ufnHEXtoSID] Script Date: 06/18/2009 12:10:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ufnHEXtoSID] ( @hexNumbBIN varbinary(255) )
RETURNS varchar(255)
as
BEGIN
declare @hexNumb varchar(255)
declare @SID varchar(255)

declare @AuxChar varchar(10)

declare @fst varchar(2)
declare @sec varchar(2)
declare @thr varchar(12)
declare @fou varchar(8)
declare @fif varchar(8)
declare @six varchar(8)
declare @sev varchar(8)
declare @eig varchar(8)

declare @res varchar(3)

declare @step smallint
declare @bigStep smallint, @stepi int
declare @sum1 bigint, @numb bigint, @base bigint

declare @pivot varchar(12), @pivoti varchar(12)
--'0x01040000000000051500000003A42F702A46973F9BDCFDF6'
-- set @hexNumb = '0x010500000000000515000000064E7D7F1157567A0411C520F4010000'
-- set @hexNumb = '0x01050000000000051500000003A42F702A46973F9BDCFDF66B040000'

-- set @hexNumbBIN = substring(@hexNumbBIN, 3, len(@hexNumbBIN))

SET @hexnumb = ''
set @step = 0
while @step <= len(@hexnumbBIN)
begin
set @res = substring(@hexNumbBIN, @step,1)
select @AuxChar = dbo.BINtoASCIItoHEX(ascii(@res))
set @auxchar = isnull(@auxchar, '')
SET @hexnumb = @hexnumb + @AuxChar
set @step = @step + 1
end

set @fst = substring(@hexNumb,1,2)
set @sec = substring(@hexNumb,3,2)
set @thr = substring(@hexNumb,5,12)
set @fou = substring(@hexNumb,17,8)
set @fif = substring(@hexNumb,25,8)
set @six = substring(@hexNumb,33,8)
set @sev = substring(@hexNumb,41,8)
set @eig = substring(@hexNumb,49,8)

set @SID = 'S-'
set @bigStep = 1
while @bigStep <= 8
begin
set @pivoti = ''

if @bigStep = 1
set @pivot = @fst
if @bigStep = 2
set @pivot = @sec
if @bigStep = 3
set @pivot = ''
--set @pivot = @thr
if @bigStep = 4
set @pivot = @fou
if @bigStep = 5
set @pivot = @fif
if @bigStep = 6
set @pivot = @six
if @bigStep = 7
set @pivot = @sev
if @bigStep = 8
set @pivot = @eig

set @step = len(@pivot)
-- set @sevi = ''

while @step > 0
begin
set @pivoti = @pivoti + substring(@pivot, @step - 1, 2)
set @step = @step - 2
end

set @sum1 = 0
set @numb = 0
set @base = 16

set @step = len(@pivoti)
set @stepi = 0
while @step > 0
begin
if substring(@pivoti, @step, 1) in ('0','1','2','3','4','5','6','7','8','9')
set @numb = cast(substring(@pivoti, @step, 1) as int)
else
if substring(@pivoti, @step, 1) = 'a'
set @numb = 10
else
if substring(@pivoti, @step, 1) = 'b'
set @numb = 11
else
if substring(@pivoti, @step, 1) = 'c'
set @numb = 12
else
if substring(@pivoti, @step, 1) = 'd'
set @numb = 13
else
if substring(@pivoti, @step, 1) = 'e'
set @numb = 14
else
if substring(@pivoti, @step, 1) = 'f'
set @numb = 15
set @sum1 = @sum1 + @numb * power(@base,@stepi)
set @stepi = @stepi + 1
set @step = @step - 1
end

set @SID = @SID + '-' + cast(@sum1 as varchar(15))

set @bigStep = @bigStep + 1
end

return replace(replace(@sid, '--','-'),'-0','')
END

go

/****** Object: UserDefinedFunction [dbo].[BINtoASCIItoHEX] Script Date: 06/18/2009 11:57:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* CONVERT BIN HEX TO CHAR HEX*/
CREATE FUNCTION [dbo].[BINtoASCIItoHEX] (@asci decimal(10,3))
RETURNS VARCHAR(10)
AS
BEGIN
--declare @asci decimal(10,3)
declare @res decimal(10,3)
declare @resV decimal(10,0)
declare @resC varchar(10)
declare @FINALCHAR varchar(10)

set @FINALCHAR = ''
-- set @asci = 590
if @asci > 9
begin
WHILE @asci > 0
begin
set @res = @asci / 16
set @ASCI = cast(@res as int)
Set @res = @res - @asci
set @res = @res * 16
set @resV = Cast(@res as varchar(10))

if @resV in (0,1,2,3,4,5,6,7,8,9)
begin
set @resC = Cast(@resV as varchar(10))
end
else
if @resV = 10
set @resC= 'A'
else
if @resV = 11
set @resC = 'B'
else
if @resV = 12
set @resC = 'C'
else
if @resV = 13
set @resC = 'D'
else
if @resV = 14
set @resC = 'E'
else
if @resV = 15
set @resC = 'F'
set @FINALCHAR = @resC + @FINALCHAR
end
end
else
begin
set @ASCI = cast(@asci as int)
--set @resC = substring( cast(@asci as varchar(100)), 1, len(cast(@asci as varchar(100))-4) )

set @resC = cast( cast(@asci as int) as varchar(10))
set @FINALCHAR = '0' + @resC
end
RETURN @FINALCHAR
END
go

i use it this way
select @sid = dbo.ufnHEXtoSID( SUSER_SID() )



for my local domain it works great, but when i try it on other domains... the conversions aren't the same they were, could it be possible that the SID format is Active directory config dependant? is it works on one AD how could it not work on another?


Thanks in advance.


Jorge

Post #738690
Posted Monday, June 22, 2009 9:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 17, 2010 7:13 AM
Points: 15, Visits: 41
Well it looks like my post didn't catch a lot of interest as i thought... so i kept working, and fixed the script, now it seems to be working with all SIDS... the issue was when a string like 0E appeared, it turned it in just E.

if this can be of some use to anyone.. just let me know....


Regards

Jorge
Post #739473
Posted Wednesday, June 24, 2009 2:41 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 11:29 AM
Points: 402, Visits: 47
It's funny, I needed exactly this script and found your article. Thanks for taking the time to post it. Can you post what your changes were to fix it?


Post #741381
Posted Wednesday, June 24, 2009 3:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 17, 2010 7:13 AM
Points: 15, Visits: 41
Sure:

at ufnHEXtoSID after this:

set @res = substring(@hexNumbBIN, @step,1)
select @AuxChar = dbo.BINtoASCIItoHEX(ascii(@res))
set @auxchar = isnull(@auxchar, '')

add this:
if len(@auxchar)= 1
set @auxchar = '0' + @auxchar

Regards
Post #741398
Posted Tuesday, February 1, 2011 2:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 1, 2011 2:59 PM
Points: 1, Visits: 2
This is awesome! Just what I needed, thanks!!
Post #1057075
Posted Thursday, April 28, 2011 11:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 20, 2011 1:32 PM
Points: 1, Visits: 19
agreed, great script!
thanks.
Post #1100441
Posted Tuesday, June 7, 2011 11:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 43, Visits: 190
I understand the context of this forum is SQL Server side but if you can use C# vb.net etc. to do the transformation then here is a great link to do so.
http://blogs.msdn.com/b/alextch/archive/2006/03/04/convertobjectsidtostring.aspx

Why Msoft has not provided a built in function yet I don't know? Seems like it would be widely accepted.
Post #1121200
Posted Thursday, December 12, 2013 10:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 5:18 AM
Points: 2, Visits: 124
This helped me out. Thanks for posting!
Post #1522401
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse