Azure Syanpse Analytics

Question

I have a need to connect to Synapse Analytics Serverless SQL Pool database using SQL Authentication. I created a serverless SQL Pool database and created a SQL User and provided db_owner access.

Then created an external table below

IF NOT EXISTS (SELECT * FROM sys.external_file_formats 
   WHERE name = 'SynapseDeltaFormat') 
   CREATE EXTERNAL FILE FORMAT [SynapseDeltaFormat] 
   WITH ( FORMAT_TYPE = PARQUET)
GO

IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 
'test_dfs_core_windows_net') 
CREATE EXTERNAL DATA SOURCE [test_dfs_core_windows_net] 
WITH (
    LOCATION = 'abfss://test.dfs.core.windows.net' 
)
GO

CREATE EXTERNAL TABLE table_staging (
<columns here>
)
WITH (
LOCATION = 'bronze/table_staging/',
DATA_SOURCE = [test_dfs_core_windows_net],
FILE_FORMAT = [SynapseDeltaFormat]
)
GO


SELECT TOP 100 * FROM dbo.table_staging
GO

Get below error when trying to access data of the table using SQL User External table 'dbo.table_staging' is not accessible because location does not exist or it is used by another process.

Table data is accessible using AD user. Created DataSource using SQL User.

It seems like that SQL Server User does not have access to data lake/data storage. How to grant that access?

Answer 1

Just started delta lakes myself :)

Per default the serverless sql authenticates using the user context. When you are querying from within the synapse data studio you are using your AD users context which is why you can connect to the external storage.

However for SQL users and AD users that do not have access to be able to query you need to use credentials when setting up the query.

You can finde detailed instructions here: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-storage-files-storage-access-control?tabs=user-identity

You need to have way of accessing the storage - either service principal, SAS, Managed Identity

I just set up credentials using a service principal "App registration"

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<secret>';
go

CREATE DATABASE SCOPED CREDENTIAL [credentialname] WITH
IDENTITY = '<Client-ID>@https://login.microsoftonline.com/<tenant-id>/oauth2/v2.0/token' 
, SECRET = '<token>'
GO


CREATE EXTERNAL DATA SOURCE mydatasource  WITH (
    LOCATION = 'https://<storageaccount>.dfs.core.windows.net/onedatahubtest',
    CREDENTIAL = credentialname
);

CREATE VIEW MYVIEW AS
SELECT   *  
FROM OPENROWSET(
    BULK 'Example/table',
    DATA_SOURCE='mydatasource',
    FORMAT = 'delta') as rows;

And now my sql user can access the view