Getting SID from Active Directory - SID transformation problems
-
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
endset @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 = @eigset @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
-
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
-
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?
-
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
-
This is awesome! Just what I needed, thanks!! 😀
-
agreed, great script!
thanks.
-
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.
-
This helped me out. Thanks for posting!
-
I needed this great script just today. Thanks.
- jwminer - Thursday, December 12, 2013 10:26 AM
Hi I am stucked with the same problem and I really need your help to find out why my column SID only shows the same result all the way?..
I have this query:
SELECT top(900)
@setmds.dbo.ufnHEXtoSID( SUSER_SID(objectsid)) as Login_SID,
* FROM OpenQuery (
ADSI,
'SELECT objectsid, title, sAMAccountName,
givenname
FROM ''LDAP://dansk-retursystem.dk''
WHERE objectClass = ''User''
') as tblADuserAnd the result I am getting is this:
As you see it only returns S-?? what is the issue?
-
I got this function off the web a few months ago:
ALTER FUNCTION [dbo].[fn_SIDToString]
(
@BinSID AS VARBINARY(100)
)
RETURNS VARCHAR(100)
AS BEGIN
IF LEN(@BinSID) % 4 <> 0 RETURN(NULL)
DECLARE @StringSID VARCHAR(100)
DECLARE @i AS INT
DECLARE @j-2 AS INT
SELECT @StringSID = 'S-'
+ CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 1, 1))))
SELECT @StringSID = @StringSID + '-'
+ CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 3, 6))))
SET @j-2 = 9
SET @i = LEN(@BinSID)
WHILE @j-2 < @i
BEGIN
DECLARE @val BINARY(4)
SELECT @val = SUBSTRING(@BinSID, @j-2, 4)
SELECT @StringSID = @StringSID + '-'
+ CONVERT(VARCHAR, CONVERT(BIGINT, CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @val)))))
END
RETURN ( @StringSID )
END
-
@Jorge E P:
Thank you for posting this great script!
BIG help. Worked as designed. -
@chris-2 Hurlbut:
Great stuff, just one function and it returns correct result. Awesome. Thanks!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply