Home
    Shop
    Advertise
    Write For Us
    Affiliate
    Newsletter
    Contact

How to Create SQL Membership Schema at Run-Time

ASP.NET Forms authentication with SQL Server used to store user's information is very popular way to secure web site. There are numerous tutorials on Internet that explain how to create tables, views and procedures for SQL membership using ASPNET_REGSQL tool. ASPNET_REGSQL is easy to use, but it is not available in every case. If you create ASP.NET web applications, like portal or blog, for users worldwide, you can't expect that your clients who are not programmers know how to use ASPNET_REGSQL. Also, if you have a lot of web sites it is better to automate things and avoid repetitive work.

 

More efficient approach is to create Setup page where you can install SQL membership schema programmatically with ASP.NET server side code. You can do this with SqlFeatures class from System.Web.Management namespace. To see how it works, create new web site and place Button control on web form, named "btnInstallMembership". On button's click event, SQL schema will be created in selected database:

[ C# ]

using System;
using System.Configuration;
using System.Web.Management;
 
public partial class Admin_Setup : System.Web.UI.Page
{
 
  protected void btnInstallMembership_Click(object sender, EventArgs e)
  {
    try
    {
      // Installs SQL membership schema to selected database
      SqlServices.Install(
        // first parameter is database name
        "your_database_name",
        // we'll select installing of all features
        SqlFeatures.All,
        // get connection string from web.config
        ConfigurationManager.ConnectionStrings["Your-Connection-String-Name"].ConnectionString);
        // Informs user that schema is installed successfully
      Response.Write("SQL membership schema installed successfully");
    }
    catch(Exception ex)
    {
      // Something went wrong, get exception message
      Response.Write(ex.Message);
    }
  }
}

[ VB.NET ]

Imports System
Imports System.Configuration
Imports System.Web.Management
 
Partial Class Default2
  Inherits System.Web.UI.Page
 
  Protected Sub btnInstallMembership_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSendEmail.Click
    Try
      ' Installs SQL membership schema to selected database
      ' first parameter is database name
      ' in second parameter we'll select installing of all features
      ' third parameter is connection string that we'll take from web.config
      SqlServices.Install( _
        "your_database_name", _
        SqlFeatures.All, _
        ConfigurationManager.ConnectionStrings( _
        "Your-Connection-String-Name").ConnectionString)
      ' Informs user that schema is installed successfully
      Response.Write("SQL membership schema installed successfully")
    Catch ex As Exception
      ' Something went wrong, get exception message
      Response.Write(ex.Message)
    End Try
  End Sub
End Class

As you see, installing of SQL membership schema requires only one call of the SqlServices.Install method.

SqlServices.Install method has three overrides:

1. SqlServices.Install(string database, SqlFeatures features, string connectionString)

database - SQL Server database where we want to install objects
features - specifies which features to install, possible self-descriptive values are:

- SqlFeatures.All
- SqlFeatures.Membership
- SqlFeatures.None
- SqlFeatures.Personalization
- SqlFeatures.Profile
- SqlFeatures.RoleManager
- SqlFeatures.SqlWebEventProvider

connectionString - connection string to Sql Server, used only to connect to server not to specify database. To choose database, use database parameter. If you have database selected in connection string, it will be ignored.

2. SqlServices.Install(string server, string database, SqlFeatures features)

server - Sql Server name or address
database - database name where SQL schema will be installed
features - features to install

3. SqlServices.Install(string server, string user, string password, string database, SqlFeatures features)

server - server name or address
user - database user name
password - user's password
features - features to install

Possible permission error when you try to install Sql membership schema

Using of SqlServices.Install method requires AspNetHostingPermission. This permission is often denied by hosting companies, especially if you are on shared hosting. If you try to install SQL schema with limited rights you'll get an error like this:

Server Error in '/' Application.

Security Exception

Description: The application attempted to perform an operation not allowed by the security policy.  To grant this application the required permission please contact your system administrator or change the application's trust level in the configuration file.

Exception Details: System.Security.SecurityException: Request for the permission of type 'System.Web.AspNetHostingPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

The possible solution is to ask your hosting provider to increase permissions for your web application. But, many hosting providers refuse to do this because of security risk. Fortunately, there is a possibility to execute needed SQL scripts manually and install SQL membership schema even on shared hosting with medium trust. Instead of direct use of SqlServices.Install method, you can use SqlServices.GenerateApplicationServicesScripts method to get needed SQL code and then execute SQL script against your database.

Notice that these SQL scripts are also easily accessible on local disc of your development computer too. GenerateApplicationServicesScripts method only returns content of several .sql files located usually in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\ folder (or C:\WINDOWS\Microsoft.NET\Framework\v4.0.21006 if you use ASP.NET 4.0). Depending of do you want to install or uninstall Sql membership schema, you need to execute SQL scripts with Install or Uninstall prefix.

These SQL scripts contain GO keyword so SQL can't be executed with standard SqlCommand.ExecuteNonQuery() method. There are two possible solutions for this. You can split script to sub queries without GO keywords and then execute them in sequence with SqlCommand.ExecuteNonQuery method, or use SQL Server Management Services. I found the second option much simpler to implement but it requires FullTrust for your code. Let's see how both methods works:

Installing SQL membership schema with Server Management Objects (SMO)

First, add reference in your project to Microsoft.SqlServer.Smo.dll and Microsoft.SqlServer.ConnectionInfo.dll files (you can do this through menu, go Website -> Add Reference... and add these files. Notice they are not part of core .Net Framework. You can find them in Microsoft SQL Server/100/SDK/Assemblies/ folder). In this example, I'll use web form with one Button control named btnInstallSqlMembership. Here is the code that installs SQL membership schema on site with FullTrust permission:

[ C# ]

using System;
using System.Collections.Generic;
using System.Web.Management;
using System.Data.SqlClient;
// Located in Microsoft.SqlServer.Smo.dll file
using Microsoft.SqlServer.Management.Smo;
// This assembly you can find in Microsoft.SqlServer.ConnectionInfo.dll
using Microsoft.SqlServer.Management.Common;
 
public partial class Admin_Test : System.Web.UI.Page
{
 
  protected void btnInstallSqlMembership_Click(object sender, EventArgs e)
  {
    // get SQL script for manual installation of SQL membership schema
    string SqlScript = SqlServices.GenerateApplicationServicesScripts(true,
      SqlFeatures.All, "your-database-name");
 
    // Establish connection to SQL Server database
    SqlConnection conn = new SqlConnection(
      ConfigurationManager.ConnectionStrings[
      "Your-Connection-String-Name"].ConnectionString);
 
    // Declare server object from Server Management Objects (SMO)
    Server server = new Server(new ServerConnection(conn));
 
    // Execute SQL script that contains GO keywords
    server.ConnectionContext.ExecuteNonQuery(SqlScript);
    }
}

[ VB.NET ]

Imports System
Imports System.Configuration
Imports System.Web.Management
Imports System.Data.SqlClient
' Located in Microsoft.SqlServer.Smo.dll file
Imports Microsoft.SqlServer.Management.Smo
' This assembly you can find in Microsoft.SqlServer.ConnectionInfo.dll
Imports Microsoft.SqlServer.Management.Common
 
 
Partial Class Default2
  Inherits System.Web.UI.Page
 
  Protected Sub btnInstallSqlMembership_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSendEmail.Click
    ' get SQL script for manual installation of SQL membership schema
    Dim SqlScript As String = SqlServices.GenerateApplicationServicesScripts(True, _
      SqlFeatures.All, "your-database-name")
 
    ' Establish connection to SQL Server database
    Dim conn As SqlConnection = New SqlConnection( _
      ConfigurationManager.ConnectionStrings( _
      "Your-Connection-String-Name").ConnectionString)
 
    Dim server As Server = New Server(New ServerConnection(conn))
    server.ConnectionContext.ExecuteNonQuery(SqlScript)
  End Sub
End Class

If your web site runs in Medium Trust you can't use this option because these dlls don't allow calls from partially trust callers. But, you can run code on your localhost to see how it works and review SQL returned from GenerateApplicationServicesScripts method, e.g. you can execute code against remote SQL Server, store value of SqlScript variable to TextBox, save it as .sql file to disc etc.

Installing Sql Membership schema with ASP.NET code in Medium Trust

Previous example is very simple, but in Medium Trust level you can't use SqlServices or ConnnectionContext. But, you can execute required SQL scripts using simple SqlCommand object. In this example, required SQL scripts are uploaded to web server and placed in /SQL subfolder. /SQL subfolder will contain these files:

InstallRoles.sql
InstallCommon.sql
InstallMembership.sql
InstallPersonalization.sql
InstallProfile.sql
InstallWebEventSqlProvider.sql

Code example bellow will load these files and execute them against chosen database. Unlike previous code, this is more complex but it works on shared hosting with limited permissions:

[ C# ]

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
// We need this namespace to read .sql files from disc
using System.IO;
 
public partial class Admin_Setup : System.Web.UI.Page
{
 
  protected void btnInstallMembership_Click(object sender, EventArgs e)
  {
    // Declare list of .sql files,
    // notice that InstallCommon.sql must be installed first
    string[] fileNames = {"InstallCommon.sql",
      "InstallRoles.sql",
      "InstallMembership.sql",
      "InstallPersonalization.sql",
      "InstallProfile.sql",
      "InstallWebEventSqlProvider.sql"};
 
    string SqlQuery = "";
    string CurrentLine = "";
    SqlConnection conn = new SqlConnection(
      ConfigurationManager.ConnectionStrings[
      "connection-string-name"].ConnectionString);
    SqlCommand comm = new SqlCommand();
    conn.Open();
    SqlTransaction trans = conn.BeginTransaction();
 
    comm.Connection = conn;
    comm.Transaction = trans;
    comm.CommandType = CommandType.Text;
 
    // Iterates through all files
    for (int i = 0; i < fileNames.Length; i++)
    {
      // Open current SQL script from SQL/ sub folder
      string FileContent = File.ReadAllText(Server.MapPath("SQL/") + fileNames[i], System.Text.Encoding.UTF8);
      // replace default database name 'aspnetdb' to name of your database
      FileContent = FileContent.Replace("aspnetdb", "your-database-name");
 
      // Loads string to StreamReader
      MemoryStream ms = new MemoryStream(System.Text.Encoding.UTF8.GetBytes(FileContent));
      StreamReader sr = new StreamReader(ms);
 
      while (!sr.EndOfStream)
      {
        CurrentLine = sr.ReadLine();
        // Check if line is empty
        if (CurrentLine != null && CurrentLine != "")
        {
          if (CurrentLine.Trim().ToUpper() != "GO")
          {
            // Build Sql to execute
            SqlQuery += CurrentLine + "\n";
          }
          else
          {
            // Current line is 'GO' so execute code chunk
            comm.CommandText = SqlQuery;
            try
            {
              comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
              Response.Write(ex.Message);
              try
              {
                trans.Rollback();
              }
              catch (Exception exception)
              {
                Response.Write("<br />" + exception.Message);
              }
            }
            SqlQuery = "";
          }
        }
      }
 
    }
    Response.Write("SQL membership schema installed successfully");
    conn.Close();
    comm.Dispose();
    trans.Dispose();
    conn.Dispose();
  }
}

[ VB.NET ]

Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
' We need this namespace to read .sql files from disc
Imports System.IO
 
 
Partial Class Default2
  Inherits System.Web.UI.Page
 
  Protected Sub btnInstallSqlMembership_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSendEmail.Click
    ' Declare list of .sql files,
    ' notice that InstallCommon.sql must be installed first
    Dim fileNames() As String = {"InstallCommon.sql", _
      "InstallRoles.sql", _
      "InstallMembership.sql", _
      "InstallPersonalization.sql", _
      "InstallProfile.sql", _
      "InstallWebEventSqlProvider.sql"}
 
    Dim SqlQuery As String = ""
    Dim CurrentLine As String = ""
    Dim conn As SqlConnection = New SqlConnection( _
      ConfigurationManager.ConnectionStrings( _
      "connection-string-name").ConnectionString)
    Dim comm As SqlCommand = New SqlCommand()
    conn.Open()
    Dim trans As SqlTransaction = conn.BeginTransaction()
 
    comm.Connection = conn
    comm.Transaction = trans
    comm.CommandType = CommandType.Text
 
    ' Iterates through all files
    For i As Integer = 0 To i < fileNames.Length
    ' Open current SQL script from SQL/ sub folder
    Dim FileContent As String = File.ReadAllText(Server.MapPath("SQL/") _
      & fileNames(i), System.Text.Encoding.UTF8)
    ' replace default database name 'aspnetdb' to name of your database
    FileContent = FileContent.Replace("aspnetdb", "your-database-name")
 
    ' Loads string to StreamReader
    Dim ms As MemoryStream = New MemoryStream(System.Text.Encoding.UTF8.GetBytes(FileContent))
    Dim sr As StreamReader = New StreamReader(ms)
 
    While Not sr.EndOfStream
      CurrentLine = sr.ReadLine()
      ' Check if line is empty
      If Not (CurrentLine Is Nothing) And CurrentLine <> "" Then
        If CurrentLine.Trim().ToUpper() <> "GO" Then
          ' Build Sql to execute
          SqlQuery += CurrentLine + "\n"
        Else
          ' Current line is 'GO' so execute code chunk
          comm.CommandText = SqlQuery
          Try
            comm.ExecuteNonQuery()
          Catch ex As Exception
            Response.Write(ex.Message)
            Try
              trans.Rollback()
            Catch exception As Exception
              Response.Write("<br />" + exception.Message)
            End Try
          End Try
          SqlQuery = ""
        End If
      End If
    End While
 
    Next
    Response.Write("SQL membership schema installed successfully")
    conn.Close()
    comm.Dispose()
    trans.Dispose()
    conn.Dispose()
  End Sub
End Class

Also, you can uninstall SQL schema with similar code. Just use files with uninstall prefix located in your v2.0 folder (probably located on c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\ ). To uninstall SQL membership schema, use the same code but run these files:

UninstallRoles.sql
UninstallCommon.sql
UninstallMembership.sql
UninstallPersonalization.sql
UninstallProfile.sql
UninstallWebEventSqlProvider.sql

Conclusion

ASPNET_REGSQL tool is often recommended as a way to install SQL membership schema to SQL Server database. Although you can use this tool whenever is appropriate, now you know that ASPNET_REGSQL doesn't do anything special. It just runs few SQL scripts against selected database. To automate this task, you can use provided code examples instead of manual repetitive work.

To understand deeply how ASP.NET user and role management works, check Professional ASP.NET 3.5 Security, Membership, and Role Management with C# and VB (Wrox Programmer to Programmer) book which covers these issues in details.

Happy coding!


Tutorial toolbar:  Tell A Friend  |  Add to favorites  |  Feedback  |   


comments powered by Disqus