question

SirilakshmiVeerapaneni-1115 avatar image
0 Votes"
SirilakshmiVeerapaneni-1115 asked AnnuKumari-MSFT commented

How to connect to storage account from dedicated pool via External table from SQL user ( Non- AD User)

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

azure-synapse-analytics
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·
PratikSomaiya avatar image
0 Votes"
PratikSomaiya answered SirilakshmiVeerapaneni-1115 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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;

0 Votes 0 ·
AnnuKumari-MSFT avatar image
0 Votes"
AnnuKumari-MSFT answered AnnuKumari-MSFT commented

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 130616-image.png or upvote 130671-image.png 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


· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

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.

0 Votes 0 ·

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.

  1. CREATE DATABASE SCOPED CREDENTIAL with SHARED ACCESS SIGNATURE IDENTITY of Data Lake.

  2. CREATE EXTERNAL DATA SOURCE with the same CREDENTIAL.

  3. CREATE EXTERNAL FILE FORMAT [SynapseParquetFormat] WITH ( FORMAT_TYPE = PARQUET)

  4. CREATE SCHEMA 'SCHEMANAME'

  5. 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

200669-image.png


0 Votes 0 ·
image.png (15.8 KiB)
Show more comments