Connect To SQL Server With Windows Authentication From A Linux Machine Through JDBC

Question

I want to be able to connect to a SQL Server using jdbc and windows authentication. I saw some answers on the internet saying i should add the following property to the connection string:

integratedSecurity=true;

And also add

sqljdbc_auth.dll

To the java path.

But this, as far as i understand applies only when i'm connecting from a Windows machine. When i try this on a Linux machine i get:

java.sql.SQLException: This driver is not configured for integrated authentication

My question is how do I do it from a Linux machine.

Thanks

"dll" is a hint that this is probably a windows library. On Linux you'd probably have a ".so" or something other than "dll". — Fildor, Jun 15 '16 at 13:02
@Fildor i know, this is why i asked how do i do it from linux. — zuckermanori, Jun 15 '16 at 13:13
@mjn i read this article but it applies to Windows and not Linux as i asked — zuckermanori, Jun 15 '16 at 13:13
I was able to get it to work using jTDS, as explained in my answer [here](http://stackoverflow.com/a/26465897/2144390). — Gord Thompson, Jun 15 '16 at 13:35
Thank you @GordThompson my problem is that I can't use jTDS as I also use ssl and the jTDS has a bug that prevents using ssl. — zuckermanori, Jun 15 '16 at 14:12
Have you tried `authenticationScheme=JavaKerberos` of the offical driver? https://stackoverflow.com/a/50561067/1266906 — TheConstructor, May 28 '18 at 10:16
@TheConstructor this solution applies only to connections from Windows machines as it requires a .dll file. I need to connect from a linux machine. — zuckermanori, May 28 '18 at 19:17
@zuckermanori I think that the .dll is only needed for the other authenticationScheme, but I am currently lacking a testing opportunity. — TheConstructor, May 28 '18 at 19:19

zuckermanori · Accepted Answer · 2018-03-04T07:22:27.280

44

Well, eventually I answer my own question: This is not possible to use Windows authentication from a linux machine using the Microsoft JDBC driver. This is possible using the jTDS JDBC driver using the following connection string:

jdbc:jtds:sqlserver://host:port;databaseName=dbname;domain=domainName;useNTLMv2=true;

Thank you all for all the comments

edited Mar 04 '18 at 07:22

answered Jun 15 '16 at 15:04

zuckermanori

1,675
5
22
31

2

I am trying to implement the same. I am able to connect to SqlServer using jtDs and Windows Authentication from Unix machine but the insert queries are failing. Requesting you to please take a look at [this](http://stackoverflow.com/questions/39658960/exception-while-using-jtds-for-sqlserver-connectivity) Stack Overflow question – gaurs Sep 27 '16 at 16:54
1

How are you proving user auth details in this connection string ? I need to embed everything in the connection url. – Pradatta Jun 26 '17 at 12:13
2

@Pradatta to provide the user and password in the connection string simply add them as key-value pairs at the end of the url. The template url for SQL Server is: jdbc:jtds:://[:][/][;=[;...]] and so your url would be jdbc:jtds:sqlserver://host:port/database;user=someuser;password=somepass – zuckermanori Jun 26 '17 at 20:50
1

@zuckermanori Thanks. I exactly did that by random guess, after posting this comment, And it worked. Thanks a lot. – Pradatta Jun 29 '17 at 13:52
2

Thanks for your answer. – Faisal Basra Jul 13 '17 at 08:20
1

I confirm this works in my case too. For those wondering what the value of their domain is in linux environment, you can obtain it from your login credentials. Example: `CONTOSO.IT\Julio-PC` or `Julio-PC@CONTOSO.IT`, `CONTOSO.IT` is the domain name, and user is `Julio-PC`. – Julius Delfino May 15 '18 at 15:49
1

Hi @zuckermanori - does it workd without ntlmauth.dll, do we need to place any specific file or any specific steps/config to connect from Linux machine. Thanks in advance – springcloudlearner Jan 16 '19 at 20:59
1

@bharatbhushan no need for ntlmauth.dll. even if you put it it won't help as this is a dll file and you're on linux. no need for any additional file other than the jtds jdbc driver. – zuckermanori Jan 17 '19 at 06:20
I am getting `Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.` Is there any thing should be done on sqlserver side. – Yashwanth Kambala Sep 17 '19 at 13:20
@YashwanthKambala with the above mentioned configuration? – zuckermanori Sep 17 '19 at 17:06
It does possible to use `Windows Authentication` on Linux machines provided you have to add your Linux machine under `Domain Controller`. I used this DB URL `jdbc:sqlserver://172.18.44.171:1433;integratedSecurity=true;authenticationScheme=javaKerberos;authentication=NotSpecified` more details are here https://stackoverflow.com/q/67190767/1665592 – Swapnil Kotwal Apr 21 '21 at 07:52

mjn · Answer 2 · 2016-06-15T13:29:10.457

7

TL;DR

It is not possible to use native Windows Authentication for JDBC connections to MSSQL from a JVM running on Linux.

This MSDN article explains the authentiation methods with JDBC on Linux, potential errors, and available options:

https://blogs.msdn.microsoft.com/psssql/2015/01/09/jdbc-this-driver-is-not-configured-for-integrated-authentication/

...in the JDBC 4.0 driver, you can use the authenticationScheme connection property to indicate how you want to use Kerberos to connect to SQL. There are two settings here.

NativeAuthentication (default) – This uses the sqljdbc_auth.dll and is specific to the Windows platform. This was the only option prior to the JDBC 4.0 driver.

JavaKerberos – Makes use of the Java API’s to invoke kerberos and does not rely on the Windows Platform. This is java specific and not bound to the underlying operating system, so this can be used on both Windows and Linux platforms.

...

The following document outlines how to use Kerberos with the JDBC Driver and walks through what is needed to get JavaKerberos working properly.

Using Kerberos Integrated Authentication to Connect to SQL Server http://msdn.microsoft.com/en-us/library/gg558122%28v=sql.110%29.aspx

edited Jun 15 '16 at 13:29

answered Jun 15 '16 at 13:12

mjn

36,362
28
176
378

1

I read this article as well. I still don't understand if i can use the JavaKerberos flag to authenticate with Windows authentication. – zuckermanori Jun 15 '16 at 13:17
1

@zuckermanori Kerberos authentication requires a Kerberos server. It is a different standard than native Windows authentication. So you need to check with your system / network administrator first if Kerberos is available for your environment. – mjn Jun 15 '16 at 13:24
1

I know it is different, i'm looking for a way to use Windows authentication, not kerberos. Thanks anyway. – zuckermanori Jun 15 '16 at 13:25
1

Kerberos can (and does) work with Active Directory via LDAP (usually). If you can get Kerberos configured and talking with your active directory service, then you should be able to authenticate through it to you MS SQL Server. I'm in the process of proving if this works (or not). – Sir Geek Dec 08 '16 at 18:58
@Mishter_Jingles - Never had the time to go into the kerberos solution (Adds a level of complexity that I don't have time to work through right now). I'll try it again at some point.. Just not now. – Sir Geek Jul 26 '17 at 14:28
To my knowledge "Windows Authentication" in SQL Server actually effectively means Kerberos authentication. Remember that an AD is one big fat KDC in Kerberos terminology. Active Directory is indeed a very good implementation of a KDC and Kerberos protocol is used natively throughout Windows whenever you request access to some resource, e.g. to a file share, a database, etc. However, most people do not realize that Kerberos is underpinning just about everything in Windows. Most people assume that it is some proprietary auth mechanism, but it really isn't. It is just Kerberos. – peterh Jun 10 '18 at 22:08
@RossBrigoli - NOPE. Great for Web Connections but not so good for DB. I'll have to try the solution below – Sir Geek Jun 19 '18 at 13:58
@Mishter_Jingles NOPE. Great for Web Connections but not so good for DB. I'll have to try the solution below – Sir Geek Jun 19 '18 at 13:58

score 7 · Answer 3 · answered Apr 08 '19 at 18:46

For those who are using DBeaver the way to connect to the SQL Server Database is:

In order to connect to the SQL Server from Linux Debian using DBeaver

1.- Select SQL Server jTDS driver

2.- Enter the connection information

3.- Go to Driver Properties tab and add the domain, user, password

Just as a note, in some post I found that they needed to change the property USENTLMV2 to TRUE but it worked for me either by putting the USERTLNMV2 in true or false.

A problem that I found was that when I was trying to connect to the database using my user and password the next error was thrown:

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

This error was thrown because of my user was about to expire. I tried with another AD user and it could connect.

Awesome! Worked for me! I did have to set USERTLNMV2 to true — nemaroller, Jul 08 '21 at 14:19

score 4 · Answer 4 · answered Nov 06 '18 at 23:17

I know this is kind of an older topic but in case Google sends people here:

There are two main JDBC drivers for SQL Server. One is from Microsoft and the other from jTDS. jTDS can, amazingly, connect using Windows auth (NTLM) from other platforms, including Linux, as described here: http://jtds.sourceforge.net/faq.html#windowsAuth. It can, of course, also use SQL-authenticated logins. SQL-authenticated logins are no harder to use from any OS than any other, so don't forget about those an option.

The version provided by Microsoft is the one from which @mjn provided a quote from the documentation. It is able to connect using Windows authentication by specifying integratedSecurity=true, authenticationScheme=javaKerberos, and authentication=NotSpecified.

It is tricky to get this working even if you don't go out of your way to find more confusion, so always keep in mind which driver you are using - and tell us in these posts so that you can get more specific help.

getting ` WARN AuthenticationJNI: Failed to load the sqljdbc_auth.dll`,
```Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication.``` — Yashwanth Kambala, Sep 17 '19 at 13:53

score 2 · Answer 5 · edited Dec 08 '20 at 18:43

2

This JDBC URL is validated to work with latest Microsoft SQL Server JDBC driver:

jdbc:sqlserver://[server]:[port];database=[db\;trustServerCertificate=true;integratedSecurity=true;user=[user without domain];password=[pw];authenticationScheme=NTLM;domain=[domain];authentication=NotSpecified

Example:

jdbc:sqlserver://mysql.myorg.com:1433;database=mydb;trustServerCertificate=true;integratedSecurity=true;user=myuser;password=mypwd;authenticationScheme=NTLM;domain=ad.myorg.com;authentication=NotSpecified

edited Dec 08 '20 at 18:43

Kirby

15,127
10
89
104

answered Dec 08 '20 at 17:53

Eyal Edelman

21
1

This will automatically trust the server certificate and is not recommended from security perspective. – zuckermanori Dec 09 '20 at 04:54

score 1 · Answer 6 · answered Jan 04 '21 at 03:01

I was able to connect to a SQL Server 2016 Data Mart and JDBC connection Microsoft JDBC Driver using Windows Authentication using the following script on a Ubuntu Linux Docker Image running on Windows 10.

# initializes spark session
from pyspark.sql import SparkSession
spark = SparkSession\
    .builder\
    .master('local[*]')\
    .appName('FDM')\
    .config("spark.driver.extraClassPath","pyspark_jars/*")\
    .config('spark.executor.memory', '4g')\
    .config('spark.driver.memory', '16g')\
    .config('spark.executor.cores', '4')\
    .getOrCreate()

jdbc_url = '''jdbc:sqlserver://SERVER;databaseName=DBNAME;trustServerCertificate=true;integratedSecurity=true;user=USERID;password=PASSWORD;authenticationScheme=NTLM;domain=US;authentication=NotSpecified'''


spark_df = spark.read\
    .format("jdbc")\
    .option("url", jdbc_url)\
    .option("driver","com.microsoft.sqlserver.jdbc.SQLServerDriver")\
    .option("query", 'select top(1000) * from SCHEMA.TABLE')\
    .option("fetchsize", 100000)\
    .load()

spark_df.write.csv('TEST.csv', mode = "overwrite", header=True)

Connect To SQL Server With Windows Authentication From A Linux Machine Through JDBC

6 Answers6

Linked