Integrating ASP.NET security with SQL Server security using LINQ

Security

Integrating ASP.NET security with SQL Server security using LINQ

Data structures

Layout and design techniques

MUST Unique Features

MUST+SQL Features

MUST+Web Features

Integrating ASP.NET security with SQL Server security using LINQ

Using SQL Servers built in security model with Windows Authentication is a great way to manage application security, but for an internet application where users will not be authenticated by windows this is not so easy to achieve.

Once users are using SQL Server security SQL Server can identify users using functions like SUSER_NAME(), this can then be used to stamp records for auditing purposes, or restrict the data a user can see, or filter data when a user makes specific choices in the web application.

ASP.NET provides it’s own security model, which is a table driven security structure, with information residing in a database called aspnetdb, which you can easily mount on your SQL Server. ASP.NET comes with a role based security model and a rich set of interface controls such as the Login control for managing security.

This article shows you how to get the best of both worlds and provide seamless integration between SQL Server security and ASP.NET membership based security. One key factor in achieving this is to utilise the LINQ DataContext which can be wrapped to dynamically map to the security model, and leave you free to develop with an absolute minimum of programming effort.

This is not a trivial article, as you need to have some familiarity with ASP.NET, SQL Server and a need for forming dynamic user specific links to databases but it is an article which illustrates several technique that we believe will allow you to further unlock the power of .net. At the very least we hope it will give you some inspiration for your own applications.

Set up aspnetdb

If you are not familiar with doing this, then either search on the web or use one of the following links to help you setup the ASP.NET security database aspnetdb.

Setting Up ASP.NET 2.0 Application Services

SQL Membership Provider and aspnet_regsql.exe Utility

When doing this don’t yet start to add users or database roles, because we want to link the aspnetdb to your application database, so we have a map between the two security environments.

Top of Page

Create Triggers in both aspnetdb and your application database

We are interested in the following tables inside aspnetdb.

aspnet_Users : modified when users are added and deleted in ASP.NET security model

aspnet_Roles : modified when roles are added and deleted in ASP.NET security model

aspnet_UsersInRoles : modified when users and added or removed from the roles

We have provided a script in the download at the end of this article which will generate the appropriate triggers on the tables which behave as follows.

aspnet_Users – Tr_aspnet_Users_CreateUser : creates a SQL Server login for the user and uses the UserId GUID in upper case as a password, also maps the user into your application database

aspnet_Users – Tr_aspnet_Users_DropUser : drops the user from your database and drops the SQL Server Login

aspnet_Roles – Tr_aspnet_Role_AddNewRole : creates a SQL Server database role in your application database to match the name of the asp.net role

aspnet_Roles – Tr_aspnet_Role_DropRole : drops a SQL Server database role in your application database

aspnet_UsersInRole – Tr_UserInRoles_Add : maps your user in the application database to the target role

aspnet_UsersInRole – Tr_UserInRoles_Remove : removes your user in the application from the target role

You also need to create the following table in aspnetdb.

This table contains one row, which is the name of your application database.

Once you have had roles created in your application database you will need to grant them permissions on the database schemas.

Before you can use the above triggers we also need to create several supporting stored procedures in your application database, these are also in the script file.

The way all this works is that when ASP.NET creates for example a new user, the trigger in aspnetdb fires and pickups up your application name from the IntergrationSettings table, the trigger then executes one of the xsecusp_ procedures in your application database to get the work done.

This also means that the security account which manages the login for adding and removing users in aspnetdb will need to have suitable permissions to create logins and manage security in the SQL Server.

Top of Page

Build your LINQ dbml files

Either you build your LINQ dbml files by hand, or you use SqlMetal, or you may like to check out the GUI interface for launching SQL Metal downloaded from http://sourceforge.net/projects/sqlmetalbuilder.

Once you have created some forms using the LINQ you will have had to specify a DataContext and that results in something like the following.

And when you look at your Northwind dbml file, you will see that it has an associated connection string.

So here is the problem, your controls use a LinqDataSource which links to the dbml file which has a fixed connection string. What we need is a connection string which is constructed at runtime to contain individual users SQL Login credentials.

Top of Page

Create your own DataContext

The following was difficult to do before LINQ; that is to build user specific connection strings. But now this is very easy.

Firstly you need a class which can dynamically build a connection string. This can be constructed as follows:-

This class uses session level variables which your application will set when a user logs in, and is created for the sole purpose of returning a connection string.

The next step is to create a new DataContext which wraps around the existing dbml DataContext class (here called Northwind) to allow the connection string to be dynamically generated:-

The final step is to change your LINQ data sources to use the new wrap around class.

Top of Page

Testing your work

As a first test create a page with for example a LINQDataSource and a ListView connected through to your data context. Then having already added in some test users you can place code along the lines of the following on your page load event.