Dan's Blog

If you see SID, tell him

2008-03-17T13: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-17T13: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-05T13: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

Strong Naming + Remoting = Noth'g but ye liveliest Awfulness

2007-08-30T10:51:00Z

"What you sente, did not Worke, whether because of Any Thing miss'g, or because ye Wordes were not Righte from my Speak'g or yr Copy'g. I alone am at a Loss...Certainely, there was Noth'g but ye liveliest Awfulness in that which H. rais'd upp from What he cou'd gather onlie a part of." -- H P Lovecraft

Strong naming assemblies has many advantages. My favourites:

Trust. Users of the assembly can trust that it came from the signee, such as Red Gate Software, thanks to the public key cryptography involved.
Tamper prevention. Users of the assembly can trust that nobody has tampered with it since the signee released it. The .NET runtime will not load assemblies whose signed hash doesn't match their current hash.
Compatibility with the GAC. The global application cache only accepts strong named assemblies.

It also has disadvantages. My favourites:

"The const problem", or "One naming policy to rule them all". Strongly named assemblies cannot use assemblies which aren't strongly named. Like a virus, strong naming must spread throughout an application, or perish. This causes difficulties interacting with open source or other unsigned, third party components.
Version coupling. Since an assembly's strong name includes its version, the .NET framework generally requires that, if assembly A is using assembly B, the exact version of assembly B against which A was linked must be available at runtime.
Compatibility with the GAC. The global application cache only accepts strong named assemblies.

Thanks to the advantages of strong naming, Red Gate tends to strongly name its assemblies.

To tangent slightly before weaving this story together into a coherent whole, .NET Remoting is in many ways another Good Thing. A simple, distributed .NET application is far simpler to set up and work with than some other approaches: I come from a background involving distributed DCOM applications, so I know whereof I speak.

The ability to choose whether objects passed back between tiers are executed remotely via a proxy, or are transmitted across the network and executed locally, is simple and logical. Derive from MarshalByRefObject or add the Serializable attribute to your class.

You still have to contend with one basic problem: in a client/server architecture based on Remoting, the client and server have to have a common set of types. There are several ways to achieve this:

Ensure that each client has a reference to the server assembly, and that the server assembly is installed locally on each client purely for access to its metadata. When the client needs types, it pulls them from the server assembly.
Since in the case of well known (ie. server activated) MarshalByRefObjects the client only needs metadata about the remote object in order to synthesize a proxy, a utility such as Soapsuds can be used to generate a metadata-only assembly for the client. This does require that HTTP/SOAP is your channel of choice.
Put all common types into a separate, common assembly. Provided interfaces are used, no server-side code needs to exist in this assembly: the common assembly simply defines the interface which the server implements, and provides enough metadata for clients to use it.

Let's consider a realistic deployment scenario. We're writing a client/server application using .NET Remoting. Both parts, client and server, are to be shipped out to users. They install the server in one place and the clients in several places.

Now let's introduce one more realistic factor to spice things up: we're going to need to roll out new versions of both client and server at some point. And let's say that our build system rolls a new assembly version number each time it builds any assembly.

With that in mind, let's review the above options. The first one is ugly, even if it were likely to fly. The second one is even grubbier, requiring additional build process in order to achieve a notional separation, and places techincal limitations on our communication protocol. The third one has to be the way to go.

It should also fit with our stated scenario. Provided none of the types in our common assembly change, we should be free to issue updates to the client and the server independently. (If the common assembly needs to change, then we need to issue updates to both client and server, and this will be a breaking change for existing customers unless they update both client and server. But this should be a rather rarer occurrence than, say, a patch containing purely UI-related client fixes, or a couple of server fixes, or both.)

The problem we encounter is this: by default, given the constraints I've outlined, IT DOESN'T WORK.

Sorry, I went a bit forum troll esque there with my caps key. But this topic has been vexing me all day, and I've only now managed to find a solution that has any chance in hell of working.

The problem one encounters is this: as soon as there is a difference between the strong name of the common type library on the client, and the strong name of the common type library on the server, everything breaks. Remoting throws exceptions as soon as any notable client/server communication starts.

And given that the strong name of an assembly includes its version number, and that we revise our assembly version numbers each build, this is a bit of a showstopper. (Even if we only rolled a new assembly version number when new features came in, it would still be a showstopper. An old client should be able to connect to a new server, provided interfaces are backwardly compatible.)

One cause of this applies only to Serializable types. By default, serialization for remoting always includes the version number of the type in question, which corresponds to the version number of its containing assembly. Since the build numbers differ between client and server, we hit exceptions upon deserialization. The fix for this is to instruct .NET remoting not to include version numbers when serializing. This is easily done by supplying includeVersions = false to the formatter sink provider associated with the channel being used for client/server communication.

The other, and more irritating cause of this occurs when a type comes across the wire by other means: for example, when an argument or return value supplied from/to a well known object is of a custom type belonging to the common assembly. (Oddly it doesn't seem to occur when instantiating a well known, server-activated object; just when calling its properties and methods with common types.) It also occurs if you pass an instance of the Type class across, and that type came from eg. typeof(ICommonInterface).

This is less easy to resolve. .NET is, in some ways, legitimately flagging a potential problem: the client and the server "do not agree" on the set of types used for communication. I enquote "do not agree" because .NET's idea of "agreeing" is: "types' strong names exactly match", which is clearly extraordinarily unlikely in any realistic situation. But by buying into strong naming and remoting, we buy into .NET's over-simplistic world view.

The way to resolve this is to take the situation out of .NET's hands entirely, just as per the serialization versioning issue, but more so. At the lowest level, these issues come down to assembly load resolution. When a remoting client receives a type over the wire from the server which belongs to strongly named assembly, .NET is wired up to attempt to load that specific assembly version to work with that type. Likewise when the server receives a type from the client.

Let's get concrete here. Say we have Client.exe version 1.0.0.1 using Common.dll version 1.0.0.1. Remotely we have Server.exe version 1.0.0.20 using Common.dll version 1.0.0.20. We need the Client to work with types from Common.dll version 1.0.0.20, and the Server to work with types from Common.dll version 1.0.0.1. This is because we know, behind the scenes, that the types are actually identical in both versions of Common.dll. No breaking changes were made to the client/server interfaces.

One thing we can do is to set up "binding redirects". We can explictly tell .NET to replace references to certain versions of an assembly with a specific version. So in the client we could have a Client.exe.config which looked like this:

And similarly on the server, except with newVersion="1.0.0.20".

This works, but the problem with it is that every time the version of Common.dll on the client changes, we have to roll a new Client.exe.config file with the correct "newVersion" number in it. There's no way to say newVersion="any" or similar.

A rather funkier solution I came across today is to handle this in code, rather than with configuration files. Ben Hall points out in his blog, http://blog.benhall.me.uk/2006/08/appdomaincurrentdomainassemblyresolve.html, that it's possible for Client.exe to handle the AppDomain.AssemblyResolve event, and manually resolve which assembly gets loaded. So we can say to .NET "ah, you want version 1.0.0.20 of the Common assembly? Well here's the version of that assembly I've got in my local directory, ignore the version number, take it and shut up, it's good for you."

Since types are loaded when they're first referenced, if you register for this event as the very first thing inside your Main function, you can handle all assembly versioning issues this way:

static void Main()
{
AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler( CurrentDomain_AssemblyResolve );

Startup();

Application.Run( new MainForm() );

Shutdown();
}

static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs e)
{
    try
    {
        MessageBox.Show( "Asked for:\n" + e.Name, "Client" );
        string[] assemblyDetail = e.Name.Split(',');
        string assemblyBasePath = System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
        Assembly assembly = Assembly.LoadFrom( Path.Combine( assemblyBasePath, assemblyDetail[0] + ".dll" ) );
        MessageBox.Show( "Supplying:\n" + assembly.GetName(), "Client" );
        return assembly;
    }
    catch (Exception)
    {
        return null; // fail
    }
}

