How to authenticate Azure SQL server with Access Token through SSH Tunnel with SqlServerDataSource JDBC?

Question

How to authenticate Azure SQL server with Access Token through SSH Tunnel with SqlServerDataSource JDBC?

Link 5

I have a JAVA cloud application that is using Microsoft JDBC driver (9.4 or 12.2) to make a database connection to Azure SQL Server through a SSH tunnel (Bastion Server/Jump Server). I know my SSH tunnel is working because when I authenticate with SQL username/password through the SSH server, I have no issue. My application is able to login and make query. The issue is when I switch over to Azure Active Directory access token authentication through the same SSH server, I kept getting the login failed at 127.0.0.1. Also, I know my access token is valid because I can authenticate fine without going through the SSH Tunnel. The issue arise when I try to authenticate with access token through the SSH tunnel.

User's image

I'm using the SqlServerDataSource class from MS JDBC driver. The only difference I see between username/password versus accessToken is these 3 setter methods:

SqlServerDataSource#setUser

SqlServerDataSource#setPassword
SqlServerDataSource$#setAccessToken

What am I missing here in the SqlServerDataSource configuration for Access Token beside setAccessToken? It failed the login when going through the SSH server 127.0.0.1. I tried setting the host certificate with SqlServerDataSource#setHostNameInCertificate but that didn't make a difference.

Anyone know if we can use Azure Active Directory access token through SSH tunnel? What am I missing in my setting?

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

3 answers

Your answer

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Answer 1

Sedat SALMAN 14,180 MVP

I want to an example document for Access token usage

https://towardsdatascience.com/how-to-use-azure-sql-access-token-authentication-from-azure-devops-pipelines-344fa7dafa49

as you can understand from the document to get an access token you need to authenticate your used from the active directory first.

I think your problem is related with your SSH tunnel

by using that tunnel you can connect toi SQL Server but not Azure AD

so you need to reconfigure your tunnel and routing at first to create connection to the Azure AD also

in such cases it is better to use VPN instead of SSH tunneling to reduce the complexity also

Link 5 Reputation points

2023-05-31T17:47:10.73+00:00

Hi,

I already have the valid access token. I'm simply pass that token to the SqlServerDataSource object which open up the connection through the SSH server at 127.0.0.1. My thought was would all request to the SSH server will just forward to my actual azure db host like mydbserver.database.windows.net?

I suspected access token authentication might not work through the SSH tunnel, but I'm not sure. All the example I see from Microsoft are outside SSH tunnel which I have no issue with.

https://learn.microsoft.com/en-us/sql/connect/jdbc/connecting-using-azure-active-directory-authentication?view=sql-server-ver16
اسماء امين عبده ابوزيد 0 Reputation points

2023-05-31T17:50:03.85+00:00

i like the subject
Link 5 Reputation points

2023-06-15T22:40:43.62+00:00

Anyone have any idea?
Sedat SALMAN 14,180 Reputation points MVP

2023-06-16T04:54:28.46+00:00
as i mentioned in my post if your SSH tunnel can access the database server and
the following link to get accesstoken

String stsurl = "https://login.microsoftonline.com/...

it should work. I have similar case and I works
but i arrange my routing table accordingle to my computer can access borh SQL server and

authentication site at the same time

please check can you access both from the same server at the same time first
Link 5 Reputation points

2023-06-16T20:32:26.99+00:00

I already have the valid access token, but the SqlServerDataSource keep failing when I open the connection in the SSH tunnel.
Sedat SALMAN 14,180 Reputation points MVP

2023-06-18T12:31:08.4566667+00:00

interesting problem let me try to recreate the problem in my test environment
Alfredo Revilla - Upwork Top Talent | IAM SWE SWA 27,521 Reputation points Moderator

2023-06-27T08:12:52.21+00:00

Hello @Link , I will help you with this one. But first I need a detailed description of your env. I understand you're having issues authenticating with Azure AD to Azure SQL trough a SSH tunnel. What OS is the SSH host? Windows? Linux? Is this an Azure VM? What's Bastion participation. Are Azure AD Identity endpoints reachable from within the host?
Link 5 Reputation points

2023-06-27T15:48:39.91+00:00

@Alfredo Revilla - Upwork Top Talent | IAM SWE SWA Thank for responding. Basically I have a cloud application using Microsoft JDBC driver. Our cloud application need to make a database connection to Azure SQL Server with access token authentication. Sometime customer don't allow us to make a direct connection to their DB so they give us access to a jump server that we need to make a connection to ssh tunnel.

My test env
Azure SQL Server Instance: example.database.windows.net
SSH Server: AWS EC2 Linux

I have no issue with basic SQL username/password going to through the SSH tunnel so I know the tunnel is good. I have no issue with access token outside of the SSH tunnel. I just don't know why when I pass the access token through the SSH tunnel, the SQL Server failed to authenticate.

We use the SqlServerDataSource object in JDBC driver and set all the necessary parameters. The only difference between username/password was to call setAccessToken. Also we make sure the access token still valid.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Link 5 Reputation points

2023-07-10T20:55:32.6133333+00:00

Hi @Alfred Revilla . Did you get the chance to look at this yet? Thanks!
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Alfredo Revilla - Upwork Top Talent | IAM SWE SWA 27,521 Reputation points Moderator

2023-07-18T18:20:49.77+00:00

Hello @Link , I sent you an email.
Alfredo Revilla - Upwork Top Talent | IAM SWE SWA 27,521 Reputation points Moderator

2023-08-14T06:57:49.4966667+00:00

Hello @link, to re-confirm this has been rscalated to the appropiate teams. I will come back with an update ASAP.

Answer 2

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Answer 3

اسماء امين عبده ابوزيد 0

Hi ,, all thanx for answering my question 🙋🏼‍♀️

Share via

How to authenticate Azure SQL server with Access Token through SSH Tunnel with SqlServerDataSource JDBC?

3 answers

Your answer