SqlConnection Integrated Security

Question

I have a web app that runs on a Windows Server within IIS. This app is using Windows Authentication. I am trying to use the C# SqlConnection, as I need to call a stored procedure with a TVP, but I am getting a login issue.

The error is

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON

but my HttpContext.Current.Identity is my Windows account.

using (var conn = new SqlConnection(connection))
{
    conn.Open();
}

This works if I use Entity Framework:

using (var context = new DatabaseEntities())
{
  ...       
}

I was curious why in the standard c# SqlConnection, it doesn't use the Windows identity, but in the Entity Framework connection, it does. Any help?

What do you mean "standard c# sql connection"? As opposed to what? You've shown two identical pieces of code. Are you saying one works and the other doesn't? What is the content of the `connection` variable? (replace any addresses, usernames and password with dummy data but keep all the *parts*). — Lasse V. Karlsen, Aug 16 '16 at 21:32
As an aside, you know you can invoke/call a stored proc with Entity, right? I'm curious why you are normally using EF but for this one sproc call, you apparently don't want to? — Nikki9696, Aug 16 '16 at 21:34
I have updated my OP. This is only failing when running on a different server, so I cannot get you the connection information. I am not using Entity Framework because EF doesn't support table valued parameters as arguments for SP. — user3726393, Aug 16 '16 at 21:53
That logon error usually indicates a double hop issue: http://stackoverflow.com/questions/10957443/web-app-getting-login-failed-for-user-nt-authority-anonymous-logon How many server are involved in this including the client? Do you have three: Client,IIS,SQL Server? Is the SQL Server on a different box to IIS? — Nick.Mc, Aug 16 '16 at 23:15
Yes, currently there is three and SQL Server is on a different box than IIS. I have figured this was the case. Are there any good docs that show how to implement delegation that fixes this? — user3726393, Aug 17 '16 at 18:17

score 0 · Answer 1 · answered Aug 16 '16 at 21:51

To use HttpContext.Current.Identity when making the database call, you need to enable impersonation. This changes the asp.net process to run impersonating you.

To enable it, add the following to the system.web node of the web.config:

<identity impersonate="true"/>

You can find more information at https://msdn.microsoft.com/en-us/library/xh507fc5.aspx

SqlConnection Integrated Security

1 Answers1