Which is very splendid. And admittedly, terribly terribly evil. We just bypassed the simple-minded sanity which .NET is trying to inflict on our remoting of types from strongly named assemblies, and introduced much more flexible chaos. Now it's up to us to roll enough version control of our own that the whole shebang doesn't explode and shower our users with unseemly goup. But this way we at least have a chance of updating our client and server independently, without forcing clients to patch them in lock step.

There is another solution to this whole mess of course: Use a proper language. I mean, reroll a simpler in house remoting mechanism that works. Damn, two Freudian slips, I mean: stop using strongly named assemblies. Depending on your situation, as Craig points out in his blog, http://www.pluralsight.com/blogs/craig/archive/2005/03/11/6653.aspx, this may be a far simpler solution to your woes.

"...I wou'd have you Observe what was told to us aboute tak'g Care whom to calle upp, for you are Sensible...and can judge how truely that Horrendous thing is reported.

I say to you againe, doe not call up Any that you can not put downe; by the Which I meane, Any that can in Turne call up Somewhat against you, whereby your Powerfullest Devices may not be of use. Ask of the Lesser, lest the Greater shal not wish to Answer, and shal commande more than you." -- H P Lovecraft

The timeout period elapsed prior to obtaining a connection from the pool.

2007-03-15T09:27:00Z

As I believe I have already opined, dealing with databases is not one of my all time favourite activities. It's not right up there with drinking, skiing and World of Warcraft, for example. For someone in my position, dealing with databases is naturally an occupational hazard; but I do attempt to minimise the amount of time I spend doing it.

Obscure errors connecting to databases can suck up a lot of time. There are so many things can can go wrong. Fortunately, .NET makes this rather easy to handle in many cases (catch SqlException, do something sensible), but there are occasions when this all falls down.

One exception in particular has been something of a major niggle over the last few weeks, as from time to time my colleague James Moore and I tried to establish why the application we're working on was suffering from it:

InvalidOperationException

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

It looks so straightforward. Googling suggests that the error message is telling the truth: this error occurs when I, niave fool that I am, open dastardly numbers of pooled connections to a server in a cavalier fashion, cackling as I disregard all sensible cleanup procedures, spilling objects out all over the place and generally jumping up and down on System.Data and grinning.

Unfortunately, this is not the case. The error message is entirely misleading. You can get this exception when not doing anything dangerous or complicated, or opening hoards of connections. In fact, as we discovered when trying to reproduce the problem outside of our application, you can get this exception when hardly doing anything at all.

Here's some sample .NET 2 code. To achieve the exception, simply stop your SQL Server (2000 or 2005, dealer's choice), and run this code to attempt to connect to it.

using System;
using System.Collections.Generic;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Threading;

namespace CrashTest
{
    static class Program
    {
        [STAThread]
        static void Main()
        {
            ThreadPool.QueueUserWorkItem(new WaitCallback(GetData));
            GetData(null);
        }

        static void GetData(object state)
        {
            using (SqlConnection connection = new SqlConnection("Data Source=DAN"))
            {
                connection.Open();
            }
        }
    }
}

As you can see, we simply attempt to open two simultaneous connections to a non-existent server. Not a hundred, or any other arbitrary limit, but two. 100% of the time that I run this, I get the InvalidOperationException.

Having flicked through the underlying System.Data code with Reflector, the best theory we have come up with is that there's some kind of race condition in System.Data which is causing the problem. If a connection cannot be obtained from the pool for more or less any reason, System.Data throws this particular exception. It's not necessarily limited to timeouts of any variety; hence the juicily choice, "this may have occurred because..." in the exception message. An excellent get-out clause for providing exceedingly unhelpful information, in my view.

To preserve our sanity, we haven't drilled down further into exactly what the offending code is doing that causes this issue. It turns out we don't need to. All we need to do is ensure that our application never attempts to open two connections to the same server at the same time. We may do something clever, but certainly one easy way to do this is to replace the line:

                connection.Open();

with a simple monitor lock:

                lock (m_Lock)
                {
                     connection.Open();
                }

on an appropriate object, which for the purposes of this example we can just declare elsewhere in the class:

          private static readonly object m_Lock = new object();

And bingo, checkmate, ding, and other gameplay-oriented culminatory expostulations; Bob is a close relative.

Transforming parrots

2006-10-12T11:11:00Z

Whilst I agree with luminaries such as Paul Graham on a reasonable number of points he makes about our industry and profession, I generally disagree with him on the subject of typing. He doesn't believe in compiler-enforced strong types, whereas I do.

On this occasion though, I find myself leaning towards a slightly more permissive stance, with regard to templating, types and interfaces in C#.

If you're thinking that this blog entry is uncharacteristically light on conversational detritus so far, then you're right. Surprisingly, this may continue to the end of the blog entry, mainly because I'm in rather a hurry developing the piece of software which the compiler is complaining about. But I had to pause long enough to whine, have a coffee, and get my breath back.

So, to be more explictly explict, my gripe is as follows.

Generics in C# permit me to have a List<Class>, that is a list whose members may only be of the given class. Splendid and good. List implements various useful interfaces, notably (for the purposes of this discussion) IEnumerable<Class>, which can be used to iterate over (one might even say enumerate) the members of the list, but not to change them. So far, so splendid.

But classes can implement interfaces. My class, let's call it File, may just be the concrete realisation of the abstract interface IFile. IFile may be a specialisation of a less flexible interface called IFileDescription, which permits only certain simple properties to be read, but not written. All well and good.

I may then have a class called Directory, which contains files. I may create interfaces, IDirectory and IDirectoryDescription; again, the former permits read/write access to most properties, whereas the latter permits only certain simple properties to be read but not written. Good show.

Since a directory contains files, it seems wise for IDirectoryDescription to allow enumeration of the IFileDescriptions it contains; and for IDirectory to allow enumeration of the IFiles it contains.

Why would I want all this? Well, let's say for the sake of this example that I have one part of my application in which I create Directory and File objects from the local file system, where it's very likely that the user can read and write to that file system, and should be able to within the application. But perhaps the application retains a log of files and directories it created in previous sessions, even if those files and directories have long since been deleted. These can certainly be read, and I can use my File and Directory objects to store that data in memory, even load it to and from disk. But I shouldn't supply that history to other parts of the application as IFile and IDirectory, since those files and directories may not exist, and in this context I only wish to view simple information, not permit it to be edited.

So, back to the interfaces. Here's a recap in code.

interface IDirectoryDescription
{
    string Name { get; }
    IEnumerable<IFileDescription> FileDescriptions { get; }
}

interface IDirectory : IDirectoryDescription
{
    string Name{ get; set; }
    IEnumerable<IFile> Files { get; }
    ...
}

interface IFileDescription
{
    string Name { get; }
}

interface IFile : IFileDescription
{
    string Name { get; set; }
    ...
}

So that works nicely. To implementation.

class File : IFile /* and implicitly also IFileDescription */
{
    ...
}

class Directory : IDirectory /* and implicitly also IDirectoryDescription */
{
    private List<File> m_Files;
    ...
    public IEnumerable<IFileDescription> FileDescriptions
    {
        get { return m_Files; }
    }
    public IEnumerable<IFile> Files
    {
        get { return m_Files; }
    }
}

So that's all good. Oh, except that it doesn't compile.

C# bitches, whines and generally crows about the fact that it cannot convert m_Files (which is a List<File>) into either an IEnumerable<IFileDescription> or an IEnumerable<IFile>. This, it claims, is my problem.

To which I say, yes, but I happen to know, as should you, that File implements IFile and IFileDescription. So, make it happen, make it so, engage, and chop chop.

