Can't Access SQL Express from IIS

Peter T Roberts · October 13th, 2011, 08:57 AM

I'm using Windows 7 Home Premium with VWD 2010 Express and Visual Studio 2010 Express SP1 and SQL Server 2008 R2 Express. I have developed a small VB application following the steps outlined in the book which is truly excellent.

I proceeded successfully to Chapter 19 Deploying Your Web Site and was able to use IIS 7.5 having set the correct file permissions outlined in the book. This included logging in to the site using the standard Login facilities with the aspnetdb database. I then decided to install Management Studio to enable me to inspect table data but having done this I found that I could no longer access SQL SERVER Express. The error was:

"System.Data.SqlClient.SqlException: Failed to generate a user instance of SQL Server due to a failure in impersonating the client."

I checked the Microsoft documentation and found that the message is associated with Error Number 15369 with a Severity Code of 16 which apparently means the error can be corrected by the user. However there is no further explanation on the reason for the error or what needs to be done.

Today I have recreated the Website on a different physical path and following Chapter 19 to the letter. (I have deliberately not employed Management Studio as this seems to have provoked the error.) The aspx pages are again displayed correctly but login fails with the same error. The Web.config file is the one I have used throughout development with both the internal Web Server and IIS 7.5 Express, both of which continue to function without problem with SQL Server Express.

I have searched High and Low for a possible explanation but have failed to find one. I would be most grateful for any advice on how to proceed as I'm stuck!

Imar · October 13th, 2011, 12:40 PM

Can you provide a bit more information? For example:

1. How does your connection string look?

2. Under which account does the Application run?

3. Did you attach the database to SQL Server? And if not, can you try it and then use Initial Catalog instead of the db attach option?

4. Did you remove user instance from the connection string and if not can you try it?

Cheers,

Imar

Peter T Roberts · October 13th, 2011, 02:29 PM

Imar - Thank you for the points to check.
I discovered that once you attach the database using SSME you are obliged to change the connection string to the format used when connecting to a non-Express version of SQL Server. Even if you detach the database the system appears to "remember" the database and the original connection string is no longer valid. To fix the problem I changed from:
connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI; AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true"
to:
connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI; Database=aspnet_login;User Instance=false" (Initial Catalog instead of Database is valid)
I feel rather foolish because I would have avoided the problem had I read the relevant sections of Appendix B in your excellent book. Perhaps a reference to the use of SSME in Chapter 19 would be helpful.
Many thanks for responding so quickly to my problem.
Regards
Peter

Imar · October 13th, 2011, 02:35 PM

Hi Peter,

Good to hear you got it working. I am currently planning for th next version of the book, so I welcome any feedback. Could you indicate where a reference in Chapter 19 to Appendix B would have helped you out? That is, where did you get stuck and which section of Appendix B helped you out eventually?

Cheers,

Imar

Peter T Roberts · October 15th, 2011, 01:17 PM

Imar – If one follows the instructions in Chapter 19 to the end of the Section “Configuring the File System” you end up with a Web Site working with IIS as the Web Server. However, at this stage the connection strings haven’t been changed so SQL Express is still operating in User Instance mode. At the same time the database contents cannot be inspected or changed.
It’s reasonable to think that SQL Server Express could continue to be used for a small capacity production service, but not in User Instance mode. In addition, to allow the database to be accessed for diagnostic purposes SSMSE needs to be installed. After attaching the database to SSMSE however, the connection string has to be changed to the standard non-User Instance form specifying the Database (or Initial Catalog) name. Once this has been done you can’t revert to User Instance mode.
Currently Point 10 of “Configuring the File System” refers readers to the Section “Moving Data to a Remote Server” and Appendix B “only if one is using a different database server”. I suggest that it needs to be pointed out that Appendix B Section “Using SQL Server Management Studio” is certainly relevant should SSMSE be installed for the reasons I’ve outlined above. The Appendix B title and initial content don’t suggest that Appendix B is relevant to the Express version as well although it is.
Regards,
Peter

Imar · October 16th, 2011, 08:29 AM

Thank you. I added this post to my Todo list for the next version of the book.

Cheers,

Imar