topic Re: Connect to On-prem SQL Server via Fabric Notebook in Data Engineering

Connect to On-prem SQL Server via Fabric Notebook

wzbeaver — Mon, 10 Jun 2024 15:18:51 GMT

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?

Re: Connect to On-prem SQL Server via Fabric Notebook

Anonymous — Tue, 11 Jun 2024 06:25:56 GMT

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.

Re: Connect to On-prem SQL Server via Fabric Notebook

Anonymous — Wed, 12 Jun 2024 16:26:37 GMT

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

Re: Connect to On-prem SQL Server via Fabric Notebook

wzbeaver — Wed, 12 Jun 2024 19:13:52 GMT

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.

Re: Connect to On-prem SQL Server via Fabric Notebook

wzbeaver — Mon, 17 Jun 2024 13:46:16 GMT

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.

Re: Connect to On-prem SQL Server via Fabric Notebook

Anonymous — Tue, 25 Jun 2024 17:01:44 GMT

Re: Connect to On-prem SQL Server via Fabric Notebook

Devsql1234 — Tue, 24 Dec 2024 14:23:03 GMT

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/m-p/4019995/highlight/true#M2787

FYI - @wzbeaver, @Anonymous

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

Thanks

Re: Connect to On-prem SQL Server via Fabric Notebook

Anonymous — Wed, 19 Feb 2025 01:49:50 GMT

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

Re: Connect to On-prem SQL Server via Fabric Notebook

wzbeaver — Wed, 20 Aug 2025 02:48:15 GMT

Hey All,

I appreciate the follow up here, unfortunately was unable to get back to you all in a timely manner. My team is now finally in a position to work on this activity I will forward these details to them and see if we can mark this as resolved.