3

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.

Henrik Sonesson
  • 79
  • 1
  • 11

4 Answers4

3

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.

Oded
  • 489,969
  • 99
  • 883
  • 1,009
  • Nope no luck with those either, same error :(. That page you linked is however quite nice. – Henrik Sonesson Feb 02 '12 at 20:39
  • @HenrikSonesson - And you are certain you have a working `Login` for the user `IIS APPPOOL\MyAppPool`? Did you try logging in as that user? – Oded Feb 02 '12 at 21:12
  • Im certain that the user exists in the database, but its not a full account, just a service/virtual user. I haven't been able to find any leads on how to test that this user can access the database unfortunately. (Other than calling the database from a webservice that is, and thats what Im trying to solve) – Henrik Sonesson Feb 03 '12 at 19:50
  • @HenrikSonesson - There is a difference between a _user_ and a _login_. And even if you have both, they would need to be associated with each other. As for testing - open Management Studio, connect to the database only change the setting from trusted connection and specify the login. – Oded Feb 03 '12 at 19:52
  • Yeah I have them mapped to each other via user mapping. Connecting to the SSMS with SQL Server Authentication instead of Windows Authentication and using IISAPPPOOL\MyAppPool results in (win event log): "Server is configured for Windows authentication only". I've checked and double checked that the server is indeed in the mode to accept BOTH windows authentication and SQL Server Authentication. – Henrik Sonesson Feb 03 '12 at 20:15
  • @HenrikSonesson - Have you restarted the server after setting it to allow SQL Server auth? – Oded Feb 03 '12 at 20:28
  • Yupp. Just for the heck of it I rebooted the PC aswell but still the same error. I did however manage to log into SSMS with the SQL Server Auth user I created. Not via Web Service though... Trying the AppPool user now renders "Attempting to use an NT account name with SQL Server Authentication". Im getting too confused here... I have to be missing something basic. – Henrik Sonesson Feb 03 '12 at 20:42
  • Try running SSMS with runas so it executes as the APPPOOL user and use Windows Authentication. – Oded Feb 03 '12 at 20:47
  • Hmm... I tried doing that but it wont let me. Are you sure that a virtual user can be used to run an actual program? I get the error "The name provided is not a properly formed account name". Im guessing it has something to do with either the space or the \. I tried with the local pc's domain name in front aswell to not confuse the IIS APPPOOL\ part for a domain. – Henrik Sonesson Feb 03 '12 at 20:55
1

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

Reference

Community
  • 1
  • 1
Ali Exalter
  • 420
  • 2
  • 8
  • 13
  • 1
    This version of IIS is targeting the AppPool Identity as the account to establish login permission. This is security improvement in IIS. Setting it to use the Local System again allows the security vulnerability that this IIS enhancement was designed to fix. – htm11h Aug 05 '15 at 19:52
  • Thank you Ali! It worked for me. Is there a way with keeping the security and access it? – Gurusinghe Oct 23 '15 at 12:47
0

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"

Tomek
  • 3,267
  • 2
  • 22
  • 23
  • Sorry for being a bit unclear, I'll edit the original post to describe that better. I did try these steps but to no success. I had missed the "user must change password" tick though, but unfortunately that didn't help either. – Henrik Sonesson Feb 02 '12 at 21:45
  • @HenrikSonesson: do you get the same exception? Can you connect to that database from SSMS using the login from connection string? – Tomek Feb 02 '12 at 22:06
  • Yes I get the same exception. Trying the SQL Server Authentication login renders the same "Server is configured for win authentication only" regardless of if I connect vie webservice or SSMS. The problem is that it's not... Its set to use both :(. I'm Leaning towards reinstalling SSMS soon. – Henrik Sonesson Feb 03 '12 at 19:53
0

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!

Henrik Sonesson
  • 79
  • 1
  • 11