Unique SQL Logins and SqlMembership Provider

There are cases where it is advantageous to have a separate sql login for each user login who will be editing your database.  If you are using Windows authentication, this is not a problem because you can pass the windows authentication tokens on through to the db without having to do a whole lot of work.

But, what about times where setting up a separate windows user for each person who will be accessing the system is more work than it’s worth, or as is the case in shared hosting plans, just isn’t practical?

To solve this problem, we make use of the ability in ASP.NET to associate profile properties with users.

There are two parts to this.  First we need to set up our Business logic layer and Web.Config file to use the properties, second, we’ll need to create a user interface that allows us to associate the profile properties with the users who will be doing the edits.

Enable Profile Properties

The first thing we’ll need to do is to modify the web.config file so that it will associate the profile properties with a user.  To do this, add the following entries into your web.config file:

<configuration>

    <system.web>

        <profile>
            <properties>
                <add name="SQLUserName" defaultValue="defaultSqlUser"/>
                <add name="SQLPassword" defaultValue="defaultSqlPwd"/>
            </properties>
        </profile>
    </system.web>
</configuration>

This tells .NET to use the defaultSqlUser and defaultSqlPassword for the SQL login values if the user does not have those properties associated with them.  This is useful for cases where a user is not logged in or is not able to edit and therefore does not need a special SQL login.  Therefore, the login you use for the defaults should not have the ability to change the records in your database.

Add a Connection String

Next, you’ll need to add an entry into your connectionStrings section that we can use in the business logic layer (bll) to change the connection string.  You’ll still want to keep a standard connection string so that your visual studio Dataset designer will still work, this is in addition to any connection strings you already have:

<connectionStrings>
    <add name="ConnectionString"
        connectionString="standard connection string"
        providerName="System.Data.SqlClient"/>
    <add name="ProfileConnection"
       connectionString="Data Source=...;User ID={0};Password={1}"
       providerName="System.Data.SqlClient"/>
</connectionStrings>

Note that the ProfileConnection string has the bracketed parameters for the user id and password.  Those markers will be replaced with the SQL username and password from the profile properties.

Changing the BLL

The last step, for today, is to change the BLL.  If you remember from previous post, we create a BLL class for each table we will be connecting to.  One of the things we do in that step is to create an Adapter property that all our other methods will be able to access so that the BLL can use the adapter to call the DAL’s data access methods.

To make this all work, we will need to create a parent class that all of our other BLL classes will inherit from.  This class will have one property in it call, “ConnectionString”  Here’s the code for the connection property:

public static string ConnectionString
{
    get
    {
        string sqlUserName =
             HttpContext.Current.Profile.
            GetPropertyValue("SQLUserName").ToString();
        string sqlPassword =
             HttpContext.Current.Profile.
            GetPropertyValue("SQLPassword").ToString();
        return String.Format(
            ConfigurationManager.ConnectionStrings["ProfileConnection"].
            ConnectionString, sqlUserName, sqlPassword);
    }
}

And one last change to your Adapter properties to use that ConnectionString property:

private static DataSetTableNameTableAdapters.TableNameTableAdapter Adapter
{
  get
  {
   DataSetTableNameTableAdapters.TableNameTableAdapter
      _adapter = new DataSetTableNameTableAdapters.TableNameTableAdapter ();
     _adapter.Connection.ConnectionString = ConnectionString;
     return _adapter;
  }
}

Tomorrow, we’ll look at the code that is required to associate these properties with the users.  If you implement the code above, everything should work and you’ll be ready for the final touch tomorrow.

See Also:

Datasets ObjectDataSource and 3-Tier ASP.NET
Datasets StoredProcedures and 3-Tier ASP.NET
Swapping Out the Data Access Layer
Swapping the Data Access Layer Abstractly
Using Reflection to Load a Class in ASP.NET

Related Post

One Response to “Unique SQL Logins and SqlMembership Provider”