Failure to connect from databricks to ADW error 18456

This article is contributed. See the original author and article here.

This was a very interesting case like a puzzle. So I was working with my colleague Valentin Ursu from databricks team and we had this situation where the customer was able to connect to data bricks with JDBC but not with Synapse connector using like https://docs.databricks.com/data/data-sources/azure/synapse-analytics.html

 

The failure on ADW was the error 18456. State 8 – https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error?view=sql-server-ver15

 

State 8 means password incorrect.

 

Databricks failed with an authentication error.

 

If we tried to connect to ADW with the same user and password using SSMS or ADF it worked. So how the password would be incorrect if I copy and paste in another platform it works.

 

We checked the firewall, syntax, connection string and it seems everything was correct. 

So from both technologies ADW and databricks it seems there was not failure.

Note: If you click on SynapseSQL Pool Azure portal -> Connection strings you will have the syntax per drive.

So my colleague point to this and that was the problem.

 

The connection was been done by URL to ADW and the complexity of the password while encoding was leading to failure:

 

 

val sqlDwUrlSmall = "jdbc:sqlserver://" + dwServer + ":" + dwJdbcPort + ";database=" + dwDatabase

…
We asked them to try the follow: 


.option("url", sqlDwUrlSmall)

.option("user", dwUser)

.option("password", dwPass)

…

So in other words a complex password with:.;:=?~^

it would become %3B%3A%3D%3F~%5E after encoding.

 

So basically when we are using URL the complexity of the password can lead to failures to connect.

 

Checkout about password complexity: https://docs.microsoft.com/en-us/sql/relational-databases/security/password-policy?view=azure-sqldw-latest

 

More information here:

https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?view=sql-server-ver15

 

Thanks, Valentin!

 

That is it!

Liliam UK

Engineer

 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.