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.
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.
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.
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
Streets Ahead 