Connect to domain SQL Server 2005 from non-domain

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 domain\user), and password.

In C# I have tried various things around:

string connectionString = "Data Source=server;Initial Catalog=database;User Id=domain\user;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:\WINDOWS\system32\runas.exe /netonly /savecred /user:megacorp\joe.bloggs "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.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.

Answer 2

You have to configure SQL Server to allow SQL Server Authentication, i.e. authentication using username and password.

You can't authenticate by domain username/password 'like' server authentication, i.e. specify domain username/password directly.

I can be wrong of course, but I'm sure that this isn't a problem of C# or .NET. How can you login on SQL Server in your Perl or Java application??

Answer 3

Here is the sample code that I use to connect from a non-domain machine using the jTDS JDBC driver:

Class.forName("net.sourceforge.jtds.jdbc.Driver").newInstance(); String url = "jdbc:jtds:sqlserver://server/database;domain=domain"; conn = DriverManager.getConnection(url, "user", "password");

Answer 4

I'll give you the Java answer which I'm more familiar with: I use the jTDS JDBC driver with the four parameters mentioned above. The Perl application I know less about, but is running on a Linux box, and is able to connect with the same parameters. I cannot change the SQL Server to support SQL Authentication.

To answer Remus' suggestions, I cannot do any of those three things he suggests and yet Java and Perl applications are able to connect. Any other ideas?

Thanks, Martin

Answer 5

As you correctly say, JDBC or Perl on a Linux machine can both connect to an SQL Server using Windows authentication and credentials which differ from the currently logged on user. The same is true for Windows CE devices, by the way.

I think that this is that this is not an issue of C# but of the SQL Server OLE DB driver. I guess the methods mentioned above "pretend to be a Windows machine using some specific credentials" on the network level; a feature, which the SQL Server OLE DB driver lacks. Thus, my suggestion would be to look for an alternative (maybe commercial?) OLE DB driver that can access SQL Server databases. I'm not sure if such a thing exists, though.

Answer 6

Is it an option to prompt for credentials?