Reader Level:
Article
Security in .NET

ASP.NET Security With SQL Server 2005

By Sanjay Gulati on Jul 11, 2006
This article focuses on security concerm when using SQL Server 2005 in ASP.NET application.

Today I was trying to access the web page from and getting following error
Error Message:Login failed for user . The user is not associated with a trusted SQL Server connection.

Web server and database are on different server and I am using Virtual machines (VMWare) for my development and DB server.

There can be problem in SQL server or IIS or application, so lets start with SQL Server.

SQL Server Settings: The security should be set for SQL Server and Windows Authentication.
pic1.gif

The SQL server has the logins for authenticated users and has proper permissions as I also use development machine to maintain DB.

IIS: Open IIS and select properties and under directory security tab click edit button.
pic2.gif

Uncheck Anonymous access and check integrated windows security; integrated windows security will enable users login to be used for authentication instead of IIS default user.

pic3.gif

Now, lets put application web.config under microscope.

I have custom error tag to redirect error to default page

<customErrors mode="On" defaultRedirect="showError.aspx">

</customErrors>

 

This is good and for testing I set the Mode tag to off.

 

Connection string uses integrated security tag and set it to true.

"server=servername;database=TestDB;Integrated Security=true" />

 

Here is another tag identity. Ah! Impersonate is false and I want it false so I can have connection pooling.

 

<identity impersonate="false"/>

 

So where is the problem, all the setting seems right, why the heck it does not work.

 

I changes the identity impersonate tag to true and Voila! It works.J

 

For the time being I can work but it's not a solution. L Adding a login to SQL Server for IIS user of web server is also not going to work.