Failure to connect from databricks to ADW error 18456

Published Oct 05 2020 08:24 AM 1,108 Views
Microsoft

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

 

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 Synapse\SQL Pool Azure portal -> Connection strings you will have the syntax per drive.

syntax_conn.png

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

 

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

 

Version history
Last update:
‎Oct 05 2020 08:23 AM
Updated by: