Wednesday, December 20, 2006

Connect IIS to SQL 2005

After the initial R&D work is over in our web application we have to transfer our web site from cassiny to IIS.

The first problem we encounter is that our web app stoped talking to SQL Server with the problem, "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections."

Due to this reason I looked at the ways a web server (IIS) can connect to SQL server and found 4 generic ways.

  1. Using windows integrated security
  2. Mapped windows domain user
  3. Using a connection string
  4. As a local user

Our web application actually uses the connection string to communicate with the SQL Server.  But in order to convince SQL server that this connection request comes from a valid party u need to have an initial access to the DB. That can be done by using impersonation.

What u need to do is to Map IIS anonymous user to a mapped domain user which is trusted by the DB server. This scenario is evident when u have the DB server and Web server in two different machines.

In the web.config put the following entry

<identity impersonate="true" userName="domain\username" password="password"/>

You also need to add this domain user to the SQL server with the necessary rights.

More info can be found in in MSDN.


Post a Comment