Prerequisites to use service principal authentication

Back Next

Configuring service principal user for dedicated SQL pool

Create a service principal user for the dedicated SQL pool and assign the Storage Blob Data Contributor role.

Access Microsoft SQL Server Management Studio, Azure Data Studio, or any other tool that allows access to Azure database and run the following commands:

USE SQLDW_DEV
CREATE USER [servprincipalapp] FROM EXTERNAL PROVIDER
grant ALTER ANY EXTERNAL FILE FORMAT to [serviceprincipalapp];
grant ALTER ANY EXTERNAL DATA SOURCE to [serviceprincipalapp];
grant create table to [serviceprincipalapp];
grant create schema to [serviceprincipalapp];
grant select to [serviceprincipalapp];
grant update to [serviceprincipalapp];
grant insert to [serviceprincipalapp];
grant delete to [serviceprincipalapp];
grant create view to [serviceprincipalapp];
grant select on schema :: sys to [serviceprincipalapp]
grant control to [serviceprincipalapp]
or
grant ALTER ANY SCHEMA TO <servprincipalapp>;// To grant permissions only on the schema.
grant CREATE TABLE TO <servprincipalapp>;

where,

SQLDW_DEV

is the name of the database and

serviceprincipalapp

is the service principal application. You can also create the user in master database. Ensure that the service principal application name matches the one that was configured in the previous steps.

If you have the ALTER ANY SCHEMA permissions, you must create the Master Key, Database Scoped Credential, and External Data Source in Microsoft Azure Synapse SQL that require the CONTROL permission on the database and specify the external data source when you create a connection.

Also, Microsoft Azure Synapse SQL Connector does not delete the Database Scoped Credential and External Data Source. You must manually delete the Database Scoped Credential and External Data Source.

Navigate to the Microsoft Azure Data Lake Storage Gen2 storage account. This image displays the Azure resources.