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.

4
  • Is the connection string being cached? Could that be the cause of the problem?
    – joeqwerty
    Sep 8, 2010 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. Sep 9, 2010 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 9, 2010 at 23:02
  • You're right, transcription mistake on my part - edited to fix. Sorry for the confusion. Sep 10, 2010 at 11:30

2 Answers 2

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.

1
  • +1. Glad you figured it out.
    – joeqwerty
    Sep 9, 2010 at 11:53
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.

1
  • I don't think this is the case - take a look at my edits to the question this morning. Sep 9, 2010 at 11:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .