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

Sirilakshmi Veerapaneni 1 Reputation point
2022-04-27T18:43:57.423+00:00

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
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,252 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Pratik Somaiya 4,201 Reputation points
    2022-04-28T06:46:38.167+00:00

    Hello @Sirilakshmi Veerapaneni

    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


  2. AnnuKumari-MSFT 30,101 Reputation points Microsoft Employee
    2022-05-02T09:50:40.21+00:00

    Hi @Sirilakshmi Veerapaneni ,
    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