3

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/

2 Answers 2

2

Create an account for sql to use, either a windows or sql account, and specify that in the connection string. For security purposes, you should also encrypt the web.config after doing this using the command:

%SYSTEMROOT%\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -prov "RsaProtectedConfigurationProvider" -pef connectionStrings "[PathToWeb.Config]"  

That account should have the required permissions to the database, such as db_DataReader and db_DataWriter.

6
  • This is preferred to using the account the Application Pool is running as?
    – Marcus
    Mar 1, 2012 at 23:02
  • I usually prefer to not couple the application pool identity with the SQL authentication. If I needed to, I could impersonate the logged on user to perform various functions using their token. However, the most important consideration for me would be scale and reliability. Using a SQL logon account just works, and it is by far the most common scenario in N-Tier applications. In particular, if using integrated authentication, a separate SQL connection pool would need to be created for each user, which does not scale.
    – Greg Askew
    Mar 1, 2012 at 23:07
  • Should we create a separate user account on the domain to be used for each SQL database?
    – Marcus
    Mar 1, 2012 at 23:14
  • I don't know. That would most likely depend on the security requirements. It isn't too difficult to change later and add separate accounts if required, although it would require brief downtime to change the credentials. Some people would probably prefer to use a separate account just in case.
    – Greg Askew
    Mar 1, 2012 at 23:35
  • Is there any way to set this up without having to type out a password in the config or the code itself (Classic ASP)?
    – Marcus
    Mar 1, 2012 at 23:50
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .