Synapse Analytics SQL Authentication

This Article will go through steps to enable Synapse Analytics SQL Authentication (local authentication) and create SQL User.

This article will also go through setup to run server less queries on on-demand pool using Synapse Analytics SQL User and Managed Identity.

Error

Following error can occur with Server less Query Execution and SQL Authentication, if permission are not set properly.

Cannot find the CREDENTIAL ‘https://test123.dfs.core.windows.net/devicetelemetry/////*/’, because it does not exist or you do not have permission

MSFT SQL Authorization Documentation Link

Synapse Analytics SQL Authentication Setup

Enable SQL Authentication

• Log into portal.azure.com
• Go to Synapse workspace
• Open Azure Activity Directory from Settings

• Un-check “Support only Azure Active Directory Authentication for this workspace”

Create SQL Login and User

• Log into SQL on-demand pool using SQL Server Management Studio. Use Admin Credentials for this step, as we will create SQL user in next step.

• Create new SQL Login “DBUser”. Give this new user access on required data base using following script.

--use following script to create DB user and give reader permissions
USE database
GO

CREATE USER [DBUser] FOR LOGIN [DBUser] WITH DEFAULT_SCHEMA=[dbo]
GO

ALTER ROLE db_datareader ADD MEMBER [DBUser]
GO

SQL User Permission to Run Server less Query

Use following script to create Database scoped credentials and data source. This data source will be used by SQL user to run server less query. Behind the scene Managed identity is going to be used for storage location access.

Replace database name and Storage location with your required values. Also use admin user to run following scripts.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'abcd1234'
GO

CREATE DATABASE SCOPED CREDENTIAL SynapseIdentity
WITH IDENTITY = 'Managed Identity';

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[SynapseIdentity] TO [DBUser];


CREATE EXTERNAL DATA SOURCE mysample
WITH (    LOCATION   = 'https://test.blob.core.windows.net/curatedzone/',
          CREDENTIAL = SynapseIdentity
)

SELECT TOP 10 * FROM OPENROWSET(BULK 'ACCOUNTENTRY/', DATA_SOURCE = 'mysample', FORMAT='DELTA') as rows;

Synapse Analytics SQL Authentication- Test Server Less Query

• Log into synapse on demand pool with Synapse SQL User
• Run Server less query using Data Source created in above step. Server less query should execute successfully.

SELECT TOP 10 * 
FROM OPENROWSET(BULK 'ACCOUNTENTRY/', DATA_SOURCE = 'mysample', FORMAT='DELTA') as rows;

---

Related Article: Disable Public Access on D365 Export to Data Lake