Dan's Blog

If you see SID, tell him

2008-03-17T18:29:00Z

As a small addendum to my previous blog on the subject of authenticating users, and checking for administrator privileges, under Windows 2000, XP and Vista, I should add this little note. It turns out, thanks to the sort of heavily industrious testing that's par for the course here at Red Gate, that LogonUser / SSPI has a habit under certain circumstances of accepting invalid logins. When a computer is not on a domain, but a workgroup, credentials validation seems to take a different route with respect to validating the existence of the domain to which the login belongs. Specifically, if it can find a local account matching the username and password from the (username,password,domain) tuple, then if not on a domain, the domain part is often summarily ignored and authentication says "yes, that's fine, this user is acceptable".

I suspect it does this for some good reason. Exactly what that reason is I couldn't say, though I wouldn't be surprised to find that it was something in the area of permitting users to easily access machines on a workgroup provided their usernames and passwords match.

This is all very fine and splendid until one comes to use the login for other purposes. The CreateService API, for example, doesn't find it as amusing as the authentication APIs with regard to accepting invalid domains.

So in order to make the previous authentication code robust, we have to do some more legwork. After succeeding with the LogonUser / SSPI APIs, we need to manually verify the correctness of the domain before treating the user provided credentials as correct.

Once again this solution involved a pinch of Google and a dash of experimentation. On the way I learned a little more about SIDs, which I discussed previously. The string format of a SID, it turns out, is a very literal interpretation of the "black box" contents of the SID data structure. It's described in some detail on MSDN, but briefly each SID is composed as follows:

S-<revision>-<identifier authority>-<first sub authority>-<second sub authority>[-<third sub authority> ... ]

All SIDs to date are revision 1, so we always start "S-1-...". The identifier authority tells us where the SID was originally issued, in broad terms. The subsequent sub authorities are also known as relative identifiers, or RIDs.

Now a user's SID looks like the following:

S-1-5-xx-xxxxxxxxx-xxxxxxxxx-xxxxxxxxx-nnnn

S-1-5 means "SID version 1, issued by "NT Authority" (the originator of pretty much all user, computer and domain SIDs). It turns out that "xx-xxxxxxxxx-xxxxxxxxx-xxxxxxxxx" is the standard format for the RID of a computer or domain. "nnnn" is a number indicating which user we're dealing with on that domain.

Domains and computers themselves have valid SIDs. This is understandable, given that a SID is pretty universal as the identifier used to identify something for security purposes. Can you guess what a computer or domain SID looks like?

S-1-5-xx-xxxxxxxxx-xxxxxxxxx-xxxxxxxxx

That's right - we just lop the last sub-authority off the end.

What are the magic numbers in the "xx-xxxxxxxxx-...." portion? Well, they're unique to the computer or domain in question. A computer's SID is generated when Windows is installed, and stay the same for its lifetime. (This has actually led to issues in companies which literally clone machines for deployment purposes; they'd end up with identical SIDs, leading to confusion between users and other fun. SysInternals provide an handy utility to change a computer's SID in this sort of situation.) A domain's SID is generated when the domain is set up. I suspect that it is the computer SID of the first (chronologically) domain controller on the domain (the primary domain controller in pre Windows 2000 terms, before domain controllers started taking joint and several liability for domain security, particularly assigning SIDs) but I haven't got any evidence for that. Certainly, in the good old days, the primary and backup domain controllers had to have the same computer ID, which would seem to fit.

The upshot of this discussion is that once a user is logged in it's quite easy to determine whether the account in question is a user account or a domain account, provided one has the SIDs for the domain or computer. One simply lops off the last "-nnnn" portion of the SID, and then can compare SIDs either the nice way via the EqualSID() API or, if you're feeling hacky, by strcmp'ing the SID strings.

When looking at this problem initially, my plan was to do just that. Initially I couldn't find an easy way to get hold of a computer or domain SID. I then tripped over the psgetsid tool from SysInternals.

This tool is capable of converting account, machine and Windows domain names to SIDs, and SIDs to names, on local or remote computers. A quick dumpbin revealed how it does it:

C:\> vcvars32.bat
Setting environment for using Microsoft Visual Studio 2005 x86 tools.
C:\> dumpbin d:\data\sysinternals\psgetsid.exe /imports | more
Microsoft (R) COFF/PE Dumper Version 8.00.50727.762
Copyright (C) Microsoft Corporation. All rights reserved.

Dump of file d:\data\sysinternals\psgetsid.exe

File Type: EXECUTABLE IMAGE

Section contains the following imports:
...
    ADVAPI32.dll
                40A000 Import Address Table
                40A7C4 Import Name Table
                     0 time date stamp
                     0 Index of first forwarder reference

                  140 IsValidSid
                  116 GetSidIdentifierAuthority
                  119 GetSidSubAuthorityCount
                  118 GetSidSubAuthority
                   1D AllocateAndInitializeSid
                   AF DeleteService
                   42 ControlService
                  1AD OpenSCManagerA
                  1AF OpenServiceA
                  249 StartServiceA
                  1C3 QueryServiceStatus
                   64 CreateServiceA
                   3E CloseServiceHandle
                  149 LookupAccountSidA
                  147 LookupAccountNameA

LookupAccountSid and LookupAccountName are the important APIs here. LookupAccountSid takes a SID and returns its name. LookupAccountName takes a name and returns its SID. In both cases invalid names/SIDs are picked up.