Sadly it has a point, according to the letter of the language. Strong typing tells us that IEnumerable<File> is entirely distinct from IEnumerable<IFile>, even if File isa IFile.

So, a crufty adaptor class (or more specifically, classes) have to be created. Here's mine.

    internal class EnumerableAdaptor<Interface, Class> : IEnumerable<Interface> where Class : Interface
    {
        private IEnumerable<Class> m_List;

        public EnumerableAdaptor(IEnumerable<Class> list)
        {
            m_List = list;
        }

        public IEnumerator<Interface> GetEnumerator()
        {
            return new EnumeratorAdaptor<Interface, Class>(m_List);
        }

        System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
        {
            return new EnumeratorAdaptor<Interface, Class>(m_List);
        }
    }

    internal class EnumeratorAdaptor<Interface, Class> : IEnumerator<Interface> where Class : Interface
    {
        private IEnumerator<Class> m_Inner;

        public EnumeratorAdaptor(IEnumerable<Class> list)
        {
            m_Inner = list.GetEnumerator();
        }

        public Interface Current
        {
            get { return m_Inner.Current; }
        }

        public void Dispose()
        {
            m_Inner.Dispose();
        }

        object System.Collections.IEnumerator.Current
        {
            get { return m_Inner.Current; }
        }

        public bool MoveNext()
        {
            return m_Inner.MoveNext();
        }

        public void Reset()
        {
            m_Inner.Reset();
        }
    }

Now I can finish my implementation of Directory:

        public IEnumerable<IFile> Files
        {
            get { return new EnumerableAdaptor<IFile, File>(m_Files); }
        }

        public IEnumerable<IFileDescription> FileDescriptions
        {
            get { return new EnumerableAdaptor<IFileDescription, File>(m_Files); }
        }

I find this sort of thing exceptionally irritating when all I'm trying to do is get a simple job done. I'm one of the world's biggest fans of object orientation and strong typing, but a bit of me believes strongly that it should be up to the compiler to work this out. Admittedly that would require some reinterpretation of generics. But I find C# generics overly restrictive compared to C++ anyway...why can't I have class S<T> : T where T is a class, not an interface? Sigh.

Anyhoo. Back to the code for me...

Exceptional service

2006-07-28T09:54:00Z

A constant niggle in the arena of computing is the way in which nobody takes responsibility. If your computer crashes whilst you're, say, playing an online game, and you call the manufacturer, they'll explain it's probably a software problem, perhaps a driver you, the user, stupidly installed. If you call the driver vendor, they'll explain that it's probably some other driver you, the user, stupidly installed; or perhaps some other hardware (not theirs, of course) is causing you problems; or perhaps your game has a bug in it. If you call the game publisher, they'll explain that it's clearly a hardware problem, or you need to upgrade your drivers. Nobody will hold their hand up and say, "Sorry, my bad, we screwed up. Our product is inferior, and we will rectify that for you, right now."

On a microscopic level, the same situation occurs when a contract breaks down between the caller and callee of an interface. The case which I wish to briefly whine about is that of web services.

I'm working on a .NET 2.0 desktop application which uses a third party web service. Now it generally works nicely. Much to my surprise, however, I periodically notice the application cough, gasp for air, and finally collapse spreadeagled on the floor. By the time I can attend it, I observe that it has written me a final message with one trembling finger, using its own vital fluids as ink:

System.Net.WebException : The underlying connection was closed: A connection that was expected to be kept alive was closed by the server.

How nice of it.

Googling around revealed a few more facts. It appears that .NET and the web service implementation are having a disagreement over the lifetime of the HTTP connection which they're using to talk. .NET asked for the connection to be kept up, if you don't mind, so that it can avoid the overhead of reopening it all the time. The remote server agreed, but at some stage this became impossible. This may be because the remote server is a lying son of a VAX; or because there are inconvenient firewalls between my client and the server, interfering with the keepalive; or because the server encountered a minor problem which .NET didn't understand, and this issue got blown out of proportion until it finally manifested as a dropped connection. Opinion on the internet seems divided; all of these are cited as plausible reasons. Meantime, .NET, for its part, doesn't like the internet failing to work flawlessly. In its idealistic view, this simply should not happen! What is the world coming to? And, it reasons, if this is occurring, surely someone should be alerted so they can a) fix the internet; and/or b) rewrite the server code? Overall, the best thing to do from .NET's point of view is not to, say, reopening the offending connection and try again; no no, the best plan is to throw an exception.

Now the thing that vexes me is not that .NET likes throwing exceptions whenever anything even vaguely unconventional occurs; such as, for example, if I were to come to work wearing a hat. I've grown accustomed to its idiosyncracies in this regard. What vexes me is that .NET has deliberately set itself up to work under a theoretical and idealistic optimal case (where keepalive connections are always kept alive), and then complains bitterly when its lofty ideal is not upheld by the cruel world.

In any case, there is a solution to this problem. One simply has to find the web service's generated code within the client application, which derives from WebClientProtocol or one of its subclasses. One then overrides the GetWebRequest method as follows, to tell .NET not to bother trying to keep connections alive:

        protected override System.Net.WebRequest GetWebRequest(Uri uri)
        {
            WebRequest request = base.GetWebRequest(uri);
            (request as HttpWebRequest).KeepAlive = false;
            return request;
        }

Simple stuff. But there are a few little niggles; the .cs file in which one is supposed to override this method is generated code. So, if the web service's documented interface changes (which has been known to occur rather regularly on the internet), the code needs to be regenerated. Now I personally object to trawling through generated code each time I regenerate it, adding in hacks here and there. It's just wrong.

So, a slightly more tasteful solution was required. Instead, I derive a class from the generated code for the web service, called eg. SafeWebService. Inside this class, I override the GetWebRequest method. I then be sure to make use of SafeWebService rather than the original generated code. This way, I can regenerate the code and not have to make any changes, since my modifications are not contained therein.

Hoop jumping

2006-07-14T08:56:00Z

Although this may come as a surprise to some, as I work at a company which happens to produce simple tools for database professionals, I am not the world's biggest fan of databases. "Don't get me wrong", as the poet said: they are extraordinarily powerful and highly necessary. Many splendid online games, in which I have been known to occasionally indulge, would not function without a rather spiffy database behind the scenes, doing the grunt work of serving up interesting data concurrently to millions around the globe.

However, I usually find my more direct encounters with databases in the day to day course of developing desktop software to be, if not vexing, then at least very much a case of rolling up the sleeves, getting a cup of coffee, and bending my brain to that database's particular way of working. Or more specifically, the way of working with the particular API de jour.

Currently that happens to be .NET's SqlClient namespace, and I have one question. Why is it so difficult to call a stored procedure from .NET code?

Here's an example. I'll assume for brevity that I already have a nice SqlConnection set up. I'm going to call a hypothetical "hello world" procedure that will display the name with which it is supplied.

I should mention that I don't intend to exhaustively explain the workings of the SqlClient classes for the purposes of this

