I want to read the data from a External Table in serverless SQL Pool using a python script

Question

Hi Folks, I Have a problem statement, where I am trying to read the data using a python script from External table in Serverless SQL Pool available into Synapse Analytics. Steps I have followed yet:
1: Linked ADLSGen2 to the Synapse Analytics 2: Create Login and USER for the database 3: Granted SELECT, EXECUTE, UPDATE permission to the user 4: create a python script as below

import pyodbc
server = 'serverless-ondemand.sql.azuresynapse.net'
database = 'database'
username = 'username'
password = 'password'
driver = '{ODBC Driver 17 for SQL Server}'  # Use the appropriate ODBC driver
connection_string = f'Driver={driver};Server={server};Database={database};Uid={username};Pwd={password};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=120;'
try:
    conn = pyodbc.connect(connection_string)
    
    if conn is not None:
        print("Connection established successfully")
    cursor = conn.cursor()
    sql = """
        SELECT TOP 100 * FROM [dbo].[customerLists]
    """
    
    cursor.execute(sql)
    rows = cursor.fetchall()
    
    print(rows.head(3))
    cursor.close()
    conn.close()
except Exception as e:
    print(f"Error: {str(e)}")

Using above I am getting below error:>
Error: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]External table 'customerLists' is not accessible because location does not exist or it is used by another process. (16562) (SQLExecDirectW)")

Here, I am not sure where I am missing, and what else permission is required on ADLSGen2 or Synapse level. If anyone can help me into this. Thanks in advance.

Answer 1

@Maneesh Agarwal Welcome to Microsoft Q&A platform and thanks for posting your question.

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.

Ask: Hi Folks, I Have a problem statement, where I am trying to read the data using a python script from External table in Serverless SQL Pool available into Synapse Analytics. Steps I have followed yet: 1: Linked ADLSGen2 to the Synapse Analytics 2: Create Login and USER for the database 3: Granted SELECT, EXECUTE, UPDATE permission to the user 4: create a python script as below

import pyodbc
server = 'serverless-ondemand.sql.azuresynapse.net'
database = 'database'
username = 'username'
password = 'password'
driver = '{ODBC Driver 17 for SQL Server}'  # Use the appropriate ODBC driver
connection_string = f'Driver={driver};Server={server};Database={database};Uid={username};Pwd={password};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=120;'
try:
    conn = pyodbc.connect(connection_string)
    
    if conn is not None:
        print("Connection established successfully")
    cursor = conn.cursor()
    sql = """
        SELECT TOP 100 * FROM [dbo].[customerLists]
    """
    
    cursor.execute(sql)
    rows = cursor.fetchall()
    
    print(rows.head(3))
    cursor.close()
    conn.close()
except Exception as e:
    print(f"Error: {str(e)}")

Using above I am getting below error:> Error: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]External table 'customerLists' is not accessible because location does not exist or it is used by another process. (16562) (SQLExecDirectW)")

Here, I am not sure where I am missing, and what else permission is required on ADLSGen2 or Synapse level. If anyone can help me into this.

Solution: I want to add here my findings here in above ref, to work with external table from a local environment using python script. we need to follow below steps in terms to access the external tables:

1: create a master key within the database

2: using that master key create a Database scope credentials

3: use credentials while creating the external data source

4: create the file format

5: then you need to create external tables using that external data source and file foramt.

6: After this setup, you need to create a login and an user to access the db from python script.

I hope above explanation can help anyone in future for the same issue

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

The error message you're receiving indicates that the location of your external table 'customerLists' either doesn't exist or is being used by another process.

To troubleshoot, please follow these steps: Verify Location: Ensure that the path to the data in Azure Data Lake Storage Gen2 (ADLSGen2) defined in the external table customerLists exists and the data is accessible. File Locks: Make sure no other processes are using or have locked the files at the time of query execution. Security Setup: Verify whether the Synapse Studio has been set up correctly for Azure AD with necessary permissions on ADLSGen2. Remember that the account used to authenticate must have the Storage Blob Data Contributor role assigned in ADLSGen2. Grant Permissions: External tables require additional permissions. You need to grant the necessary permissions on the external data source and the database containing the external table.

Here's how you can grant permissions:

GRANT EXTERNAL ACCESS ASSEMBLY TO [username];

GRANT CREATE PROCEDURE TO [username];

GRANT VIEW DEFINITION TO [username];

1. Managed Identity: If you are using managed identities for Azure resources, make sure it's correctly configured.

If the problem persists after checking all these areas, it would be helpful to look at the exact SQL statement you're using to create the external table 'customerLists'. The problem could be related to this configuration as well. I hope this helps! Let me know if you have any more questions.

Answer 3

Hi @Maneesh Agarwal,

Query fails because file can't be opened

If your query fails with the error File cannot be opened because it does not exist or it is used by another process and you're sure that both files exist and aren't used by another process, serverless SQL pool can't access the file. This problem usually happens because your Microsoft Entra identity doesn't have rights to access the file or because a firewall is blocking access to the file. By default, serverless SQL pool tries to access the file by using your Microsoft Entra identity. To resolve this issue, you must have proper rights to access the file. The easiest way is to grant yourself a Storage Blob Data Contributor role on the storage account you're trying to query. For more information, see:

• Microsoft Entra ID access control for storage
• Control storage account access for serverless SQL pool in Synapse Analytics See Troubleshoot serverless SQL pool in Azure Synapse Analytics

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 4

I want to add here my findings here in above ref, to work with external table from a local environment using python script. we need to follow below steps in terms to access the external tables:

1: create a master key within the database

2: using that master key create a Database scope credentials

3: use credentials while creating the external data source

4: create the file format

5: then you need to create external tables using that external data source and file foramt.

6: After this setup, you need to create a login and an user to access the db from python script.

I hope above explanation can help anyone in future for the same issue as I had.

Thanks