This last property made my job even simpler. All I need to know is whether the domain part of the user supplied (username,password,domain) is in some sense valid. So all I need to do is ti call LookupAccountName to fetch the SID for that domain or computer. If it fails, then I reject the login.

To get this to work, I added the following method to my security classes (adapted, as always, from the internet via cut and paste, followed by some tidying and/or bug fixing):

        public static void GetSidForAccountOrDomain(string strAccountName,
             out string accountSid, out string strDomainName,
             out short AccountType)
        {
            int lSidSize;
            int lDomainNameSize;
            IntPtr Sid = IntPtr.Zero;
            string strServer = null;

            // First get the required buffer sizes for SID and domain name.
            if (!NativeSecurityApis.LookupAccountName(
                                strServer,
                                strAccountName,
                                Sid,
                                ref lSidSize,
                                null,
                                ref lDomainNameSize,
                                ref AccountType))
            {
                if (Marshal.GetLastWin32Error() == NativeSecurityApis.ERROR_INSUFFICIENT_BUFFER)
                {
                    // Allocate the buffers with actual sizes that are required
                    // for SID and domain name.
                    strName = new StringBuilder(lDomainNameSize);
                    Sid = Marshal.AllocHGlobal(lSidSize);
                    if (!NativeSecurityApis.LookupAccountName(
                              strServer,
                              strAccountName,
                              Sid,
                              ref lSidSize,
                              strName,
                              ref lDomainNameSize,
                              ref AccountType))
                        throw new Win32Exception(); // last error
                }
                else
                    throw new Win32Exception(); // last error
            }
            else
                throw new InvalidOperationException("Expected LookupAccountName to fail given no buffers"); // shouldn't get here

            strDomainName = strName.ToString();

            IntPtr pString;
            if (!NativeSecurityApis.ConvertSidToStringSid(Sid, out pString))
                throw new Win32Exception(); // last error

            accountSid = Marshal.PtrToStringAuto(pString);
            NativeSecurityApis.LocalFree(pString);

            //Console.WriteLine("Domain Name: {0}", strDomainName);
            //Console.WriteLine("Account Sid: {0}", sidText);
            Marshal.FreeHGlobal(Sid);
        }
    }

Then, a more than elementary wrapper to ensure that a given domain is valid:

        private static void VerifyDomain(IntPtr hToken, string domain)
        {
            if (string.IsNullOrEmpty(domain) || domain == ".")
                return; // don't attempt to verify null or local (BUILTIN\.) domain

            // attempt to look up the SID of the "domain", be it an actual domain
            // or a computer. If we succeed, then it's valid and LogonUser() should
            // pick
            try
            {
                string domainSid;
                string canonicalDomainName;
                short accountType;
                GetSidForAccountOrDomain(domain, out domainSid, out canonicalDomainName, out accountType);
            }
            catch (Exception ex)
            {
                throw new LogonException(string.Format("Cannot verify domain {0} is valid.", domain), ex);
            }
        }

Then I simply modified the IsUserAdmin() function described in my previous entry, so it now reads as follows:

        public static bool IsUserAdmin(string userName, string domain, string password)
        {
            IntPtr hToken;
            if (NativeSecurityApis.LogonUser(userName, domain, password, NativeSecurityApis.LOGON32_LOGON_INTERACTIVE, NativeSecurityApis.LOGON32_PROVIDER_DEFAULT, out hToken))
            {
                try
                {
                    VerifyDomain(hToken, domain);

                    return IsUserAdmin(hToken);
                }
                finally
                {
                    NativeSecurityApis.CloseHandle(hToken);
                }
            }
            else
            {
                try
                {
                    WindowsPrincipal principal = Win32SSPI.LogonUser(userName, domain, password);

                    IntPtr hPrincipalToken = (principal.Identity as WindowsIdentity).Token;
                    VerifyDomain(hPrincipalToken, domain);

                    return IsUserAdmin(principal);
                }
                catch (Exception ex)
                {
                    throw new LogonException(ex.Message, ex);
                }
            }
        }

So that was pretty straightforward. At least, relative to the prior steps I describe in my previous blog entries. So far the C# code to authenticate a user, and check that they are an administrator, is still sub 1000 lines including whitespace, comments and API import definitions. Cheap at the price?

SID. Vicious?

2008-03-17T18:27:00Z

Some activities on any operating system fall into that category of "should be extraordinarily simple, and yet is full of the sort of pitfalls that cause headaches, confusion and (at least in my case) bouts of cursing and ranting".

My favourite of the moment is a simple security task: authenticating credentials provided by the user to ensure they are valid; and detecting programmatically if an authenticated user is an administrator. The fun-inducing caveat: this code has to work on Windows 2000, XP and Vista.

I'll give a few code examples in this article. A couple of caveats: firstly, none but the last will be the complete and correct solution, so quick cutting and pasting may be inadvisable. Secondly, the code is C# and will assume the existence of a class called NativeSecurityApis in which all the relevant native API declarations are located. For those needing to create such a class, I recommend the pinvoke.net website.

So, let's pretend we know nothing about this task, and type appropriate phrases into Google. Most of the code samples and advice one will find explain how to check if the current user is an administrator. In .NET this is triviality itself:

        public static bool IsUserAdmin()
        {
            return new WindowsPrincipal(WindowsIdentity.GetCurrent()).IsInRole(WindowsBuiltInRole.Administrator);
        }

Which is splendid. Two problems here: we are checking the current user, not the user whose user and password we have to hand; and, this code doesn't work on Windows Vista. We'll come back to that later.

The Simple Approach

Leaving aside the latter problem for the moment, let's focus on the former problem. Given a username and password, how do we authenticate that user?

Google again provides. We call the LogonUser() API. This takes a user name, domain, and password, and returns a handle to a token representing that user; or, if the login failed, an error. Super. Then we have a choice: we can either temporarily "become" that user via impersonation, and then use the above to check whether "we" are an administrator, and then revert back to being ourselves; or we use the WindowsIdentity constructor which takes the sort of token we have just acquired, and check that user. Impersonation is a useful thing to know about, but strictly speaking isn't relevant here. So we should take the second route.

Slight obstacles: firstly, how do we robustly turn a user-supplied username and password into the triple required by LogonUser: user name, domain, password. Secondly, LogonUser is not provided by .NET, so we have to PInvoke our way to the underlying API. Thirdly, it's badly implemented on Windows 2000, such that you have to more or less be SYSTEM in order for it to ever work, and is therefore useless for this purpose.

Let's leave the latter two aside (again) for the moment, and press on. So let's imagine we've asked the user for a username and password. The user may have supplied a username in one of these formats:

"UserName" (unqualified; local user name)
"DOMAIN\UserName" (old style)
"UserName@DOMAIN" (so called "UPN format")

Can we just supply all of these these to LogonUser, with a null domain string, and have it work? I'll give you a clue: no. So, which of these can we just supply these to LogonUser? Well, number three is fine. As the API documentation suggests, UPN fomat is accepted if you specify a doman of null. The others aren't supported. What, you say, supplying "UserName" on its own is not allowed? That's right. In the world of Windows Security, the domain for the local machine can be expressed as ".", but not as null. And in the second case, you have to manually strip the domain out yourself and pass it in as a separate parameter, as LogonUser isn't bright enough to do this for you.

Another incidental bit of fun is that if you are, as I was, doing all this in an installer which also has to install a Windows Service using .NET's AssemblyInstaller classes, you'd better steer clear of UPN format, as usernames not in "DOMAIN\UserName" format will cause exceptions courtesy of tht component. Fun!

So either way, time to write some highly trivial but irritating code to split out a (username,password) tuple into a (username,domain,password) tuple.

        public static string GetDomain(ref string userName, bool includeUPNFormat)
        {
            string[] domainAndUser = userName.Split(new char[] { '\\' });
            if (domainAndUser.Length > 2)
                throw new ArgumentException("Username format incorrect.");

            string domain = null;
            if (domainAndUser.Length == 2)
            {
                domain = domainAndUser[0];
                userName = domainAndUser[1];
            }
            else if (includeUPNFormat)
            {
                domainAndUser = userName.Split(new char[] { '@' });
                if (domainAndUser.Length > 2)
                    throw new ArgumentException("Username format incorrect");

                if (domainAndUser.Length == 2)
                {
                    domain = domainAndUser[1];
                    userName = domainAndUser[0];
                }
            }

            if (domain == null)
                domain = ".";

            return domain;
        }

That little bit of fun over with, we can now call LogonUser(). If the credentials are valid we get a token back. If they're not, we get an error. Super.

Then it only remains to check whether the user corresponding to our nice token is an administrator, as already described; and our job is done.

        public static bool IsUserAdmin(string userName, string password)
        {
            string name = userName;
            string domain = GetDomain(ref name, true);

            return IsUserAdmin(name, domain, password);
        }

        public static bool IsUserAdmin(string userName, string domain, string password)
        {
            IntPtr hToken;
            if (NativeSecurityApis.LogonUser(userName, domain, password, NativeSecurityApis.LOGON32_LOGON_INTERACTIVE, NativeSecurityApis.LOGON32_PROVIDER_DEFAULT, out hToken))
            {
                try
                {
                    return new WindowsPrincipal(new WindowsIdentity(hToken)).IsInRole(WindowsBuiltInRole.Administrator);
              }
                finally
                {
                    NativeSecurityApis.CloseHandle(hToken);
                }
            }
            else
            {
                    throw new Win32Exception(); // last error
            }
        }

Coping with Windows 2000

Now as noted previously, if your code has to work on Win2K, then your headaches aren't quite over. On Win2K, LogonUser is implemented via the low level API call LsaLogonUser, the user owning the calling process is required to have a grubby privilege called SeTcbPrivilege, otherwise known as "Act as part of the operating system". This is because LsaLogonUser allows some rather nasty things to be done other than just checking credentials. Very few Windows processes (those running as SYSTEM) are likely to have this privilege; everyone else doesn', for obvious security reasons, and so therefore can't call LogonUser.

Microsoft's suggested workaround is to use SSPI authentication instead of calling LogonUser. This is a very protracted client/server authentication approach designed for authenticating users remotely (such as over a SQL Server connection which uses "Windows Authentication"). For once, it's quite a lot of grubby native code to call, but in .NET 2.0, the lovely NegotiateStream class was added which is capable of executing the right manoevres for us.

One may ask why, if SSPI authentication works on Windows 2000 and above, we shouldn't just always use it instead of LogonUser? Well, one argument is because of its particular behaviour w.r.t. the "Guest" account on Windows XP and above. As noted in http://support.microsoft.com/default.aspx?scid=kb;EN-US;180548 SSPI may always try and logon as "Guest", or indeed do no authentication at all and just claim to have logged in, depending on registry settings. This would make our attempt to validate user credentials rather academic. SSPI should therefore be a fallback approach, not a replacement for calling LogonUser where we can.

