How to setup ASP.Net 2.0 Providers in SQL Server 2005

Note: The new Aquest Hosting control panel now has a page that will install your ASP.Net 2.0 Providers to your SQL database for you. All you have to do is pick your database and click the button. If you are already hosting with Aquest Hosting, then you can skip this tutorial and use the installer in the control panel.

For the rest of you, this quick tutorial will show you how to setup the ASP.Net 2.0 Providers in SQL Server 2005

To follow this tutorial, you will need 'dbo' permissions to your database, and if you are an Aquest Hosting customer with a SQL Server 2005 database, then you already have 'dbo' permissions to your database.

Part 1: Create the script:

1. Open the ASP.Net 2.0 command prompt on your computer by clicking "Start | All Programs | Microsoft Visual Studio 2005 | Visual Studio Tools | Visual Studio 2005 Command Prompt".
2. Enter "aspnet_regsql.exe -A all -sqlexportonly C:\runproviders.sql" and hit the enter key.
3. *You have written the script to "C:\runproviders.sql", so open that file, and use your text editor's "Replace" feature to replace all occurences of "aspnetdb" with your database name.

Part 2 - Run the script:

1. Connect to your SQL Server 2005 database server.
2. Click "New Query" in the top left corner.
3. Copy and paste your sql script into the query window.
4. Click the "Parse" check mark button to see if your script is error free.
5. Click the "Execute" button to run your script.

Part 3 - Verify Roles were added:

1. Look in the Security folder under your database and see if the provider roles were added (see image). They all start with "aspnet_".

Part 4 - Add your sql user account to the roles:

1. Clear out the text in the Query window, or open a new Query window.
2. Make sure your database is selected in the drop down list in the tool bar.
3. Add the following script to the window.

   Exec sp_addrolemember 'aspnet_Membership_FullAccess', 'yourUser'
   go
   Exec sp_addrolemember 'aspnet_Personalization_FullAccess', 'yourUser'
   go
   Exec sp_addrolemember 'aspnet_Profile_FullAccess', 'yourUser'
   go
   Exec sp_addrolemember 'aspnet_Roles_FullAccess', 'yourUser'
   go
   Exec sp_addrolemember 'aspnet_WebEvent_FullAccess', 'yourUser'
   go

4. Replace 'yourUser' in each command with your sql user name.
5. Click "Parse" to check for errors.
6. Click "Execute" to run the script.

Completed:

You have now completed setting up your ASP.Net 2.0 Providers in SQL Server 2005.