unable to load data in azure sql using azure databricks

1
votes

I am new to azure databricks . I have written a sample spark program in scala to load in azure sql via below query . I am getting an error . can someone please help me in this

Error Message ----
com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host b63da5ce2d2d.tr27.northeurope1-a.worker.database.windows.net, port 65535 has failed. Error: "connect timed out. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall."

Scala code -

import com.microsoft.azure.sqldb.spark.config.Configimportcom.microsoft.azure.sqldb.spark.connect._// Aquire a DataFrame collection (val collection)valconfig=Config(Map("url"->"mysqlserver.database.windows.net","databaseName"->"MyDatabase","dbTable"->"dbo.Clients""user"->"username","password"->"xxxxxxxx"))importorg.apache.spark.sql.SaveModecollection.write.mode(SaveMode.Append).sqlDB(config)

2
azure-sql-databaseazure-data-factoryazure-data-factory-2azure-databricks

2 Answers

0
votes

It sounds like you have the firewall enabled on the SQL DB. Try disabling that first to confirm the problem.

If it works then you have two choices if you want the firewall enabled:

1) vnet attach your databricks and sqldb to the same vnet. https://docs.azuredatabricks.net/administration-guide/cloud-configurations/azure/vnet-inject.html

2) on the sqldb whitelist all the azure ips for the location you are. These are available for download. Note that some locations have more than 128 which is the maximum number of firewall rules available. https://www.microsoft.com/en-gb/download/details.aspx?id=41653

0
votes

Why your port number is "65535" not 1433?

SQL Database communicates over port 1433. I think this is the reason why you can not connect to the Azure SQL DB. enter image description here

Please reference: Server-level IP firewall rules.

We cannot change the port in Azure SQL Server. Your database is hosted on load balanced cloud server instances which are out of 'customer' control.

Reference: Change port in azure sql server.

Port 1433 is the only port that must be open on your desktop computer that hosts your SQL Database client application.

If you don't how to open the port 1433 on your computer,

please see: How To Open Firewall Port 1433 for SQL Server Database Engine.

About how to load data in Azure SQL Database, Azure provides many methods.

  1. Load data from CSV into Azure SQL Database (flat files).

  2. Loading files from Azure Blob storage into Azure SQL Database.

For more details, you can see Load data into SQL Server or Azure SQL Database with SQL Server Integration Services.

Both of them can help you load data in Azure SQL Database.

Hope this helps.