Mar 4, 2012
tom

Authentication between IIS & SQL Server for Load Balanced App

Question

I’m looking to use this connection string however I’m not sure how to set this up:

Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;

Previously our SQLNCLI10 connection string used a Uid & Pwd. We’ve now moved servers and have set up Active Directory, looking for some help as to how to configure security between IIS & MSSQL.

In IIS 7.5, there is an option to specify a user account in the Basic Settings of a web site or just use Pass-through authentication. I presume if pass through is set, it uses the account set up for the Application Pool?

By default the app pool is running as ApplicationPoolIdentity. Only websites in this app pool should have access to this database. Also there are multiple web servers using a shared config, each connecting to the same database server (or servers as they are mirrored using MSSQL Database Mirroring)

Currently when the script is run, this is the error message (MSSQL is on a different server in the same domain): Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

What identity should I make the app pool, and how should I set this account up in MSSQL?

I hope that all makes sense and thanks again!

I have a feeling this page is relevant, although I don’t really understand it: http://learn.iis.net/page.aspx/624/application-pool-identities/

Asked by Igor K

Answer

The solution was to create a new domain user with limited access rights. Add this new user as a login to MSSQL only for the specific database for just the permissions you need (eg read & write only).

Then configure the IIS website to connect under this new domain user. You would think that setting this up as the Application Pool Identity would work, but it doesn’t! To make it work you also must assign the computer with delegation rights in AD (Trust this user for delegation to any service (Kerberos only)).

Instead you could just open the Basic Settings of the website and click on the Connect As... option and enter the login details here.

This method doesn’t require you to specify the username and or password in the connection string at all, it’s all managed in IIS.

Answered by Igor K

Related posts:

  1. Load-Balanced IIS 7.5 Web Server ASP.NET Session State problem
  2. sql server windows authentication with IIS
  3. IIS 7 cannot log into Sql Server Database
  4. Load balanced IIS. Should I use NLB, or linux-based reverse proxy, or something else?
  5. Replicate IIS Settings for Load Balanced Websites

Leave a comment