Issue Passing Credentials from ASP.NET To SQL Server

Question

Setup:

IIS on Windows 2008 Server R2 Enterprise, SQL Server 2008 R2 Enterprise, ASP.NET 2.0 Web Application.

Purpose:

We are converting the security model of an old web app from using a single SQL Server account to using Active Directory accounts. In the ASP.NET application, we've added <identity impersonate="true" /> as the first item in the <system.web> section of web.config. We've added database roles, which contain Active Directory groups, for granting access to the existing stored procedures. Our web.config already contains <authentication mode="Windows"/>. Here is my connection string, editing out server and DB names:

<add name="DbConn" 
     connectionString="
         Data Source=SERVER;
         Initial Catalog=DBNAME;
         Integrated Security=SSPI;"
     providerName="System.Data.SqlClient"
/>

Problem:

I am getting an error when trying to open an SqlConnection to the database:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Instead of going through all the logical layers of the application (web page to .DLL layers), I threw in a quick, plain web page that does some Response.Write in Page_Load(). I get this (account info edited out):

HttpContext.Current.User.Identity.Name = domain\my account

My.User.Name = domain\my account

Then Page_Load calls a Sub that does some Response.Write and tries to run an existing stored procedure. I get this:

Before opening the DB connection ...

HttpContext.Current.User.Identity.Name = domain\my account

My.User.Name = domain\my account

Sub TestDbCall() Failed! ex.Message = Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

It fails on opening the SqlConnection with error message above (Login failed ...). Clearly, the ASP.NET application recognizes my domain account. It seems that my Windows credentials are not propagating from the ASP.NET app to SQL Server. I'm at a loss as to why.

EDIT:

I found this page on microsoft.com that says in order to access an SQL Server that is not on the IIS server (who would even DO this???), you can't use Windows Authentication in IIS. Rather, you have to use Basic Authentication. It works, but I don't like the idea of users' account name and password floating around the corporate network in plain text. So even though it "works", I don't accept my own finding as an answer.

Silly question: Does your connection string have "Trusted Connection=true"? — CodingGorilla
– CodingGorilla, Commented Oct 11, 2011 at 18:25
there might be something in your iis settings. I can't remember what to change but I think you have to change the application pool setting to not use the .net service account. — Eonasdan
– Eonasdan, Commented Oct 11, 2011 at 18:36
@Coding Gorilla: Integrated Security=SSPI; is equivalent to Trusted Connection. — HardCode
– HardCode, Commented Oct 11, 2011 at 18:39
@HardCode Right; just wanted to check the obvious first. What authentication methods does the website (in IIS) allow? — CodingGorilla
– CodingGorilla, Commented Oct 11, 2011 at 18:42
@HardCode try changing that to Digest authentication and see if that works. — CodingGorilla
– CodingGorilla, Commented Oct 11, 2011 at 18:53

Remus Rusanu · Accepted Answer · 2011-10-11 21:39:50Z

4

When your IIS authenticates the user using Windows Authentication and then, while impersonating the connected user, it connects to any resource outside the local IIS box (like a SQL Server instance) the process is called Delegation, as described in Kerberos Protocol Transition and Constrained Delegation. The process, sometimes referred to informally as 'double hop', is subject of many How-Tos:

answered Oct 11, 2011 at 21:39

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HardCode Over a year ago

I'll check these out and see what I can come up with. Thanks.

DaveB · Accepted Answer · 2011-10-11 18:52:36Z

1

There is some IIS setup required. See the following document for a good explanation:

How to: Access SQL Server Using Windows Integrated Security

answered Oct 11, 2011 at 18:52

DaveB

9,5584 gold badges43 silver badges68 bronze badges

4 Comments

HardCode Over a year ago

I've found this article, but it requires Basic Authentication. I don't feel that within the Microsoft circle of technologies (Active Directory, IIS, ASP.NET, SQL Server) that sending login information over the wire in plain text is a solution :(

DaveB Over a year ago

@HardCode - I am using Integrated Windows authentication that doesn't send the credentials in clear text. The article mentions this. Your setup may vary.

HardCode Over a year ago

Is your SQL Server on the same box as IIS? Mine are on separate boxes, hence the article's mention of Basic Authentication being the only means to pass the security token to SQL Server.

DaveB Over a year ago

@HardCode - No, My IIS and SQL Server are on different boxes.

vittore · Accepted Answer · 2011-10-14 03:23:42Z

0

I would suggest to change the way you are going to use AD in your app.

I would map AD users to app internal users together with their AD roles mapped to app roles and use app roles to determine access not to stored proc directly but to Business Layer functions

answered Oct 14, 2011 at 3:23

vittore

17.6k6 gold badges46 silver badges84 bronze badges

1 Comment

HardCode Over a year ago

I do have Database Roles (not Application Roles) which the Active Directory groups map to, and those DB roles are mapped to the SPs. However, in the end, it still works out to allowing users in an AD group to access an SP.

Collectives™ on Stack Overflow

Issue Passing Credentials from ASP.NET To SQL Server

3 Answers 3

1 Comment

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related