[PostScript: Since I first posted this I was thinking, while biking to work this morning, that Option 2, while useful, is not necessarily the best course of action because this will require you to have to update the machine.config on the server(s) to which you deploy the app, which you'd rather not do, Not to mention that you will probably forget to do this and scratch your head for a long time before you remember the fix. Instead, I think the best option (Option 3) would be to change the web.config template on the dev box so it always plunked in the connectionString element that I specified in Option 1. I have not, however, worked up Option 3 yet because I am currently at work where I do not yet have VS 2005 Pro installed. So I will work up Option 3 (and figure out where the web.config template is stored) and repost later today. - Paul]

Microsoft's application services (membership provider, role provider, personalization provider, and profile provider) need to store their data somewhere. There is an issue when you opt not to install SQL Server 2005 Express on your machine because the machine.config is essentially hard-wired to use the SqlExpress instance on the local machine to store this data. In addition, if you wish to redirect the app services store to another machine you will have to initialize the data store. (Aside: if you are interested in creating a custom provider, check out this ASP.NET 2.0 Provider Model: Introduction to the Provider Model.)

The issue comes up when you attempt to run Website|ASP.NET Configuration. Basically, this utility won't be able to find your provider if you haven't installed SQLExpress on your machine (which should be very common if you are installing Visual Studio 2005 Pro or Team System).

So if you install any version of Visual Studio along with SQLExpress, you're fine. But if you don't install SQLExpress or wish to redirect your app store to another location that you need to select one of the following two options:

Option 1. Fix it on an app by app basis. This is the method suggested by Scott Guthrie in his MSDN Library article entitled Configuring ASP.NET 2.0 Application Services to Use SQL Server 2000 or SQL Server 2005 . If you follow this method, you'll need to do two things:

   a. provision your database. you can do this, by running aspnet_regsql.exe at a .net command prompt. This will add the necessary tables to a SQL Server database of your choosing. The default is named aspnetdb but you can change that.

   b. add the following to your web.config file:

<configuration>
    <connectionStrings>
        <remove name="LocalSqlServer"/>
        <add name="LocalSqlServer"
         connectionString="Data Source=localhost;
           Initial Catalog=aspnetdb;
           Integrated Security=True"
           providerName="System.Data.SqlClient"/>
    </connectionStrings>
</configuration>

The name LocalSqlServer is important here. This is the connection string that ASP.NET will be looking for and is defined in your machine.config to point to SQLExpress. The above will override the machine.config and have it point to a local unnamed SQLServer instance so if you are using a named instance or wish for it to pont to a different SQLServer, change the connectionString attribute in the above.

Option 2. Change your machine.config (carefully after backing it up, of course) to fix this once and for all on your each development machine. The steps:

   a. Like option 1, you will need to provision your database. you can do this, by running aspnet_regsql.exe at a .net command prompt. This will add the necessary tables to a SQL Server database of your choosing. The default is named aspnetdb but you can change that.

   b. Change the connectionStrings and/or providers sections of the machine.config. These sections appear very close to the end of the machine.config. By default, these sections look like this:

-------------------------------------------

<connectionStrings>

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

</connectionStrings>

<system.data>

<DbProviderFactories>

<add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />

<add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />

<add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />

<add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />

<add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />

</DbProviderFactories>

</system.data>

<system.web>

<processModel autoConfig="true" />

<httpHandlers />

<membership>

<providers>

<add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="LocalSqlServer" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="/" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression="" />

</providers>

</membership>

<profile>

<providers>

<add name="AspNetSqlProfileProvider" connectionStringName="LocalSqlServer" applicationName="/" type="System.Web.Profile.SqlProfileProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

</providers>

</profile>

<roleManager>

<providers>

<add name="AspNetSqlRoleProvider" connectionStringName="LocalSqlServer" applicationName="/" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

<add name="AspNetWindowsTokenRoleProvider" applicationName="/" type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

</providers>

</roleManager>

-------------------------------------------

I suggest adding a new connection string and then pointing the providers to point to your new connection string. So I changed my connectionStrings section of my machine.config to look like (note that I left the LocaSqlServer connection string alone and just added a new one named LocalSqlServer2005):

-------------------------------------------

<connectionStrings>

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

<add name="LocalSqlServer2005" connectionString="data source=.\SQL2005;Integrated Security=SSPI;Database=aspnetdb" providerName="System.Data.SqlClient" />

</connectionStrings>

-------------------------------------------

In this case, I am using a local named instance of Sql Server. Then I carefully located all the providers looking for "LocalSqlServer" and replaced them with "LocalSqlServer2005" in the machine.config. (In a production environment, I would make this point to a shared SQL Server on another server but you should get the point here. I would also name it differently since it no longer would be local.) In my machine.config I found three references to the connection string located in the following providers: AspNetSqlMembershipProvider, AspNetSqlProfileProvider, AspNetSqlRoleProvider.

Once I modified my machine.config to the above, the application services worked just fine for all newly created Web sites.

Paul
Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!
Sponsor