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

Question

question

HetaDesai-5854 asked Apr 22, '22 PRADEEPCHEEKATLA-MSFT commented Apr 27, '22

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

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

PRADEEPCHEEKATLA-MSFT · Apr 27 at 11:11 AM

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 or upvote button whenever the information provided helps you.

0 ·

Answer 1 · 2022-04-25T07:19:17.207Z

PRADEEPCHEEKATLA-MSFT answered Apr 25, '22 PRADEEPCHEEKATLA-MSFT edited Apr 27, '22

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.

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 or upvote 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

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

Answer 2 · 2022-04-22T11:30:31.93Z

pituach answered Apr 22, '22 HetaDesai-5854 commented Apr 25, '22

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

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

HetaDesai-5854 · Apr 22 at 11:40 AM

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

0 ·

pituach HetaDesai-5854 · Apr 22 at 11:59 AM

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 ·

HetaDesai-5854 pituach · Apr 25 at 06:44 AM

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 ·

question