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 |
|
