Sql-server – How to obtain SIDs of Sql Server installed NT Service Accounts

Securitysql serversql-server-2008

I am using WMI to obtain members of server local groups, as well as the SIDs of the members. The WQL queries work for members that are local users, local groups, AD domain users and AD domain groups. However when the script is looking at members that happen to be SQL Server related, it cannot find the object in the class it thinks it should look at, and therefore I don't get the SID of the members. For example, here are some queries constructed dynamically for group members that happen to be SQL Server related:

select SID from Win32_SystemAccount where Domain='NT SERVICE' and Name='SQLBrowser'
select SID from Win32_SystemAccount where Domain='NT SERVICE' and Name='MsDtsServer100'
select SID from Win32_SystemAccount where Domain='NT SERVICE' and Name='SQLAgent$SQLEXPRESS'
select SID from Win32_SystemAccount where Domain='NT SERVICE' and Name='MSSQL$EPROVISIONAPP'
select SID from Win32_SystemAccount where Domain='NT SERVICE' and Name='MSOLAP$EPROVISIONAPP'
select SID from Win32_SystemAccount where Domain='NT SERVICE' and Name='ReportServer$EPROVISIONAPP'
select SID from Win32_SystemAccount where Domain='NT SERVICE' and Name='SQLAgent$EPROVISIONAPP'

The queries above do not return any result, even though the PartComponent of such members tells me that the member is from the Win32_SystemAccount class, and the Domain is 'NT Service'.

On the server, if I look at Local Users and Groups -> Groups, and look at the properties of a group, say "SQLServerSqlAgentUser$myservername$SQLEXPRESS", it has one member "NT SERVICE\SQLAgent$SQLEXPRESS (S-1-5-80-592940576-165…". I cannot see the entire value of the SID.

I cannot figure out how Microsoft is pulling the SID of such members, where it is getting them from.

Any ideas?

Best Answer

From the WMI documentation, I couldn't find any way to directly obtain a service SID, probably because as far as I've seen, service SIDs aren't physical directory objects, and thus couldn't be queried except where they appear as part of ACLs. This would also explain why the values aren't persisted in the registry.

Having said that, the service SIDs definitely do not appear in Win32_SystemAccount. Using PowerShell on a sample machine, you can verify this by running Get-WmiObject -Query "SELECT * FROM Win32_SystemAccount".

Luckily, however, there are alternatives:

  1. Per this TechNet blog post, the non-well-known portion of a service SID is generated by taking a SHA1 hash of the uppercase service name, and then splitting apart the results into five unsigned 4-byte numbers. The following code performs this computation and returns the SID as displayed in Windows:

    DECLARE @serviceName nvarchar(128) = 'MSSQL$SQL2008R2DEV';

SELECT
    'S-1-5-80' +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 17, 4), 2))) +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 13, 4), 2))) +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 9, 4), 2))) +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 5, 4), 2))) +
        '-' + CONVERT(varchar(20), CONVERT(bigint, CONVERT(varbinary(4), sys.fn_varbintohexsubstring(0, a.BinSid, 1, 4), 2)))
    FROM
    (
        SELECT
            CONVERT(varbinary(20), REVERSE(HASHBYTES('SHA1', UPPER(@serviceName)))) AS BinSid
    ) a;

    The binary SID can also be obtained by using the SUSER_SID function (with some extra processing to handle the fixed portion) in lieu of HASHBYTES. (Note that the raw binary SID must have its endianness reversed before conversion and output. Also, the @serviceName variable being nvarchar is important.)

    If you have .NET available, doing the hash calculation is trivial. Here's the C# code to do it:

    using System.Security.Cryptography;

...

string serviceName = "MSSQL$SQL2008R2DEV";

HashAlgorithm ha = HashAlgorithm.Create("SHA1");
byte[] hash = ha.ComputeHash(serviceName.ToUpper().SelectMany(c => BitConverter.GetBytes(c)).ToArray());

string sid = "S-1-5-80";

for (int i = 0; i < 5; i++)
    sid += "-" + BitConverter.ToUInt32(hash, i * 4);

  2. I suspect the SIDs could be found using classes in the .NET System.DirectoryServices namespace, either by inspecting group membership (fragile), or by some other method. Unfortunately, the methods I tested to directly translate a principal name into a SID failed, so I'm not sure how SQL Server does it (PInvoke?). In any event, computing the hash isn't a big deal if .NET is available (in fairness, the code will be longer without the use of LINQ).

  3. Capture the relevant part of the output of the sc utility as used below, or run this manually if you don't care about being able to automate the solution:

    sc showsid MSSQL$SQL2008R2DEV
Related Question