Unable to connect external table on Serverless SQL Pool

Question

I am having an issue while working with Synapse Analytics. The Requirement is to read the table data using a python3 script. For this we are using a serverless SQL pool on the Synapse analytics and created an external table using a parquet file from the ADLSGen2 (after setting up the linked service). I am able to query the tables within the Synapse Analytics > Develope > SQL Script but when I am trying to access it using a script, I am getting below error:

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]External table 'Employee_table' is not accessible because location does not exist or it is used by another process. (16562) (SQLExecDirectW)")

The Python script i am using:

import pyodbc   
import pandas as pd
import sys
import logging
import traceback

# Set up logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
        
        
def main():
    try:
        # Set up connection to Synapse
        server = 'serverless-synapse-analytics-ondemand.sql.azuresynapse.net' #serverless endpoint
        database = 'synapse_analytics'
        username = 'liveuser'
        password = 'StrongPassword!@#0'
        driver= '{ODBC Driver 18 for SQL Server}'
        conn_str = 'DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password
        
        conn = pyodbc.connect(conn_str)
        cursor = conn.cursor()
        
        #print success message if connect is successful
        if conn:
            print('Connection established')
        
        # Execute a query and print the top 100 result rows
        sql = """select top 10 * from SAM_table"""
        
        cursor.execute(sql)
        rows = cursor.fetchall()
        print(rows.head(3))
        
        # Close the connection
        conn.close()
    except Exception as e:
        logger.error(f'Error: {e}')
        logger.error(f'Trace: {traceback.format_exc()}')
        
if __name__ == '__main__':
    main()

With above code I am able to connect with synapse (getting success message: Connection established) but when I am trying to execute the SQL query, then getting error.

what am I missing here? or how to modify the code, so that I can read the data?

Answer 1

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]External table 'Employee_table' is not accessible because location does not exist or it is used by another process. (16562) (SQLExecDirectW)")

The user whom you are trying to connect to the SQL database, i.e., liveuser, is not having access to read data from the ADLS Gen2 account attached to the serverless SQL pool. That may be the reason for getting the above error while reading the table data from the serverless SQL pool. You can follow the procedure below to read data from the serverless SQL pool using a Python script in a Synapse notebook:

1. Create an Azure SQL Database linked service with the serverless SQL pool endpoint, using system-assigned authentication as shown:

   

2. Publish the linked service, and use the following code to connect to the serverless SQL pool in a Synapse notebook:

   server = '<serverName>-ondemand.sql.azuresynapse.net'
   Port = 1433
   Database = "db"
   jdbcUrl = f"jdbc:sqlserver://{server}:{Port};databaseName={Database};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30"
   token=TokenLibrary.getConnectionString("<linkedServiceName>")
   query = "(SELECT * FROM <tableName>) as tb"
   conn_Prop = {
   "driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver",
   "accessToken" : token
   }
   
   df = spark.read.jdbc(url=jdbcUrl, table=query, properties=conn_Prop)
   display(df)
   
   

3. The connection will be successful, and the table will be queried as shown below:

   

Update:

As per this MS doc

the SQL user exists only in serverless SQL pool and permissions are scoped to the objects in serverless SQL pool. Access to securable objects in other services (such as Azure Storage) can't be granted to SQL user directly since it only exists in scope of serverless SQL pool. The SQL user needs to use one of the supported authorization types to access the files.

When you want to read external table from serverless pool locally you can use Active directory MFA authentication. You can use below code:

import pyodbc
conn = pyodbc.connect(driver='{ODBC Driver 18 for SQL Server}', host='<serverName>-ondemand.sql.azuresynapse.net', database='<databaseName>',
                      user='<ADUserName>',Authentication='ActiveDirectoryInteractive')
cursor = conn.cursor()
cursor.execute("SELECT * FROM <tableName>")
rows = cursor.fetchall()
for row in rows:
    print(row)

It will read the data table successfully as shown below: