Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Getting SID from Active Directory - SID transformation problems


Getting SID from Active Directory - SID transformation problems

Author
Message
jorge escobar pacheco
jorge escobar pacheco
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
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
jorge escobar pacheco
jorge escobar pacheco
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
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
DevinLee
DevinLee
SSC-Addicted
SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)

Group: General Forum Members
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?



jorge escobar pacheco
jorge escobar pacheco
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
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
jeremystaples
jeremystaples
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 2
This is awesome! Just what I needed, thanks!! :-D
georgedavid
georgedavid
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 19
agreed, great script!
thanks.
nfs_john
nfs_john
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 191
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.
jwminer
jwminer
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 129
This helped me out. Thanks for posting!
Flemming_Thor
Flemming_Thor
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 91
I needed this great script just today. Thanks.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search