Azure Synapse Connection

[DarinAfni]

Hello,

I'm wondering if anyone in the community has been able to successfully connect to an Azure Synapse Analytics datawarehouse?

We've always been a SQL Server shop and I always have to use some additional parameters in my SQL Server connections:

i.e. useNTLMv2=true;domain=MY_DOMAIN_NAME

That doesn't work with Synapse. I've tried to have the DBA team create a Talend specifc user that is local to the server and I can't even get that to connect.

I've read through the Synapse Documentation and I see they reference integratedSecurity=true as a possible parameter. However, I get an error that the microsoft jdbc driver isn't configured for integrated security.

I'm really at a loss here on how I'm supposed to connect Talend successfully to an Azure Synapse instance. Any help would be greatly appreciated.

I've used tAS400Connection and tVerticaConnection and tMSSQLConnection and I've never had this much trouble just trying to connect to the database.

[Anonymous]

Hello @Darin Coulter​ ,

Please go to the Azure SQL Databases/Connection strings/JDBC to get the additional parameters as the below in yellow line

, then setup it for the Additional parameters in your above snapshot in talend studio to re-try?

Best regards

Aiming

[DarinAfni]

Thanks achen. I have tried this a number of different ways after retrieving the jdbc connection string from the Azure portal. I've tried to use the Microsoft db version with those additional parameters. I've tried to use the open source jtds db version and I still cannot get a connection.

When I get try to use the JTDS jdbc option then I get an error that the driver isn't supported.

 

When I try the Microsoft JDBC option then I get an entirely different error that makes it seem like it doesn't recognize any driver to use to connect:

I've tested a few different things and I *think* I have a hunch as to what the problem is with this new server and new 8.0 version of open studio. I do not think the Microsoft jdbc driver is installed and if it is installed Talend isn't using it. I think this because I set up a basic SQL Server connection to a server that I know I can connect to. When I use the JTDS version for that SQL Server connection, I connect without a problem. When I switch it to the Microsoft driver then I get the same error I'm getting when I'm trying to connect to Azure Synapse.

I've also tried to just pull in the tAzureSynapseConnection component into a job and set the connection up there rather than through the Metadata -> Db Connections. That tAzureSynapseConnection component *only* offers the Microsoft driver as an option to set up a built-in connection. It doesn't offer the JTDS as an option as the tMSSQLConnection component does. So - I don't think I'm able to use JTDS with Azure?

If that's the case - does anyone know how I can ensure the Microsoft driver is installed and also that Talend knows where it's installed? Normally when there's a missing artifact in a job then I get the alert that I have to download the .jar file but when I built the connection using tAzureSynapseConnection component it didn't prompt for any missing maven. So - I'm not entirely sure but I do think this is a Microsoft jdbc driver problem.

[DarinAfni]

I made some progress. What I did was I went out and downloaded the latest Microsoft jdbc driver that I knew support Synapse from the Microsoft website Download - JDBC Driver for SQL Server | Microsoft Learn. That got me a version 12.2.0. Then I went to the Modules view and searched for mssql-jdbc and changed the Maven URI for all components that use the mssql-jdbc driver to use this new one.

 

That got me the ability to connect to synapse using the tAzureSynapseConnection component and I was able to extract some data.

 

However - I still am unable to use the Metadata -> Db Connections GUI to create a database connection to Synapse. It's like that GUI is still using the old mssql-jdbc.jar file instead of the updated one. I am using all the same connection properties that I used in the tAzureSynapseConnection component above but it's still not letting me connect and I think it's because of the 6.0.0 version of the mssql-jdbc.jar file. Anyone know how to tell the Db Connection GUI to use a different .jar?

 

[Anonymous]

How about to re-create a new Metadata connection of MSSQL to see if it use the latest mssql-jdbc driver?

thanks

[DarinAfni · Accepted solution]

What I ended up doing was downloading the latest microsoft mssql-jdbc..jar file. Then I re-named it and replaced every single instance of that mssql-jdbc.jar file contained within the Talend8.0 install directory.

Now all components are working and the Metadata -> Db Connections GUI also uses the proper .jar file that actually supports Azure Synapse Analytics database.

That may not be a supported way of doing it but the driver that ships with Talend 8.0 is not compatible with Azure Synapse and it doesn't prompt you to retrieve the proper one when using components that require the mssql-jdbc.jar.