none
Programmatically determine security context a given SQL Server instance is running under RRS feed

Answers

  • So the issue is getting the account under which the service is running?  You didn't post how you're getting the services but I assume that you're using ServiceController.  But it doesn't provide enough information.  One option is to P/Invoke to Win32 to call the SCM function that returns that info. However I think WMI is a cleaner approach. The returned property is properly formatted for NTAccount.

    static void Main ( string[] args )
    {
        using (var searcher = new ManagementObjectSearcher("root\\CIMV2",
                                "SELECT * FROM Win32_Service"))
        {
            foreach (ManagementObject queryObj in searcher.Get())
            {
                var serviceName = queryObj["DisplayName"] as string;
                var accountName = queryObj["StartName"] as string;
    
                var identity = ToIdentity(accountName);
    
                Console.WriteLine("{0} running as {1} ({2})", serviceName, accountName, identity);
            }
        };
    }
    
    static IdentityReference ToIdentity ( string account )
    {
        if (account == null)
            return new SecurityIdentifier(WellKnownSidType.LocalSystemSid, null);
        else if (String.Compare(account, "LocalSystem", true) == 0)
            return new SecurityIdentifier(WellKnownSidType.LocalSystemSid, null);
    
        //Just for demo purposes force conversion so it'll fail if it isn't valid
        return new NTAccount(account).Translate(typeof(SecurityIdentifier));
    }
    The only 2 cases I've seen it fail are given in the conversion routine.  LSM for some reason returns null for the account.  I suspect this is a security-related so it is pretty easy to special case.  The other is with LocalSystem which actually maps to BUILTIN\SYSTEM.  All other accounts (at least on my machine) work fine.  If you do find that BUILTIN\* accounts aren't coming back right then you could special case them by looking for the machine\domain separator and, if not found, append BUILTIN instead.

    Michael Taylor
    http://msmvps.com/blogs/p3net

    Thursday, March 27, 2014 10:04 PM
    Moderator

