top of page
Writer's pictureChen Hirsh

External tables with SQL user

When you create a Synapse SQL serverless pool, you can set it to use either only Azure AD authentication, or both Azure AD and SQL users (similar to what you have in SQL server).

Let's say you want to use both authentication methods. When you try with an Azure AD user to select from the external tables, there are no problems.

So you create an SQL user and give it read permissions:


USE master
CREATE login somename WITH PASSWORD =  'somepassword'

USE [some_db]
CREATE USER somename FOR LOGIN somename

ALTER ROLE [db_datareader] ADD MEMBER [somename]

However, when you try to select with this SQL user from an external table, you will get the following error:


External table 'dbo.ext_table' is not accessible because location does not exist or it is used by another process.

Well, the location does exist, and the file is not being used. what's missing are permissions for the SQL user to access the underlying data storage.


You need to create an object called database credential that will hold the credentials needed to access the storage. In the example below I used managed identity, which will use synapse workspace identity to access the files. You can also use shared access key.


Follow the steps in the code below:


-- 1.create a master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='somepassword'

-- 2.create a database credential
CREATE DATABASE SCOPED CREDENTIAL myCred
WITH IDENTITY = 'Managed Identity'

--3.Give the user permissions to the credential
GRANT REFERENCES     
    ON DATABASE SCOPED CREDENTIAL :: myCred  
    TO [somename]

--4.create a data source that used the credential
CREATE EXTERNAL DATA SOURCE ext_ds_mycred
WITH (LOCATION = N'abfss://filesystemname@worksapcename.dfs.core.windows.net', CREDENTIAL = [mycred])

--5.create an external table that uses the data source
CREATE EXTERNAL TABLE [dbo].[ext_table]
(
	[id] [int],
	[updated] [date]
)
WITH (DATA_SOURCE = [ext_ds_mycred],LOCATION = N'myfolder/myfile.parquet',FILE_FORMAT = [SynapseParquetFormat])

now you should be able to access the external table with the SQL user.




1 comment

1 Comment


Guest
Aug 18, 2023

Thanks! Great post!


Like

STAY IN TOUCH

Get New posts delivered straight to your inbox

Thank you for subscribing!

bottom of page