Support Questions

Find answers, ask questions, and share your expertise

MSSQL Connection using Windows Authentication

avatar
Explorer

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. 

2 REPLIES 2

avatar
Master Collaborator

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

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 

avatar
Super Guru

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?vie...

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