Unable to Integrate with Azure Synapse DB

[prashantv45]

Hello Team,

I am trying to connect to Azure Synapse DB. When I am connecting through SSMS, I am able to do it
But When I am trying to connect from Saviynt connector Its not allowing to connect.
I am getting Below Error

2024-06-21T13:40:16+02:00-ecm-domain.EcmConfigController-http-nio-8080-exec-2-p5fvm-ERROR-ErrorMessage res : [Target Error Message: [com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open server "****" requested by the login. The login failed. ClientConnectionId:e052dd36-f033-4dba-bb0b-a5584f32f82e| ]]

2024-06-21T13:40:17+02:00-ecm--null-p5fvm--com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open server "****" requested by the login. The login failed. ClientConnectionId:e052dd36-f033-4dba-bb0b-a5584f32f82e at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:254) at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:84) at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:2908) at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:2234) at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:41) at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:2220) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1326) at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827) at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012) at java.sql.DriverManager.getConnection(DriverManager.java:664) at java.sql.DriverManager.getConnection(DriverManager.java:208) at com.saviynt.provisoning.DBProvisioningService.getConnection(DBProvisioningService.groovy:198) at com.saviynt.ImportExternalDbService.connectToDB(ImportExternalDbService.groovy:659) at com.saviynt.ImportExternalDbService.testDBConnection(ImportExternalDbService.groovy:677) at com.saviynt.ecm.integration.ExternalConnectionCallService.testExternalConnection(ExternalConnectionCallService.groovy:1033) at com.saviynt.ecm.utility.domain.EcmConfigController$_closure21.doCall(EcmConfigController.groovy:776) at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53) at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:159) at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:62) at grails.plugin.springsecurity.web.SecurityRequestHolderFilter.doFilter(SecurityRequestHolderFilter.java:59) at com.mrhaki.grails.plugin.xframeoptions.web.XFrameOptionsFilter.doFilterInternal(XFrameOptionsFilter.java:69) at com.brandseye.cors.CorsFilter.doFilter(CorsFilter.java:82) at java.lang.Thread.run(Thread.java:750)

When I checked the Microsoft documentation, they have shared some Library dependencies based on the SQL driver version.

Below is the URL I am using
jdbc:sqlserver://servername.sql.azuresynapse.net:1433;database=dbname;encrypt=true;authentication=ActiveDirectoryPassword;trustServerCertificate=false;hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30;
Reference Microsoft article
https://techcommunity.microsoft.com/t5/azure-database-support-blog/lesson-learned-366-cannot-open-se...
Did anyone came across this issue?

[NM]

@prashantv45 it says login failed.. are the creds right??

Plus have you added the driver?

[SumathiSomala]

@prashantv45 DId you pass the credentials ?

[prashantv45]

@NM , Yes Credentials are correct as I am able to login to the DB using SSMS.

[prashantv45]

@SumathiSomala ,
I tried by both adding and removing the username and password in the URL but same error in both the case.

The Microsoft documentation says that If we have to use authentication=ActiverDirectoryPassword there a specific library should be present.

[rushikeshvartak]

Did you allowed Saviynt EIC IP in Azure to access DB from Saviynt ?

[rushikeshvartak]

The error message you're encountering suggests that the login attempt to the Azure Synapse database is failing when connecting through the Saviynt connector. There are several potential causes for this issue:

1. Incorrect Credentials: 

2. Authentication Method: 

3. Firewall Rules:

4. JDBC Driver Version:

5. Connection String: 

   jdbc:sqlserver://<servername>.sql.azuresynapse.net:1433;database=<dbname>;encrypt=true;authentication=ActiveDirectoryPassword;trustServerCertificate=false;hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30;

6. SSL/TLS Configuration: 

[prashantv45]

Hello @rushikeshvartak 
The credentials are correct as I am able to login in SSMS.
The Authentication method is also correct as I am using the same in SSMS.
Firewall Rules confirmed by customer that its not restricted
For JDBC Driver version I have raised a ticket to support team to see if we have required Dependent libraries.
Connection string is also correct as I tried all parameters from SSMS and it works fine.

[rushikeshvartak]

Did you allowed Saviynt IP in database to connect ? You need to whitelist saviynt IP in DB

To whitelist a client IP in Azure Synapse Analytics (formerly SQL Data Warehouse), you need to configure the firewall settings. This process allows you to define specific IP addresses or IP ranges that can access your Synapse workspace. Here’s a step-by-step guide to whitelist a client IP:

Step 1: Open the Azure Portal

1. Sign in to the Azure Portal.

Step 2: Navigate to your Synapse Workspace

1. In the left-hand menu, select "All services" and then search for "Azure Synapse Analytics".
2. Click on your Synapse workspace from the list.

Step 3: Configure Firewall Settings

