How to connect to on-premise SQL Server database from Azure Synapse Pyspark notebook ?

Question

How to connect to on-premise SQL Server database from Azure Synapse Pyspark notebook ?

Prashant Panaskar 25

I want to query on-premise SQL server tables from Azure Synpase notebooks. Can I do this? Please give detailed jdbc/pyodbc code.
As per MS community answers till 2021 this was not possible but posting this questions again in case Microsoft has added any way to do this

PRADEEPCHEEKATLA 90,641 Reputation points Moderator

2023-12-05T05:32:04.0766667+00:00

@Anonymous - Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 answer

Your answer

PRADEEPCHEEKATLA 90,641 Reputation points Moderator

2023-12-05T05:32:04.0766667+00:00

@Anonymous - Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

PRADEEPCHEEKATLA 90,641 Moderator

@Anonymous - Thanks for the question and using MS Q&A platform.

Yes, you can connect to on-premise SQL Server database from Azure Synapse Pyspark notebook.

For more details, checkout the SO thread addressing similar issue: Fetching data from On-Premise Sql Server to Azure Synapse Notebook using Linked Service.

If your data store is located inside an on-premises network, an Azure virtual network, or Amazon Virtual Private Cloud, you need to configure a self-hosted integration runtime to connect to it.

For more details, refer to Copy data from a SQL Server database to Azure Blob storage by using the Copy Data tool.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

PRADEEPCHEEKATLA 90,641 Reputation points Moderator

2023-12-07T07:04:05.5033333+00:00

@Prashant Panaskar - Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Prashant Panaskar 25 Reputation points

2024-02-04T14:27:51.68+00:00

Hi @PRADEEPCHEEKATLA .. As per this, we can connect to on-premise sql server from notebook. I have tried this but I could not connect to it. I am connecting to SQL server by using linked service with Self hosted IR. I am aware of second option you have mentioned i.e copy data but I want to avoid it because it is adding redundant data in my ADLS. Please explain when I can connect to on-prem and when I can't..

Below is the error I am facing when I tried same using this solution by Bhavani.
Can you confirm then We cant connect to sql server from synapse notebook when we are using self hosted IR with linked service

Share via

How to connect to on-premise SQL Server database from Azure Synapse Pyspark notebook ?

1 answer

Your answer