internal void sayHello(string name)
{
    using ( SqlCommand command = new SqlCommand( "EXECUTE sp_HelloWorld @Name", m_Connection )
    {
       command.Parameters["Name"] = name;
       command.ExecuteNonQuery();
    }
}

So here we're being fairly nice. We're using SqlParameter objects rather than munging the command string together by hand: this protects against SQL injection attacks, and should avoid us having to manually massage .NET types into the correct format for a SQL query. We're taking advantage of the type-agnostic approach to using the Parameters collection, where it accepts an object and will automatically figure out the correct SQL type based on the underlying .NET type. Shiny.

What's so hard about that, you may ask? Well, not a lot, in the simple case. Provided whenever I call a stored procedure I want to have this nice little block of code, rather than having a generalised abstraction. Which, incidentally, I don't. And provided I can trust the SqlParameters collection to correctly marshal my data types into their nice SQL equivalents. Which, it appears, I can't.

All I'd like to do is have a function as follows:

internal void ExecuteStoredProcedure(string name, params object[] args);

This works like string.Format(): you can pass a variable number of arguments after the stored procedure name. So I can say:

ExecuteStoredProcedure("sp_HelloWorld", name);

Well, this turns out to be easy enough. Here's some code for you. It's rather rough, ready and unoptimised, to make the example simpler. But to make things interesting, it does take two slightly different but equivalent approaches to creating parameter names. And it has some hard coded limits. I know, I need slapping for this, but it's easily fixable, and it's Friday afternoon.

internal int ExecuteNonQueryStoredProcedure(string procedureName, params object[] args)
{
    return ExecuteNonQueryCommand(GetExecuteCommand(procedureName, args), args);
}

internal string GetExecuteCommand(string procedureName, object[] args)
{
    string command;
    StringBuilder sb = new StringBuilder();
    sb.AppendFormat("EXECUTE {0}", procedureName);
    int nArgsSoFar = 0;
    if (args != null)
    {
        // longhand
        //
        for (int iArg = 0; iArg < args.Length; iArg++)
        {
            if (nArgsSoFar != 0)
                sb.AppendFormat(", @P{0}", iArg);
            else
                sb.AppendFormat(" @P{0}", iArg);

            nArgsSoFar++;
        }
    }
    command = sb.ToString();
    return command;
}

internal int ExecuteNonQueryCommand(string template, params object[] args)
{
    using (SqlCommand command = CreateSqlCommand(template, args))
    {
        return command.ExecuteNonQuery();
    }
}

internal SqlCommand CreateSqlCommand(string query, params object[] sqlParameters)
{
    SqlCommand command = new SqlCommand(query,m_Connection);
    if (sqlParameters != null)
    {
        int iParam = 0;
        string[] paramNames = new string[] { "P0", "P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12", "P13", "P14", "P15", "P16", "P17", "P18", "P19" };
        if ( sqlParameters.Length > 20 )
            throw new ArgumentException(Localize.Me("Too many arguments supplied to SQL command creator; maximum is 20."));

        foreach ( object arg in sqlParameters )
        {
            object o = arg;

            SqlParameter param = new SqlParameter(paramNames[iParam],o);
            iParam++;

            command.Parameters.Add(param);
        }
    }
    return command;
}

So, that's all fine and splendid.

Or so I thought, until I tried to use it.

Now mostly this works very nicely, as it should since it's not being particularly clever. The problem occurred when I tried to pass a NULL value to a stored procedure in this fashion:

ExecuteStoredProcedure("sp_HelloWorld",null);

Now naturally if my stored procedure isn't expecting this, it should complain. However, we didn't get that far. .NET threw a tantrum way before then:

RedGate.HelloWorld.HelloTest.Test1 : System.Data.SqlClient.SqlException : Prepared statement '...' expects parameter @P0, which was not supplied.

Having discovered the source of this, I'm more than a teensy bit vexed by it.

It seems that the SqlParameter class, whilst able to freely handle int, DateTime, string, wildebeast, diplodocus and assorted other standard goodies, has a bit of a problem with null. It doesn't realise that a null string should be passed in as DBNull.Value. Which seems a fraction catastrophically dim of it.

The solution is easy enough. We can intercept parameters before they hit SqlParameter, and massage nulls into DBNull.Value ourselves:

internal SqlCommand CreateSqlCommand(string query, params object[] sqlParameters)
{
    SqlCommand command = new SqlCommand(query,m_Connection);
    if (sqlParameters != null)
    {
        int iParam = 0;
        string[] paramNames = new string[] { "P0", "P1", "P2", "P3", "P4", "P5", "P6", "P7", "P8", "P9", "P10", "P11", "P12", "P13", "P14", "P15", "P16", "P17", "P18", "P19" };
        if ( sqlParameters.Length > 20 )
            throw new ArgumentException(Localize.Me("Too many arguments supplied to SQL command creator; maximum is 20."));

        foreach ( object arg in sqlParameters )
        {
            object o = arg;

          if ( o == null )
             o = DBNull.Value;

            SqlParameter param = new SqlParameter(paramNames[iParam],o);
            iParam++;

            command.Parameters.Add(param);
        }
    }
    return command;
}

And Bob is a close relative.

As usual though, I'm left beating my head against a table, wondering why, why, and even, why, this occurs? Am I missing something intrinsically difficult about this? Now I'm sure someone's going proffer the explanation that database NULL is distinct from, say, a C++ NULL, which is in fact just a pointer to memory location zero. Even assuming I agree that such a distiction is worth making, it doesn't apply here. a C++ null is a genuine null reference. Now at a purely semantic level, this is may be arguably not the same as a valid reference to a nullable type with a value of null. But, forgive me, I really don't care in the slightest. All I wish to express, clearly and succinctly, is the absence of a value, and I expect to be able to do that in the standard fashion for my language of choice. After all, if I don't have to convert between DateTime and SqlDateTime, which on earth should I care which flavour of null this API happens to prefer?

I think the phrase is, Do What I Mean.

1mL Adrenaline, stat

2006-06-12T06:40:00Z

It is a balmy summer evening in a suburb of Cambridge, England. Young gentlemen play cricket on the village green. Young ladies walk the country paths, shielded from the sun's rays with fashionable parasols. An iced cream vendor perambulates the streets with a cheery smile. And a young couple sit before their computers, beating the living daylights out of some wayward troll cultists in the World of Warcraft.

All perfectly idyllic. A typical British summer evening. But, unpointful as it may seem to draw down such a melancholic and improbable path, what could spoil such a serene setting? Martians landing on the green, disrupting the cricket until destroyed by bacteria from inappropriately stored iced cream? An influx of crazed barely humanoid loons wearing brightly striped clothing, screeching barely polysyllabic mantras relating to the relative merits of two loosely geopolitically based groups forming the basis of some sort of banale, fruitless orchestrated confrontation of skill?

No, but worse. On the two brightly lit internet-facing screens, heroes and villians froze mid swing as the ADSL router coughed, spluttered and gave up.

It had done this before. It was a device of evil disposition, or rather it had been crafted by experts who themselves were sufficiently machiavellian as to contrive and shape designs into raw plastic with such painstaking malice that their work itself took on the dark aspect of its creators. Its very nature was transformed by the housing which contained it: transformed from a device whose purpose was to transmit the odd datum or two from one locale to another into a device eptly and precisely contrived to stop working whenever its operating temperature reached the meagre heights of a typical British summer.

Fortunately, even the most rigerous of master criminals always make one fatal mistake. In this case, the anglo saxon masons of telecommunications (whose company shall remain relatively nameless) who assembled the dread device erred in permitting it to be opened by a skilled God-fearing ecclesiast with the aid of a small, consecrated screwdriver. Naturally the device would hiss and writhe beneath his grasp, but their faith would be a worthy shield against the demoniac, and against the tiny, maliciously sticky footpads which had to be removed to gain access to the aforementioned screws. Once open, the vile humours which the device's crafters had masterfully sought to enclose were freed, and set to disperse in the great outdoors, lost and winnowing amongst the cricketers and cucumber sandwiches.

And so would remain only the task of preventing the same from happening again. Fortunately, mundane but effective means were at hand. With the aid of the perforated aluminium bin which normally housed the hamster-bedding output from a paper shredder, a more ventilated housing for the router was fashioned. Next, from the bowels of one of the sturdy Warcraft-ready difference engines, a surplus fan was retrieved, without disconnecting it from the umbilical cord which allowed its mother board to sustain it. This, placed atop the perforated bin with its blowing-inclined side downwards, proved exquisitely capable of maintaining the surface temperature of the Dread Device at something more akin to a drab, English wintery morning.

And so the trollectomy was permitted to continue. Fortunately, the cricket, the iced cream, and more distantly the mantra-chanting, stripe-apparelled supporters of geopolitics were never disrupted. And, even more fortunately, the chances of anything coming from Mars were a million to one.

The game's afoot

2006-05-10T13:58:00Z

I'd like to squeeze in another reference to the Killing Game Show, or other Amiga classics, at this point. I really must wind up the emulator and dig out the ADFs I made of my old games, at some point. Sadly I lost the original machine when I sold a house, neglecting to clear out lots of ancient goodies from the roof. Sigh. The beige box with the chicken-head on the outside and B52 ROCK LOBSTER on the inside is no more.

What's he drivelling about? Well folks, that was a cunningly disguised reference to my previous blog entry, Making Tracks, which referred to the beta release of SQL Dependency Tracker. Why should I bring that up, at this point? And why doesn't this blog make much sense, really, when you look at it closely? The latter question I'm just going to have to /shrug to. However, as to the former, I have an answer for you.

We've recently released the full, 2.0 release version of SQL Dependency Tracker, and it's really quite spiffy. Even if I do say so myself, being project manager and co-developer. As I've been telling some journalistic types over the last few days, it's an excellent way to analyse the impact of potential changes to your database before you make them, as a number of our beta testers and Friends of Red Gate have discovered. It's also great for exploring your database. Even though we didn't have time to put printing features into the app, all credit to one of our beta users who cunningly used Dependency Tracker's export features to paste a diagram into Excel, and printed up a wall-sized block diagram of his database courtesy of Red Gate. The man has determination and style.

Anyhoo. If you fancy getting your hands dirty, then why not go download a free 14 day trial from:

http://www.red-gate.com/products/SQL_Dependency_Tracker/index.htm

Meantime, I'll stroll away reminiscing about 16-bit machines.

Making Tracks

2006-03-28T11:33:00Z

Feeling as we did that it was time to take longer strides (ah, the Killing Game Show. A fine example of early 90's computer gaming) Red Gate has just released the beta version of our shiny new SQL Dependency Tracker.

This is, in many ways, a long-planned revamp of last year's SQL Dependency Viewer beta. We put that product out there to see what kind of reaction it would get, and our feedback was very positive. Turns out rather a lot of database developers and their managers could make good use of such a tool. Doing a spot of research, we discovered that a primary motivation for this for database developers was in order to do impact analysis for a change about to be made to a database; and then to document that change in diagrammatic form for use in the sort of internal company documentation that's part of the outdated Waterfall model process that so many of us know, hate, and still find ourselves doing. (I must confess that I am no longer subject to such outmoded blitherings since I've been at Red Gate Software, but I have in the past been found desparately trying to fight my way out from 'neath piles of such paper).

Digressions aside, you should find this up-and-coming new version should be much tastier than the old. You can add dependencies selectively to the diagram, and related dependencies will make their way in automatically; so if you're about to make a change to a table and want to see what references it, just drop the table onto the diagram and all its "used by" dependencies will shuffle on obediently. If objects on the diagram have references that aren't also on the diagram, they'll display a little leftward stalk, and tooltippage gives you more detail. Just right click the object to bring in its dependencies. You can copy your diagram to the clipboard and paste it into Word; or get an XML report or export a full or partial image of your diagram.

Of course you can move objects around, change the colours, pan and zoom, load and save diagrams without reconnecting to the original databases, all that sort of jazz. More interestingly, we also (as I may have hinted above) have the ability to track dependencies across databases, and to show you unresolved references within databases (to eg. missing stored procedures). In the final version you'll likely be able to select an unresolved external dependency, frob the mouse, and shout "make it so, engage" at the screen whilst SQL Dependency Tracker goes and finds the database in question and pulls in your object.

Under the hood, we're of course not relying on the evil that is the sysdepends table; we make good use of the same technology that powers the battleship SQL Compare to get down, if not dirty, with the database schema directly.

Anyhoo. If you find yourself with a spare moment and have even a passing interest in a) tracking dependencies within and between databases in SQL Server, or b) pretty diagrams, then do check out our beta version. You can find it at http://www.red-gate.com/products/SQL_Dependency_Tracker/beta.htm, and there's a forum to post your questions, comments and problems at http://www.red-gate.com/MessageBoard/viewforum.php?f=40. Seeing as how I've been shepharding this product for the last couple of months, I'll even reply to most postings there personally. Of course my colleagues are also as able in forum penmanship as they are in cutting code, and may take their turn in turn.

