Forum Discussion
Notebook to query Synapse Serverless SQL
I'm struggling with Fabric Notebooks - attempting a basic python notebook not a spark notebook.
I want to connect and pull data from Synapse Serverless SQL. Presumably, this should be fairly well integrated with Fabric and easy to pull into a notebook (and potentially save to a lakehouse, etc.). Here is some sample error but I get a kernel error on the connection (I successfully get a token).
Error:
Kernel died: Kernel python3.11 has died, please restart the kernel.
Diagnostic Info:
session id: 208aaaf3-f0a0-40f3-977f-943d60c906e1
pid: 989
exit code: -11 (Invalid memory reference)
cluster name: Here is my sample code:
import pyodbc
# Get token using notebookutils (Synapse)
access_token = notebookutils.credentials.getToken("https://database.windows.net/.default")
# Connection string with access token
server = 'your-server.database.windows.net'
database = 'your-database'
driver = '{ODBC Driver 18 for SQL Server}' # or latest version available
connection_string = f"""
Driver={driver};
Server={server};
Database={database};
Encrypt=yes;
TrustServerCertificate=no;
Connection Timeout=30;
"""
# Create connection with access token
conn = pyodbc.connect(connection_string, attrs_before={
1256: access_token # SQL_COPT_SS_ACCESS_TOKEN
})Any suggestions?
Here is what worked in the end for me, maybe this helps someone else.
def query_synapse(db, query_string): """ Function to query synapse """ # Synapse serverless SQL endpoint server = "<yourserver>.sql.azuresynapse.net" database = "<yourdb>" driver = "ODBC Driver 18 for SQL Server" connection_string = f""" Driver={driver}; Server={server}; Database={database}; """ token = notebookutils.credentials.getToken('https://database.windows.net/').encode("UTF-16-LE") token_struct = struct.pack(f"<I{len(token)}s", len(token), token) conn = pyodbc.connect(connection_string, attrs_before={1256:token_struct}) cursor = conn.cursor() cursor.execute(query_string) # Get column names columns = [column[0] for column in cursor.description] # Fetch data rows = cursor.fetchall() # Convert to DataFrame df = pd.DataFrame.from_records(rows, columns=columns) return df
3 Replies
Hi mike9999
worth trying
Encode token for ODBC driver
token_bytes = access_token.encode("UTF-16-LE")
token_struct = struct.pack(f'<I{len(token_bytes)}s', len(token_bytes), token_bytes)connection_string = f"""
Driver={driver};
Server={server};
Database={database};
Encrypt=yes;
TrustServerCertificate=no;
Authentication=ActiveDirectoryInteractive; # Critical for AAD auth
Connection Timeout=30;
"""conn = pyodbc.connect(
connection_string,
attrs_before={1256: token_struct} # SQL_COPT_SS_ACCESS_TOKEN
)I gave it a try but doesn't allow with an authentication method and a token- removing the auth method but keeping your encoding also doesn't work.
Error: ('FA005', '[FA005] [Microsoft][ODBC Driver 18 for SQL Server]Cannot use Access Token with any of the following options: Authentication, Integrated Security, User, Password. (0) (SQLDriverConnect)')Here is what worked in the end for me, maybe this helps someone else.
def query_synapse(db, query_string): """ Function to query synapse """ # Synapse serverless SQL endpoint server = "<yourserver>.sql.azuresynapse.net" database = "<yourdb>" driver = "ODBC Driver 18 for SQL Server" connection_string = f""" Driver={driver}; Server={server}; Database={database}; """ token = notebookutils.credentials.getToken('https://database.windows.net/').encode("UTF-16-LE") token_struct = struct.pack(f"<I{len(token)}s", len(token), token) conn = pyodbc.connect(connection_string, attrs_before={1256:token_struct}) cursor = conn.cursor() cursor.execute(query_string) # Get column names columns = [column[0] for column in cursor.description] # Fetch data rows = cursor.fetchall() # Convert to DataFrame df = pd.DataFrame.from_records(rows, columns=columns) return df
