Could any one please suggest how to access Storage account files from a dedicated sql pool via External tables from SQL USER login. I am able to access data from Azure AD user. But unable to do the same from SQL User.
I have tried to CREATE CREDENTIAL for my DataLake with SAS. But got an error saying - 'CREATE CREDENTIAL' is not supported in this version of SQL Server.
Version is : Microsoft Azure SQL Data Warehouse - 10.0.13727.0 Mar 12 2022 08:05:11 Copyright (c) Microsoft Corporation
Hi @SirilakshmiVeerapaneni-1115 ,
Just checking in to see if you got a chance to see below response. If the suggested response helped you, please click Accept Answer as accepted answer helps community as well.
Hello @SirilakshmiVeerapaneni-1115
You need to check the following things:
1) The SQL Client version you are using, are you querying from SQL Server Management Studio (SSMS), if yes then which version, I would suggest to use version 18 and above
2) Does your SQL User have correct permissions? You will need to provide access to schema of your external tables to the SQL User
3) If the SSMS version is correct, please check the value of your SAS token and try connecting it from Azure Storage Explorer to determine if the SAS is right and active
Do let me know of any queries
Hi @PratikSomaiya ,
Thanks for the response.
1.Yes, SSMS version is 18.
2. I am using admin credentials of Dedicated SQL Pool.
3. SAS token seems to be fine. I have checked the connection with SAS in server less SQL Pool.
Looks like CREDENTIAL syntax is supported only with Serverless sql pool and Azure SQL Database. When tried to use Database scoped credentials , it worked!.
Create DATABASE SCOPED CREDENTIAL [https://XXXX.dfs.core.windows.net]
WITH IDENTITY='Access Key',
SECRET = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
GRANT CONTROL
ON DATABASE SCOPED CREDENTIAL::[https://XXXXX.dfs.core.windows.net]
to JobRunnerLogin;
Hi @SirilakshmiVeerapaneni-1115 ,
Welcome to Microsoft Q&A platform and thanks for posting your query here.
As I understand your issue, you are trying to create Credential for dedicated SQL pool to access the file in storage account via external tables.
You need to create database scoped credential in your dedicated SQL Pool on top of which you can create external data source , file format and finally external table to access ADLS files.
You can access Storage account files via external tables in Synapse SQL pools via the following steps:
CREATE EXTERNAL DATA SOURCE to reference an external Azure storage and specify the credential that should be used to access the storage.
CREATE EXTERNAL FILE FORMAT to describe format of CSV or Parquet files.
CREATE EXTERNAL TABLE on top of the files placed on the data source with the same file format.
In order to create Data source, you need to create database scoped credential .
Hope this will help. Please let us know if any further queries.
Please don't forget to click on 
or upvote 
button whenever the information provided helps you.
Original posters help the community find answers faster by identifying the correct answer. Here is how
Want a reminder to come back and check responses? Here is how to subscribe to a notification
If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
Hi @SirilakshmiVeerapaneni-1115 ,
Just checking in to see if you got a chance to see previous response. If the suggested response helped you, please click Accept Answer as accepted answer helps community as well.
Hi @SirilakshmiVeerapaneni-1115 ,
Just following up to see if you got a chance to see previous response. If the suggested response helped you, please click Accept Answer as accepted answer helps community as well.
Hi @AnnuKumari-MSFT ,
Sorry for the delayed response. I am following the below steps from admin.user account to create External Table. Could anyone please guide what am I missing? Serverless sql pool has syntax to GRANT References to database scoped credentials for sql user. but the same is not working in Dedicated sql pool. So I have tried to create external tables from admin.user of Dedicated sql pool.
CREATE DATABASE SCOPED CREDENTIAL with SHARED ACCESS SIGNATURE IDENTITY of Data Lake.
CREATE EXTERNAL DATA SOURCE with the same CREDENTIAL.
CREATE EXTERNAL FILE FORMAT [SynapseParquetFormat] WITH ( FORMAT_TYPE = PARQUET)
CREATE SCHEMA 'SCHEMANAME'
CREATE EXTERNAL TABLE [SCHEMANAME].[TABLE] (
[ID] BIGINT,
[MODIFDATE] datetime2(7),
[SEX] nvarchar(2),
[DATEOFBIRTH] datetime2(7),
[DATEOFDEATH] datetime2(7),
[DEATHFLAG] BIT,
[DELETEFLAG] BIT)
)
WITH (
LOCATION = 'Publish/',
DATA_SOURCE = [DATASOURCE-NAME_dfs_core_windows_net],
FILE_FORMAT = [SynapseParquetFormat]
)
GO
11 people are following this question.
