Notebook to query Synapse Serverless SQL

[mike9999]

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?

[mike9999]

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)')

[mike9999 · Accepted solution]

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

[nilendraFabric]

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
)