Connecting Synapse to Experian

Mehul MOdha

So I am trying to connect Experian to Azure Synapse to get DQ result. So I have tried using the JDBC SQL Server Driver (SQL Server 6.0) and in addition to this changing the customer connection string to change the Select Method from cursor to direct. However when I test connection all I get as a debug on the UI is "Failure"

Any ideas on how best to connect to synapse?

Would be much Appreciated!

Henry Simms

I have connected to my Azure Synapse Analytics SQL Pool from Data Studio with the native SQL Server driver and was able to load tables. In fact I found that I didn't need to change the SelectMethod for the server-side cursor, so I didn't need to use a custom connection string, just:

Which translates to this connection string (fill in your own synapse workspace and SQL pool):

jdbc:experian:sqlserver://<synapse_workspace>.sql.azuresynapse.net;allowPortWithNamedInstance=true;DatabaseName=<synapse_sql_pool>;LoginTimeout=15;UseServerSideUpdatableCursors=true;SelectMethod=cursor

If you save the system and then add your credentials, you will find more detailed error messaging when you "Test Connection" from the Credentials page. For example, where I've used an incorrect username to attempt to connect to Synapse:

Henry Simms

If using the Microsoft SQL Server driver, you do need to update the SelectMethod=cursor setting to SelectMethod=direct, and if you don't the Data Studio log will show the following error when you attempt to load a table:

java.io.IOException: com.microsoft.sqlserver.jdbc.SQLServerException: Cursor support is not an implemented feature for SQL Server Parallel DataWarehousing TDS endpoint.

I believe the native driver is able to handle this and automatically fall back to not use a server-side cursor. You can grab the working connection string for the MS SQL Server JDBC driver from the Azure Portal:

And use it in Data Studio, if you deploy the free MS SQL Server JDBC driver: