question

AswiniDindukurthy-8280 avatar image
0 Votes"
AswiniDindukurthy-8280 asked KranthiPakala-MSFT commented

Access Azure SQL DB through AD from data bricks

Hello,


I have installed com.microsoft.azure:azure-sqldb-spark:1.0.2 and using data bricks run time 6.4 Extended Support (includes Apache Spark 2.4.5, Scala 2.11).

Below is the code:
%python
jdbc_df = spark.read.format("com.microsoft.sqlserver.jdbc.spark").option("url", "hostname here:1433") .option("dbtable", "dbo._Temp_001").option("authentication", "ActiveDirectoryPassword").option("user","test@test.com").option("password", "password here").option("encrypt", "true").option("hostNameInCertificate", "*.database.windows.net").load()

receiving below error:

java.lang.ClassNotFoundException: Failed to find data source: com.microsoft.sqlserver.jdbc.spark. Please find packages at http://spark.apache.org/third-party-projects.html

Please help me

azure-databricks
· 1
5 |1600 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.

1 Answer

PRADEEPCHEEKATLA-MSFT avatar image
0 Votes"
PRADEEPCHEEKATLA-MSFT answered KranthiPakala-MSFT commented

Hello @AswiniDindukurthy-8280,

Thanks for the question and using MS Q&A platform.

You are experiencing this error message due to extra space between .option("url", "hostname here:1433") and .option("dbtable", "dbo._Temp_001").

Note: Try to remove the space to resolve the issue.

![115230-image.png

For more details, refer to the below links:

Apache Spark connector: SQL Server & Azure SQL

Notebook - SQL Spark Connector - Python AAD Auth

Hope this helps. Do let us know if you any further queries.


Please "Accept the answer" if the information helped you. This will help us and others in the community as well.


image.png (139.3 KiB)
· 8
5 |1600 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.

@PRADEEPCHEEKATLA-MSFT

Thank you for your response.

I have removed the extra space

%python
jdbc_df = spark.read.format("com.microsoft.sqlserver.jdbc.spark").option("url", "hostnamehere:1433").option("dbtable", "dbo._Temp_001").option("authentication", "ActiveDirectoryPassword").option("user","test@test.com").option("password", "passwordhere").option("encrypt", "true").option("hostNameInCertificate", "*.database.windows.net").load()

But still receiving below error:

java.lang.ClassNotFoundException: Failed to find data source: com.microsoft.sqlserver.jdbc.spark. Please find packages at http://spark.apache.org/third-party-projects.html


Py4JJavaError Traceback (most recent call last)
<command-4332877993495017> in <module>
----> 1 jdbc_df = spark.read.format("com.microsoft.sqlserver.jdbc.spark").option("url", "hostnamehere:1433").option("dbtable", "dbo._Temp_001").option("authentication", "ActiveDirectoryPassword").option("user","test@test.com").option("password", "passwordhere").option("encrypt", "true").option("hostNameInCertificate", "*.database.windows.net").load()

/databricks/spark/python/pyspark/sql/readwriter.py in load(self, path, format, schema, **options)
170 return self._df(self._jreader.load(self._spark._sc._jvm.PythonUtils.toSeq(path)))
171 else:
--> 172 return self._df(self._jreader.load())
173
174 @since(1.4)

0 Votes 0 ·

Hello @AswiniDindukurthy-8280,

Make sure you have installed Spark 2.4.x compatible connector.

115354-image.png

You can find the connector here: https://search.maven.org/search?q=spark-mssql-connector

Download the "https://search.maven.org/search?q=spark-mssql-connector" jar file and upload to the cluster and restart.

115327-image.png

Hope this helps. Do let us know if you any further queries.

0 Votes 0 ·
image.png (6.8 KiB)
image.png (38.9 KiB)

@PRADEEPCHEEKATLA-MSFT 115394-upload.png
Thanks for the quick response.

I have installed library.

Receiving below error.


java.sql.SQLException: No suitable driver


Py4JJavaError Traceback (most recent call last)
<command-4332877993495017> in <module>
----> 1 jdbc_df = spark.read.format("com.microsoft.sqlserver.jdbc.spark").option("url", "hostnamehere:1433").option("dbtable", "dbo._Temp_001").option("authentication", "ActiveDirectoryPassword").option("user","test@test.com").option("password", "passwordhere").option("encrypt", "true").option("hostNameInCertificate", "*.database.windows.net").load()


0 Votes 0 ·
upload.png (59.4 KiB)

Hello @AswiniDindukurthy-8280,

Could you please try the below JDBC URL to access Azure SQL DB through AD from data bricks:

 jdbcUrl = "jdbc:sqlserver://azsqldb.database.windows.net:1433;database=azsqldb;user=test@test.com;password=XXXXXXXXXX;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;authentication=ActiveDirectoryPassword"
 df = spark.read.jdbc(url=jdbcUrl, table="customers").load()

Hope this helps. Do let us know if you any further queries.

0 Votes 0 ·

@PRADEEPCHEEKATLA-MSFT

Thank you

Receiving below error now

java.lang.NoClassDefFoundError: com/microsoft/aad/adal4j/AuthenticationException


I have installed ada115395-adal4j.pngl4j library too.

.


0 Votes 0 ·
adal4j.png (63.1 KiB)

@PRADEEPCHEEKATLA-MSFT Awaiting for your response please

1 Vote 1 ·
Show more comments