Wheels within wheels

2006-02-28T16:36:00Z

I hate to depart from my usual ranting and raving and actually post some useful code, but the below is too lovely to ignore.

Before I proceed to take credit, I have to thank Google and the following poster(s): http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=228499&SiteID=1. The below is a rather elementary explanation of the code posted therein, with a generic adaptation at the end.

Subject: The mouse wheel.

The mouse wheel is an excellent widget for the scrolling with. One simply waves it over a window and, without needing to give input focus to that window, a few deft flicks of the wheel will scroll any eminently scrollable window, moving the dull, tiresome and mundane contents out of view and revealing the delicious, exciting, wonderous contents to be found just enticingly off screen. If one were extraordinarily closeted or on some serious drugs, it could even be a fulfilling experience.

In any case, this is the experience a lot of users are looking for (if you remove the parts of the above description which relate to excitement or interest in any way). Now in previous applications I'd written, this proved an issue. Mouse wheel messages are only routed by Windows to the window with input focus. Extraordinarily dumb? Why, yes, I should coco. The immediate and obvious hack is to ensure that the window under the mouse has input focus. As most users don't have PowerToys for Windows installed (and, even if they do, rarely check the "activate the window under the mouse cursor" option), this has to be done by the application. Cheap, hideous hacks follow:

internal class MyControl : UserControl
{
    ...
    MyControl()
    {
        ....
        this.MouseMove += new MouseEventHandler(MyControl_MouseMove);
    }

    private void MyControl_MouseMove(object sender, MouseEventArgs e)
    {
        this.Focus();
    }
}

Disgusting indeed. One problem with this is that every window which wants mouse wheel input has to include this deviant hack. It also has the disadvantage of vexing behaviour in various cases, such as if you add dialogs containing text boxes into the equation. Position such a dialog over your focus-stealing control, put some text in the dialog's text box, and ask a user to select that text. See the user drag select the text from right to left, ending up with the mouse cursor outside the dialog and over your focus-stealing control...which grabs the focus, thus deselecting the text and preventing keyboard input to the dialog until the user clicks the text field again. Better hope at that stage they keep the mouse within the dialog...

However, there is a much nicer solution, in the form of message filters. Like the filter tips on cigarettes, message filters can prevent carcinogenic messages from reaching the lungs of your controls. Unlike filter tips, they can also do more or less anything with the messages they filter out.

Behind the scenes in a Windows Forms application, each user interface thread of an application is running a message loop which resembles the following:

while ( GetMessage(&msg,0,0,0) )
{
    TranslateMessage(&msg);
    DispatchMessage(&msg);
}

This should be familiar to any seasoned Win32 programmer. So messages arrive in the message queue; take a quick slash and burn through keyboard accelerator translation; and then are dispatched to the destination window's window procedure (WndProc). I missed out a pertinent detail, however...

while ( GetMessage(&msg,0,0,0) )
{
    if ( Application.FilterMessage(&msg) ) // allow the application to filter out messages of its choosing
        continue;
    TranslateMessage(&msg);
    DispatchMessage(&msg);
}

So, .NET is being very obliging here. The Application class is giving us a window in which we can globally (per thread) filter out messages before they hit their destination window. How incredibly evil! And, useful.

