Advice requested : Storing SID String in SQL table

17 Aug 2006 1:13 AM

Russell Mangel

I would like to store a Windows NT Account SID in an SQL server table as a
SID String.

What is the maximum length of a SID String?
What is the maximum length of a SID byte[] array?

Is this information published anywhere?

Thanks
Russell Mangel
Las Vegas, NV

PS
I am just trying to avoid using overly large
column lengths in SQL 2005. I realize that
I could use VARBINARY(MAX) for
bytes, or VARCHAR(MAX) for SID
string.

18 Aug 2006 2:09 AM

Joe Kaplan (MVP - ADSI)

A SID in binary is a 1 byte revision, 1 byte of sub authority count, 6 bytes
of authority ID and then a variable number of 4 byte subauthorities, maxing
out at 15. Thus, you've got 68 bytes there. I've never seen a SID with
that many sub-authorities, but it is technically possible.

I'm not sure about the max length for the string, but you can probably
figure that out based on the max length of a 4 byte unsigned integer as
string, a 6 byte unsigned integer as string, etc. It will get a lot bigger
than you really need if you allow for all 15 subauthorities.

Another potential option to consider might be to store the user's AD GUID
(assuming you are talking about AD users and groups here). That fits nicely
into a normal SQL unique ID column and is always 16 bytes. You'd then need
to look up the SIDs if you need them from the directory.

Joe K.

--
Joe Kaplan-MS MVP Directory Services Programming
Co-author of "The .NET Developer's Guide to Directory Services Programming"
http://www.directoryprogramming.net
--

Show quoteHide quote

"Russell Mangel" <russ***@tymer.net> wrote in message
news:OgSJhpZwGHA.3508@TK2MSFTNGP04.phx.gbl...
>I would like to store a Windows NT Account SID in an SQL server table as a
>SID String.
>
> What is the maximum length of a SID String?
> What is the maximum length of a SID byte[] array?
>
> Is this information published anywhere?
>
> Thanks
> Russell Mangel
> Las Vegas, NV
>
> PS
> I am just trying to avoid using overly large
> column lengths in SQL 2005. I realize that
> I could use VARBINARY(MAX) for
> bytes, or VARCHAR(MAX) for SID
> string.
>

19 Aug 2006 2:41 PM

Russell Mangel

Looks like your numbers are right, thanks.

So the answer to my question is:

varbinary(68)-- pure binary
varchar(136) -- (68*2) = hexString
varchar(184) -- SID String

I wrote a little program to test, notice that .NET 2.0
has SecurityIdentifier.MaxBinaryLength, I didn't
know about this.

Console.WriteLine("SID Min. num Bytes: {0}",
SecurityIdentifier.MinBinaryLength);
Console.WriteLine("SID Min. num Bytes: {0}",
SecurityIdentifier.MaxBinaryLength);
Byte[] bytes = new byte[SecurityIdentifier.MaxBinaryLength];
for (Int32 i = 0; i < bytes.Length; i++)
{
bytes[i] = 0xFF;
}
bytes[0] = 0x01; // Must be 1
bytes[1] = 0x0F; // Max 15 (base10)
SecurityIdentifier sid = new SecurityIdentifier(bytes, 0);
String sidString = sid.ToString();
Console.WriteLine("Max length of SID in String format: {0} ",
sidString.Length);
Console.WriteLine(sidString);

Results ------------------------------

SID Min. num Bytes: 8
SID Min. num Bytes: 68
Max length of SID in String format: 184
S-1-281474976710655-4294967295-4294967295-4294967295-4294967295-4294967295-4294967295-4294967295-4294967295-4294967295-4294967295-4294967295
-4294967295-4294967295-4294967295-4294967295

---------------------------------------

Show quoteHide quote

