c# - Connect to domain SQL Server 2005 from non-domain machine

Question

I asked a question a few days ago (Access to SQL Server 2005 from a non-domain machine using Windows authentication) which got some interesting, but not usable suggestions. I'd like to ask the question again, but make clear what my constraints are:

I have a Windows domain within which a machine is running SQL Server 2005 and which is configured to support only Windows authentication. I would like to run a C# client application on a machine on the same network, but which is NOT on the domain, and access a database on the SQL Server 2005 instance.

I CANNOT create or modify OS or SQL Server users on either machine, and I CANNOT make any changes to permissions or impersonation, and I CANNOT make use of runas.

I know that I can write Perl and Java applications that can connect to the SQL Server database using only these four parameters: server name, database name, username (in the form domainuser), and password.

In C# I have tried various things around:

string connectionString = "Data Source=server;Initial Catalog=database;User Id=domainuser;Password=password";
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

and tried setting integrated security to true and false, but nothing seems to work. Is what I am trying to do simply impossible in C#?

Thanks for any help, Martin

Answer 1

I had a similar problem where I was writing a tool that needed to run on a machine on one domain and authenticate with a SQL server on another domain using a trusted connection. Everything I could find on the subject said it couldn't be done. Instead you must join the domain, use SQL authentication, get involved with some chap called Kerberos, or get your network guys to setup a trusted relationship, to name a few alternatives.

The thing is I knew I could get it working in some way using RUNAS because I'd proven it with SSMS:

C:WINDOWSsystem32
unas.exe /netonly /savecred /user:megacorpjoe.bloggs "C:Program FilesMicrosoft SQL Server90ToolsBinnVSShellCommon7IDESqlWb.exe"

The /netonly flag allowed me to execute the exe with the local credentials and access the network with the remote credentials, I think, anyway I got the result set I expected from the remote server. The problem was the runas command made it very difficult to debug the application, and it didn't smell good.

Eventually I found this article on the code project which was talking about authenticating to manipulate Active Directory, Here is the main class that does the impersonation:

    using System;
    using System.Runtime.InteropServices;  // DllImport
    using System.Security.Principal; // WindowsImpersonationContext

    namespace TestApp
    {
        class Impersonator
        {
            // group type enum
            enum SECURITY_IMPERSONATION_LEVEL : int
            {
                SecurityAnonymous = 0,
                SecurityIdentification = 1,
                SecurityImpersonation = 2,
                SecurityDelegation = 3
            }

            // obtains user token
            [DllImport("advapi32.dll", SetLastError = true)]
            static extern bool LogonUser(string pszUsername, string pszDomain, string pszPassword,
                int dwLogonType, int dwLogonProvider, ref IntPtr phToken);

            // closes open handes returned by LogonUser
            [DllImport("kernel32.dll", CharSet = CharSet.Auto)]
            extern static bool CloseHandle(IntPtr handle);

            // creates duplicate token handle
            [DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]
            extern static bool DuplicateToken(IntPtr ExistingTokenHandle,
                int SECURITY_IMPERSONATION_LEVEL, ref IntPtr DuplicateTokenHandle);

            WindowsImpersonationContext newUser;

            /// 
            /// Attempts to impersonate a user.  If successful, returns 
            /// a WindowsImpersonationContext of the new users identity.
            /// 
            /// Username you want to impersonate
            /// Logon domain
            /// User's password to logon with
            /// 
            public Impersonator(string sUsername, string sDomain, string sPassword)
            {
                // initialize tokens
                IntPtr pExistingTokenHandle = new IntPtr(0);
                IntPtr pDuplicateTokenHandle = new IntPtr(0);
                pExistingTokenHandle = IntPtr.Zero;
                pDuplicateTokenHandle = IntPtr.Zero;

                // if domain name was blank, assume local machine
                if (sDomain == "")
                    sDomain = System.Environment.MachineName;

                try
                {
                    const int LOGON32_PROVIDER_DEFAULT = 0;

                    // create token
                    // const int LOGON32_LOGON_INTERACTIVE = 2;
                    const int LOGON32_LOGON_NEW_CREDENTIALS = 9;
                    //const int SecurityImpersonation = 2;

                    // get handle to token
                    bool bImpersonated = LogonUser(sUsername, sDomain, sPassword,
                        LOGON32_LOGON_NEW_CREDENTIALS, LOGON32_PROVIDER_DEFAULT, ref pExistingTokenHandle);

                    // did impersonation fail?
                    if (false == bImpersonated)
                    {
                        int nErrorCode = Marshal.GetLastWin32Error();

                        // show the reason why LogonUser failed
                        throw new ApplicationException("LogonUser() failed with error code: " + nErrorCode);
                    }

                    bool bRetVal = DuplicateToken(pExistingTokenHandle, (int)SECURITY_IMPERSONATION_LEVEL.SecurityImpersonation, ref pDuplicateTokenHandle);

                    // did DuplicateToken fail?
                    if (false == bRetVal)
                    {
                        int nErrorCode = Marshal.GetLastWin32Error();
                        CloseHandle(pExistingTokenHandle); // close existing handle

                        // show the reason why DuplicateToken failed
                        throw new ApplicationException("DuplicateToken() failed with error code: " + nErrorCode);
                    }
                    else
                    {
                        // create new identity using new primary token
                        WindowsIdentity newId = new WindowsIdentity(pDuplicateTokenHandle);
                        WindowsImpersonationContext impersonatedUser = newId.Impersonate();

                        newUser = impersonatedUser;
                    }
                }
                finally
                {
                    // close handle(s)
                    if (pExistingTokenHandle != IntPtr.Zero)
                        CloseHandle(pExistingTokenHandle);
                    if (pDuplicateTokenHandle != IntPtr.Zero)
                        CloseHandle(pDuplicateTokenHandle);
                }
            }

            public void Undo()
            {
                newUser.Undo();
            }
        }
    }

To use it just:

Impersonator impersonator = new Impersonator("username", "domain", "password");

//Connect to and use SQL server

impersonator.Undo();

I added in the Undo method otherwise the impersonator object tended to get garbage collected. I also altered the code to use LOGON32_LOGON_NEW_CREDENTIALS but this was a poke and run to make it work; I still need to understand fully what it does, I have a feeling its the same as the /netonly flag on runas. I'm also going to break down the constructor a bit.