Spark Cluster Settings to SQL Server SSMS - Active Directory Username and Password.

Shivoy Thakral 20

Hi team.

I would like to use a 13.3 LTS Cluster and connect to SSMS SQL Server via active directory username and password.

The User and password are added to the required DB's with correct permissions.

I have used all the hit and trials - i always get the same error that Metastore is down.

I am unable to establish the connection. Please can anyone help me out?

HitenBhavsar-MSFT 381 Reputation points Microsoft Employee

2023-10-17T16:20:23.33+00:00
Hello @Shivoy Thakral

The “Metastore is down” error typically means that the Databricks cluster is not able to connect to the metastore.

For the connect to the SQL Server with Active Directory Username and Password. Please follow below document. https://learn.microsoft.com/en-us/sql/connect/spark/connector?view=sql-server-ver16#python-example-with-active-directory-password

jdbc_df = spark.read \ .format("com.microsoft.sqlserver.jdbc.spark") \ .option("url", url) \ .option("dbtable", table_name) \ .option("authentication", "ActiveDirectoryPassword") \ .option("user", user_name) \ .option("password", password) \ .option("encrypt", "true") \ .option("hostNameInCertificate", "*.database.windows.net") \ .load()
Shivoy Thakral 20 Reputation points

2023-10-18T09:36:53.44+00:00

Hi - I would like to place these settings in the advanced settings of a given cluster

What would be the syntax change for the same ?

HitenBhavsar-MSFT 381 Microsoft Employee

You can use the Key vault to store the password and secret information OR you can use the Service Principle. Try the same syntax I have not test advanced settings of a cluster.
https://github.com/microsoft/sql-spark-connector/tree/master#python-example-with-service-principal

context = adal.AuthenticationContext(authority)
token = context.acquire_token_with_client_credentials(resource_app_id_url, service_principal_id, service_principal_secret)
access_token = token["accessToken"]

jdbc_db = spark.read \
        .format("com.microsoft.sqlserver.jdbc.spark") \
        .option("url", url) \
        .option("dbtable", table_name) \
        .option("accessToken", access_token) \
        .option("encrypt", "true") \
        .option("hostNameInCertificate", "*.database.windows.net") \
        .load()

HitenBhavsar-MSFT 381 Reputation points Microsoft Employee

2023-10-27T13:39:51.3033333+00:00

HI @Shivoy Thakral

Following up to see if the below suggestion was helpful. If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know. Thanks

Share via

Spark Cluster Settings to SQL Server SSMS - Active Directory Username and Password.