Read data from Azure SQL DB

[AB_MN]

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.

[NakedSnake · Accepted solution]

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-authentic...

[Upen_databricks]

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.  

[AB_MN]

That did the trick. Thank you!

[Upen_databricks]

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.