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

Maria Megnia 31 Reputation points
2021-02-26T21:32:51.2+00:00

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@keyman .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@keyman .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

Azure Databricks
Azure Databricks
An Apache Spark-based analytics platform optimized for Azure.
2,080 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
{count} votes