Accessing Azure Synapse dedicated sql pool using jdbc

Navin Ladda 21

I have been trying to connect to Azure Synapse Analytics Dedicated SQL pool over JDBC.
I am using DBeaver as the query tool here.
Microsoft SSMS ver 18 works fine, but wanted to have a single tool to connect to various database platforms we have, hence wanted to use it with Dbeaver.

I have read several articles to setup Dbeaver to reach this stage.
I downloaded MS JDBC Driver for SQL version 10.2.
Set up a connection in Dbeaver using this driver. Added driver files for MSAL4J as I am using Active Directory with Password authentication.

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/connect-overview

I got the JDBC URL from within Azure Portal which looks like this (site specific details removed here, but had correct values for our Azure subscription)

jdbc:sqlserver://xxx.sql.azuresynapse.net:1433;database=xxx;user={your_username_here};password={your_password_here};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30;authentication=ActiveDirectoryPassword

I have even gone Synpase Workdspace, Networking and added a firewall rule for my client ip address.
As serverless sql pool was not doing anything when I clicked on it, created a small Dedicated SQL pool and testing connection with that.

Yet anytime I try the test option within Dbeaver to see if the connection is working, I get thrown this error :

Request was throttled according to instructions from STS. Retry in 95312 ms.

I have tried Googling what that means and nothing relevant shows up.

MartinJaffer-MSFT 26,036 Reputation points

2022-06-24T22:54:35.717+00:00

Hello @Navin Ladda and welcome to Microsoft Q&A.

If I understand correctly, you are trying to connect to your Azure Synapse Dedicated Pool, using DBeaver. Where you are getting trouble is in the authentication part?

The error message sounds like the authentication service (or at least the instance/mirror you connected to) was so busy, it could not service your request, and asked you to try again after a certain period of time passed.

When you tried through SSMS, was it on the same machine and approximate time? If they were nearby in time, this would rule out a service disruption, because a service disruption would affect both. This is assuming, both SSMS and dbeaver connected to the same IP address for authentication. There should be other locations also hosting the authentication service.
Navin Ladda 21 Reputation points

2022-06-24T23:17:36.843+00:00

Yes, I believe it is authentication.
Accidentally I discovered that when I am not connected to VPN and try to test the newly configured connection in Dbeaver, I get a TCP/IP error.

The TCP/IP connection to the host xxx.sql.azuresynapse.net, port 1433 has failed. Error: "connect timed out. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".

Moment I connect back to VPN, I get the throttle error 9 out 10 times. And when I dont get that error, I get this same TCP/IP.
I looked thru the firewall settings and don't quite understand if it is a port that needs to be opened, then where is that setting. Or is this port on my local windows 10 laptop ?
Throttle error does not seem to have anything to do with how busy it was. I tried searching for Azure Authentication Service something along that lines that is the name of the layer in Azure that handles incoming request, but nothing showed up.

Yes, if I use SSMS at same time, it connects fine.
MartinJaffer-MSFT 26,036 Reputation points

2022-06-28T15:22:03.863+00:00
@Navin Ladda

So I am trying to repro the issue. I had a few thought, but better I try for myself.

I downloaded dbeaver 22.1.0

Allowed it to create test db

Clicked create new connection

went into Synapse workspace > manage > SQL pools > selected a dedicated , paused pool, named "low". Copied the Workspace SQL Endpoint

Pasted the workspace SQL endpoint to the "Host" section in Dbeaver connection editor

entered the pool name ("low") in the Database / schema field in the Dbeaver connection editor

Selected Active Directory - Integrated authentication, because I want to test what I have so far, and forgot my password.

Clicked the "Driver Properties" tab. This showed missing drivers, so I allowed DBeaver to download drivers.

Testing connection brought up error "Cannot connect while the server is paused" . This was expected. Pool was paused. I didn't expect to get that far.

Unpaused the pool and tried again. Dbeaver then prompted me for password. I got that wrong.

Switched to Active Directory with password and tried again. Same thing.

Stopped to write this up

Tried again with each, and now I get different results!

Sometimes I am told I need 2 factor authentication

One time I got your error message

Sometimes I get java.net.SocketTimeoutException: Read timed out

I will now try different things. See if I can connect successfully or get your error message more often.
Navin Ladda 21 Reputation points

