Once SQL Server is configured to be used to store the user Profiles, you need to update the Web.Config file so that the Profile configuration section will interact with the SQL Server 2000 and not with the SQL Server 2005 Express. Two ways to do this are offered below.
- We can modify the LocalSqlServer connection string. If we check the Machine.Config file located at {Drive Letter:\Windows\Microsoft.Net\Framework\.NET Framework Version\CONFIG\}, we would notice the following ConnectionString configuration section:
<connectionStrings>
<add name="LocalSqlServer"
connectionString="data source=.\SQLEXPRESS;Integrated
Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User
Instance=true"
providerName="System.Data.SqlClient"/>
</connectionStrings>
This configuration section defines the connection string that is used by all providers in the ASP.NET 2.0 .NET Framework. Listing 2 removes the connection string with the name LocalSqlServer and then adds a new connection string to the SQL Server 2000 database having the same name. This means that there is no need to modify the Profile provider to use the new connection string. The default connection string will be used but with different parameters so that the Profile provider can now interact with SQL Server 2000 on all apps on the current machine.
Listing 1. Overriding LocalSqlServer Connection String
<configuration
xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<appSettings />
<connectionStrings>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="server=(local);
database=aspnetdb;Integrated Security=SSPI"
providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<authentication mode="Forms" />
<anonymousIdentification enabled="true" />
<Profile enabled="true">
<properties>
<add name ="Fname" allowAnonymous ="true" />
<add name ="Lname" allowAnonymous ="true" />
</properties>
</Profile>
<compilation debug="true" />
</system.web>
</configuration>
- The second approach is to define a new connection string and then configure the Profile providers property to use a new custom provider whose connection string name is the new one specified in the connection strings section. Listing 2 presents a sample Web.Config file that shows how to configure a custom Profile provider for each app.
Listing 2. Using Custom Provider
<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<appSettings />
<connectionStrings>
<add
name="SqlServerConnectionString"
connectionString="server=
(local);database=aspnetdb;Integrated Security=SSPI"
providerName="System.Data.SqlClient"
/>
</connectionStrings>
<system.web>
<authentication mode="Forms" />
<Profile enabled="true" defaultProvider="ProfileSqlProvider">
<providers>
<remove name ="AspNetSqlProfileProvider" />
<add
name="ProfileSqlProvider"
type="System.Web.Profile.SqlProfileProvider, System.Web, Version=2.0.3600.0,
Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
connectionStringName="SqlServerConnectionString"
applicationName="ProfileObjectProject"
/>
</providers>
<properties>
<add name="Fname" allowAnonymous="true" />
<add name="Lname" allowAnonymous="true" />
</properties>
</Profile>
<anonymousIdentification enabled="true" />
<compilation debug="true" />
</system.web>
</configuration>
In the configuration above, a new connection string named SqlServerConnectionString is added. A default provider in the Profile configuration section is specified. Start by removing the default Profile provider which is the AspNetSqlProfileProvider. Then add a new Profile provider with the conenctionStringName property set to the newly created connection string in the connection strings configuration section. User Profiles will now be using the new Profile provider, which interacts with SQL Server 2000.
