1

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.

Still stumped.

Remi Despres-Smyth
  • 141
  • 8
  • Is the connection string being cached? Could that be the cause of the problem? – joeqwerty Sep 08 '10 at 16:23
  • I don't think so, given the testing I've done this morning. Take a look at the edits to the question. – Remi Despres-Smyth Sep 09 '10 at 11:35
  • re your edit: you say it's trying to use the production database, but the error references the DevMyDbName specifed in the connection string. Are you sure the devuser account you created has access to the dev database? Can you try to use sqlcmd with the new account to access the database? – SqlACID Sep 09 '10 at 23:02
  • You're right, transcription mistake on my part - edited to fix. Sorry for the confusion. – Remi Despres-Smyth Sep 10 '10 at 11:30

2 Answers2

2

After still more digging, I found the culprit - it was an application issue, related to the configuration of NHibernate. 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.

Remi Despres-Smyth
  • 141
  • 8
0

Sounds like you have more than one connection string; is it possible there is one stored within the application someplace?

Create a new userid/password on the dev system only, change your dev connection string to use it, if it connects, the problem is elsewhere.

If you have access to the full version of SQL, you could run Profiler against the SQLExpress instance, and monitor the activity.

SqlACID
  • 2,166
  • 18
  • 18