Click HERE to see how Saviynt Intelligence is transforming the industry. |
06/25/2024 05:25 AM - edited 06/25/2024 05:27 AM
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?
06/25/2024 06:32 AM
@prashantv45 it says login failed.. are the creds right??
Plus have you added the driver?
06/25/2024 06:52 AM
@prashantv45 DId you pass the credentials ?
06/25/2024 07:00 AM
@NM , Yes Credentials are correct as I am able to login to the DB using SSMS.
06/25/2024 07:02 AM
@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.
06/25/2024 08:33 PM
Did you allowed Saviynt EIC IP in Azure to access DB from Saviynt ?
06/25/2024 09:50 PM
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:
Incorrect Credentials:
Authentication Method:
Firewall Rules:
JDBC Driver Version:
Connection String:
SSL/TLS Configuration:
06/26/2024 12:59 AM
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.
07/01/2024 09:46 AM - edited 07/01/2024 09:47 AM
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:
07/01/2024 11:32 PM
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.
07/02/2024 01:21 AM - edited 07/02/2024 01:22 AM
@prashantv45 Please share the latest URL using in the connection and how the username &password is passing the connection.
07/02/2024 02:04 AM
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.
07/02/2024 02:39 AM
@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
07/02/2024 03:12 AM
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...
07/05/2024 03:22 AM
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
07/05/2024 06:51 AM
Did you tried uploading this driver from UI ?
07/07/2024 11:48 PM
Hi @rushikeshvartak
Yes, I downloaded the supported driver for Azure Synapse DB from Microsoft site and uploaded it but same error.
07/08/2024 08:26 PM
07/05/2024 03:41 AM
Thank you so much @Darshanjain .