March 8, 2010 at 12:49 pm
Hello All,
Basically I'm trying to use TSQL to convert user SIDS from Binary to String and visa versa but I'm not getting accurate results...
If you run this in SQL Management Studio..
SELECT SUSER_SID('YourDomainUserName')
You get a result returned for your user SID as varbinary(85) http://msdn.microsoft.com/en-us/library/ms179889.aspx
To convert this SID back run this…
SELECT SUSER_SNAME (ENTER BINARY DATA FROM ABOVE QUERY HERE)
You should see your domain user name..
If you run this from the command prompt (CMD) whoami /user you receive the user's SID in the form of a string value
FAKE DATA EXAMPLE:
S-1-5-35-000500041-287000729-005005543-8000
I now want to take this string SID and convert it to varbinary data so that I can use the SQL function above to lookup names for the SIDS. I have a table that conatins all the SID's in the form of a string.
FAKE DATA EXAMPLE:
S-1-5-35-000500041-287000729-005005543-8000
I was using these queries below to test switching back and forth from SID string above to binary… Though I don’t see the same results as the SQL Function. If I test these queries below using simple data like convert “NAME” to binary and then binary to string it works great. So something is odd with the data above.
DECLARE @x varchar(85)
SELECT @x = convert(varchar(85),ENTER YOUR BINARY DATA HERE)
SELECT @x AS Value
DECLARE @x varbinary(85)
SELECT @x = convert(varbinary(85), 'ENTER YOUR STRING DATA HERE')
SELECT @x AS Value
Does anyone see something obvious that I'm doing wrong during the convert query or worked with the SID functions in SQL Server?
March 9, 2010 at 8:05 am
Take a look at the four convert scripts here. Maybe this will explain the issue you are facing.
Select Convert(char(100),0x526564,0)
Select convert(varbinary(20),'Red',0)
Select convert(varbinary(20),0x526564,0)
Select convert(varbinary(20),'0x526564',0)
The first two are regular converts. Take a look at the last two. One is trying to convert a varbinary to varbinary (No quotes).
The other one is with quotes. Look at the way it behaves.
Or maybe I did not understand your request.
-Roy
March 9, 2010 at 8:14 am
My issue is that the varbinary data results of these queries don't match up... Sorry, I probably didn't expain the issue very well..
SELECT SUSER_SID('domain\user')
AND
Results from
whoami /user as a string as the input for this
DECLARE @x varbinary(85)
SELECT @x = convert(varbinary(85), 'ENTER STRING SID DATA FROM whoami /user')
SELECT @x AS Value
June 28, 2012 at 8:04 pm
I'll take a shot at answering this. If you're dealing with a SID using TSQl, you have two choices varbinary(85), or converting it to a hexidecimal string.
Note a hexidecimal string will look like '0x' and a bunch a hex digits
What most system engineeer are used to seeing is something like this "S-1-16-28672". This is NOT a hexidecimal number.
By the way, Microsoft did something weird with the SIDS of SQL logins, so don't expect an intelligent result from non-Active Directory objects.
A bit of complex C# code will take a varbinary field and convert it to a SID. I'd suggest wrapping it in a CLR.Net function
private string ConvertByteToStringSid(Byte[] sidBytes)
{
StringBuilder strSid = new StringBuilder();
strSid.Append("S-");
// Add SID revision.
strSid.Append(sidBytes[0].ToString());
// Next six bytes are SID authority value.
if (sidBytes[6] != 0 || sidBytes[5] != 0)
{
string strAuth = String.Format
("0x{0:2x}{1:2x}{2:2x}{3:2x}{4:2x}{5:2x}",
(Int16)sidBytes[1],
(Int16)sidBytes[2],
(Int16)sidBytes[3],
(Int16)sidBytes[4],
(Int16)sidBytes[5],
(Int16)sidBytes[6]);
strSid.Append("-");
strSid.Append(strAuth);
}
else
{
Int64 iVal = (Int32)(sidBytes[1]) +
(Int32)(sidBytes[2] << 8) +
(Int32)(sidBytes[3] << 16) +
(Int32)(sidBytes[4] << 24);
strSid.Append("-");
strSid.Append(iVal.ToString());
}
// Get sub authority count...
int iSubCount = Convert.ToInt32(sidBytes[7]);
int idxAuth = 0;
for (int i = 0; i < iSubCount; i++)
{
idxAuth = 8 + i * 4;
if (idxAuth >= sidBytes.Length)
{
Console.WriteLine("OK :old NT account");
return strSid.ToString();
}
UInt32 iSubAuth = BitConverter.ToUInt32(sidBytes, idxAuth);
strSid.Append("-");
strSid.Append(iSubAuth.ToString());
}
}
November 21, 2013 at 5:41 am
SELECT distinct security_id, login_name, sy.name
FROM sys.dm_exec_sessions dms
JOIN sys.sysusers sy ON
dms.security_id = sy.sid
where security_id <> 0x01
June 2, 2017 at 9:28 am
There is an undocumented built-in function, SID_BINARY, that does this translation from the SDDL form into the binary form:
SELECT SID_BINARY(N'S-1-5-21-408552231-458724953-3089381293-513');
-- 0x01050000000000051500000027035A185996571BAD3724B801020000
This function should work across most SID types. The following two queries show it working correctly for Certificates and Asymmetric Keys (you can verify proper translation since these two system catalog views have both forms of the SID in them). And it would work for any Logins created from Certificates and Asymmetric Keys as the principal_ids for those (both Logins and Users) are the Cert / Key SIDs:
SELECT [name], [string_sid], [sid], SID_BINARY([string_sid])
FROM [master].sys.certificates;
SELECT [name], [string_sid], [sid], SID_BINARY([string_sid])
FROM [master].sys.asymmetric_keys;
Please note that principals of types "S" (SQL Server Login / SQL Server User) and "R" (Server Role / Database Role) do not have an SDDL representation as they are not Windows SIDs. These two types of principals have SQL Server properietary SIDs.
If you don't want to use an undocumented function, this can also be accomplished via SQLCLR using .NET's SecurityIdentifier class.
Pre-made SQLCLR functions to do these translations can be found in the Free version of the SQL# library (that I created): Convert_SddlSidToBinary (does the same translation as SID_BINARY) and Convert_BinarySidToSddl.
This information has also been documented in my answer to the following DBA.StackExchange question:
How do I translate a Windows SID to an SQL Server server_user_sid?
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
June 3, 2017 at 6:54 pm
Heh - a new post to a years-old thread, but it's an interesting one.
Solomon, I never knew SID_BINARY was undocumented. I just did some searching and you're right, so thank you for that.
The different types of SIDs (SQL and Windows) can be translated in both directions. The SID_BINARY converts from Windows SID to SQL SID.
To go from SQL SID to Windows SID, you can use the following ITVF:
CREATE FUNCTION dbo.SqlToWindowsSID(@sid Varbinary(85)) RETURNS TABLE WITH SCHEMABINDING
AS
RETURN (
WITH cteBase10 AS (
SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N)
),
cteTally AS (
SELECT TOP 256 N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
FROM cteBase10 T1
CROSS JOIN cteBase10 T2
CROSS JOIN cteBase10 T3
)
SELECT WindowsSID = STUFF((SELECT '-' + part
FROM (SELECT N = -1,
part = 'S-' + CONVERT(Varchar(30), CONVERT(Tinyint, CONVERT(Varbinary(30), LEFT(@sid, 1)))) + '-' +
CONVERT(Varchar(30), CONVERT(Integer, CONVERT(Varbinary(30), SUBSTRING(@sid, 3, 6))))
UNION ALL
SELECT TOP ((LEN(@sid) - 5) / 4) N,
part = CONVERT(Varchar(30),
CONVERT(Bigint,
CONVERT(Varbinary(30),
REVERSE(CONVERT(Varbinary(30), SUBSTRING(@sid, 9 + t.N * 4, 4))))))
FROM cteTally t
ORDER BY t.N
) AS x
ORDER BY N
FOR XML PATH('')), 1, 1, '')
);
GO
With this function, you can use it to convert individual SIDs or as a part of a query against a table of them.
SELECT SqlSID = logins.sid,
win.WindowsSID,
WindowsConverted = SID_BINARY(win.WindowsSID),
Match = CASE WHEN logins.sid = SID_BINARY(win.WindowsSID) THEN 'Yes' ELSE 'No' END
FROM sys.server_principals logins
CROSS APPLY dbo.SqlToWindowsSID(logins.sid) win
WHERE logins.type = 'U'
ORDER BY logins.name;
Edit: Sorry about the indenting in the code. It was indented properly in SSMS, but didn't translate properly in this editor.
June 5, 2017 at 5:17 am
SQL Dude-467553 - Monday, March 8, 2010 12:49 PMHello All,Basically I'm trying to use TSQL to convert user SIDS from Binary to String and visa versa but I'm not getting accurate results...If you run this in SQL Management Studio..SELECT SUSER_SID('YourDomainUserName')You get a result returned for your user SID as varbinary(85) http://msdn.microsoft.com/en-us/library/ms179889.aspxTo convert this SID back run this…SELECT SUSER_SNAME (ENTER BINARY DATA FROM ABOVE QUERY HERE)You should see your domain user name.
The SID from AD returned to the command prompt is a different format to the sid in sql server.
To get a string representation of the user SID in the sql server instance use the following
SELECT SUSER_SID(), sys.varbintohexstr(suser_sid())
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 6, 2017 at 9:35 am
Did you mean sys.fn_varbintohexstr()?
June 6, 2017 at 10:10 am
Steve Jones - SSC Editor - Tuesday, June 6, 2017 9:35 AMDid you mean sys.fn_varbintohexstr()?
Yes, I believe that is what Perry meant, given that sys.varbintohexstr is not a valid function. However, even with the correct function name it is still not what the O.P. was asking about. The question here is specifically about converting a SID in string / SDDL form -- S-1-5-18 -- into its binary form -- 0x010100000000000512000000. For example:
SELECT SID_BINARY(N'S-1-5-18'),
SUSER_SNAME(SID_BINARY(N'S-1-5-18'));
-- returns: 0x010100000000000512000000 NT AUTHORITY\SYSTEM
I am currently working on my first ever blog post 🙂 so that I can put all of the various pieces of info related to SIDs in one single document. There is some confusion about terminology as well as the algorithms for converting between the forms. I have been able to not only fix a small bug in Ed's code above (which appears to be mostly code from Aaron Bertrand who in turn got the original -- scalar UDF version -- code from a blog post that got the code originally from a script posted on SQL Server Central 😉 , which contains the minor bug), but I have also been able to greatly simply the code.
I had hoped to have already been done with the post by now (the updated T-SQL iTVF is done and tested), but it seems that there is more to this topic than I had originally thought. That and I have found 2 bugs in doing this research (1 has been posted to Connect), though it is possible that both are documentation errors. Hard to say.
I will post another message here with the link to the blog post when it is finished :-).
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
June 7, 2017 at 4:09 am
sorry, yes typo, it's a function.
The AD SID will be a completely different format to the SID that the SQL Server instance generates.
The AD SID applies a specific format to the SID generation including tags such as a RID, which is useful for generating accounts within the domain
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply