python to query azure syanpase serverless sql

Question

import pyodbc

server =
database = 'xxx'
username =
password =
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

I am using this python code to do a query from azure synapse serverless sql database.
I am getting error below.

ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]External table 'xxx' is not accessible because content of directory cannot be listed. (16561) (SQLExecDirectW)")

I have tried to query the database in synapse env, it is working fine.
I connected PowerBI with the database using my azure acc, it is working fine as well.

Is there any setting to enable the query using the python code?

Answer 1

@PRADEEPCHEEKATLA-MSFT

Thanks for the follow up.
I have found the issue and it is being resolved.

After creation of scoped credentials, we need to perform below code:

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::sqlondemand to [user]

Also need to grant select option for the [user] as below
GRANT SELECT ON OBJECT::[dbo].[external_table_name] TO [user]

Answer 2

Hello @Yang Chowmun ,

Thanks for the question and using MS Q&A platform.

User must have SELECT permission on an external table to read the data. External tables access underlying Azure storage using the database scoped credential defined in data source using the following rules:

• Data source without credential enables external tables to access publicly available files on Azure storage.
• Data source can have a credential that enables external tables to access only the files on Azure storage using SAS token or workspace Managed Identity - For examples, see the Develop storage files storage access control article.

Depending on the type of the external data source, you can use two types of external tables:

• Hadoop external tables that you can use to read and export data in various data formats such as CSV, Parquet, and ORC. Hadoop external tables are available in dedicated SQL pools, but they aren't available in serverless SQL pools.
• Native external tables that you can use to read and export data in various data formats such as CSV and Parquet. Native external tables are available in serverless SQL pools, and they are in public preview in dedicated SQL pools.

The key differences between Hadoop and native external tables are presented in the following table:

The following example creates an external data source in serverless or dedicated SQL pool for Azure Data Lake Gen2 that can be accessed using SAS credential:

CREATE DATABASE SCOPED CREDENTIAL [sqlondemand]  
WITH IDENTITY='SHARED ACCESS SIGNATURE',    
SECRET = 'sv=2018-03-28&ss=bf&srt=sco&sp=rl&st=2019-10-14T12%3A10%3A25Z&se=2061-12-31T12%3A10%3A00Z&sig=KlSU2ullCscyTS0An0nozEpo4tO5JAgGBvw%2FJX2lguw%3D'  
GO  
  
CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (  
    LOCATION = 'https://sqlondemandstorage.blob.core.windows.net',  
    CREDENTIAL = sqlondemand  
);  

For more details, refer to Use external tables with Synapse SQL.

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