Now I've seen a code example bandied around the internet which purports to be "the" way to get this to work, but I found it had several problems. Firstly it wasn't reliably callable more than once; especially if an authentication attempt actually failed. If you're calling from an application which has any kind of UI, this is problematic. Secondly, its use of asynchronous calls meant that it had a nasty race condition which made itself particularly manifest on successive authentication attempts. Here's a fixed up version which doesn't exhibit these problems:

    internal class Win32SSPI
    {
        private static readonly TcpListener tcpListener = new TcpListener(IPAddress.Loopback, 0);

        static Win32SSPI()
        {
            tcpListener.Start();
        }

        /// <summary>
        /// Logon user using SSPI authentication.
        /// </summary>
        /// <param name="userName">The username (without domain qualifications, so no DOMAIN\user or user@DOMAIN here)</param>
        /// <param name="domain">The domain name (or ".")</param>
        /// <param name="password">The password.</param>
        /// <returns>A valid WindowsPricipal. Throws an exception on failure.</returns>
        public static WindowsPrincipal LogonUser(string userName, string domain, string password)
        {
            try
            {
                // need a full duplex stream - loopback is easiest way to get that
                WindowsIdentity id = null;
                AutoResetEvent waitEvent = new AutoResetEvent(false);
                IAsyncResult result = tcpListener.BeginAcceptTcpClient(delegate(IAsyncResult asyncResult)
                {
                    try
                    {
                        using (NegotiateStream serverSide = new NegotiateStream(
                            tcpListener.EndAcceptTcpClient(asyncResult).GetStream()))
                        {
                            serverSide.AuthenticateAsServer(CredentialCache.DefaultNetworkCredentials,
                                                             ProtectionLevel.None, TokenImpersonationLevel.Impersonation);
                            id = (WindowsIdentity)serverSide.RemoteIdentity;

                        }
                    }
                    catch (Exception)
                    {
                        // ack.
                    }
                    finally
                    {
                        waitEvent.Set();
                    }
                }, null);

                using (NegotiateStream clientSide = new NegotiateStream(new TcpClient("localhost",
                                                                                         ((IPEndPoint)tcpListener.LocalEndpoint).Port).GetStream()))
                {
                    clientSide.AuthenticateAsClient(new NetworkCredential(userName, password, domain),
                                                     "", ProtectionLevel.None, TokenImpersonationLevel.Impersonation);
                }

                waitEvent.WaitOne();
                waitEvent.Close();

                if (id != null)
                    return new WindowsPrincipal(id);

                throw new LogonException("Cannot authenticate user using SSPI.");
            }
            catch (Exception ex)
            {
                throw new LogonException("Cannot authenticate user using SSPI.", ex);
            }
        }
     }

So, now we can adjust our IsUserAdmin() function to try this approach if LogonUser fails. Since it will fail on Windows 2000, this gives us a working fallback position without having to write any "what OS version is this" type code (which is notoriously a bad idea).

         public static bool IsUserAdmin(string userName, string domain, string password)
        {
            IntPtr hToken;
            if (NativeSecurityApis.LogonUser(userName, domain, password, NativeSecurityApis.LOGON32_LOGON_INTERACTIVE, NativeSecurityApis.LOGON32_PROVIDER_DEFAULT, out hToken))
            {
                try
                {
                    return new WindowsPrincipal(new WindowsIdentity(hToken)).IsInRole(WindowsBuiltInRole.Administrator);
                }
                finally
                {
                    NativeSecurityApis.CloseHandle(hToken);
                }
            }
            else
            {
                try
                {
                    WindowsPrincipal principal = Win32SSPI.LogonUser(userName, domain, password);
                    return prinicipal.IsInRole(WindowsBuiltInRole.Administrator);
                }
                catch (Exception ex)
                {
                    throw new LogonException(ex.Message, ex);
                }
            }
        }

UAC, one can log Coping with Windows Vista

And now we come to the real fun. Windows Vista introduced UAC (User Account Control) for reasons which are outside the scope of this article. Under UAC, one can log in as a user who is, in theory, an administrator, but not actually have administrator privileges until they are absolutely required (because some application is about to do something which needs them). Only then is the user "elevated" to having full administrative rights.

