Search code examples
scalaazurejdbcdatabricksazure-databricks

Can't connect to sql server managed instance from azure databricks

I am trying to connect to an Azure Sql managed instance from databricks. I am using Scala to connect to it. The code I have copied from the Microsoft web site

My actual scala code : (I have changed the credentials and IP. But I have made sure they are correct as I have copied them from the connection strings in the sql server managed instance options) 

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver")
val jdbcHostname = "dev-migdb.nf53e3653n43.database.windows.net"
val jdbcPort = 1433
val jdbcDatabase = "MYDB"

// Create the JDBC URL without passing in the user and password parameters.
val jdbcUrl = s"jdbc:sqlserver://${jdbcHostname}:${jdbcPort};database=${jdbcDatabase};loginTimeout=90"

// Create a Properties() object to hold the parameters.
import java.util.Properties
val connectionProperties = new Properties()

connectionProperties.put("user", "db-devmigmgd")
connectionProperties.put("password", "pwd##321232123")

val driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
connectionProperties.setProperty("Driver", driverClass)

val employees_table = spark.read.jdbc(jdbcUrl, "employees", connectionProperties)
0

Error :

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host dev-migdb.nf53e3653n43.database.windows.net, port 1433 has failed. Error: "Connection timed out: no further information. 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.".
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:227)
    at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:284)
    at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2435)
    at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:635)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2010)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1687)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1528)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:866)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:569)
    at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:63)
    at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:54)
    at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:56)
    at org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation$.getSchema(JDBCRelation.scala:210)
    at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:35)
    at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:336)
    at org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:286)
    at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:274)
    at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:198)
    at org.apache.spark.sql.DataFrameReader.jdbc(DataFrameReader.scala:301)
    at linee84eb162c20345fc84ad591cfefe930f29.$read$$iw$$iw$$iw$$iw$$iw$$iw.<init>(command-999597493877319:49)
    at linee84eb162c20345fc84ad591cfefe930f29.$read$$iw$$iw$$iw$$iw$$iw.<init>(command-999597493877319:104)

On the other hand :

  1. I am able to connect to the same managed instance from the VM that is there on the same Azure subscription (using SSMS)
  2. My custom application that is written in .Net and hosted on that VM is also able to connect to the same instance

Also, I am unable to connect to the same instance from scala code that I am executing using spark shell on the above VM. BUT the errors that I am getting are different. Please find errors below. 

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'db-devmigmgd@dev-migdb'.
  at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)
  at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:246)
  at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:83)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:2529)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:1905)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:41)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:1893)
  at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4874)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1045)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:817)
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:700)
  at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:842)
  at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:63)
  at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:54)
  at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:56)
  at org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation$.getSchema(JDBCRelation.scala:210)
  at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:35)
  at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:318)
  at org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:223)
  at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:211)
  at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:167)
  at org.apache.spark.sql.DataFrameReader.jdbc(DataFrameReader.scala:238)
  ... 76 elided
Solution

  • Thanks @simon_dmorias,

    As per your comments and discussions with the Microsoft team, resources on different VNET was the real problem.

    So as a best practice, always create resources in the same VNETs.