Add support for windows auth to sql server with jtds jdbc about databaseconnector

[chrisknoll]

@schuemie : there's one thing in DatabaseConnector that might need to be extended to allow additional paramaters to the JDBC url (I believe you construct the JDBC url behind the scenes):

In order to use non-native authentication, you need to pass additional properties, listed here:
https://docs.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties?view=sql-server-2017

I'm wondering if there's a way to provide an additional parameter to databaseConnector that will let you send in a list of urlArgs that can just be appended to the JDBC URL during connection...

[schuemie]

We just removed jtds because no one was using it, and it handles dates incorrectly. I'm sorry, it is not coming back.

[cukarthik]

We depend on the jtds driver to allow us to do windows authentication b/c we have WebAPI instances running on a Linux box and hits a SQL Server database. We have found that the sqljdbc_auth.dll file does not work for us.

[chrisknoll]

The sqljdbc_auth.dll is definitely a win32 library, so I woudln't expect it to work on linux, but This is what I found as far as authentication with Active Directory on linux:
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication?view=sql-server-linux-2017

Then, I found this article about setting up kerberos configuration to allow connections to a linux hosted sql server:
https://blogs.msdn.microsoft.com/psssql/2015/01/09/jdbc-this-driver-is-not-configured-for-integrated-authentication/

I think the information provided in both those articles should allow you to connect to sql server using JavaKerberos authentication.

-Chris

[chrisknoll]

In addition, I found an article about the latest JDBC drivers (v6.4) which seem to include new features for Azure AD Authentication on Linux, you may be able to use this new driver to get better results:
https://docs.microsoft.com/en-us/sql/connect/jdbc/system-requirements-for-the-jdbc-driver?view=sql-server-2017

[schuemie]

Just to provide some context for my perhaps brief rejection: The OHDSI developers are finding that the unlimited growth of supported platforms and drivers is causing serious headaches. For example, I remember spending quite some time on debugging issues related to using jtds instead of the Microsoft driver. From here on out I'll be the bad cop saying people should adhere to the standard, instead of expanding the standard.

[cukarthik]

I appreciate the need to limit things for support purposes. In that case, where should we raise this issue of not getting windows authentication to work from a Linux machine? I’m sure others have solved it but we weren’t able to get it to work without jtds.

…

Sent from my phone On Jun 11, 2018, at 3:11 AM, Martijn Schuemie <[email protected]<mailto:[email protected]>> wrote: Just to provide some context for my perhaps brief rejection: The OHDSI developers are finding that the unlimited growth of supported platforms and drivers is causing serious headaches. For example, I remember spending quite some time on debugging issues related to using jtds instead of the Microsoft driver. From here on out I'll be the bad cop saying people should adhere to the standard, instead of expanding the standard. — You are receiving this because you commented. Reply to this email directly, view it on GitHub<https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_OHDSI_DatabaseConnector_issues_58-23issuecomment-2D396144208&d=DwMFaQ&c=G2MiLlal7SXE3PeSnG8W6_JBU6FcdVjSsBSbw6gcR0U&r=TPTYwzTWSsANHhlchP4RdqWisSl-75uhGlqJwzRcISU&m=JyneNUHNfcRpFobgAfsp-sKaKj7zpb_HPA_gNwb4D28&s=CRmxAgU3IpbGo4Licu2UT6CgiHZg1CFcN9frhLfSI-4&e=>, or mute the thread<https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_notifications_unsubscribe-2Dauth_AMakufvX2zjGg-5FSlgmrMw5ayfxPJRXZoks5t7hgtgaJpZM4UeywL&d=DwMFaQ&c=G2MiLlal7SXE3PeSnG8W6_JBU6FcdVjSsBSbw6gcR0U&r=TPTYwzTWSsANHhlchP4RdqWisSl-75uhGlqJwzRcISU&m=JyneNUHNfcRpFobgAfsp-sKaKj7zpb_HPA_gNwb4D28&s=CFWFtBfOSs3dAXwZyIth_7P4I979l7uiMbXtJBgDjaY&e=>.

[schuemie]

I'm obviously not aware of the context here, but my first question would be to your DB admin. It seems that requiring Windows authentication in an environment with non-Windows machines is a bit odd. At the very least, creating one or a few login accounts that do not require Windows authentication but just regular username-password authentication should be trivial.

