One of the developers at work had used the Visual Studio web authentication tool thingy, which created an SQL Server Express database which was configured in his web.config to attach the file for use.

When migrating this to a server that has SQL Server (not SQL Server Express) this obviously doesn’t work; you get connection errors.

As this useful post says:

AttachDBFilename is unique to SQL Express, it spins up a user instance of SQL Express attached to a specific DB Filename for single user mode. Database is simply the name of the database to use, it has no additional connotation. For any production server, you would most likely not be using AttachDBFilename. It is strictly useful for development and experimentation in single-user mode.

The answer is to attach the MDF (database file) to SQL Server. You may want to change the database name; it seems to just plonk in the original filename (with path) there, which is pretty unwieldly.

Create an SQL Server login, and a database user (linked to the login) for it (I gave it the dbowner role, though it’s probably possible to restrict it a bit more), and then change the web.config:

• “data source=.\SQLEXPRESS;” becomes “Server=(local);”
• “AttachDBFilename=|DataDirectory|\aspnetdb.mdf” becomes “Database=[databasename]; User=[username]; Password=[password]”
• “User Instance=true” needs to be removed

Cross your fingers and hopefully that’ll work.