This article presents how you can use an existing users database with a LightSwitch application. I found a sample about linking the current application users to another table but no sample about using an existing users database in a LightSwitch application. This article presents this process step by step.

Here is the steps:

1. Creating the Users Database

LightSwitch applications use the same database tables for security that are used by all the other .NET applications. Since I do not have an existing users database, I will create one here.

As we all know, in order to add the aspnet_* database tables and stored procedures to an existing database, we can use the aspnet_regsql.exe tool. After we have created the database by using SQL Server Management Studio, we can run the following command in order to add our tables.

In the image above, you can see that we are adding the membership, role and profile tables to the LSTestDB database. These are the tables that are used by the LightSwitch application. After this command is run, we have the specified tables in our database. This can be seen in the image below:

LightSwitch uses an additional table. This table is named RolePermissions and it is used to hold all the permissions assigned to the roles. The image below presents the table structure:

We do not need to add this table to our database. The permissions will be held in the LightSwitch application database (that uses the _IntrinsicData connection string)

At this point, the database is ready to go.

2. Creating the LightSwitch Application

The next step is to create the LightSwitch application. For this, we just follow the new project wizard. After the project is opened, we need to set up the application to use Forms Authentication. This can be done from the Access Control tab in the Properties window. This can be seen in the image below:



As you can see from the image above, I have granted the Security Administration permission for debug. This will allow us to access the corresponding screens in the UI. We can run the app now to see that everything is ok. The image below presents the application:



Remember not to add any users as this will add the users to the LightSwitch database.

3. Modifying the LightSwitch Application to Connect to the Existing Users Database

Now comes the interesting part. We will modify some of the LightSwitch generated files in order to connect to our users database. In particular, we will modify the generated web.config file. This file can be found in the ServerGenerated project. This is one of the projects that got generated when we created our application project. In order to get to the web.config file, we need to switch from Logical View to File View as in the image below:

We than need to click Show All Files and open the web.config file from the ServerGenerated project.

In this file, we can see a number of interesting things:

- A connection string has been added that points to the default data source and its name is _IntrinsicData.
- The membership, role and profile providers are set up and use the _IntrinsicData connection string.

We need to do two things in order to make the application use our users database:

- Add a new connection string to our users database. Make the providers use our connection string instead of _IntrinsicData.

We will add the following connection string to the web.config file:

<add name="myDB" connectionString="Data Source=.\sqlexpress;
         Initial Catalog=LSTestDB;Integrated Security=True;
         Connect Timeout=30;MultipleActiveResultSets=True" />

This connection string is very similar to the generated one except that the User Instance property is not set. All that is left now is to point the providers to use our connection string. In case the users already exist in the database, we also need to set the applicationName in the providers to the application from which we want to access the users.

This is it. The first thing we need to do to start testing our work is to add some random permission like in the image below:



After this, we can start our application and add a test user and role. This can be seen in the images below. Our custom permission is also added to the role.





The last thing we need to do is to check our database to see if the data has been added. The image below presents the results.



From the image above, you can see that the user and role were indeed added.