Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
the short answer to your question is "from the system list of SIDs" which should be found in the registry, at the very least. But the more important question is "why do you need the SIDs?" –  jcolebrand Feb 27 '12 at 15:52
    
We use SID as the primary key for tracking objects and memberships on local systems. For group members that are objects in some domain, we are able to obtain the objectSID attribute of the object which matches what Win Users and Groups displays. –  Nivesh Patnaik Feb 27 '12 at 17:22
    
Using D:\temp\SysinternalsSuite>psgetsid "NT SERVICE\SQLAgent$SQLEXPRESS", I am able to see the SID of the object. However, when I search the server's registry for that value, I cannot find it. –  Nivesh Patnaik Feb 27 '12 at 17:23
    
That is likely because you don't have the local server registry permissions to search for those values. Have you asked someone with domain administrative rights to look for those values for you? –  jcolebrand Feb 27 '12 at 17:36
2  
Let's start over again. Why do you need the SIDs? –  jcolebrand Feb 27 '12 at 21:30
show 5 more comments

2 Answers

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
    
share|improve this answer
add comment

This will not give you the SID, but you at least get the windows account name running the services.

DECLARE @engine varchar(100), @agent varchar(100)

EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',N'ObjectName',@engine OUTPUT,N'no_output'
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT',N'ObjectName',@agent OUTPUT,N'no_output'
SELECT serverproperty('ServerName'),@engine as EngineServiceAccount,@agent as AgentServiceAccount
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.