Yellow screen of death when creating new user

cbspira · January 1st, 2009, 03:36 PM

I'm really stuck and hope someone here can help :-)
Here's my setup:
VWD Express 2008
SQL Express 2008
Windows XP Home SP 3

I've been following the book (which is enormously helpful and usually clear), and other than a few typos on my end everything has worked as described, including the DB stuff introduced in Ch. 11. I did have a bit of trouble setting up SQL Express (apparently, even though I had checked that it should be downloaded and installed with VWD, it wasn't, so I had to install it manually when I reached Ch. 11). The data source on my machine is called SQLEXPRESS2 and is set as such in the web.config and in the database options for VWD.
Here's the connect string from my web.config:

Code:

<connectionStrings>
		<add name="PlanetWroxConnectionString1" connectionString="Data Source=.\SQLEXPRESS2;AttachDbFilename=|DataDirectory|\PlanetWrox.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
	</connectionStrings>

So, all has been going well for the most part except for delays when accessing the Genres or Reviews DBs - it sometimes times out, but if I access them first w/in DB explorer and then reload the web page everything is fine.

Then I got up to Chapter 15 which deal with User security. I created the login page and create user page - but when I click on the "create user" button I get a yellow screen of death:

Quote:

Server Error in '/' Application.
--------------------------------------------------------------------------------

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

SQLExpress database file auto-creation error:
The connection string specifies a local Sql Server Express instance using a database location within the applications App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:

If the applications App_Data directory does not already exist, the web server account must have read and write access to the applications directory. This is necessary because the web server account will automatically create the App_Data directory if it does not already exist.
If the applications App_Data directory already exists, the web server account only requires read and write access to the applications App_Data directory. This is necessary because the web server account will attempt to verify that the Sql Server Express database already exists within the applications App_Data directory. Revoking read access on the App_Data directory from the web server account will prevent the provider from correctly determining if the Sql Server Express database already exists. This will cause an error when the provider attempts to create a duplicate of an already existing database. Write access is required because the web server accounts credentials are used when creating the new database.
Sql Server Express must be installed on the machine.
The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts.

So, I checked - the App_Data folder exists, has full permissions (the other Wrox DB lives there), the sqlServer user account is an administrator as is the ASP.Net Machine Account, and I can find no other reason that the user DB could not be created.

So I decided to try to add a user manually via the ASP.Net Web Site Administration Tool. I clicked security, and after about a minute or so I got:

Quote:

There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.

The following message may help in diagnosing the problem: Unable to connect to SQL Server database.

A button titled "Choose Data Store" was on the lower right side of the screen so I clicked it and got:

Quote:

Use this page to configure how Web site management data such as membership is stored. You can use a single provider for all the management data for your site or you can specify a different provider for each feature.

Your application is currently configured to use the provider: AspNetSqlProvider

Select a single provider for all site management data
Select a different provider for each feature (advanced)

So I selected the single provider link which had one choice:

Quote:

AspNetSqlProvider

and a Test link - when I clicked "Test" I got:

Quote:

Provider Management
Could not establish a connection to the database.
If you have not yet created the SQL Server database, exit the Web Site Administration tool, use the aspnet_regsql command-line utility to create and configure the database, and then return to this tool to set the provider.

So I found the utility and ran it, but also did not get anywhere - it tried to create the aspnetdb with no success. Not sure if this is related, but in the utility, when I tried browsing the DB list I got an error, but it let me continue trying to set up a DB.

I do not want to have to create my own custom provider - even if I did, it would be nice to know why this is not working the way it should.

Anyone out there have any ideas where I went wrong? This is my first experience with SQL Server, so I apologize if it's something really obvious...

Imar · January 1st, 2009, 05:42 PM

Hi there,

.NET security doesn't use the PlanetWrox database in your application. Instead, by default it tries to create a new database called aspnetdb.mdf in the App_Data folder of your application. Furhtermore, it uses .\SqlExpress (or (local)\SqlExpress) as the default SQL Server instance

In your case, your instance is called SqlExpress2 ( the 2 seems to indicate that you did have SQL Server installed in the first place?). So, ASP.NET can't find your database server and you get an error.

The easiest way to prevent this problem is to override the LocalSqlServer connection string in web.config and have it point to your new server. See page 660 and further and appendix B for more details.

Hope this helps,

Imar

cbspira · January 1st, 2009, 09:17 PM

Thank you so much for your really quick reply.
(I'm including my step-by-step in case this helps someone else)

As suggested, I looked at pg. 661 and entered the connection string for LocalSqlServer as listed, replacing the DatabaseServer with .\SQLEXPRESS2 as it is for the PlanetWroxConnectionString and ran the ASP.Net web config admin tool. I got an error, but this time different than the last one:

Quote:

There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.

The following message may help in diagnosing the problem: The entry 'LocalSqlServer' has already been added. (C:\cadler\Personal\Touro\WebProgramming\BegASPNET \Site\web.config line 27)

Believe it or not, this was encouraging, since that was the line I had added! I then started reading Appendix B from the beginning, hoping to gain SOMETHING that would help. On page 703 there is a section called "Overriding the LocalSqlServer Connection String" and it mentions to start the section with a <clear /> element. The explanation you give is:

Quote:

This element is used to clear out all the previously defined connection strings, including (emphasis mine) the LocalSqlServer connection string defined in machine.config

BINGO! If I can't get it to work with a custom string, maybe here's where I can change the default SQL server instance name.
So I did a search on my machine and could not find the file with Windows Explorer search. But, the handy dandy index for the book shows that this file is discussed on page 537. On page 539 it indicates that this file lives here:
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONF IG
So I edited it and sure enough, found the connection string for LocalSqlServer pointing to .\SQLEXPRESS - I added the 2, saved the file, and reloaded the ASP.Net web admin tool - right away I could see that it worked because the security description noted that there were 0 existing users - something that had not appeared before. I then tried adding a user via the Create User Planet Wrox page and was successful.

So, I thank you VERY much for assisting with this. FWIW, the reason there is a SQLEXPRESS2 is because I messed up the first time I tried installing the server. There is no SQLEXPRESS on the machine - I had deleted that instance almost immediately. Had I know the trouble it would cause I would have just reinstalled the whole thing. Well, at least this has been a learning experience!

Thank you again for your timely assistance!

Imar · January 2nd, 2009, 04:59 AM

You're more than welcome. Glad it's working now, and many thanks for the detailed follow up. I am sure other visitors of this forum will find this useful some day...

BTW: Page 660 shows the <clear /> element as well. However, because the important part of the code is on page 661, it's easy to overlook.... ;-)

Cheers,

Imar