All replies

  • We let window handle the groups.  We setup a special user account(s) in windows on the PC where SQL Server is installed for each group access in the database.  Then add each user to the user group in windows.

    jdweng

    Saturday, March 22, 2014 2:17 AM
  • We are presented with customers who don't have IT and our utility needs to discover the account being used by the SQL Server (<instance>) service and then provide the appropriate ACL updates to a given directory which then allows SQL Server access to backup files there.

    We can go through the list of services and get the service properties like such

    http://social.msdn.microsoft.com/Forums/vstudio/en-US/8de5b1d8-0157-47a1-bec6-353370ebcf33/directoryservices-serviceaccountname-networkservice-rather-than-network-service?forum=netfxbcl

    However, once we have that we don't have the account per-se (i.e. cases like NT AUTHORITY\NETWORKSERVICE).  Somehow we need to get to the actual account NT AUTHORITY\NETWORK<space>SERVICE (or whatever localized version).

    Once we have the account it is straight forward to apply an ACL to the folder for the account.

    Now maybe we should also say that NT AUTHORITY\NETWORKSERVICE equals the same account as the .net System.Security.Principal.WellKnownSidType.NetworkServiceSid but something is wrong in either the way we are going about the problem or the way we are understanding the service account naming.


    May we all make money in the sequel.

    Monday, March 24, 2014 1:15 PM
  • Service normally get started when the PC is turned on and the owner is admin which has nothing to do with the credentials or port number.  Even though you customers don't have IT I would think somebody would know how to create a new user account.  I would instruct your cusotmers to create a special user group account with your company name to access the database.

    jdweng

    Monday, March 24, 2014 1:54 PM
  • I couldn't come up with a way other than to perform a select case on the 3 current possibilities.

              Dim san As String = de.Properties.Item("ServiceAccountName").Value.ToString()
    
              Select Case san
                Case "NT AUTHORITY\NETWORKSERVICE"
                  Dim sid As New SecurityIdentifier(WellKnownSidType.NetworkServiceSid, Nothing)
                  Return sid.Translate(GetType(NTAccount))
    
                Case "NT AUTHORITY\LOCALSERVICE"
                  Dim sid As New SecurityIdentifier(WellKnownSidType.LocalServiceSid, Nothing)
                  Return sid.Translate(GetType(NTAccount))
    
                Case "NT AUTHORITY\LOCALSYSTEM"
                  Dim sid As New SecurityIdentifier(WellKnownSidType.LocalSystemSid, Nothing)
                  Return sid.Translate(GetType(NTAccount))
    
                Case Else
                  Return New NTAccount(san)
    
              End Select


    May we all make money in the sequel.

    Monday, March 24, 2014 6:25 PM
  • Not sure the context you're running in but once you have the user account name you can get to the underlying principal that you need for setting ACL without a case statement.  FileSystemAccessRule requires an IdentityReference.  NTAccount implements that and can be passed any user name and it'll properly map it to the corresponding account (irrelevant of whether it is a user account or a builtin account).  If the account doesn't exist then it'll fail when you try to apply the rule though.

    static void Main ( string[] args )
    {
        var path = @"C:\temp\test";
    
        GiveReadAccess(@"MyMachine\LocalUser", path);
        GiveReadAccess(@"MyDomain\NetworkUser", path);
        GiveReadAccess(@"NT AUTHORITY\NETWORKSERVICE", path);
    }
    
    static void GiveReadAccess ( string userName, string path )
    {
        var dir = new DirectoryInfo(path);
    
        var sec = dir.GetAccessControl();
    
        var user = new NTAccount(userName);
    
        var rule = new FileSystemAccessRule(user, FileSystemRights.Read | FileSystemRights.Write, AccessControlType.Allow);
    
        sec.AddAccessRule(rule);
    
        dir.SetAccessControl(sec);
    }

    Michael Taylor
    http://msmvps.com/blogs/p3net

    Thursday, March 27, 2014 8:06 PM
    Moderator
  • Thanks Michael.  The question is mainly how to discover the account under which the SQL Server service is running in a form that can be understood by NTAccount.

    Iterating the list of services on the machine to discover the SQL Server service lead to a list of properties for that service, one of which is "ServiceAccountName".  However, this name isn't always a user account name.  For example if the service is running under the NETWORK SERVICE account the "ServiceAccountName" property will return "NT AUTHORITY\NETWORKSERVICE" where as NTAccount expects "NT AUTHORITY\NETWORK SERVICE".  Bridging that translation was the focus of this question.  For now I've used the select case for the 3 known special values but it sure seems like I'm missing a .net class that makes the translation.


    May we all make money in the sequel.

    Thursday, March 27, 2014 9:25 PM
  • So the issue is getting the account under which the service is running?  You didn't post how you're getting the services but I assume that you're using ServiceController.  But it doesn't provide enough information.  One option is to P/Invoke to Win32 to call the SCM function that returns that info. However I think WMI is a cleaner approach. The returned property is properly formatted for NTAccount.

    static void Main ( string[] args )
    {
        using (var searcher = new ManagementObjectSearcher("root\\CIMV2",
                                "SELECT * FROM Win32_Service"))
        {
            foreach (ManagementObject queryObj in searcher.Get())
            {
                var serviceName = queryObj["DisplayName"] as string;
                var accountName = queryObj["StartName"] as string;
    
                var identity = ToIdentity(accountName);
    
                Console.WriteLine("{0} running as {1} ({2})", serviceName, accountName, identity);
            }
        };
    }
    
    static IdentityReference ToIdentity ( string account )
    {
        if (account == null)
            return new SecurityIdentifier(WellKnownSidType.LocalSystemSid, null);
        else if (String.Compare(account, "LocalSystem", true) == 0)
            return new SecurityIdentifier(WellKnownSidType.LocalSystemSid, null);
    
        //Just for demo purposes force conversion so it'll fail if it isn't valid
        return new NTAccount(account).Translate(typeof(SecurityIdentifier));
    }
    The only 2 cases I've seen it fail are given in the conversion routine.  LSM for some reason returns null for the account.  I suspect this is a security-related so it is pretty easy to special case.  The other is with LocalSystem which actually maps to BUILTIN\SYSTEM.  All other accounts (at least on my machine) work fine.  If you do find that BUILTIN\* accounts aren't coming back right then you could special case them by looking for the machine\domain separator and, if not found, append BUILTIN instead.

    Michael Taylor
    http://msmvps.com/blogs/p3net

    Thursday, March 27, 2014 10:04 PM
    Moderator
  • I guess there is no getting around these "special" cases.  It sure felt like I was missing some part of .NET that married the two (i.e. service accounts and NTAccounts).  Thanks Michael.

    May we all make money in the sequel.

    Friday, March 28, 2014 12:10 PM