1. In your Synapse workspace, click on "Networking" in the left-hand menu.
2. Under the "Firewalls and virtual networks" tab, you will see the firewall settings.

Step 4: Add a Client IP to the Whitelist

1. Click on "Add client IP" to automatically add your current IP address.
   • Alternatively, to add a different IP address or range, click on "Add IP rule".
2. Enter the desired IP address or IP range in the "Rule Name", "Start IP", and "End IP" fields.
3. Click on "Add" to save the IP rule.

Step 5: Save and Apply the Changes

1. After adding the IP addresses, click "Save" at the top to apply the changes.

Example: Adding an IP Range

• Rule Name: MyClientIPRange
• Start IP: 203.0.113.0
• End IP: 203.0.113.255

Additional Tips

• Ensure that the IP addresses you add do not overlap with any restricted ranges.
• Regularly review and update the IP whitelist to maintain security.
• If you have a dynamic IP, consider using a VPN with a static IP for more consistent access.

[prashantv45]

Hello @rushikeshvartak 
There is no such restriction currently from the client side. I am able to connect through my local machine without explicitly whitelisting my system's IP.
But still when I checked with the client they confirmed that there is no such Restriction.

[SumathiSomala]

@prashantv45  Please share the latest URL using in the connection and how the username &password  is passing the connection.

[prashantv45]

Hello @SumathiSomala 
I have used below URLs to connect but no luck with any one of these
jdbc:sqlserver://<server Name>.sql.azuresynapse.net:1433;database=<Database Name>;encrypt=true;authentication=ActiveDirectoryPassword;trustServerCertificate=false;userName=${USERNAME};password=${PASSWORD};hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30.

jdbc:sqlserver://<server Name>.sql.azuresynapse.net:1433;database=<Database Name>;encrypt=true;authenticationScheme=ActiveDirectoryPassword;trustServerCertificate=false;userName=${USERNAME};password=${PASSWORD};hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30

jdbc:sqlserver://<server Name>.sql.azuresynapse.net:1433;database=<Database Name>;encrypt=true;authentication=ActiveDirectoryPassword;trustServerCertificate=false;user=${USERNAME};password=${PASSWORD};hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30

And using below Driver for the same.
com.microsoft.sqlserver.jdbc.SQLServerDriver

I also tried to upload the latest version of SQL server driver but that too didn't worked.

[SumathiSomala]

@prashantv45 Currently i am using this URL to connect the database

jdbc:sqlserver://<server Name>.database.windows.net:1433;database=<db name>;userName=${USERNAME};password={PASSWORD};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30

Give a try with below samples

jdbc:sqlserver://<server Name>.sql.azuresynapse.net:1433;database=<Database Name>;encrypt=true;trustServerCertificate=false;userName=${USERNAME};password={PASSWORD};hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30

jdbc:sqlserver://<server Name>.sql.azuresynapse.net:1433;database=<Database Name>;encrypt=true;authenticationScheme=ActiveDirectoryPassword;trustServerCertificate=false;userName=${USERNAME};password={PASSWORD};hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30

jdbc:sqlserver://<server Name>.sql.azuresynapse.net:1433;database=<Database Name>;encrypt=true;authenticationScheme=JavaKerberos;trustServerCertificate=false;userName=${USERNAME};password={PASSWORD};hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30

Hope you are passing the username and password parameters in the connetion

[prashantv45]

Hello @SumathiSomala 
Thanks for the Input but none of the above URL worked.

Basically the username is having @domainname in it, and as per the microsoft community forum, if the username is having @ in it then by default it will try to connect using SQL authentication.
But in my case, I have to use Entra ID Password authentication scheme.
So for that I have to add  "authentication=ActiveDirectoryPassword" Explicitely to use AAD authentication.
Now to use this authentication scheme there are certain dependencies on certain libraries based on the Driver version.

I have raised a support ticket to involve infra team in it to verify the driver and the dependent libraries.
https://techcommunity.microsoft.com/t5/azure-database-support-blog/lesson-learned-366-cannot-open-se...
https://learn.microsoft.com/en-us/sql/connect/jdbc/feature-dependencies-of-microsoft-jdbc-driver-for...

[Darshanjain]

Hi @prashantv45 

I believe its due to authentication=ActiveDirectoryPassword which is different and we need to validate all those drivers. As you have already raised the ticket - our team will check internally on these and will udpate you.

Thanks

Darshan

[rushikeshvartak]

Did you tried uploading this driver from UI ?

[prashantv45]

Hi @rushikeshvartak 
Yes, I downloaded the supported driver for Azure Synapse DB from Microsoft site and uploaded it but same error.

[rushikeshvartak]

• Check restart of application with all pods with help of support ticket

[prashantv45]

Thank you so much @Darshanjain .