I'm trying to connect to our Azure SQL DB from Databricks configured with JDBC as follows:
DBUser = 'test2'
DBPword = 'xxxxxx'
DBServer = 'hst37dg5zxxxxxxy-exnwgcizvwnurfcoqllpyaj3q4'
DBDatabase = 'newconfigdb-xxxxxxxxxxx8-a7ea-13f21e4ab25b'
jdbcUrl = f"jdbc:sqlserver://{DBServer}.database.fabric.microsoft.com:1433;database={DBDatabase};user={DBUser};password={DBPword};encrypt=true;trustServerCertificate=false;authentication=ActiveDirectoryPassword"
df.write.mode("overwrite") \
.format("jdbc") \
.option("url", jdbcUrl) \
.option("dbtable", table)\
.save()
I am getting the following error:
com.microsoft.sqlserver.jdbc.SQLServerException: Failed to authenticate the user test2 in Active Directory (Authentication=ActiveDirectoryPassword). AADSTS50034: The user account "EUII Hidden" does not exist in the 8cbfa73c-xxxxxxx8faef12fc6 directory. To sign into this application, the account must be added to the directory.
Can someone let me know what "EUII Hidden" means? Also, how to overcome this issue?
By way of an update, when I enter the DBUser = 'test@keyxxxx.co.uk' I get error 'Failed to authenticate the user test@keyxxxx.co.uk in Active Directory (Authentication=ActiveDirectoryPassword). AADSTS50055: The password is expired'
However, when I created the account in Azure I wasn't given the option that the user would need provide a new password at sign on, so not sure why I'm getting the password expiry error?
I think I have nearly fixed the issue.
I have changed the password, but when I now try to execute the code I get the error:
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '<'.
In Entra ID create a new App Registration, and generate a client secret for it. Store the client secret in a Databricks secret scope. Grant the service principal access to the database, and switch your authentication from ActiveDirectoryPassword (which is for user principals, and is normally blocked by your Entra ID access policies) to ActiveDirectoryServicePrincipal, and use the client secret as the password.