Adding AD Users and Security Groups to Azure SQL Data Warehouse

  • Article

Azure offers you the ability to federate your corporate Active Directory to the cloud through Azure Active Directory Connect and Federation. The federation to the cloud allows you to authenticate against your Azure SQL Data Warehouse instance using your domain credentials. We often are asked how to create a user or security group in SQL DW. This blog will show you the simple steps to create a Windows authenticated user in the cloud.

Prerequisites

Creating a User for an individual

To create a windows user account, simply connect to your SQL Data Warehouse and execute the following script:

 CREATE USER [<alais@domain>] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = [<schema>];

You simply have to add the user by their alias and domain and optionally set their default schema. If you want to allow user@company.com access to the sales schema you would simply execute:

 CREATE USER [user@company.com] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = [sales];

Create a User for a security group

A best practice for managing your database is to use Windows security groups to manage user access. That way you can simply manage the customer at the Security Group level in Active Directory granting appropriate permissions. To add a security group to SQL Data Warehouse, you use the Display Name of the security group as the principal in the CREATE USER statement.

 CREATE USER [<Security Group Display Name>] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = [<schema>];

In our AD instance, we have a security group called Sales Team with an alias of salesteam@company.com . To add this security group to SQL Data Warehouse you simply run the following statement:

 CREATE USER [Sales Team] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = [sales];

If you run the statement using the alias, you will get an error that the principal is not found:

 CREATE USER [salesteam@company.com] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = [sales];

Msg 33130, Level 16, State 1, Line 4
Principal 'salesteam@company.com' could not be found or this principal type is not supported.