CODEWITHSUNDEEP
c#sql-serverimpersonation
JTennessen
JTennessen

Reputation: 317

Impersonation succeeds but database connection fails

I have C# (.NET 4.5) code like this (simplified for demonstration) used to connect to a SQL Server 2012 database:

public static void Test(WindowsIdentity ident)
{
    using (WindowsImpersonationContext ctx = ident.Impersonate())
    {
        using (SqlConnection con = new SqlConnection("Data Source=MyServer;Initial Catalog=MyDatabase;Persist Security Info=False;Integrated Security=SSPI;Network Library=dbmssocn"))
        {
            con.Open();
        }
        ctx.Undo();
    }
}

The Open() method throws the following exception every time:

System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)

The impersonation is succeeding because if I add tracing like this:

public static void Test(WindowsIdentity ident)
{
    using (TextWriterTraceListener listener = new TextWriterTraceListener(@"C:\temp\trace.log"))
    {
        using (WindowsImpersonationContext ctx = ident.Impersonate())
        {
            listener.WriteLine("Impersonated");
            using (SqlConnection con = new SqlConnection("Data Source=MyServer,MyPort;Initial Catalog=MyDatabase;Persist Security Info=False;Integrated Security=SSPI;Network Library=dbmssocn"))
            {
                listener.WriteLine("About to open connection; WindowsIdentity.GetCurrent().Name = " + WindowsIdentity.GetCurrent().Name);
                con.Open();
            }
            ctx.Undo();
            listener.WriteLine("Impersonation undone");
        }
    }
}

I get this:

Impersonated
About to open connection; WindowsIdentity.GetCurrent().Name = MyDomain\MyUser

If I put the exact same connection string in a .udl file, run it under the same "MyDomain\MyUser" account on the same machine, and click Test Connection, it succeeds. Since that's the case, I just don't see how the problem could be a firewall or anything of that nature. Does anyone know what could be going wrong? I do not have access to log onto the database server itself, so I cannot check its event log (etc.) at this point.

Upvotes: 6

Views: 5545

Answers (2)

StrayCatDBA
StrayCatDBA

Reputation: 2880

The anonymous login failed error means that kerberos authentication failed for some reason.

Kerberos delegation (delegation is when your impersonated credentials are passed to a different machine) is relatively simple in theory. In practice it is fraught with gotcha's and can be one of the most frustrating things to troubleshoot.

In your case, it is entirely possible that from the standpoint of your program, the impersonation is succeeding, but the token it is passing to sql server isn't usable.

If you are lucky and have access to a really good network/system admin, then they can help you troubleshoot it.
Otherwise googling "sql server kerberos delegation" will point you in the right direction.

This is the kind of problem that requires substantial back and forth to resolve and is more suited to a forum than a Q&A site.

Upvotes: 0

Toan Vo
Toan Vo

Reputation: 1298

Your problem come from the connection string. The "Network Library = dbmssocn" in connection string will make client attempting to connect to SQL server on the UDP port 1434 rather than the TCP port 1433. You remove the "Network Library = dbmssocn" from the your application's connection string the application will connect to SQL server successfully.

Upvotes: 2

Related Questions