celedonpartners.com

How to setup Azure Active Directory/Office 365 Authentication with Azure SQL database (PaaS)

by | Jun 22, 2017 | Azure, Blog, Microsoft SQL Server |

As more users are starting to move their SQL workloads to the cloud, one of the common questions is how to setup user authentication with their Office 365/Azure Active Directory tenant. There is of course some limitations. At this time, Azure AD authentication can only be assigned at the database level, not the server level and for the connection string, you need to specify the database. Below is a quick guide to get you started with connecting to Azure SQL with your Azure AD/Office 365 credentials.

 

Assign a Azure Active Directory admin at the server level

  1. Login to your Azure tenant (https://portal.azure.com)
  2. Bring up the SQL Servers blade (not SQL databases)
  3. Click the SQL Server you created and want to assign access to
  4. Click Active Directory admin from the menu under settings
  5. Select who you want to be admin (In my case, I selected myself)
  6. Click Set Admin
  7. Click Save

 

Connect to your SQL database as your Azure Active Directory admin

  1. Download and install the latest version of SQL Server Management Studio (SSMS) from here (I am using version 17.1 at the time of this writing.)
  2. Start SSMS
  3. Enter in your Azure SQL server name
  4. Change authentication to Active Directory Password Authentication
  5. Enter in your Active Directory admin account you assigned in the previous step and the associated password
  6. Click Connect
  7. (Optional) If you are prompted to add a new firewall rule, click sign In with your admin credentials and add your client IP address

 

Create the Azure AD/Office 365 User in the SQL database

  1. Click New Query
  2. Change the database from master to your Azure SQL database you want to assign permissions to
  3. Create the user or group in the SQL database – If you want to give access to a user, just enter in the full email address, in my case the user is ‘[email protected]’
    If you want to give access based on a Azure AD/Office 365group, make sure the security group exists in Azure AD/Office 365 and add the group name in the brackets. In my case, the group name is ‘testgroup.’ Going forward, all examples will be based on a single user, but to use a group just change the name within the brackets.
  4. Click Execute – Command(s) completed successfully will be returned and if you refresh and expand users, you should see the user account (or group) has been created.

 

Assign access to the user group

  1. Click New Query
  2. Again, change the database from master to your Azure SQL database you want to assign permissions to
  3. For this blog posting, I will be using the built in roles db_datareader and db_datawriter essentially giving my user account read/write access to the database. More information on built-roles can be found here: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles
  4. Click Execute – It should return Command(s) completed successfully.

 

Test the access with SSMS

  1. Start SSMS
  2. Enter in your Azure SQL server name
  3. Change authentication to Active Directory Password Authentication
  4. Enter in the new Azure AD user credentials you just granted access to
  5. Click Options
  6. Specify the name of the database and click Connect, if you don’t specify this, you will get a “Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’. )Microsoft SQL Server, Error: 18456)

 

You should now be able to connect to the Azure SQL database with Azure Active Directory.

 

In my next blog post, I will explain how to create a OCDB connection from Office Access 2016 to Azure SQL (PaaS) databases.