CODEWITHSUNDEEP
c#asp.netiissqlconnection
Barnabeck
Barnabeck

Reputation: 481

How to configure connection string and IIS application pool Identity to enable access SQL Server with current windows credentials

The question seems popular and repetitive, but I haven't found an answer that works for me in the last 24h, so don't blame me if I post it (again):

I have an IIS Server (ver.10) and an application that should connect to a SQL Server that runs on a different machine in our network. The credentials of the current AD Windows user should be passed to the SQL Connection String and grant access if that specific user is entitled to access. The correct syntax for that scenario seems to be this:

"Data Source=MY-SQL-INSTANCE;Initial Catalog=MY-DATABASE;Integrated Security=SSPI;"

If I do that, I get the SqlException "Login failed for user 'CORP\SERVERS0765$'" with CORP being the domain and SERVERS0765 being the server that runs the IIS. So for some reason the Identity of the IIS is passed, but not the identity of the one that called the app.

The Identity of the Application Pool is set to NetworkService, but I also tried all the other built-in-accounts. In my understanding it should be set to all "Domain\USERS" but then there is a password required which is never accepted.

Update: I picked this issue up again after some days and learned how to display the relevant parameters that defines your authentication:

Identity of the application Pool:      System.Security.Principal.WindowsIdentity.GetCurrent().Name;

Is User Authenticated:                 User.Identity.IsAuthenticated.ToString();

Authentication Type, if Authenticated: User.Identity.AuthenticationType;

User Name, if Authenticated:           User.Identity.Name;

That way I can actually see that switching on IIS ASP.NET Impersonation, changes the WindowsIdentity from the AppPool Identity to the User Identity.

Identity of the application Pool:      MyDomain\MyUserID

Is User Authenticated:                 True

Authentication Type, if Authenticated: Negotiate

User Name, if Authenticated:           MyDomain\MyUserID

Therefore it confuses me even more that if I launch the SQL Connection, the SQL Server replies: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' Anonymous Logon had been disabled!

Upvotes: 1

Views: 1830

Answers (0)

Related Questions