Connecting SQL Server 2008 R2

[#1]

Apache NiFi 1.25.0

I'm trying to connect SQL Server 2008 R2 using ConvertJSONToSQL processor but got this error :

Using this driver :

jar : mssql-jdbc-12.6.0.jre11.jar

class : com.microsoft.sqlserver.jdbc.SQLServerDriver

I have no problem connect SQL Server 2008 R2 using SSMS. Am I missing some configuration? I did using default https configuration on nifi.properties provided by Apache NiFi when installed.

[#2]

Hi @MvZ ,

What service pack you have installed for SQL Server 2008 ? It seems the support for TLS 1.2 has started from service pack 3 and above.

[#3]

hi @SAMSAL 

I just query using SSMS.

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

It is RTM, so I think they need to update their SQL Server 2008 R2 Service Pack to the latest.

[#4]

According to this  site its very very old one. They have to upgrade or you cant specify the "encrypt" & "trustServiceCertificate"  from your jdbc connection string.

[#5]

The other side team decide to give me an access into their SQL Server 2019 (Development Server)

And I got another issue when trying to connect into SQL Server 2019

[#6 · Accepted solution]

Hi @MvZ ,

Is there a reason why you are using the following config in your jdbc connectionstring:

"encrypt=true; trustServerCertificate=false;" 

Can you try it with the trustServerCertificate set to true instead to see if its going to work?

If you dont care about encryption and you are using Nifi and SQL server within the firewall you dont have to provide the above config, but if the SQL server and Nifi live in different domains then I can see why you would configure it as such. In my case I did not have to. The problem with the above setting is that you might need to provide additional info like trustStore and trustStorePassword to validate the SQL server certificate per the following article:

https://learn.microsoft.com/en-us/sql/connect/jdbc/connecting-with-ssl-encryption?view=sql-server-ve...

@MattWho, do you know anything about this?

[#7]

Sorry for my late post, Yes like you said @SAMSAL  I miss the parameter from connection string :

this is my previous connection string

jdbc:sqlserver://#{db_cmk_colo_host};port=#{db_cmk_colo_port};database=#{db_cmk_colo_name}

And this is after (its working now)

jdbc:sqlserver://#{db_cmk_colo_host};port=#{db_cmk_colo_port};database=#{db_cmk_colo_name};authentication=NotSpecified;encrypt=true;trustServerCertificate=true

 Thank you again for your help. 😊