This has a consequence for our authentication code: it no longer works. On Vista, calling WindowsPrincipal.IsInRole(WindowsBuiltInRole.Administrator) will return false unless the user is currently elevated to proper administrator status. If we've just come from a LogonUser call, they won't be so elevated; nor do we really want to attempt to elevate them (even if this were feasible, which it really isn't). So how do we check if the user is, in theory, an administrator, despite the fact that they aren't currently administrating?

Google provides a bunch of code snippets resembling the following:

                string sddlAdmin = "S-1-5-32-544"; //Sid of administrators group
                IdentityReference adminSid = new SecurityIdentifier(sddlAdmin);
                if (principal.Identity is WindowsIdentity &&
                     ((WindowsIdentity)principal.Identity).Groups.Contains(adminSid))
                {
                    return true;
                }

Which is excellent, except for the fact that this doesn't have a snowball's chance in hell of working either. This code is simply a protracted way of calling the same APIs as WindowsPrincipal.IsInRole(). Doomed to failure.

There are native APIs which look like they should help: IsUserAdmin(), previously an undocumented and unsupported internal API, is now a documented but very-likely-to-become-obsolete public API. Likewise the CheckTokenMembership() API which can check if a user is an administrator, as follows:

         public static bool IsImpersonationTokenUserAdmin(IntPtr hToken)
        {
            bool success;
            IntPtr pNTAuthority = Marshal.AllocHGlobal(Marshal.SizeOf(NativeSecurityApis.SID_IDENTIFIER_AUTHORITY.SECURITY_NT_AUTHORITY));
            Marshal.StructureToPtr(NativeSecurityApis.SID_IDENTIFIER_AUTHORITY.SECURITY_NT_AUTHORITY, pNTAuthority, false);

            try
            {
                IntPtr pSidAdministratorsGroup;

                success = NativeSecurityApis.AllocateAndInitializeSid(
                        pNTAuthority,
                        2,
                        NativeSecurityApis.SECURITY_BUILTIN_DOMAIN_RID,
                        NativeSecurityApis.DOMAIN_ALIAS_RID_ADMINS,
                        0, 0, 0, 0, 0, 0,
                        out pSidAdministratorsGroup);
                try
                {
                    if (success)
                    {
                        if (!NativeSecurityApis.CheckTokenMembership(hToken, pSidAdministratorsGroup, out success))
                        {
                            success = false;
                        }
                    }
                }
                finally
                {
                    NativeSecurityApis.FreeSid(pSidAdministratorsGroup);
                }
            }
            finally
            {
                Marshal.FreeHGlobal(pNTAuthority);
            }

            return success;
        }

But this equally turns out to be damned all use. All routes are answering the same question: "is this user currently an administrator". Not, as we want, "does this user have the theoretical capacity to be an administrator". What to do?

After much painful searching, I can across this article: http://www.microsoft.com/technet/technetmag/issues/2007/06/ACL/default.aspx which explains the changes to the Windows security APIs in Vista. I'd been asking myself "what's the difference between the token for an adminstrator under XP, and the token for an administrator under Vista?" And this article provides the answer. It all comes down to SIDs and attributes.

What's a SID? Well, to quote from the Microsoft Knowledge base:

"A security identifier (SID) is a unique value of variable length that is used to identify a security principal or security group in Windows operating systems. Well-known SIDs are a group of SIDs that identify generic users or generic groups. Their values remain constant across all operating systems." I'd add that SIDs have an obscure binary format, and a more readily readable string format.

How is this relevant to us? Well, here I'll refer you to MSDN for a decent explanation - http://msdn2.microsoft.com/en-us/library/aa374862(VS.85).aspx - but briefly, a logged in user is assigned an access token, which contains a set of SIDs (security IDs) corresponding to the various groups of which that user is a member, and a set of privileges which the user has. Securable "things" such as files have access control lists (ACLs) which allow or deny various different SIDs access to the thing in question. When it needs to know if a user has permission on some object, Windows runs through these lists in tandem; as soon as it hits a relevant "deny" entry, or if it doesn't find any "allow" entries, then access is denied; otherwise, it's allowed.

SIDs are usually accompanied by flags (known as attributes) in a SID_AND_ATTRIBUTES structure. Generally the SIDs associated with a user's access token are "positive" flags, if you like: they list groups of which that user is a member. Here, for example, is a dump of the SIDs I have when logged into my own XP machine, with their display names, SID strings, and corresponding attributes in text format:

"FOO\Domain Users" S-1-5-21-xxxxxxxxx-xxxxxxxxx-xxxxxxxxx-xxxx Mandatory EnabledByDefault Enabled
"Everyone" S-1-1-0 Mandatory EnabledByDefault Enabled
"MYMACHINE\Debugger Users" S-1-5-21-xxxxxxxxx-xxxxxxxxx-xxxxxxxxx-xxxx Mandatory EnabledByDefault Enabled
"BUILTIN\Administrators" S-1-5-32-544 Mandatory EnabledByDefault Enabled Owner
"BUILTIN\Users" S-1-5-32-545 Mandatory EnabledByDefault Enabled
"NT AUTHORITY\INTERACTIVE" S-1-5-4 Mandatory EnabledByDefault Enabled
"NT AUTHORITY\Authenticated Users" S-1-5-11 Mandatory EnabledByDefault Enabled
"LOCAL" S-1-2-0 Mandatory EnabledByDefault Enabled
"FOO\SoftwareDeveloper" S-1-5-21-xxxxxxxxx-xxxxxxxxx-xxxxxxxxx-xxx Mandatory EnabledByDefault Enabled
"FOO\UserOfVirtualMachinesInSomeWay" S-1-5-21-xxxxxxxxx-xxxxxxxxx-xxxxxxxxx-xxx Mandatory EnabledByDefault Enabled
"BAR\LargeAdministrativeCheese" S-1-5-21-xxxxxxxxx-xxxxxxxxx-xxxxxxxxx-xxx Mandatory EnabledByDefault Enabled

Where you see "xxxx", replace with an arbitrary sequence of numbers particular to my current domain.

So you can see I'm a user on a domain; I'm an administrator; I've been properly authenticated; I'm a member of a group of people who write software for a living; and I also log on to virtual machines, and can administer the BAR domain with impunity.

If you want to try this on your own machine, you can download the "whoami" tool which is part of the "Windows XP Service Pack 2 Support Tools" from Microsoft. It gives you all of the above except for the permission flags.

To see the same on Windows Vista, we can run the bult in "whoami" command, which produces rather similar output. The key difference is as follows:

"BUILTIN\Administrators" S-1-5-32-544 UseForDenyOnly

This is the essential difference between an "un-elevated" administrator on Vista, and an administrator on 2000 and XP. Previously the administrator had the BUILTIN\Administrators SID enabled. In Vista pre elevation, administrators have the BUILTIN\Administrators SID but set to "deny only". Hence the lack of actual administrative powers until elevated.

This proves to be about the only way we can tell an un-elevated administrator on Vista from a standard user, who will not exhibit the BUILTIN\Administrators SID at all.

So we have something we can check on Vista. How do we go about it? Well, we end up rewriting our existing IsUserAdmin(username,domain,password) method as follows:

         public static bool IsUserAdmin(string userName, string domain, string password)
        {
            IntPtr hToken;
            if (NativeSecurityApis.LogonUser(userName, domain, password, NativeSecurityApis.LOGON32_LOGON_INTERACTIVE, NativeSecurityApis.LOGON32_PROVIDER_DEFAULT, out hToken))
            {
                try
                {
                    return IsUserAdmin(hToken);
                }
                finally
                {
                    NativeSecurityApis.CloseHandle(hToken);
                }
            }
            else
            {
                try
                {
                    WindowsPrincipal principal = Win32SSPI.LogonUser(userName, domain, password);
                    return IsUserAdmin(principal);
                }
                catch (Exception ex)
                {
                    throw new LogonException(ex.Message, ex);
                }
            }
        }

We'll add another helper overload for the case where, courtesy of the SSPI authentication for Windows 2000, we have a WindowsPrincipal rather than a token:

        public static bool IsUserAdmin(WindowsPrincipal principal)
        {
            WindowsIdentity identity = principal.Identity as WindowsIdentity;
            return IsUserAdmin(identity.Token);
        }

And we can then write the function underlying both of these overloads:

        public static bool IsUserAdmin(IntPtr hToken)
        {
            string adminSid = NativeSecurityApis.STRING_SID_BUILTIN_ADMINISTRATORS; // "S-1-5-32-544"
            IntPtr pTokenGroups = GetTokenGroups(hToken);
            try
            {
                foreach (SidAndAttributes sid in GetTokenGroupStringSids(pTokenGroups))
                {
                    if (StringComparer.InvariantCultureIgnoreCase.Compare(sid.SidText, adminSid) == 0)
                    {
                        if (sid.IsGroupEnabled /* what we'd normally expect */||
                             sid.IsGroupUseForDenyOnly /* Vista: present but is deny only */ )
                        {
                            return true;
                        }
                    }
                }

                return false; // no admin SID, or not enabled and not deny only.
            }
            finally
            {
                FreeTokenGroups(pTokenGroups);
            }
        }

This function uses a lot of helper mojo which we haven't defined yet, but demonstrates the basic algorithm. We acquire the set of user groups associated with the authenticated user's token; we iterate through them looking for the BUILTIN\Administrator SID; and we count the user as an administrator if the SID's attributes are either "enabled" (2000, XP) or "deny only" (Vista).

To get the token's groups, we employ the following methods. We call the GetTokenInformation() API which can pull out lots of interesting things about a user's token.

        private static IntPtr GetTokenGroups(IntPtr hToken)
        {
            uint dwSize = 0;
            if (!NativeSecurityApis.GetTokenInformation(hToken, NativeSecurityApis.TOKEN_INFORMATION_CLASS.TokenGroups, IntPtr.Zero, dwSize, out dwSize))
            {
                if (Marshal.GetLastWin32Error() != NativeSecurityApis.ERROR_INSUFFICIENT_BUFFER)
                    throw new Win32Exception();
            }

            IntPtr pTokenGroups = Marshal.AllocHGlobal((int)dwSize);
            try
            {
                if (!NativeSecurityApis.GetTokenInformation(hToken, NativeSecurityApis.TOKEN_INFORMATION_CLASS.TokenGroups, pTokenGroups, dwSize, out dwSize))
                    throw new Win32Exception();

                return pTokenGroups;
            }
            catch (Exception)
            {
                Marshal.FreeHGlobal(pTokenGroups);
                throw;
            }
        }

        private static void FreeTokenGroups(IntPtr pTokenGroups)
        {
            Marshal.FreeHGlobal(pTokenGroups);
        }

Essentially we call GetTokenInformation() to find out how much memory we need to allocate for a copy of the token group information; then we allocate said memory and call the API again to fetch the information.

The token groups we've retrieved are defined in the platform SDK as follows:

typedef struct _TOKEN_GROUPS {
  DWORD GroupCount;
  SID_AND_ATTRIBUTES Groups[ANYSIZE_ARRAY];
} TOKEN_GROUPS, 
 *PTOKEN_GROUPS;

typedef struct _SID_AND_ATTRIBUTES {
  PSID Sid;
  DWORD Attributes;
} SID_AND_ATTRIBUTES, 
 *PSID_AND_ATTRIBUTES;

So we have a memory block which contains a group count, then a SID for each group with an accompanying set of attribute flags. We can treat the contents of the SID as a black box, since all we need to do is to be able to compare these SIDs against the standard SID for BUILTIN\Administrators; if we find a match, we then ensure that the SID's attributes include either SE_GROUP_ENABLED or SE_GROUP_USE_FOR_DENY_ONLY.

A caveat in dealing with the above is that in the platform SDK these structures are not explicitly packed. The compiler will therefore align each field on the nearest n byte boundary where n is 4 on 32 bit systems and 8 on 64 bit systems. Consequently on 64 bit systems, their layout in memory equivalent to the following:

#pragma pack(push,1) 
typedef struct _TOKEN_GROUPS_64 {
  DWORD GroupCount;
  DWORD __Unused;
  SID_AND_ATTRIBUTES_64 Groups[ANYSIZE_ARRAY];
} TOKEN_GROUPS, 
 *PTOKEN_GROUPS;

typedef struct _SID_AND_ATTRIBUTES_64 {
  PSID Sid;
  DWORD Attributes;
  DWORD __Unused;
} SID_AND_ATTRIBUTES, 
 *PSID_AND_ATTRIBUTES;
#pragma pack(pop)

Unfortunately there's no magic we can insert into a C# structure definition to say "Align this structure in the same way it would be aligned in C++ by default". So when reading this information I chose to just read it in an IntPtr/Int32 at a time using the Marshal class. One could equally define multiple versions of structures with different packing and switch between them based on platform/sizeof(IntPtr).

I created the following simple wrapper to hold a SID and its attributes:

    internal class SidAndAttributes
    {
        private readonly string m_SidText;
        private readonly int m_Attributes;

        public SidAndAttributes(IntPtr pSid, int attributes)
        {
            IntPtr pString;
            if (!NativeSecurityApis.ConvertSidToStringSid(pSid, out pString))
                throw new Win32Exception(); // last error

            m_SidText = Marshal.PtrToStringAuto(pString);
            NativeSecurityApis.LocalFree(pString);

            m_Attributes = attributes;
        }

        public string SidText
        {
            get { return m_SidText; }
        }

        public int Attributes
        {
            get { return m_Attributes; }
        }

        public bool IsGroupEnabled
        {
            get { return (m_Attributes & NativeSecurityApis.SE_GROUP_ENABLED) == NativeSecurityApis.SE_GROUP_ENABLED; }
        }

        public bool IsGroupUseForDenyOnly
        {
            get { return (m_Attributes & NativeSecurityApis.SE_GROUP_USE_FOR_DENY_ONLY) == NativeSecurityApis.SE_GROUP_USE_FOR_DENY_ONLY; }
        }
}

And used the following method to traverse the TOKEN_GROUPS structure and read its array of SID_AND_ATTRIBUTES structures into an IEnumerable<SidAndAttributes>:

        private static IEnumerable<SidAndAttributes> GetTokenGroupStringSids(IntPtr pTokenGroups)
        {
            List<SidAndAttributes> list = new List<SidAndAttributes>();
            if (pTokenGroups == IntPtr.Zero)
                return list;

            int groupCount = Marshal.ReadInt32(pTokenGroups, 0);

            // read in SID_AND_ATTRIBUTES items.
            // Due to the way these are packed, sizeof(SID_AND_ATTRIBUTES) and sizeof(TOKEN_GROUPS) varies
            // depending on whether the platform is 32 or 64 bit.
            //
            long sizeof_Int32 = Marshal.SizeOf(typeof(Int32));
            long sizeof_IntPtr = Marshal.SizeOf(typeof(IntPtr));

            long offset = (long)pTokenGroups;
            offset += Marshal.SizeOf(typeof(Int32));

            if (Marshal.SizeOf(typeof(IntPtr)) != Marshal.SizeOf(typeof(Int32)))
                offset += sizeof_Int32; // extra padding on Win64

            for (int iGroup = 0; iGroup < groupCount; iGroup++)
            {
                IntPtr pSid = Marshal.ReadIntPtr((IntPtr)offset);
                offset += sizeof_IntPtr;

                int attributes = Marshal.ReadInt32((IntPtr)offset);
                offset += sizeof_Int32;

                if (Marshal.SizeOf(typeof(IntPtr)) != Marshal.SizeOf(typeof(Int32)))
                    offset += sizeof_Int32; // extra padding on Win64

                SidAndAttributes item = new SidAndAttributes(pSid, attributes);
                list.Add(item);
            }

            return list;
        }
    }

