How to connect on-prem SQL Server using Synapse Notebook

Question

Hi Team, we have to connect to on-prem SQL Server using synapse notebook we have the below details to connect to it.
Server=tcp:N11-xxxxxxxx.com;Initial Catalog=xxxx;User ID=xx;Password=xx

we have tried the below syntax it is not working, Could you please share the alternate solution to connect to SQL server with the server name and userid and password. Could you please help me on it.

from pyspark import SparkContext, SparkConf, SQLContext appName = "PySpark SQL Server Example - via JDBC" master = "local" conf = SparkConf().setAppName(appName).setMaster(master).set("spark.driver.extraClassPath", "sqljdbc_7.2/enu/mssql-jdbc-7.2.1.jre8.jar") sc = SparkContext(conf=conf) sqlContext = SQLContext(sc) spark = sqlContext.sparkSession database = "test" table = "dbo.Employees" user = "zeppelin" password = "zeppelin" jdbcDF = spark.read.format("jdbc") \ .option("url", f"jdbc:sqlserver://localhost:1433;databaseName={database}") \ .option("dbtable", table) \ .option("user", user) \ .option("password", password) \ .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver") \ .load() jdbcDF.show()

Answer 1

@SaiSekhar, MahasivaRavi (Philadelphia) - 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 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.