Skip to main content
MvZ
MvZ
Silver ⭐️⭐️
March 8, 2024
Solved

Connecting SQL Server 2008 R2

Apache NiFi 1.25.0

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

MvZ_0-1709872233545.jpeg

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.

    SAMSAL
    SAMSALAnswer
    Platinum ⭐️⭐️⭐️
    March 15, 2024

    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-ver16

    @MattWho, do you know anything about this?

    View in thread

    6 replies

    SAMSAL
    SAMSAL
    Platinum ⭐️⭐️⭐️
    March 8, 2024

    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.

    Translate
      MvZ
      MvZAuthor
      Silver ⭐️⭐️
      March 8, 2024

      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.

      Translate
      SAMSAL
      SAMSAL
      Platinum ⭐️⭐️⭐️
      March 8, 2024

      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.

      Translate
        MvZ
        MvZAuthor
        Silver ⭐️⭐️
        March 15, 2024

        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

        MvZ_0-1710474843676.png

         

        Translate
        SAMSAL
        SAMSALAnswer
        Platinum ⭐️⭐️⭐️
        March 15, 2024

        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-ver16

        @MattWho, do you know anything about this?

        Translate
        MvZ
        MvZAuthor
        Silver ⭐️⭐️
        March 18, 2024

        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. 😊

        Translate
        Terms & ConditionsAccessibility statement