• You must run aspnet_regsql.exe to generate the security database (default aspnetdb) if you are using ‘real’ SQL Server and not an Express version. You cannot generate it automatically from ASP.ET Website Administrator. Since the directory where the utility is located is n levels deep, I use a batch file:
"c:\windows\Microsoft.Net\Framework\v4.0.30319"\aspnet_regsql
• The full SQL Server does not allow you to put a database anywhere on the system by default. Instead, when you run aspnet_regsql, the aspnetdb.mdf database (or whatever name you give it), is generated in the default directory of Sql Server (under C\Program Files\Micrsofot SQL Server...\Data). That’s okay.
Note: If you want to use a local database in the App_Data directory of your web site, follow these steps.
1. Ensure that that database is not being used by any other application but a single instance of SQL Server Management Studio.
2. Expand the Databases node.
3. Right click on aspnetdb (or whatever you’ve called it) and select Tasks->Detatch. Note that the Message column should blank, indicating no open connections, or Detach will fail.
- Click OK to detach the database.
- In Windows Explorer, navigate to C”\Program Files\Micrsofot SQL Server...\Data and locate the aspnetdb.mdb and aspnetdb_log.ldf files.
- Cut (or copy) these files from the directory.
- In Windows Explorer, navigate to the directory where you want the new database located (the App_Data folder for the website, for example).
- Paste the database and the log file into this directory.
- In SQL Server, right-click on the Databases Node and select Attach.
- Click the Add button.
Navigate to the directory where you copied the new database and select the mdf (database) file (aspnetdb.mdf, for example).
If you want to use one database for security for all of your websites, you can leave the name the same. If you want different security databases for different web sites, change the name to a unique name.
- Click OK.
- Click OK.
- If you right-click on the Databases node and select Refresh, you should see the new security database in Sql Server Management Studio.
• Manually add the required code to the section of the Visual Studio project’s Web.config file. I have tried hundreds of different variations of this theme, but this seems to be the only one that works every time.
<system.web>
<compilation debug="true" targetFramework="4.5"/>
<httpRuntime/>
<sessionState allowCustomSqlDatabase="true"
sqlConnectionString="Data Source=JAYSDELL\MSSQLSERVER2008R;
Initial Catalog=aspnetdb;
Integrated Security=True"/>
<pages controlRenderingCompatibilityVersion="4.0"/>
</system.web>
<connectionStrings>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer"
connectionString="Data Source=JAYSDELL\MSSQLSERVER2008R;
Initial Catalog=aspnetdb;
Integrated Security=True"
providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>
Obviously values for sqlConnectionString and Initial Catalog need to be tailored to the app.
Now when you go into WEBSET->ASP.NET Configuration->Security you should see the following.
SUCCESS!