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

  • DataSets, ObjectDataSource and 3-Tier ASP.NETDataSets, ObjectDataSource and 3-Tier ASP.NET As I've mentioned in other post, while I think LINQ has it's uses, I still favor the use of the ObjectDataSource control in conjunction with DataSets and a 3-tiered architecture.  Since any fu...
  • Swapping Out the DALSwapping Out the DAL One of the comments that showed up on the blog post about using 3-tiered with DataSets and the ObjectDataSource was, "How do we set this up so that we can swap out the DAL?"  The simple answer...
  • Swapping the DAL AbstractlySwapping the DAL Abstractly Last Thursday, we looked at how to replace the TableAdapter with our own TableAdapter as a way or eliminating our dependence on Microsoft SQL.  The problem with this implementation is that eve...
  • Silverlight – Navigating DataSilverlight – Navigating Data Last week I demonstrated how to access data from a web service in Silverlight and display it on the page.  Today we are going to continue on with that demo and look at how to navigate through...
  • Unable to find connection…for object web.configUnable to find connection…for object web.config I've seen this error a couple of different times.  Mostly from other people calling me with the problem.  So I still don't know what ultimately causes the problem.  But if you're having this probl...