question

MariaMegnia-2682 avatar image
0 Votes"
MariaMegnia-2682 asked ·

Connect to SQL Server from Databricks Using Active Directory Account via JDBC Connection

I have a databricks instance that I need to read/write to an on premise SQL Server database. This works with I use a SQL Server user account and password, but once I switch it to an active directory account, I can't get it to work. Below is the scala I wrote to connect:

%scala
import org.apache.spark.sql.SaveMode
import java.util.Properties

val jdbcHostname = "myservername.ultradent.com"
val jdbcPort = 1433
val jdbcDatabase = "MyDatabaseName"
val jdbcUsername = "myUser@domain.com"
val jdbcPassword = "myPassword" //""

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

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

connectionProperties.put("user", s"${jdbcUsername}")
connectionProperties.put("password", s"${jdbcPassword}")
connectionProperties.put("authentication", "ActiveDirectoryPassword")

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

jdbcUrl

// Write the results to sql server database table for parameter results
spark.sql("select DISTINCT item, grid_id, tMAPE, wMAPE, changepoint_prior_scale, seasonality_prior_scale, isBest, start_time from param_results")
.withColumnRenamed("item", "Item")
.withColumnRenamed("grid_id", "GridId")
.withColumnRenamed("changepoint_prior_scale", "ChangepointPriorScale")
.withColumnRenamed("seasonality_prior_scale", "SeasonalityPriorScale")
.withColumnRenamed("isBest", "IsBest")
.withColumnRenamed("start_time", "StartTime")
.write.mode(SaveMode.Append).jdbc(jdbcUrl, "Forecast.ParameterResult", connectionProperties)


Here is the jdbcUrl that gets generated and the error I am receiving:

jdbcUrl: String = jdbc:sqlserver://myserver.ultradent.com:1433;database=MyDatabaseName
connectionProperties: java.util.Properties = {user=myuser@domain.com, password=myPassword, Driver=com.microsoft.sqlserver.jdbc.SQLServerDriver, authentication=ActiveDirectoryPassword}

SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target". ClientConnectionId:a54f4e45-d7dc-4a0b-bbf8-f30ab26c76bc
Caused by: SSLHandshakeException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
Caused by: ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
Caused by: SunCertPathBuilderException: unable to find valid certification path to requested target

sql-server-generalazure-databricks
· 9
10 |1000 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.

@MariaMegnia-2682 Thanks for using Microsoft Q&A !!

Are you using JDBC version 9.2 ? Can you please check if TLS 1.2 is enabled for your environment please?
Please refer to the document on detailed steps on How to enable TLS 1.2 on clients.


0 Votes 0 ·

I am not sure how to check in databricks was version they are using. I can see the JDBC URL on databricks advanced options as:

jdbc:spark://adb-4760241978964855.15.azuredatabricks.net:443/default;transportMode=http;ssl=1;httpPath=sql/protocolv1/o/4760241978964855/1219-000245-alas23;AuthMech=3;UID=token;PWD=<personal-access-token>

Do you want me to check on the databricks side or on my internal sql server side?

My DBA is saying TSL isn't enabled.




0 Votes 0 ·

@mariamegnia-2682 Can you please try enabling the TLS ?

0 Votes 0 ·
Show more comments

0 Answers