Configuration Error in Web.config

BobRoberts · November 8th, 2011, 04:45 PM

I ported my site over to a 2008 R2 server using SQL Express 2008. When I run it from http://localhost, I get the login screen. When I enter the login info and click Ok, I get a Configuration Error, with the second "add name" line highlighted in red. The first one appears to run ok. I'm basing the syntax of this line from Appendix B, page 770. The aspnetdb.mdf is still in the site's App_Data folder, but was attached to the SQLEXPRESS database from there, and then given rights to the NET4 user.

Note that the error also says, "Parser Error Message: The entry 'LocalSqlServer' has already been added." I checked the Web.config file, and the word "LocalSqlServer" is only used once.

Here are the two lines - the second one is giving the error (I tried changing the second to completely match the first to the right of the inital catalog, but that didn't get rid of the error):

Code:

<configuration>
  <connectionStrings>
    <add name="BCConnectionString" connectionString="data source=IT-TEST2\SQLEXPRESS;initial catalog=BC; integrated security=SSPI;persist security info=False; Trusted_Connection=Yes" providerName="System.Data.SqlClient" />
  
    <add name="LocalSqlServer" connectionString="data source=IT-TEST2\SQLEXPRESS;Initial Catalog=aspnetdb;Trusted_Connection=True" providerName="System.Data.SqlClient" />
</connectionStrings>

It worked on the production server with the aspnetdb file unattached to SQL. Here is the line from that Web.config file:

Code:

    <add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true"
      providerName="System.Data.SqlClient" />

Imar · November 8th, 2011, 04:50 PM

It would help if you included the actual error...

Imar

BobRoberts · November 8th, 2011, 05:01 PM

Sorry - it's:

Parser Error Message: The entry 'LocalSqlServer' has already been added. I checked the Web.config file, and the word LocalSqlServer is only used once.

Imar · November 8th, 2011, 05:10 PM

It's because it's already defined at the machine level in machine.config.

Add a <clear /> element at the top of the connectionStrings node to get rid of that one.

Cheers,

Imar

BobRoberts · November 8th, 2011, 05:33 PM

The error now:
Parser Error Message: The connection name 'ApplicationServices' was not found in the applications configuration or the connection string is empty.

Now it seems to be balking at the ConnectionStringName = "ApplicationServices" in the provider sections toward the bottom. I assume I should change that ConnectionStringName to LocalSqlServer, though that isn't mentioned in this section of the book. Something similar is talked about on page 774.

BobRoberts · November 8th, 2011, 05:43 PM

I did that and am getting:

Exception Details: System.Data.SqlClient.SqlException: Cannot open database "aspnetdb" requested by the login. The login failed.
Login failed for user 'IIS APPPOOL\ASP.NET v4.0'.

I just checked and the user IIS AppPool\ASP.NET v4.0 has all Role Members checked that start with "aspnet".

Imar · November 8th, 2011, 05:56 PM

Did you assign role membership or schema ownership?

BobRoberts · November 8th, 2011, 06:07 PM

Role membership - didn't do anything with the schema, because it wasn't in your instructions. I just tried checking dbowner, and also changing the connection string to login as sa, but the login failure persists.

BobRoberts · November 8th, 2011, 06:27 PM

Tried to get it to work the old way, with the aspnetdb in the App_Data folder without being attached to the SQL server, using the same connection string as on the development server, but getting:

Exception Details: System.Data.SqlClient.SqlException: Failed to generate a user instance of SQL Server due to a failure in impersonating the client. The connection will be closed.

The only thing left to try is what's described on page 771, "Configuring Your Database for the Application Services".

BobRoberts · November 8th, 2011, 07:05 PM

I tried one more thing before going the route of configuring my database for the application services - AND IT WORKED! I reasoned there was something fundamentally wrong with the security on the aspnetdb database as it was created. So, I detached it and deleted the mdf. I then went back to my development PC and attached that database to my SQL instance, and then I backed it up. I copied the backup file to the production server, into the SQL data directory, and then I went into the SQL on the production server and restored it - effectively creating it. And, maybe important, I made sure I was logged on as sa when I did these things. I got a fresh Web.config from the development PC, and modified it exactly as 770 specifies. And that seemed to do the trick - it might have been the combination of restoring instead of attaching, and making sure it was done while logged in as "sa". Thanks Imar - I want to tell you you've written an extraordinary book that's clear and detailed without being confusing.