User and Application Service account setup in Azure SQL Datawarehouse

Question

We just completed the data migration from Teradata system to Azure SQL DW. Now we just need to provide necessary access to all supporting Teradata Users and Service accounts to Azure SQL DW. For this i have some basic query:

1. All the accounts/Users are of domain type,so can i use Azure Active directory to set up those accounts and configure it to have access Azure SQL DW. Please let me know the process.

2. What kind of privilege/Access is needed in Azure Portal for creating those account within Azure Active Directory.

3. In Azure SQL DW how do i add Azure AD account to specific roles that i'll be creating. How the authentication will happen here.

Let me know if the above steps is feasible. Is there any alternative approach in providing on-prem User accounts access to Azure SQL DW

Thanks

Answer 1

You can follow the instructions on the Use Azure Active Directory Authentication for authentication with SQL Database, Managed Instance, or SQL Data Warehouse for setting up Azure Active Directory with SQL Data Warehouse. A subscription admin can configure the permissions - see the Configure and manage Azure Active Directory authentication with SQL Database, Managed Instance, or SQL Data Warehouse.

In addition, there is a description of how to create AD users Adding AD Users and Security Groups to Azure SQL Data Warehouse. It's a simple matter of using the CREATE USER and CREATE LOGIN statements.

For users:

CREATE USER <alias@domain> FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = <schema>;

For security groups:

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