Reader Level:
ARTICLE

ASP.NET Security With SQL Server 2005

Posted by Sanjay Gulati Articles | Security in .NET July 11, 2006
This article focuses on security concerm when using SQL Server 2005 in ASP.NET application.
  • 0
  • 0
  • 58487

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. 

COMMENT USING

Trending up