C# asp.net cannot open user default database

Question

I pulled an ASPNETDB.MDF off of a server for a ASP site using Forms authentication to my local machine. I added a new user to the database file using the ASP.NET Website Configuration tool. I copied the MDF back to the server and now, i get the following exception whenever I try to log in as any user:

Cannot open user default database. Login failed. Login failed for user 'NT AUTHORITY\NETWORK SERVICE'

I have been googling and read all of the other solutions to this problem here on SO as well and haven't found anything that works.

The server has SQL Server 2008 on it and that's what I am using on my local machine with VS 2008 Professional.

My connection string:

<add name="ASPNETDBConnectionString1" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>

I'm new to SQL Server so I am pretty sure I messed something up somewhere.

Any help greatly appreciated.

Thanks!

Answer 1

Your description of the issue is incomplete or not consistent with the error. You will need to provide a bit more information for a better answer, but here is my preliminary answer.

The error indicates that your application is using integrated authentication to SQL. So, it would help to see the connection string. It likely says 'SSPI' somewhere. What this means is that the user that the app container is running as (NETWORK SERVICE) is trying to connect to the database, and it can't because it does not have the permission to do so.

So you either changed the connection string as part of what you did, or NETWORK SERVICE was previously a database user (not a good idea, I think).

There are possible other security-related settings at play in your web.config. Your app appears to use forms authentication. If IIS is set to allow anonymous access, and your web.config is set to do impersonation, then that could also cause the NETWORK SERVICE trying to connect to SQL.

How is the application supposed to connect? Do you have a service account (a single user account) that all users who authenticate through forms authentication should go through for database operations? If so, then you need to change your database connection string to contain a user and password.

In order to fix this, you need to check your connection string, any forms-auth related settings in the web.config, and the security settings in IIS. None of this I think has to do with the fact that you added a user.

Edit: So you are using Integrated Security, like I suspected. This won't work with Forms Auth, because while a user is authenticating against your app with their user id, your app authenticates against the database with integrated auth, i.e. NETWORK SERVICE. You need a database service account, imo, and I don't know how this can have worked before if you did not change the connection string.

Answer 2

I think that your problem is that you aren't keeping track of the difference between a database login and a database user. A login gives access to SQL Server (in general). A database user is a login that has been given access to a specific database. When you copy files back and forth as you've done, you can invalidate the underlying identifier for a login account (especially Windows accounts).

I would suggest that you use a SQL account to log in to your SQL server (rather than a Windows account) and place the credentials in the web.config file. I've simply found that this is far easier to configure, use and change than a Windows account login. Once your login is set up, just make sure that it also has user access to the database on the server you wish to use. You may still run into transfer problems (although they are less likely), but fixing them is quite a bit easier.

Important note: others disagree - arguing that Windows accounts are more secure - so you might want to educate yourself on this issue!

Answer 3

All questions about who or what or why regarding the config aside...

Your problem can be deduced from the connection string. That will work locally against sql express but not against sql proper on the server.

The first clue is that only SqlExpress enables User Instances. On Sql proper you will have to attach the mdf.

If that connection string works on your machine while in VS 2008, you have SQL EXPRESS installed. If that connection string works on the server then the server has SQL EXPRESS installed.

If the server does NOT have Sql Express installed and configured to allow user instances, as I would hope it did not, that connection string will result in the login failure exception you are experiencing.

I also wonder about your copy to/from. Typically access will be denied an active mdf, even for copying.

Please confirm Sql versions both client and server side and try to find a previous copy of both web.config files to compare connection strings.

Answer 4

Can you try changing this Application Pools->Advanced Settings -> Process Model -> Identity ?

I got the same error and fix by changing the process model.