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.
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:
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:
That is it!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.