Authenticate against a non-trusted domain from WPF application

Question

I have a WPF application that requires access to an SQL Server 2008 R2 database. The database is secured using active directory. The application runs fine on workstations that are connected to the domain and where the user has succesfully logged on.

I now need to be able to run the same application on workstations connected to a different domain. Unfortunately, this new domain is controlled by SBS and as such I cannot set up a trust relationship between them.

Effectively, what I (think I) need is a facility to allow a user to enter credentials that have been set up on the domain hosting the database server (user name and password) and then impersonate that user when connecting to the database server.

I have tried the Microsoft sample using LogonUser, LOGON32_PROVIDER_DEFAULT & LOGON32_LOGON_INTERACTIVE but this does not seem to do what I want - instead complaining that I cannot log on as the workstation I am using does not have a domain account.

Does anybody have any alternative suggestions?

Answer 1

Well, it seems that maybe it can be done after all. I found the following link, and that lead me to realise that my primary problem was incorrect usage of the LOGON32_LOGON_INTERACTIVE parameter for the LogonUser API call (it should have been LOGON32_LOGON_NEWCREDENTIALS).

As such, I can now use the following code to connect to a database on SQL Server, protected by Windows Authentication but on a totally unrelated domain to the workstation from which the code is running...

static void Main(string[] args) {

SafeTokenHandle safeTokenHandle;

try {

    string userName = @"*****", domainName = @"*****", password = @"*****";
    bool returnValue = NativeMethods.LogonUser(userName, domainName, password, 
        NativeMethods.LogonType.NewCredentials, NativeMethods.LogonProvider.Default, out safeTokenHandle);

    if (false == returnValue) {
        int ret = Marshal.GetLastWin32Error();
        Console.WriteLine("LogonUser failed with error code : {0}", ret);
        throw new Win32Exception(ret);
    }

    using (safeTokenHandle) {

        WindowsIdentity windowsIdentity = new WindowsIdentity(safeTokenHandle.DangerousGetHandle());
        using (WindowsImpersonationContext impersonationContext = windowsIdentity.Impersonate()) {

            using (DataTable table = new DataTable()) {
                using (SqlDataAdapter adapter = new SqlDataAdapter()) {
                    using (adapter.SelectCommand = new SqlCommand(@"select * from dbo.MyTable")) {
                        adapter.SelectCommand.CommandType = CommandType.Text;
                        using (adapter.SelectCommand.Connection = new SqlConnection(@"Data Source=Server;Initial Catalog=Database;Integrated Security=Yes")) {
                            adapter.SelectCommand.Connection.Open();
                            adapter.Fill(table);
                        }
                    }
                }

                Console.WriteLine(string.Format(@"{0} Rows retrieved.", table.Rows.Count));

            }

        }

    }

}
catch (Exception ex) {
    Console.WriteLine("Exception occurred. " + ex.Message);
}

Of course, it needs tidying up and I need to prompt the user for their credentials (there is no way they will convince me to hard code credentials) but in principle it works (save for the anonymisation).

Hope this helps somebody else some time.

Oh, and you will also need the following...

public sealed class SafeTokenHandle : SafeHandleZeroOrMinusOneIsInvalid {

    private SafeTokenHandle() : base(true) {
    }

    protected override bool ReleaseHandle() {
        return NativeMethods.CloseHandle(handle);
    }

}

[DllImport(@"kernel32.dll")]
[ReliabilityContract(Consistency.WillNotCorruptState, Cer.Success)]
[SuppressUnmanagedCodeSecurity]
[return: MarshalAs(UnmanagedType.Bool)]
public static extern bool CloseHandle(
    IntPtr handle);

[DllImport(@"advapi32.dll", SetLastError = true, CharSet = CharSet.Unicode)]
public static extern bool LogonUser(
    String lpszUsername,
    String lpszDomain,
    String lpszPassword,
    LogonType dwLogonType,
    LogonProvider dwLogonProvider,
    out SafeTokenHandle phToken);

public enum LogonType {
    Interactive = 2,
    Network = 3,
    Batch = 4,
    Service = 5,
    Unlock = 7,
    NetworkClearText = 8,
    NewCredentials = 9
}

public enum LogonProvider {
    Default = 0,
    WinNT35 = 1,
    WinNT40 = 2,
    WinNT50 = 3
}

Answer 2

WITH YOUR CURRENT SET UP

The database is secured using active directory

I assume you mean that your SQL connection is using Windows authentication. Either way, there's a little more going on than perhaps you realize. If you're using the "active directory" for authentication identities, I'd bet you're relying on Kerberos for Windows authentication (it's not just your authentication type, but also your credential type). The article I've linked explains the difference between Kerberos and NTLM for SQL Server 2005, but it's the same for 2008 R2.

Unfortunately, without the trust relationship between the domains, it does not matter if you successfully impersonate a user in the domain hosting the database, you will not be able to connect (as you're witnessing). You need that trust relationship.

If you do manage to establish a trust relationship between the domains, I've posted how to accomplish cross-domain Windows authentication for SQL Server using domain groups here, which may be useful to you.

ALTERNATIVE SET UP

If you don't want to use SQL authentication (I don't like packaging credentials with my applications), I encourage you to separate your database operations into a more service oriented architecture. So the actual SQL work being done would go into a WCF service (that is hosted in the same domain as your database and impersonates the service identity) and your application would simply solicit the service. Then you can use NTLM to secure your web service to still use Windows authentication. This way, you can still verify who the soliciting user is, and rely on your own basic security structures (i.e. simple tables) to authorize usage.

Let me know if this doesn't make sense to you or if you need further clarification.

Answer 3

I do have a suggestion... but I don't think you're going to like it.

Use SQL Authentication instead of Integrated.

I had a similar issue and after working it through - turned out there really wasn't a method for making it happen. See here for my post on dba.stackexchange.com

If using SQL Authentication isn't an option there still "may" be a duct-tape/bailing wire/total hacker way of getting it done but you'd probably have to migrate the question to one of the server administrator forums and not StackOverflow - since it's no longer a programming question.