[chrisknoll]

I may be mistaken, but the context I understood was: they have a windows domain with Windows clients, but the WebAPI is hosted on a linux server, which needs to connect to mssql server (it's unclear if the mssql server is hosted on linux or not).

I believe the solution is to leverage JavaKerberos authentication (the default is NativeAuthentication wich will attempt to levergae Win32API to get credentials, which won't work on linux). But to get that all working, there's Kerberos setup, possibly the server needs to join the domain, you have to go through the steps of a SPN, etc. It's non-trivial to be sure, but I think if you follow the links I gave or find documentation about a specific linux setup, it should be possible to use the JDBC driver from MS to authenticate.

This would be very good information to know (since we're seeing more heterogeneous environments with Linux and Microsoft tech) so if you do plan on going through the process of getting your linux to talk to SqlServer over kerberos auth, please write everything down and share your experience.

[t-abdul-basser]

I have to agree with @kembree1 and advocate that we support jTDS driver in DatabaseConnector so that we can cover the important scenario that @cukarthik outlined above, namely, authenticating in a Linux environment against SQL Server (on Windows) using windows/native authentication. Think Docker container (Broadsea)!
@chrisknoll Setting up Kerberos in order to support this common scenario when a JDBC driver exits that supports it out of the box with the additional of a single JDBC URL parameter seems...onerous.
@schuemie DBAs In our department, DBA prefer to configure SQL Server DBs to support native authentication and are in fact required by data security policies to do so.
@schuemie If there is a date type handling issue then perhaps we can re-visit and try to workaround it?

[chrisknoll]

So, I could be misreading this, but from the JTDS faq:

domain
Specifies the Windows domain to authenticate in. If present and the user name and password are provided, jTDS uses Windows (NTLM) authentication instead of the usual SQL Server authentication (i.e. the user and password provided are the domain user and password). This allows non-Windows clients to log in to servers which are only configured to accept Windoes authentication.
If the domain parameter is present but no user name and password are provided, jTDS uses its native Single-Sign-On library and logs in with the logged Windows user's credentials (for this to work one would obviously need to be on Windows, logged into a domain, and also have the SSO library installed -- consult README.SSO in the distribution on how to do this).

So I wondered what README.SSO said, which I found here:
https://github.com/hramos/repository/blob/master/jtds-1-1/README.SSO

But I thought this was interesting:

Installation
============

    In order for Single Sign On to work, jTDS must be able to load the native
    SPPI library (ntlmauth.dll). Place this DLL anywhere in the system path
    (defined by the PATH system variable) and you're all set.


Usage
=====

    In order to set up the jTDS driver to use Windows Single Sign On, users
    will have to connect without providing a user name and password. This will
    only work on Windows.

So, I thought this was about a linux client connecting to a linux sql server, but if these are all Windows clients (which appears to be required for the JTDS driver), then what's the trouble adding auth_dll to the system path in Windows? However, I thought the WebAPI instance was on Linux, tho, so this is a linux client....I don't see how JTDS supports that use-case...does it? I might have missed something, I didn't dig too deeply....
I found this:
https://stackoverflow.com/questions/45667675/jtds-driver-for-sql-authentication-from-linux

They didn't get it to work...

[chrisknoll]

Last thing I can add on this:
https://confluence.atlassian.com/bitbucketserver/transitioning-from-jtds-to-microsoft-s-jdbc-driver-776640388.html

See section on 'Why Change Drivers?' Interesting part:

Since jTDS 1.3.1 does not provide a functioning JDBC4 implementation, the decision was made to replace jTDS with Microsoft's own SQL Server driver. Microsoft's driver is actively maintained, where jTDS hasn't been updated since 2014 (and prior to the small round of updates done in 2014 it hadn't been updated for multiple years).

[schuemie]

@chrisknoll : you can add properties to the connection string in two ways:

1. Use the extraSettings argument of createConnectionDetails or connect. The contents of this argument will simply be appended to the connection string constructed by DatabaseConnector (with a semicolon inbetween).

2. Use the connectionString argument of createConnectionDetails or connect to completely specify the connection string yourself.

See the logic here.

@t-abdul-basser : one bug that has already been reported in jtds in 2012 and hasn't been fixed since (proving @chrisknoll 's last point) is the inability of jtds to recognize dates, which in OHDSI is kind of a big thing.