We can use this little loophole to detect when the mouse wheel is scrolled when the focus is with any window. Then before the window gets the message, we'll whisk it away under cover of darkness, and send it to a more deserving candidate: viz. the window actually under the mouse cursor at the time. Best of all, this requires no code whatsoever in the recipient window. It just gets mouse wheel messages, and gratefully processes them as normal.

So, we take the following steps.

1. Derive a class from IMessageFilter.
2. Do what we want with messages therein.
3. At an early stage in our application's lifetime, call Application.AddMessageFilter( new MyMessageFilterClass() ).
4. Bingo.

To make things really easy for you, here's some sample code in full.

using System;
using System.Windows.Forms;
using System.Runtime.InteropServices;

namespace MyCompany.MyApplication.UI
{
       /// <summary>
       /// Ensures that all mouse wheel messages are dispatched to the
       /// window under the cursor, rather than the window with input
       /// focus.
       /// </summary>
       public class MouseWheelMessageFilter : IMessageFilter
       {
              [StructLayout(LayoutKind.Sequential)]
              private struct POINT
              {
                     public int x;
                     public int y;
              }

              [DllImport("user32.dll")]
              private static extern IntPtr WindowFromPoint([In] MouseWheelMessageFilter.POINT point);

              [DllImport("user32.dll")]
              public static extern int SendMessage(IntPtr hWnd, uint msg, IntPtr wParam, IntPtr lParam);

              private const int WM_MOUSEWHEEL = 0x020A;

              private uint LOWORD(IntPtr value)
              {
                     return (uint)( ( ((ulong)value.ToInt64()) & 0xffff ) );
              }

              private uint HIWORD(IntPtr value)
              {
                     return (uint)( ( ((ulong)value.ToInt64()) & 0xffff0000 ) >> 16 );
              }

              public bool PreFilterMessage(ref Message m)
              {
                     if ( m.Msg == WM_MOUSEWHEEL )
                     {
                           uint screenX = LOWORD(m.LParam);
                           uint screenY = HIWORD(m.LParam);

                           MouseWheelMessageFilter.POINT point = new MouseWheelMessageFilter.POINT();
                           point.x = (int)screenX;
                           point.y = (int)screenY;

                           IntPtr hWnd = MouseWheelMessageFilter.WindowFromPoint( point );
                           if ( hWnd != IntPtr.Zero )
                           {
                                  MouseWheelMessageFilter.SendMessage(hWnd,(uint)m.Msg, m.WParam, m.LParam);
                           }

                           return true; // stop this message being dispatched
                     }

                     return false;
              }
       }
}

Let's take a quick look through this code.

Firstly, we declare some helpful Win32 glue. We need a binarily-compatible .NET equivalent of the Win32 POINT structure. We also need to make native calls to the WindowFromPoint() and SendMessage() APIs from user32.dll, so we pull them in.

Then the meat of the message filter. We implement IMessageFilter.PreFilterMessage(). If the message is a mouse wheel message, we extract the cursor position (in screen coordinates) from the message parameters, and see which of our windows is under those coordinates. (Nowdays this function only tells you about windows owned by your thread of your process, so no need to worry about accidentally enabling this functionality across all the user's applications.) Then we immediately route the message directly to that window's WndProc. We call SendMessage() rather than PostMessage(), since the latter would add the message to the message queue, where it would be retrieved by .NET's GetMessage() call and we'd go round and round to infinite. SendMessage() dispatches the message directly to the window procedure, bypassing the message queue. It's also faster.

So there we go. Now don't say I never give you anything.

Trivial Persuits

2006-02-07T12:43:00Z

Once again I find myself penning a missive on trivia, rather than matters of import. Still, now we're here...

Regular expressions. To a large extent, I hate them. To begin with, they are a complete misnominer. They are neither regular, nor expressions. Don't believe me? To demonstrate this I'll consult an authority on such topics. The Oxford English Dictionary would be handy, but being incredibly lax I don't seem to have a copy lying around. However, dictionary.com will do...

reg·u·lar
adj.

   1. Customary, usual, or normal: the train's regular schedule.
   2. Orderly, even, or symmetrical: regular teeth.
   3. In conformity with a fixed procedure, principle, or discipline.
   4. Well-ordered; methodical: regular habits.
   5. Occurring at fixed intervals; periodic: regular payments.
   6.
         1. Occurring with normal or healthy frequency.
         2. Having bowel movements or menstrual periods with normal or healthy frequency.
   7. Not varying; constant.
   8. Formally correct; proper.
   9. Having the required qualifications for an occupation: not a regular lawyer.
10. Informal. Complete; thorough: a regular scoundrel.
11. Informal. Good; nice: a regular guy.
12. Botany. Having symmetrically arranged parts of similar size and shape: regular flowers.
13. Grammar. Conforming to the usual pattern of inflection, derivation, or word formation.
14. Ecclesiastical. Belonging to a religious order and bound by its rules: the regular clergy.
15. Mathematics.
         1. Having equal sides and equal angles. Used of polygons.
         2. Having faces that are congruent regular polygons and congruent polyhedral angles. Used of polyhedrons.
16. Belonging to or constituting the permanent army of a nation.

ex·pres·sion
n.

   1. The act of expressing, conveying, or representing in words, art, music, or movement; a manifestation: an expression of rural values.
   2. Something that expresses or communicates: Let this plaque serve as an expression of our esteem.
   3. Mathematics. A symbol or combination of symbols that represents a quantity or a relationship between quantities.
   4. The manner in which one expresses oneself, especially in speaking, depicting, or performing.
   5. A particular word or phrase: “an old Yankee expression... ‘Stand up and be counted’” (Charles Kuralt).
   6. The outward manifestation of a mood or a disposition: My tears are an expression of my grief.
   7. A facial aspect or a look that conveys a special feeling: an expression of scorn.
   8. The act of pressing or squeezing out.
   9. Genetics. The act or process of expressing a gene.

Now, I know someone's going to tell me that if you look up "regular expression" on dictionary.com it gives you the "proper" definition. But to that I say, bah. There's far too much rather pants terminology in the world, particularly in the computing arena. Next you'll be telling me that "non-deterministic finite state automaton" is easier to say than "state machine". And put those 5-tuples away, you don't know where they've been. It's not big and it's not clever.

Without getting overly semantic (though I may deviate from the canonical definition of "overly" here) regular expressions are not regular. There are at least three different flavours of which I'm aware, all of which have their own magical incantations: Perl-compatible (as seen in Perl - shocking I know - PHP, and the open source PCRE engine, http://pcre.org); Microsoft (as seen in Visual Studio and .NET's regular expression library); and Java (as seen in, well, Java). "Expressions" expressed in one dialect are not necessarily freely transferable to the other. This doesn't meet any sensible definition of regular that I'm aware of. "Self consistent" would perhaps be more appropriate.

Nor are they expressions. Expressions tend, by definition, to express something to somebody. I blame Perl entirely for this. Any "high level" language in which software can be expressed entirely without reference to any alphabet used by a civilisation, past or present, cannot claim to adequately express anything. If I wanted unreadable gumf, I'd write software in pure machine code. With a decent lookup table and a few cups of coffee it wouldn't be much less straightforward.

However, despite their inadequate definition and manifest unreadability, they do have some extraordinary merits.

Today, for example, 10 minutes of faffing around trying to remember which bits of the Visual Studio RE (see, even handy, descriptive phrases like "regular expression" are too long to type occasionally) syntax are Microsoft-specific, and which are generally applicable, in which time I eventually resorted to the help...proved to be exceedingly handy.

Our software tends to be sold in many international markets, and thus internationalisation (I18N; which isn't a regular expression, but another bludgeoningly stupid acronym bordering on haxx0r lingo) and localisation (L10N, if I recall...but please don't tell me if I'm wrong, I'd like to keep it as a surprise) is an issue for us. All the strings in our source code need to be pulled out of resources, rather than hard-coded. This is sensible practice anyway, but means you end up not being able to express text literally but dereferencing it.

