Skip to main content

Connecting to SQL Server with jTDS using Windows Authentication

UPDATE:  At one point the tip in this article to set the USENTLV2 flag to true was accurate, but it appears it is no longer necessary, at least as far as DBeaver's use of the driver is concerned.  If it solves an authentication problem, then great.  The expectation is that, an update in one of the packages that make this sort of connection possible has resolved the issue that at one time made this flag necessary.


Connecting to Microsoft SQL Server using jTDS with a username and password that's managed by the database manager is pretty straightforward.  It may become a little more problematic however, when trying to use Windows authentication when connecting from a Windows machine.

  1. Do not supply a username and password.
  2. Set the USENTLMV2 property to true.

NOTE:  Step 1 assumes a connection being made from a Windows machine.  If the connection is being made from something else, user credentials may actually be necessary.

If the connection is being set up using DBeaver, the USENTLMV2 property is set on the Advanced tab of the connection properties dialog.

If connecting is made using a connection string, the format is as follows.

jdbc:jtds:<server_type>://<server>[:<port>][/<database>][;<property>=<value>[;...]]

An example might be:

jdbc:jtds:sqlserver://dbserver.domain.tld:1433/ApplicationDB;USENTLMV2=true
Labels:

Comments

  1. UnknownJuly 16, 2015 at 9:50 AM

    I use DBeaver on Linux to connect to an MS SQL Server and your post almost solved my problem. On Linux you have to set DOMAIN, USER and PASSWORD in addition to USENTLMV2=true. Now it works :)

    Thanks!

    ReplyDelete
    Replies
    1. Scott OrsburnJuly 16, 2015 at 2:28 PM

      Excellent! I'm glad that you got it working. It's true that this tip takes a bias towards Windows environments.

      Delete
  2. RajeJanuary 19, 2017 at 6:08 AM

    Hi,
    I am trying to connect MS SQL from Unix machine through JAVA code but its not working as DB us authiencation is based on windows active directory login, please guide me how to connect it

    ReplyDelete
    Replies
    1. UnknownJune 2, 2017 at 10:27 AM

      are you able to connect now? if yes please suggest me how to connect

      Delete
  3. Scott OrsburnJanuary 19, 2017 at 10:13 AM

    I have not used this in a Unix and Java environment, so it's difficult for me to determine. I'd imagine however, that Windows Authentication won't work with this setup.

    Unless I miss my guess, there are APIs in Windows that allow authentication without explicitly passing credentials.

    In an earlier comment, Sebastian Stammler indicated that he had success by specifying the domain, user, and password. It seems likely that this is because Windows Authentication has no meaning on non-Windows systems.

    I hope you can get it working.

    ReplyDelete
  4. UnknownJune 2, 2017 at 10:26 AM

    hi,
    I am not able to connect from UNIX to Remote SQL server database.jdbc:jtds:sqlserver://testserver.domainname:1433/MYDBNAME;instance=test;USENTLMV2=true"

    please suggest me ... what i am doing wrong

    ReplyDelete
    Replies
    1. Scott OrsburnJune 2, 2017 at 11:01 AM

      The first comment indicated some success by setting the DOMAIN, USER, and PASSWORD properties in addition to the USENTLMV2 setting. However, this sort of defeats the point of Windows authentication since it's supposed to pull the credentials from the system itself.

      It's not surprising that it doesn't work on non-Windows systems, but I haven't done any research to see if jTDS is smart enough to pull Unix user credentials and pass them along to the database.

      I have used DBeaver with the jTDS driver on Ubuntu to connect to SQL Server, but it's like Sebastian said, you need to provide all three bits (domain, user, password, and the usentlmv2).

      Delete

Post a Comment

Popular posts from this blog

PHP built-in webserver and IPv6 addresses

Though it's difficult to confirm around the Web, it appears as though the IP address scheme the built-in PHP server uses depends on the host name that's provided.  If a fully-qualified domain name (FQDN) is used, such as server.domain.tld, then the built-in server will be listening for IPv6 based requests.  If the server is started with an IPv4 address identified as the host, then it will naturally listen for IPv4 based requests. The closest to anything official around the Web was a comment in the PHP docs that only seeded this conclusion, at best.  But here it is in any case. Built-in web server http://php.net/manual/en/features.commandline.webserver.php#120449 Ultimately, the following answer from Stack Overflow held the "universal" solution.  Thank you dew010. Running PHP 5.4 built-in web server outside localhost http://stackoverflow.com/a/8377378/2487485 Start your development server like: php -S 0.0.0.0:8989
Post a Comment
Read more

HP webOS interface kudos

UPDATED 06/07/2013 Pandora no-longer makes the webOS version of its player available. While quite obviously a piece rooted in opinion, a subtle nod is offered to Palm for the excellent interface work it did with webOS .  Since it’s initial release with Sprint in June 2009, Palm quickly slipped into relative oblivion.  With the recent purchase of the company by HP coupled with new product releases set for the summer, webOS may become relevant once again.  If so, then perhaps a whole new lot of users will come to appreciate the consideration that was taken with the webOS user interface (UI). An example is the popular webOS application for the even more popular Pandora Internet radio service.  And as it turns out, the company has a collection of screen captures for the various mobile platforms that have native applications for the service.  This happens to double as a quick way to compare the interface elements of other platforms by using the same applic...
Post a Comment
Read more

Allow Windows authentication using SQL Server driver with DBeaver

DBeaver will allow Microsoft Windows single sign on access when connecting to Microsoft SQL Server using the SQL Server driver (rather than jTDS ).  From the driver properties settings, set the integratedSecurity flag to true . Open the Connection configuration panel and choose the Driver properties section. Set the integratedSecurity flag to true . A subtle, but important step is to not provide username and password credentials to the connection.
7 comments
Read more