Duplicating an Azure database with row level security to a new resource group

Introduction

One of the benefits of using Azure resource groups is being able to quickly create identical environments using resource group templates and powershell scripts, so when I was asked to quickly whip up a new test environment, I was prepared, and feeling pretty good about having automated all the things in advance.

Using the template I had created earlier, I had a shiny new environment within a few minutes. Then I thought.. but what about the data?

The Problem

The solution we are developing uses a Sql server row level security policy to provide access restrictions, and one of the drawbacks of this method is that it’s not easy to extract data using traditional data extraction tools, as every login only sees their own data (as it should be). We could have dropped the policy, created a backup (bacpac) file, and then imported to the new empty database. However, this would require a little downtime to prevent users seeing each other’s data, and anyway, that’s not really an elegant solution.

What I wanted to achieve was:

  • Make a copy of an existing database, including all data, users, and schema information.
  • Keep the same SID (security identifiers) on the new server, so that the row level security wasn’t affected.
  • Avoid modifying the original database in any way.
  • Create the new database in a different resource group.
  • Provide a solution that could be automated.

The Solution

The first step was to create a new database, and populate it with the data from the existing one. The easiest way is to use the portal to create the new database, and specify a backup source, which should list your existing database. Once you click  ‘Create’, Azure will do the hard work to import all the data, users and schema information for you.

Step 1. Select source backup.

Add_database

Step 2.
The next bit is to recreate the logins using the same SID’s on the new server. This is important as row level security uses the SID to determine which user owns which data. To help in doing this, I wrote a small Sql script that needs to be run on the source database. It prints a Sql statement that creates the logins, and maps the logins to the users in the new database.


declare @sid varchar(200)
declare @name varchar(200)
declare @sql varchar(4000)
set @sql = ''

DECLARE my_cursor CURSOR FOR
select name, dbo.fn_varbintohexstr(sid) from sys.sql_logins
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @name, @sid
WHILE (@@fetch_status <> -1)
BEGIN

set @sql = @sql + 'CREATE LOGIN '+ @name +' WITH PASSWORD = '''', SID = ' + @sid + CHAR(13)+CHAR(10)
set @sql = @sql + 'ALTER USER '+ @name +' WITH LOGIN = ' + @name +CHAR(10)
FETCH NEXT FROM my_cursor INTO @name, @sid
END

CLOSE my_cursor
DEALLOCATE my_cursor

print @sql

Step 3.

Copy the output of the previous step, log into the new server as admin, connect to the new database, and run the generated script. The script will create logins on the new server, and then link the login to the user. They key ingredient is to create the login with the correct SID. For this scenario, the password is not important, but if you need to also get the password from the old server, you will need to modify the script to create a login with a given password hash.

Step 4.
Publish the application.
With the hard work done, the last step is to just publish the code. To do this, you can download the publish settings of your application from the portal, import them into Visual Studio, and publish.

Final Thoughts

This solution at this stage was a bit of a quick hack – the next obvious step is to wrap this all into a nice package for easy re-usability. With this process, we are now able to take a snapshot of the current state of our solution, and replicate the entire environment, including data and security, in a little over 30 minutes. While this solution was implemented and tested on Azure, the same process should work in SQL Server 2016.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s