Forum Discussion

wzbeaver's avatar
wzbeaver
Frequent Visitor
2 years ago

Connect to On-prem SQL Server via Fabric Notebook

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?

data engineering
notebook

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        wzbeaver
        Frequent Visitor
        2 years ago

        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's avatar
    wzbeaver
    Frequent Visitor
    1 year ago

    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.