question MSSQL Connection using Windows Authentication in Support Questions

MSSQL Connection using Windows Authentication

Ytch — Wed, 03 Apr 2024 08:01:51 GMT

How can I establish connection with MSSQL using DBCPconnectionpool if I have windows authentication. I tried adding integratedSecurity = true in the connection URL but it didn't work.

Re: MSSQL Connection using Windows Authentication

ckumar — Thu, 04 Apr 2024 13:28:56 GMT

Since NiFi runs on Linux, you have to use Kerberos as a security Auth when connecting from Nifi to MSSQL:

Please refer

https://learn.microsoft.com/en-us/sql/connect/jdbc/using-kerberos-integrated-authentication-to-connect-to-sql-server?view=sql-server-ver16&source=recommendations

DBCPconnectionpool has to use the following

jdbc:sqlserver://URL:port;domain=<Domianame >;databasename=<DBNAME >;authenticationScheme=JavaKerberos;TrustServerCertificate=True;integratedSecurity=true

with KerberosPasswordUserService controller to set the Kerberos username Principal and password

Hope this helps.

Thank you

Re: MSSQL Connection using Windows Authentication

SAMSAL — Thu, 04 Apr 2024 17:31:17 GMT

Hi @Ytch ,

The way I was able to get to work is through the following instruction:

1- After you have download the jdbc drive from here:

https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15

you should be able to find the following dll "mssql-jdbc_auth-8.2.2.x64.dll" under the path:

..\sqljdbc_8.2\enu\auth\x64

2- Copy the dll and place under the JAVA Home path bin folder:

.. \Java\jdk-21\bin

3- Adjust the DB Connection URL in the DBCPconnectionpool controller service by adding and setting "integrated security" flag to true:

jdbc:sqlserver://{SQL SEVER NAME};databaseName={DB NAme};integratedSecurity=true;

4- Restart Nifi

If that helps please accept solution.

Thanks