IIS APPPOOL\MyAppPool unable to log in to my SQL Server database

3
votes

I've got a local WCF web service project that I'm trying to get to access my database. This is my setup:

  • SQLServer 2008 R2 Express
  • IIS 7.5
  • Using IIS APPPOOL\MyAppPool application pool

The AppPool is set to target .Net 4.0 and its identity is set to ApplicationPoolIdentity. The AppPool user is added in the database and has been assigned dataReader and dataWriter rights. I've tried adding the user to the database both as a "Login" under Security\Logins and as a user under MyDatabase\Security\Users.

Since I'll eventually switch to sql server authentication, I also tried using a real windows user that I assigned reader/writer rights in the database. I then tried converting the ApplicationPool's identity to NetworkService and added the NT AUTHORITY\NETWORK SERVICE user to the DB aswell but with equal lack of success.

This is the connection string that I'm currently using (With integrated security):

Server=.\SQLEXPRESS;Database=MyDatabase;Integrated Security=true"

As soon as I try to interact with the database in my web service code I get this error:

System.Data.SqlClient.SqlException: Cannot open database "MyDatabase" requested by the login. The login failed. Login failed for user 'IIS APPPOOL\MyAppPool'.

When using other users than the ApplicationPool then their user names are displayed instead of the IIS APPPOOL one. Does anyone have any idea what I could've missed?

UPDATE:

With some help from Oded and Tomek I'm now pretty sure that it has to do with the SQL Server. When using SQL Server Authentication I get this error (In the windows event log)

Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.>

Using Integrated Security (IIS APPPOOL\MyAppPool user) I get this error in the event log

Reason: Failed to open the explicitly specified database.

The server is configured to use "SQL Server and Windows Authentication mode" though which puzzles me. It seems like the second message simply means that the credentials were wrong, which also seems weird since the AppPool user does not have a password. Using the script posted by guptam in post 6 here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=121202 does NOT show my IISAPPPOOL user nor the one created for SQLServer Authentication. The users do exist under both Login and Users and they have the correct rights assigned.

4
c#wcfsql-server-2008webiis-7.5

4 Answers

3
votes

The connection string should be:

"Server=.\SQLEXPRESS;Database=MyDatabase;Integrated Security=SSPI;"

The value for Integrated Security is SSPI, not True.

There is an alternative syntax:

"Server=.\SQLEXPRESS;Database=MyDatabase;Trusted_Connection=True;"

The key here is Trusted_Connection, not Integrated Security.

I suggest taking a look at connectionstrings.com - a good resource for correct connection strings.

1
votes

Go to your IIS Manager -> ApplicationPool. Right Click your website ApplicationPool and chose Advance Settings. And change Identity to LocalSystem.

Reference

0
votes

Can you try these steps (some steps of your description are a bit unclear). Let's try to use "sql server authentication"

  1. Create Login Security->Logins, mark "sql server authentication", provide password, untick "user mast change password at next login"
  2. Select "default database" to the one you use
  3. Go to "User Mapping" page and select your database

  4. Use below connection string with user you just configured

    connectionString="Data Source=YourDbServerName;Initial Catalog=YourDbName;User ID=YourLogin;Password=YourPass"

0
votes

I eventually decided to reinstall SSMS and to recreate my database from scratch. It's now up and running as it should. I must've had something fundamentally wrong in some basic setting. Sry for all the confusion and thx for all the help!