Connect to On-prem SQL Server via Fabric Notebook

Hi wzbeaver 
Thanks for using Fabric Community.

Here are a few potential solutions and things to check:

1) Network Settings:
Ensure that the network settings allow Fabric to access the VM environment. You might need to set up and use private links for secure access to Fabric.
Set up and use private links for secure access to Fabric - Microsoft Fabric | Microsoft Learn
Overview of managed virtual networks in Microsoft Fabric - Microsoft Fabric | Microsoft Learn

2) ODBC Driver:
Make sure that the ODBC driver is correctly installed and configured. Sometimes, driver issues can cause connection problems.

3) Permissions:
Ensure that the necessary permissions are granted to allow Fabric to access the SQL Server instance.

4) Firewall Settings:
Check the firewall settings of the SQL Server instance to ensure that they allow connections from Fabric.

Below is a sample python code for connecting to SQL server using pydobc:

import pyodbc

# Get the secret value from Key Vault
clientId = "<clientId>" 
tenantId = "<tenantId>"
principalSecret = mssparkutils.credentials.getSecret("https://<keyValutName>.vault.azure.net/", "<secretName>")  

# Set up SQL Server connection details
server = "<SQL Endpoint FQDN>"
database = "<database>"
username = f"{principalId}@{tenantId}"

# Create the connection string with the access token
connection_string = f"Driver={{ODBC Driver 18 for SQL Server}};Server={server};Database={database};Authentication=ActiveDirectoryServicePrincipal;UID={username};PWD={principalSecret};ConnectRetryCount=4"

print(connection_string)
# Connect to the Azure SQL Database using pyodbc
conn = pyodbc.connect(connection_string)


# Execute a query
query = "SELECT @@version version"
cursor = conn.cursor()
cursor.execute(query)

# Fetch all rows from the result set
rows = cursor.fetchall()

# Print the rows
for row in rows:
    print(row)

# Close the connection when done
conn.close()

 
Hope this helps. Please let me know if you have any further questions.