Previously I was doing as follows. Rather than expressing, eg.

namespace MyCompany.MyApp
{
    class HelloWorld()
    {
        HelloWorld()
        {
            string name = "world";
            string message = string.Format("Hello, {0}",name);
            Debug.WriteLine(message);
        }
    }
}

One might write:

namespace MyCompany.MyApp
{
    class HelloWorld
    {
        private static ResourceManager m_Resources = new ResourceManager("MyCompany.MyApp.HelloWorldClass_Resources");

        HelloWorld()
        {
            string name = m_Resources.GetString("Name.Text");
            string message = string.Format( m_Resources.GetString("HelloWorldMessage.Text"), name );
            Debug.WriteLine(message);
        }
     }
}

Which is not overlong, but is a bit less expressive. Note how I decided at some point to adopt a "dotted text" convention for my resource string names at some point. Not a particularly clever idea, in retrospect, since most of them end in ".Text", except when I decide they should end in ".Caption" as they represent eg. a button caption on a form. Of course then you discover that the button's property is also called Text rather than Caption, but there'd be retyping involved in changing it...and so on.

Nowadays I've adopted a rather simpler syntax. Assuming the existence of a handy UI class with some helpful static methods, I tend to write as follows:

namespace MyCompany.MyApp
{
    class HelloWorld
    {
        private static ResourceStrings S = UI.GetResourceStrings(typeof(HelloWorld));

        HelloWorld()
        {
            Debug.WriteLine( string.Format( S["HelloX"], S["World"] ) );
        }
     }
}

Which I think is remarkably tidier. Probably because it reminds me of the Visual C++ _T() macro to encapsulate text which, at compile time, may or may not turn out to be Unicode.

Anyhoo. The point was, that having adopted this new convention I was faced with having to change a number of source files to use the new style rather than the old.

Conventional search-and-replace is of little value here. Replacing m_Resources.GetString("SomeText") with S["SomeText"] can't quite be done. You could try it in two parts, replacing m_Resources.GetString(" with S[", and replacing ") with "], but you're bound to find a number of ")'s littered all over the shop which you'll have to fix manually, that belong to other things than a GetString() call.

With regular expressions, however, you can make the hougan work his vodun for you. Since Visual Studio's Find/Replace widget supports regular expressions, one can incant the following:

