Connect to On-prem SQL Server via Fabric Notebook

[wzbeaver]

I am currently trying to connect to our on-premises (VM) instance of SQL Server 2017 by leveraging the Spark Notebooks available in Fabric.

I have successfully done this with an Azure SQL DB instance, and am leveraging the same code structure to attempt to connect to our primary data warehouse hosted on an Azure VM.

However, when doing so I receive this error message: 

I have taken the code that I'm using, pictured above, and tested locally in Visual Studio Code, and am able to connect with no issues.

I have also installed the ODBC driver 18 on the environment that I'm attempting to connect to.

Are there some sort of specific network settings that I need to check on to allow Fabric to access this VM environment? Or is it not possible currently to connect to a VM SQL Server instance via Fabric Notebooks?

[Devsql1234]

Hi Team,

May be below is answer to issue posted here ... !!!

https://community.fabric.microsoft.com/t5/Data-Engineering/Notebooks-On-Prem-SQL-server-to-Lakehouse...

FYI - @wzbeaver, @Anonymous

Or if above link is Not an answer then please post another solution because i am facing same issue now.

Thanks

[Anonymous]

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.

[Anonymous]

Hi @wzbeaver 
We haven’t heard from you on the last response and was just checking back to see if your query got resolved. Otherwise, will respond back with the more details and we will try to help.
Thanks

[wzbeaver]

Hey @Anonymous I greatly appreciate you responding and providing the level of detail that you have. I have read through your response, but have not yet gotten an opportunity to vet the recommendations that were provided. I will be trying to do so this week, and will be sure to respond back once I either find a solution or reach another dead end.

[wzbeaver]

Hey All,

Just following up again. I have read through the provided articles and the steps provided make sense as I do suspect that we need to enable the Fabric tenant to communicate on the same VNET. I have not yet set this up since our organization tenant is currently used for production Power BI as well as our development Fabric capacity, and I did not want to enable this Managed Private Endpoint configuration at the tenant level until I discuss the potential implications with our infosec/networking team.

[KartikN]

Hi @wzbeaver , did you find any resolution on connecting notebooks to on-prem SQL server? please share. It will be much helpful. Thanks.

[Anonymous]

Hi @wzbeaver 
We haven’t heard from you on the last response and was just checking back to see if your query got resolved. Otherwise, will respond back with the more details and we will try to help.
Thanks