"Joe Kaplan (MVP - ADSI)" <joseph.e.kap***@removethis.accenture.com> wrote
in message news:%231cYWtmwGHA.1624@TK2MSFTNGP02.phx.gbl...
>A SID in binary is a 1 byte revision, 1 byte of sub authority count, 6
>bytes of authority ID and then a variable number of 4 byte subauthorities,
>maxing out at 15. Thus, you've got 68 bytes there. I've never seen a SID
>with that many sub-authorities, but it is technically possible.
>
> I'm not sure about the max length for the string, but you can probably
> figure that out based on the max length of a 4 byte unsigned integer as
> string, a 6 byte unsigned integer as string, etc. It will get a lot
> bigger than you really need if you allow for all 15 subauthorities.
>
> Another potential option to consider might be to store the user's AD GUID
> (assuming you are talking about AD users and groups here). That fits
> nicely into a normal SQL unique ID column and is always 16 bytes. You'd
> then need to look up the SIDs if you need them from the directory.
>
> Joe K.
>
> --
> Joe Kaplan-MS MVP Directory Services Programming
> Co-author of "The .NET Developer's Guide to Directory Services
> Programming"
> http://www.directoryprogramming.net
> --
> "Russell Mangel" <russ***@tymer.net> wrote in message
> news:OgSJhpZwGHA.3508@TK2MSFTNGP04.phx.gbl...
>>I would like to store a Windows NT Account SID in an SQL server table as a
>>SID String.
>>
>> What is the maximum length of a SID String?
>> What is the maximum length of a SID byte[] array?
>>
>> Is this information published anywhere?
>>
>> Thanks
>> Russell Mangel
>> Las Vegas, NV
>>
>> PS
>> I am just trying to avoid using overly large
>> column lengths in SQL 2005. I realize that
>> I could use VARBINARY(MAX) for
>> bytes, or VARCHAR(MAX) for SID
>> string.
>>
>
>

19 Aug 2006 2:51 PM

Russell Mangel

"Joe Kaplan (MVP - ADSI)" <joseph.e.kap***@removethis.accenture.com> wrote
in message news:%231cYWtmwGHA.1624@TK2MSFTNGP02.phx.gbl...

> Another potential option to consider might be to store the user's AD GUID
> (assuming you are talking about AD users and groups here). That fits
> nicely into a normal SQL unique ID column and is always 16 bytes. You'd
> then need to look up the SIDs if you need them from the directory.
>
> Joe K.

Thanks, I also found this information in your
excellant book. (2006 Directory Services Programming).

FYI
I really like the System.DirectoryServices.Protocols
for LDAP access the best. I use LDAP access to get
as much information as I can for Exchange mailboxes,
before I use MAPI 1.0 for mailbox archiving.

Russell Mangel

19 Aug 2006 4:13 PM

Joe Kaplan (MVP - ADSI)

I'm glad you like the book. Thanks a lot. I too like using SDS.Protocols.
It is a little geekier and requires a bit more code, but once you get some
nice wrappers going, it is pretty effective. In retrospect, it would have
been nicer to have more coverage on it in the book, but we had to finish it
sometime and it actually started before we ever saw 2.0, so it was hard to
backtrack.

If you are interested, I started a series of blog posting on my blog
(www.joekaplan.net) demonstrating a bunch of things that can't be done in
ADSI and require SDS.P (or native LDAP API).

My main issue with SDS.P is with Microsoft with some of their own APIs.
Stuff like CDOEXM for Exchange mailbox provisioning requires ADSI and makes
it hard to do this kind of thing in raw LDAP (IADsTSUserEx is another
example). Most of the Exchange programming story is pretty crappy though
(as you have probably already seen). :)

Joe K.

--
Joe Kaplan-MS MVP Directory Services Programming
Co-author of "The .NET Developer's Guide to Directory Services Programming"
http://www.directoryprogramming.net
--

Show quoteHide quote

"Russell Mangel" <russ***@tymer.net> wrote in message
news:Ofsbt75wGHA.4596@TK2MSFTNGP05.phx.gbl...
>
> "Joe Kaplan (MVP - ADSI)" <joseph.e.kap***@removethis.accenture.com> wrote
> in message news:%231cYWtmwGHA.1624@TK2MSFTNGP02.phx.gbl...
>
>> Another potential option to consider might be to store the user's AD GUID
>> (assuming you are talking about AD users and groups here). That fits
>> nicely into a normal SQL unique ID column and is always 16 bytes. You'd
>> then need to look up the SIDs if you need them from the directory.
>>
>> Joe K.
>
> Thanks, I also found this information in your
> excellant book. (2006 Directory Services Programming).
>
> FYI
> I really like the System.DirectoryServices.Protocols
> for LDAP access the best. I use LDAP access to get
> as much information as I can for Exchange mailboxes,
> before I use MAPI 1.0 for mailbox archiving.
>
> Russell Mangel
>