Find all   m_Resources.GetString${[^$]#}\)
And replace with   S[\(0,1)]

Once the sparks have settled down and the broom has finished sweeping the magician's workshop, one finds cheerfully that tedium has been averted and it's time for lunch.

For those who are relentlessly interested in regular expressions, I've also just found the aptly named http://www.regular-expressions.info/, which does exactly what it says on the tin, if URL's were to come in tins. URL. Don't get me started on that little moniker...

Super size me

2005-12-12T15:12:00Z

Having fixed all the outstanding "Oh my [deity/dictator of choice], we have to fix this now or people will be really rather unhappy" issues (as they're referred to under our bug tracking system. Or is that my imagination? You know, I think it probably is) against DTS Package Compare 2.0, it was time to turn attention to some more of those niggling little issues which don't really make much of a difference to the masses, but you know are really going to irritate somebody at some point.

Remember, Remember...

Applications should remember the state they were in when you last ran them, goes an unwritten tenet. The old version of DTS Compare 1.5 didn't do this (very naughty of it), and so we'd duly carried this bug over to the new DTS Package Compare 2.0 to make sure we fixed it. For some time, including our private beta, the application had gotten away with simply maximising itself to the primary monitor on the user's system, and this was generally splendid, if extraordinarily cheap. But since lots of other applications do recall where they were when you were last using them, we shouldn't really be the exception.

Now I don't like working in this little simple looking area of user interface programming, as it rarely gets done properly. Take well known spreadsheet and word processing applications, for example: I've on several occasions had to help out colleagues or family members who started the application only to have it appear on the Windows taskbar but not exhibit any notable window in any way. Now those of us In The Know can often fix these problems by activating the (rather non-visible) window, pressing Alt-Space then M (for the system menu's "Move" option on that window, which you could of course pick with the mouse...if you could see the window in question), and pressing cursor keys for a while until the window appears. In these cases the application just got confused (or you've dropped your screen resolution rather substantially since last running the app) and positioned its window offscreen. So moving it back on screen tends to help. Occasionally of course some well known applications get themselves into such a pickle that you have to start mumbling mantras like "Aich kee current user, Software, Vendorname, ApplicationName, ah let's just delete everything" in order to fix the problem. And should you invoke such voodoo at your workstation, expect your computer to notice if you have a microphone attached, and a booming voice to warn you that messing with the registry can cause infertility, global warming, and seriously damage the economy of small countries. Well, not really. But in any case it's not a fun way to spend a morning when you haven't had your second coffee.

I also have to mention, as an aside, a well known brand of developer tool which can occasionally encounter fatal problems loading projects. It also has a setting to load the last project when you open the tool. Windows of course now has a setting whereby if it encounters a fatal problem it will automatically restart the troubled application. These three work together in an amusing fashion: sepuku.mpg on a loop.

The Registry

The point being that simply dumping out window positions as reported by Windows and stuffing such dumps into the registry is not generally clever. This situation is not helped if you're using third party user interface libraries, which helpfully provide mechanisms to do exactly this, thus saving you the bother of considering whether it's a good idea since you can simply call them, rebuild, and close any bugs you might have in this area, before cheerfully strolling down to the boozer.

There are two principal reasons why this approach is, IMHO, not a wise plan. The first is that stuffing user-related data into the registry is now frowned upon in many circles. Let's be honest, it wasn't a great place to put things in anyway. The registry was a wonderful new way of crossing interstellar distances without all that tedious messing about with INI files, but INI files were handy as you could usually just delete them without breaking applications. Deleting things from the registry is not an activity for the faint hearted, at least before they've had a few drinks, and they're likely to regret it in the morning anyway. (At least one headache will result. For some reason I have a fleeting image of a PC booting, looking in its registry and discovering an unexpected traffic cone planted in HKEY_LOCAL_MACHINE, and then promptly BSOD'ing with an 0x80040200 NoIdeaWhatHappenedLastNightError. But then I may just need a holiday.) INI files were accessible and user-visible; the registry is neither nor. Luckily, Windows nowadays provides applications with an Application Data folder for each user (and a choice whether this should accompany roaming profiles, or stay doggedly attached to the current workstation) in which they can place their own settings files. And, hopefully, react well if a user comes along and gleefully trashes them all.

Lies, Damned Lies, and Window Positions

The second reason, which was originally by way of being the subject of this article, is that Windows lies about window positions. This is hard for non-programmers to see (but probably easier to believe).

If you minimise a window, for instance, and then ask Windows where it is (the Win32 API GetWindowRect), it will tell you that it's top left corner is at coordinates (-32000, -32000). Skeptics, realising that the top left of the screen is (0,0), and the bottom right usually say (1024,768) or similar, may doubt the verisimilitude of this report. And with good reason. What Windows means is that the Window is minimised, and so it isn't going to tell you where the Window actually is. That's no longer any of your concern. If you wanted to know where the Window was when it wasn't minimised, you should have asked before the user went and minimised it. Silly application programmer.

And indeed if you maximise a window, and then ask Windows where it is, it will tell you that its top left corner is at coordinates are of the order of (0,-4). Skeptics might be willing to let that pass, as they might deduce that Windows has, in order to make the window take up the whole screen, moved the top of it slightly off the screen.

Now one could ignore these little foibles. But of course, just because a minimised window has coordinates (-32000,-32000) doesn't mean that setting a window's coordinates to (-32000,-32000) will make it minimised. That would be silly. No, windows have a fairly sensible state that can be detected, amongst other things, via the Win32 API GetWindowPlacement. This window state (also referred to as its ShowWindow flags or Show Command) is either SW_SHOW, SW_HIDE, SW_MINIMIZE, SW_MAXIMIZE, or other variations on the theme. This, not the window rectangle, is your true indicator of whether a window is minimised, maximised or regular. And in order to super size (or sub size) your window, you'll have to set this window state directly.

So. One could save this state with the application; save the window coordinates as well; and restore them both when the application is next started. Correct? Er, no. Because as previously mentioned, the coordinates returned when a window is minimsed or maximised are not the coordinates you should be saving. Those are the coordinates imposed on the window due to its minimised/maximised state, which do not reflect the actual window position prior to this occurring. A window might be 100 by 100 pixels in the bottom right of the screen, for example, but be maximised to take up the full screen. If you save its position after it's been maximised, and then when the app is run set the maximised state and position, then when the user un-maximises (restores) the window it will not return to its diminutive location in the bottom right, but stay taking up the full screen.

So in terms of remembering window position to save on application exit, one has to watch when window sizes and positions change. One then has to record the true position of the window before the user minimises or maximises it, and ignore the window position (not record it) if that window is currently minimised or maximised. Then, on exit, you save the last recorded position, along with whether the window is minimised or maximised. On startup, you restore this information, and Bob is a close relative.

In C#, you do this via the Form.WindowState property (which accepts Maximized, Minimized or Normal) and the Form.Location and Form.Size properties. This maps to calling the Win32 APIs GetWindowRect() (for coordinates) and GetWindowPlacement() (for window state).

Screening for Problems

Of course it's not as easy as that. This is software. Just because you're trying to do something so trivial that it should be possible whilst drinking coffee and eating doughnuts, doesn't mean that you've got a hope of getting it done swiftly.

The above works pretty well, but then what if the user has multiple monitors? Under .NET, saving the position works fine, but restoring it doesn't. Specifically, if the window is maximised on the second monitor when you quit the application, when you restore it it will be maximised on the first monitor. However, if you then restore (de-maximise) it, it will return to its non-maximised position on the second monitor. Clear? Jolly good.

The reason is, it turns out, that .NET sets the window rectangle through the Win32 SetWindowPos API(). This should be fine, as it accepts all the relevant parameters, and in most cases works well. Sadly, this API doesn't deal well with muliple monitors: the above bizarrety occurs.

Of course there is a solution: the GetWindowPlacement() API, previously referred to, can retrieve window coordinates as well as window state. And there is a corresponding SetWindowPlacement() API which accepts both pieces of information, and behaves correctly. (Indeed, coordinates returned from GetWindowPlacement() can only be provided to SetWindowPlacement(), and not eg. SetWindowPos(): they're special in some irritating fashion.) The only minor downside (apart from having spent valuable beer time figuring this out) is that from .NET one has to invoke native code, and DllImport GetWindowPlacement and SetWindowPlacement from user32.dll. No big deal. DTS Package Compare has to use a whole slew of native functions for cases such as this, so a couple more is not, as it were, a biggy.

So that's another completed tour through the mundane and slightly vexing. At some stage I may blog about something that was actually difficult to solve...but there seems to be so much more milage in life's little irritations.

Cowboy filenames

2005-12-02T16:53:00Z

If you ask me, and I take it as implicit by your visit that you do (sorry about that, but it's Friday afternoon and I need a weekend), Windows is far too facist about filenames. Most notably in terms of the characters one can put into a filename, and the obstreperous way it will cough and whine at you if you dare to break the rules.

For the application I'm working on, we automatically generate filenames based on certain criteria. These filenames can contain descriptive text which we don't necessarily want to restrict to the set of filename-legal characters (I'll return to this point momentarily). So we have to filter the descriptive text and strip out illegal characters.

So what is the set of legal characters? I consulted my trusty internet, and after the usual few minutes throwing seaweed over it and sticking pins in effigies, it yielded the following rather useful bit of wisdom, from the Windows Platform SDK, under Win32 and COM Development, System Services, Files and I/O, SDK Documentation, Storage, Storage Overview, File Management, Creating, Deleting and Maintaining Files, Naming a File.

...
Use any character in the current code page for a name, except characters in the range of 0 through 31, or any character that the file system does not allow. A name can contain characters in the extended character set (128–255). However, it cannot contain the following reserved characters:
< > : " / \ |

The following reserved device names cannot be used as the name of a file: CON, PRN, AUX, NUL, COM1, COM2, COM3, COM4, COM5, COM6, COM7, COM8, COM9, LPT1, LPT2, LPT3, LPT4, LPT5, LPT6, LPT7, LPT8, and LPT9. Also avoid these names followed by an extension, for example, NUL.tx7.
...

Now at first, as I was in a hurry, I read this as "everything except < > : " / \ | is ok".

But I forgot that characters below 32 are explicitly disallowed.

And then I noticed that (quite naturally) applications don't let you save filenames containing the "*" key, as this is used as a wildcard.

And also filenames containing the "?" key are disallowed. This is also a wildcard character, and used to escape long paths by prepending "\\?\" (I always liked that little hack. Such style.)

But with a firm conviction that MSDN Does Not Lie, I checked my ASCII table to make sure I wasn't hallucinating. Sure enough, "*" and "?" are ASCII 42 and 63 (please don't anyone tell me they knew that from memory, or I may cry).

But, and aha, there's another clause I hadn't read properly: "...or any character that the file system does not allow". So, time to work out what characters are considered illegal by FAT, FAT32 and NTFS, methought.

Around this point I came across the system structure BIGFATBOOTFSINFO, which I only mention as it's an excellent name. But I digress.

So, I wandered towards the Platform SDK again, by way of Win32 and COM Development, Development Guides, Windows 95/98/Me Programming, Long File Names, Long File Names and the Protected-Mode FAT System. It was a long walk, but I sustained myself on the way with a coffee. Herein I found more wisdom.

...
When an application creates a file or directory that has a long file name, the system automatically generates a corresponding alias for that file or directory using the standard 8.3 format. The characters used in the alias are the same characters that are available for use in MS-DOS file and directory names. Valid characters for the alias are any combination of letters, digits, or characters with ASCII codes greater than 127, the space character (ASCII 20h), as well as any of the following special characters.

$ % ' - _ @ ~ ` ! ( ) { } ^ # &

The space character has been available to applications for file names and directory names through the functions in current and earlier versions of MS-DOS. However, many applications do not recognize the space character as a valid character, and the system does not use the space character when it generates an alias for a long file name. MS-DOS does not distinguish between uppercase and lowercase letters in file names and directory names, and this is also true for aliases.

The set of valid characters for long file names includes all the characters that are valid for an alias as well as the following additional characters.

+ , ; = [ ]
...

So, we're getting a little closer. At least FAT16 fesses up to not allowing "*" and "?".

As to exactly which characters are disallowed by NTFS, this remains a mystery to me. It doesn't fess up to disallowing "*" and "?", and indeed googling suggests that all Unicode characters are allowed by NTFS as filenames. So it is presumably the OS level Windows, quite possibly not much deeper than the Win32 API, which disallows "*" and "?" as well as the characters listed in the first quoted section above. Could it disallow others? Since "*" and "?" are not documented explicitly, one has to presume so.

If it wasn't for the fact that our application had to support Unicode, I would write a simple filter based on ASCII character codes, and be rather facist about it. In our case we can simply replace offending characters with underscores "_" and have done with it. Once, of course, we've determined what those offending characters are.

I'm sure I've encountered this issue before, and I seem to recall wishing then just for a simple table, for both Unicode and ASCII characters, showing which were disallowed and by what level of the system (shell, file system API, file system itself). My wish is still pending, but since I have some pressing work to do I should Alt-Tab and get on with it...