And finally, we're done.

Conclusion

I don't see any reason why this entire task can't be made courtesy of a single API call. On Windows XP, it requires a handfull of calls; on Windows 2000, a large sequence (hidden by C#, thankfully) of mandatory but rather tangential calls; on Windows Vista, less than a dozen API calls to check something which is in fact little more than an artifact of the implementation of UAC, there being no obvious other route to take. In any case, there is no reason why information on how to actually validate user credentials, and check whether that user account has administrator privileges, should be difficult to track down or comprehend. Hopefully this article somewhat addresses that issue.

Step up to Red Alert!

2008-02-05T19:12:00Z

"Step up to Red Alert!"
"Sir, are you absolutely sure? It does mean changing the bulb..."
-- Red Dwarf

Computers are stupid. This is a well established fact, which has been en-harped on by much greater and perspicacious authorities than I. They, that is, computers, tend to blindly and relentlessly do what they're told. Given conflicting, asymptotic or just plain dumb instructions the machine will blindly and relentlessly attempt to execute said instructions with blindingly relentless blindness, and without relenting.

But despite this we have come to rely on them utterly. Many centuries ago, a lone imbicile might work away in his hut for many, many years, perchance to dream of such levels of productive, innovative dullardry. Even the Industrial Revolution didn't come close, even having conceived the idea that if one were to shepherds right-thinking imbiciles together from a large area into a sort of pen, or congregation, then their productivity would greatly increase. Certainly the noise level and the smell increased, and these were signs that the humours were ticking over nicely, thereby giving the industrialist a warm feeling of success and busy-ness, ne business. But, alack, no cluster of imbiciles savant, however skilled, can replace a computer's ability for quickly and unthinkingly doing as instructed, even if it takes said machine into the valley, so to speak, of the shadow of death. Wherein lie monsterous hazards known by obscure, demonic names: O'exate'oofor'ooofyve, Ess'teegee'eee'filenotfound, Bee'ess'o'dy.

Which almost brings me to my point, and although I hesitate to arrive so alacritously having only taken a more or less single and relatively brief tangent, this may be universally judged to be a good thing. Computers are stupid, and given the chance will rush headlong into the abyss without so much as a by your leave. And since they're capable of making mistakes at such high velocity (bing-bing-bing-bing, 3.5 billion mistakes per second without touching the sides) it can be a hard task to keep them on the straight and narrow. To shepherd them, as it were, through the valley. To catch the cogs flying from the difference engine before they decapitate an innocent passer by.

There are many professionals whose job it is to do just that, for SQL Servers. And it can be a difficult and thankless task. DBAs, and other professions sharing similar responsibilities, have to make sure that the SQL Servers in their charge are rattling along in maximum Maytag mode without killing anyone and without too many bits falling off in the process. And this, usually, "at the same time" as they are performing their other duties, which already consume a full working day's worth of effort.

SQL Server itself isn't much help. It's quite happy to answer questions as to how well it's doing in, say, executing overnight jobs which were backing up important databases. But, for preference, it would rather play 20 questions with you ("Did this job go well?", "No", "Was it a backup?", "Yes", "Animal, vegetable or mineral?") than give you a quick summary of what went well, or more importantly what went badly, in the last N hours. And should you be faced with a small room of these things, let alone an enterprise, then your chances of extracting some sense from the ensemble each morning, just to make sure that no servers caught fire over night and that all your backups actually took place, are drastically reduced.

Enter SQL Response. A new tool from Red Gate, and a continued foray into the area of tools focused towards DBAs, SQL Response is part watchman, part messenger and part coroner; part missile warning system and part CSI investigation team.

Installed in a convenient nook from whence it can monitor nearby SQL Servers, SQL Response keeps a watchful eye on your SQL Servers looking for problems. Problems, as far as SQL Response is concerned, are things which are really likely to spoil your day as a DBA. A SQL Server which appears to be down is one obvious candidate. Jobs which have failed are another. Yet another is a disk drive looking very full. A blocking or deadlocked process. A job which worked, but took longer than expected. And so on.

Being a Red Gate tool, SQL Response doesn't just cover the obvious cases. How about a job which missed its run because the SQL Agent is disabled? Or which should have run, but couldn't because it's scheduled to run every half hour and the previous run took 31 minutes? If a disk drive is full, then is it the system (Windows) drive, or a drive used by a SQ Server database? Is that SQL Server down, or did the login just fail? And is the Windows box itself down?

More than just alerting you to the fact that the problem has occurred, SQL Response tries to help you find out why the problem occurred by looking at the "scene of the crime". So whilst it's firing off an e-mail to you telling you about the problem, it's simutaneously gathering up information on SQL performance, SQL connections, running Windows processes. Even, if requested, the SQL code running around the time the problem occurred. SQL Response captures as much of this information as seems appropriate, together with as much information as possible on the problem itself, and files this bundle of fun as an "incident report". These incidents are available from within the SQL Response client application. If you have the client and your e-mail application installed on the same machine, you can even click the link inside all SQL Response e-mail alerts to jump straight into the application and view the incident report.

Once inside SQL Response you can set about diagnostic the problem. Some problems have a simple remedy inside SQL Response itself - a failed job is easily restarted. Most problems, of course, require the seasoned eye of a DBA or other professional to diagnose. SQL Response provides all the information it can, like any good investigator. Leaving the professional to work their Holmsian powers of deduction with data at their fingertips.

SQL Response can also make recommendations. If your databases are in need of backing up, reindexing, or otherwise maintaining in key ways, SQL Response will do its best to point this out.

Of course, it's all very well having a sturdy watchman nearby who will sound alarm bells when things go wrong. But there is such a thing as crying wolf. Computers being what they are, things go wrong all the time, and one doesn't necessarily want someone town crier throwback heckling you constantly about every little niggle. If you have a job which runs every 5 minutes, and which starts failing at 2am whilst you're happily convalescing, do you really want 72 "Job failed" e-mails sitting primly in your inbox when you arrive in the morning? And continuing to arrive, bing-bing-bing-bing, every 5 minutes until you fix the problem?

So, naturally SQL Response will allow you to ignore future incidents of a particular type, either on a particular server or just generally. But more importantly, even when its monitoring is full steam ahead, it doesn't spam e-mails at you constantly. In any 24 hour period, SQL Response will send one e-mail when a particular problem first arises (say when job A on server B goes wrong), and another if it happens again. Since deathly silence isn't necessarily a vast improvement over crying wolf, SQL Response will continue to raise incidents in its own client UI, for every failure, collapsed down into a single row to avoid visual clutter. It just stops heckling you about them. Should you care about the 56th job failure more than the first or last, you can easily drill down and inspect the gritty details.

However, much as I could easily sit here in a continuing pretence that this unspeakable waffle is a reasonable justification for my paycheck, I suspect the world would be better served through my cessation in doing so. As a closing thought, therefore, if you're a DBA or other professional who has to worry about the health and general well being of SQL Servers, why not check out our beta at the below link. We'd love to hear what you have to say.

And perhaps you can avoid having to change the bulb.

SQL Response 1.0 Beta