2022-06-28T15:42:05.093+00:00

I checked dbeaver.io page and looks like version 22 is the latest one. Not sure which one I have, but it shows my version as 7.0.3 with release from April 2020 and no new updated version.
So I will try the same version you have as looks like they now are including SQL Server driver as on Step 3 you would have to pick the database type. Mine did not have it, so I download the version 10.2 driver from MS site and added that into Dbeaver (you can add your own driver). Additionally added the Auth library as an artifact.
But looks like all you had to do was add the SQL Endpoint as host, database name and auth type. I never even got the server is paused message.

Accepted answer

MartinJaffer-MSFT 26,036 Reputation points

2022-06-28T15:37:33.613+00:00

I am trying out the other authentication Methods. Active Directory -MFA worked very well. It opened up a browser window for a moment, though.

Tryign again the Active Directory with password:
I intentionally gave the wrong password a few times, then after that I got your Request was throttled according to instructions from STS. Retry in 14954 ms. message. Trying again and again, I notice the number decreased, and didn't matter what password I gave.
Eventually I got the AADSTS50076: Due to a configuration change made by your administrator, or because you moved to a new location, you must use multi-factor authentication to access ' message again.

From this behavior, I believe it is a case of "You got the password wrong too many times in a row. We will stop letting you try to login, because this looks like brute-force password hack. You can try again later."
My Smartphone does something similar.

Does this make sense @Navin Ladda ?
Please sign in to rate this answer.

1 person found this answer helpful.
Navin Ladda 21 Reputation points

2022-06-28T17:02:05.437+00:00

I never got the window thrown to enter password at all.
An issue I am struggling with is to get same Dbeaver version installed. It did not want to before uninstalling my current version 7. I have few different connections, so I did not want to uninstall.
So when I choose the option to install not for all users, but just my user, it installed the new version in c:\users\username\appdata\local
Then when I tried the new version, it successfully imported all my previous version connections as I had exported them before in the default location c:\users\username.
But then it went downhill :)
My existing connections to Cloudera Hadoop (PaaS in Azure) stopped working. Started throwing some java errors. I then removed Dbeaver v22 and had to re-create these connections in older version. Everything then started working.
I then installed the new Dbeaver back and now in this one Cloudera Hadoop don't work. Same jar file errors and what not. And old version also the recently created connections all bombed.
So in summary, looks like I have to give up old version. I can if atleast all connections work.

Your trial gave me a hint, so in v22, I changed auth type to ActiveDirectoryInteractive as I don't see MFA. Lo and behold, it opened a browser tab and said authentication successfull and it then connected to sql pool !!

I will now uninstall both versions of Dbeaver and just reinstall v 22. Thank you for your trials and recreating the environment I had.

Navin Ladda 21 Reputation points

2022-06-28T19:12:47.7+00:00

Update:
Thank you @MartinJaffer-MSFT
I was able to connect using Dbeaver 22 and Active Directory - Password authentication.
I had to uninstall both versions of Dbeaver and did a fresh install of Community Edition version 22.
And that worked.
This new interface has a place to put the Userid and Password. It also has ability to get the SQL Server JDBC Driver and msal4j auth library.

MartinJaffer-MSFT 26,036 Reputation points

2022-06-28T21:57:51.863+00:00

Thank you for letting me know this resolved your issue.

So you went from version 7 -> 22 ? That's a HUGE jump. Yeah, version 7 might have been made before ... well before a lot of Azure stuff. I know the field of databases has some well established protocols. The libraries probably were not fully compatible with version 7.

I hope you enjoy the new version and it brings you much ease.

Navin Ladda 21 Reputation points

2022-06-28T22:55:23.65+00:00

Yes, interface looks a bit refreshed. They might have gone thru version rebranding as version 7 was latest when I installed it in April 2020. Maybe next year they jumped to using 21 for year 2021 and this year 22 for year 2022. Just like Samsung jumped from Galaxy S10 to S20 in year 2020 :)

Your thought process on getting Dbeaver installed to replicate the environment and error I was getting was key. And finally your theory on what that error meant helped me resolve this.
Sign in to comment

Accessing Azure Synapse dedicated sql pool using jdbc

0 additional answers