Access SQL Server via Nifi using Windows Authentication

[#1]

I am looking to confirm methods for connecting to MS SQL Server without using SQL authentication. The two options I am aware of are Windows authentication and Kerberos authentication. It is also desired that the creditials of the logged in user or the service that Nifi's JVM is running under be supplied without a password needing to be supplied.

I am looking for someone who might have ran into this before as this is difficult to test. The difficulty lies in the number of components hat must be configured. Nifi, AD, MS SQL Server, Windows, Kerberos etc

Per the Microsoft documentation here https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url#Connectingintegrated

Windows authentication is made possible by adding the sqljdbc_auth.dll file to the host running Nifi. If this file is added to a directory outside the normal classpath of Nifi then the class path can be specified by updating the JVM property Djava.library.path The JDBC URL string for Windows authentication is jdbc:sqlserver://localhost:1433;databaseName=AdventureWorks;integratedSecurity=true; Where integratedSecurity=true; is the flag for using Windows authentication. In this case the credentials of the logged in user or service would automatically be provided without a prompt for a password. Which is highlighted here https://docs.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties under the details of the integratedSecurity flag

Can anyone confirm this works with Nifi? Are there any limitations other than the requirement for a Windows host?

Using Kerberos and the MSSQL JDBC Driver is documented here: https://docs.microsoft.com/en-us/sql/connect/jdbc/using-kerberos-integrated-authentication-to-connec...

What is not clear here is how the password for the Kerberos principal would be obtained as it is included in all the examples I have seen. e.g. jdbc:sqlserver://servername=server_name;integratedSecurity=true;authenticationScheme=JavaKerberos;userName=user@REALM;password=****

Can anyone confirm this works with Nifi? Is this functionality limited to a Windows host? It seems that this could also work from a xUnix host if I properly configure the Kerberos client on that machine.

Any input here is appreciated, thanks!

[#2]

@wsalazar

I ran into the same issue and I had to set up two more kerberos configuration files (krb5.ini and jaas.conf) and point Nifi to this. My HDF/Nifi is running on Linux host. Login credentials can be specified from controller's username and password value.

krb5.ini

[libdefaults] default_realm = YYYY.CORP.CONTOSO.COM dns_lookup_realm = false dns_lookup_kdc = true 
[domain_realm] .yyyy.corp.contoso.com = YYYY.CORP.CONTOSO.COM 
[realms] YYYY.CORP.CONTOSO.COM = { kdc = YYYY.CORP.CONTOSO.COM admin_server = YYYY.CORP.CONTOSO.COM master_kdc = YYYY.CORP.CONTOSO.COM default_domain= YYYY.CORP.CONTOSO.COM }

jaas.conf

SQLJDBCDriver {      
com.sun.security.auth.module.Krb5LoginModule required doNotPrompt=false
useTicketCache=false;   
};

Please see https://medium.com/@danielyahn/nifi-ms-sql-integrated-security-9efc184872db for more detail

[#3]

Hi Daniel — I’m currently going through the same setup but it doesn’t seem to be working for me. I’ve done everything you’ve mentioned apart from add the username and password into the username/password boxes but NiFi is complaining that it cannot find a user… If I add a user into the box it complains that it cannot find a password.

The reason I do not want to add a username and password is because my understanding was that by using Kerberos you could instead just tickets/keytabs to authenticate on the underlying OS… Am I wrong?

[#4]

@Louis Allen

Just to confirm: Is the server where SQL runs kerberized, and have you set up a relaton of trust?