IIS7 application using wrong SQL Server 2008 Express database

Question

I'm stumped. I have a client site on a virtual private server - Windows Web Server 2008 SP2, IIS7, SQL Server 2008 Express.

I'm trying to setup a second web application, to allow him to review updates prior to their going live. I've created the web application in IIS7, and I have added a second database to SQL Server. The second db is essentially a copy of the production db, with 'DEV' prefixed to the database name and a few new fields in a few tables.

My production site works fine. However, the test site comes back with an SqlException: "Invalid column name 'version'." This is one of the new fields - which leads me to believe that my dev site is referring to the production database, and not the dev database. Connection strings, however, do point to different databases (although the login is the same for both):

• Production site: "Server=.\SQLExpress;Database=myDbName;User ID=myUserName;Password=myUserPassword;"
• Dev site: "Server=.\SQLExpress;Database=DEVmyDbName;User ID=myUserName;Password=myUserPassword;"

Seems like this should be something obvious that I'm missing. A colleague suggested that I create another application pool for the second IIS application, but it does not seem to have helped.

UPDATE, following further testing:

I changed the dev site's connection string to

"Server=.\SQLExpress;Database=DEVmyDbName;User ID=devuser;Password=myNewDifferentPassword;"

and created a new SQL server login on the database, and only gave it access to the dev database. I also set the dev database as the account's default database. I removed access to the dev database from the production login. When I try to login to the site, I get the following error:

• The server principal "devuser" is not able to access the database "myDbName" under the current security context.

As a result, there are two things I know for certain:

1. Since it's using the new username to login, I know the app is using the connection string I'm feeding it;
2. For some reason, once logged in, it appears to be ignoring the specified database and trying to use the production database instead.

I don't think it should make a difference, but I will mention I'm using NHibernate for database access.

Still stumped.

Answer 1

It turns out that NHibernate was significant. One of the NHibernate configuration options that was set was "default_schema" - when I removed it, the app started connecting to the correct database.

It appears that the default_schema property was overriding whatever database was specified in the connection string.