Creating an SQL Azure Database for Metadata

Some of the steps in this post will require ADALSQL.DLL this can be found in SQL Management Studio 2016, or Data Tools for Visual 2015; and .NET framework 4.6 or later.

Setup the Database Server and Metadata Database

On your Azure Portal (portal.azure.com) you’ll need to create a new Database, and if you do not yet have a Server a new Sever as well.  This can all be completed from the SQL Database Blade.

New - > Data + Storage -> SQL Database

For several steps you’ll need to sure ensure that it is a V12 Server, and you will want to allow azure services to access the server.

Add Client IP Address to Firewall

You will need to at least add your client IP Address, as well as any other well known IP Address ranges into the Firewall Settings.

Add Client IP to SQL Firewall

Configure Azure Active Directory Authentication

For a database that is designed to be shared across many users, the best solution will be to integrate this with your Azure Active Directory, using Azure Active Directory Groups. I will assume you already have an Azure Active Directory Instance setup and running.

Setup the Required Security Groups

Using Security groups is the preferred method for controlling access, this is especially true in Enterprise Scenarios; with Azure Active Directory Premium we can use Dynamic groups for this as well which can keep maintenance very low once the business rules are agreed on.

Create SQL Administrator Group

First, you will want to create an Azure Active Directory Security Group to hold the SQL Server Admins for the server; I am going to name it “a-ca-sql-admins”.

Add the users who will require administrator rights to the SQL Servers to this group, most likely your admin account.

Create an SQL Users Group for the Database

Secondly, you will need a group for the users of the metadata.  This will hold all of the users who are approved to access this database. I will name it “a-ca-sql-metadata-users”.

Creating user groups that have functions for each database will make it easier to maintain in the future, if someone outside the sql admin team was going maintain the dimensions on this database you could also create “a-ca-sql-metadata-readers” and “a-ca-sql-metadata-writers”.

Assign the Azure Active Directory Administrator

Open the SQL Server Blade from the new Azure Portal and on the settings menu you will find an option to assign the Active Directory admin.  In our case this will be a security group.  Note that MSN and other non-integrated domains cannot be the assigned admin.

Add Azure SQL Admin.PNG

Create the user in the metadata Database

Open a connection to the Server using one of the accounts in the Azure Active Directory Administrator group defined above.  In this case, because I am not using Federation I am using Active Directory Password Authentication.

SSMS - Connect to Azure Active Directory

When connected Open a New Query run the following command.

CREATE USER [a-ca-sql-metadata-users] FROM EXTERNAL PROVIDER
GO
EXEC sp_addrolemember 'db_datareader', [a-ca-sql-metadata-users]

This will create the new user into the metadata database and then assign the db_datareader role, in this case, due to the nature of the information we want everyone to be able to read everything.

You would not do this for a database with internal data and instead, would create a unique role set for the tables required.

Summary

The steps above are a straightforward way to create a central database that can use various utility tables to use across various reporting platforms, this will reduce the duplication of functions and allow for improvements in data models to be quickly shared.

Additional Information and Resources

If you’re looking for in the in-depth steps you can find them here:

https://azure.microsoft.com/en-us/documentation/articles/sql-database-aad-authentication/

Additionally, there is a helpful video on Channel9 https://channel9.msdn.com/Shows/Data-Exposed/Azure-Active-Directory-Authentication-for-SQL-Database-V12

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s