SqlException: Login failed for user

Question

I use a dbml for my Data Access Layer to provide the data that i need in my app. When i connect from the server explorer everything seems fine. I choose to use my windows authentication and the connection test shows everything works just fine. When i Build my solution and run it on my IIS it says that i'm using a login that is not working. How to solve this issue?

Answer 1

The web site will be logging in to the SQL Server using the identity of the application pool of the web site, you have to grant that user (normally network service) access to the database.

Answer 2

Did you define the appropriate connectionstring in your web.config?

Answer 3

When you try to connect from server-explorer and chose windows authentication, it uses currently logged in user to authenticate with the sql-server. You must be logged in as Administrator of the local system so it lets you in using windows authentication.

But when you run your application through IIS, and try to authenticate using windows authentication it uses local system aspnet_user account to authenticate which must not have authorization to access your database. You need to authorize aspnet_user to access your database and it will work.

A recommended practice is to not authenticate using windows authentication but to use sql server authentication which will work in every context.