question

HetaDesai-5854 avatar image
0 Votes"
HetaDesai-5854 asked PRADEEPCHEEKATLA-MSFT commented

How to connect to on-premise SQL database from azure synapse notebook ?

I need to connect on-premise SQL database from synapse notebook and want to load data into Delta tables.

azure-synapse-analytics
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @HetaDesai-5854,

Following up to see if the below suggestion was helpful. And, if you have any further query do let us know.


  • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you.

0 Votes 0 ·
PRADEEPCHEEKATLA-MSFT avatar image
0 Votes"
PRADEEPCHEEKATLA-MSFT answered PRADEEPCHEEKATLA-MSFT edited

Hello @HetaDesai-5854,

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

Unfortunately, you cannot directly connect on-premise database to Azure Synapse notebooks.

The best way to work with this is to pull the data into Azure Data Lake store, and then run your notebook on the storage account and then write it back to your on-prem servers. Synapse Pipelines should allow this, as well as Azure Data Factory.

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.

192283-image.png

For more details, refer to Copy data from a SQL Server database to Azure Blob storage by using the Copy Data tool and Tutorial: How to access on-premises SQL Server from Data Factory Managed VNet using Private Endpoint.

Hope this will help. Please let us know if any further queries.


  • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

  • Want a reminder to come back and check responses? Here is how to subscribe to a notification

  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pituach avatar image
0 Votes"
pituach answered HetaDesai-5854 commented

Hi @etaDesai-5854 and welcome to the QnA forums

You can use Python Code to connect to any external database including on-premises SQL Server (like any connection you do with Python Code in application)

For full example on using Python Code to connect Azure DATABASE you can check this thread:

https://docs.microsoft.com/en-us/answers/questions/564284/connect-azure-synapse-notebook-to-azure-sql-databa.html?WT.mc_id=DP-MVP-5001699

You just need to change the connection parameter to fit your server. For example in the jdbcHostname parameter you need to use the address to your server

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This is to connect Azure SQL database. But I need to connect to SQL database which on local server.

0 Votes 0 ·
pituach avatar image pituach HetaDesai-5854 ·

Hi,

Did you read my answer to the end?

You just need to change the connection parameter to fit your server. For example in the jdbcHostname parameter you need to use the address to your server

Connecting to on-premises is almost the same. It is done exactly like any connection from Python application to on-premises SQL Server.

Once you learn how to execute Python and you learn Python and know hoe to connect SQL Server on-premises from Python then you solve your needs.

The doc in the link gives the way to do it once you fix the connection to fit your server

0 Votes 0 ·

The On-Premise DB source on local server I am trying to access through notebook is not publicly accessible.

In ADF / synapse pipelines self-hosted Integration Runtime is available to access the on-premise data source which is not publicly accessible. same way I need to leverage self-hosted IR in my notebook to access data source on local server which is not publicly accessible.

0 Votes 0 ·