Impersonation succeeds but database connection fails

Question

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.

score 2 · Accepted Answer · answered Jun 14 '13 at 22:20

2

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.

answered Jun 14 '13 at 22:20

Toan Vo

1,270
9
19

Thanks for the suggestion. After removing that segment from the connection string, I get the following error instead: System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. However, the trace remains the same, meaning the impersonation is still succeeding, but for some reason it is not being used in the attempt to connect? – JTennessen Jun 14 '13 at 22:29
So that's another issue. You should check the network configuration which lead to use anonymous instead of using current logged user. – Toan Vo Jun 14 '13 at 22:33
Try this "Data Source=192.162.1.100,1433;Network Library=DBMSSOCN;..." which specific port to connect by replace 192.162.1.100 is your computer port. – Toan Vo Jun 14 '13 at 22:34
OK, fair enough. Unfortunately, I am woefully ignorant of networking and, as mentioned, I do not have access to the SQL Server box itself. I am going to accept your answer, since it did answer my original question. I will submit another question to see if anyone can help me with the new problem. Thanks very much! – JTennessen Jun 14 '13 at 22:36
No luck, I still get the "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'". I will submit another question. Much appreciated! – JTennessen Jun 14 '13 at 22:41

score 0 · Answer 2 · answered Jun 17 '13 at 06:24

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.

Impersonation succeeds but database connection fails

2 Answers2