Synapse – SQL User

The most secure method for protecting your data in Synapse is through the use of Azure AD. To query data stored in the data lake through a Synapse Serverless endpoint, the AD account must possess the necessary permissions for both the database and the underlying storage account data files. This is known as pass-through authentication which allows the user’s credentials to be used for accessing both the database objects and the data files in the storage account. In a previous post, I explained how to secure the data created by Synapse Link for Dataverse by using an AD group. The approach demonstrates a convenient way to grant access by simply adding a user or service principal to an AD group.

While using a service principal to access your data in Synape is the preferred approach you may have some legacy software that needs to use a SQL user account to query your serverless endpoint. You can create a SQL User account in Synapse Serverless just like you would in a regular SQL Server. The script below can be run in serverless SQL to create a SQL user and gives the account rights to access and query all databases.

use master 
CREATE LOGIN sqluser WITH PASSWORD = 'jUb5DTBA6HD!' 
GO
GRANT CONNECT ANY DATABASE TO sqluser
go
GRANT SELECT ALL USER SECURABLES TO sqluser
go

You can then use this account to logon to your Synapse serverless endpoint using SQL Server Authentication. You will be able to view all the database and tables and views therein. Unfortunately, if you run a query you will get a ‘Cannot find the CREDENTIAL’ error and this is because it does not have rights to read the CSV files in the storage account. Storage accounts are protected by Azure AD and you cannot give a SQL user account AD rights.

To provide access to the underlying storage for your SQL user account, you can create a credential record and share it with the account. The credential record contains all the necessary authentication details required to access resources outside of Synapse. The scope of the credentials can either be set at the server level or the database level, depending on your needs. Different authentication methods, including SAS Token, Key Vault, and Managed Identity, can be used to create the credentials. In this example, we will demonstrate how to create server-level credentials using Synapse Managed Identity and then grant access rights to the SQL user account.

CREATE CREDENTIAL [https://joepartnersynapse.dfs.core.windows.net/dataverse-powerplatfor-org2b19a138]
WITH IDENTITY = 'Managed Identity';
go
GRANT REFERENCES ON CREDENTIAL::[https://joepartnersynapse.dfs.core.windows.net/dataverse-powerplatfor-org2b19a138] 
TO sqluser;

The script above creates credentials records with the rights of the Synapse Managed Identity on the storage container for the Data Lake database created by Synapse Link for Dataverse. The url comprises of Data Lake Storage Account which can be found in your Synapse Workspace properties followed by the database container name.

For lake databases created by Synapse Link for Dataverse the database name and the container name match.

Hopefully, this post explains how to create a SQL User account that can access Serverless SQL data. By creating credentials and granting the SQL user rights to the credentials the account can query the data stored in the data lake storage account.