topic Read data from Azure SQL DB in Data Engineering

Read data from Azure SQL DB

AB_MN — Fri, 27 Jan 2023 18:40:12 GMT

I am trying to read data into a dataframe from Azure SQL DB, using jdbc. Here is the code I am using.

driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
 
database_host = "server.database.windows.net"
database_port = "1433" 
database_name = "database"
table = "table1"
user = "user@abc.com"
password = "Passwrod123"
 
url = f"jdbc:sqlserver://{database_host}:{database_port};database={database_name};"
 
 
df = (spark.read
  .format("jdbc")
  .option("driver", driver)
  .option("url", url)
  .option("dbtable", table)
  .option("user", user)
  .option("password", password)
  .load()
)

Here is the error I am receiving:

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open server "abc.com" requested by the login. The login failed.

The user does have permission to the server and database and can log in just fine using SSMS or Azure Data Studio. Is there some other configuration that I need to do so Databricks can talk to Azure SQL DB? What am I missing?

Thanks for the help.

Re: Read data from Azure SQL DB

NakedSnake — Fri, 27 Jan 2023 20:29:47 GMT

Hi there. Probably you also need to specify the correct authentication mechanism to get to the database. Try this one and check if it helps: https://learn.microsoft.com/en-us/sql/connect/jdbc/connecting-using-azure-active-directory-authentication?view=sql-server-ver15

Re: Read data from Azure SQL DB

AB_MN — Fri, 27 Jan 2023 20:50:14 GMT

That did the trick. Thank you!

Re: Read data from Azure SQL DB

Upen_databricks — Thu, 05 Oct 2023 20:54:13 GMT

Hi, I am facing the same error. I have my script as below. database is using Sql Server Authentication. I am not sure how to add the option of authentication. Also, I have a doubt that DB level also some permission is missing for the user login. Can you please guide.

Re: Read data from Azure SQL DB

Upen_databricks — Thu, 05 Oct 2023 20:55:57 GMT