Windows Authentication for SQL Server using JBDC on a Mac

Question

Is it possible to connect to SQL Server using Windows authentication/integrated security from a Mac? I am using the type 4 JDBC driver provided by Microsoft. The front end (a form application) is coded in Java. Everything works perfectly on Windows but one person in the office uses a Mac.

Is this possible? FYI, I have never used Macs so I am very much the novice with them. I have searched all over the Internet but have not found a solution. Thank you in advance.

Smiley

Answer 1

This information is hard to come by in my experience. All of my searches turned up wrong (outdated) information since Microsoft changed the rules and added the authenticationScheme parameter. In the interest of helping the next person, here is an example of a connection string that works:

jdbc:jtds:sqlserver://123.123.123;instance=server1;databaseName=students;integratedSecurity=true;authenticationScheme=JavaKerberos

Also in driver properties set "Domain". Do not include the domain in any user name setting.

This was tested using Squirrel SQL (Java) with jtds on Mac OSX. Hopefully the previous sentence has the search terms someone might use who needs to know this information.

Leucocyte

Client: Grails/Groovy 2.0.4, jtds driver MacOSX (under eclipse). Server: MSSQL 2008, using windows authentication. – Credulity

It is worth saying that you need to download jTDS driver (jtds.sourceforge.net/index.html), it seems to be the only one with Kerberos integration. I know it is in the connection string but some people might miss it and fail to connect using other drivers. – Betrothal

Also needed the following in order for things to work completely: ;domain=NT_DOMAIN_NAME – Bowling

Where do you set "driver properties"? – Burberry

Tested this today using sqljdbc_6.4 driver with squirrel on Mac, worked successfully. I have just added the part of the jdbc url after databaseName, No other settings needed. – Stripling

Answer 2

Using Kerberos Integrated Authentication to Connect to SQL Server

Beginning in Microsoft JDBC Driver 4.0 for SQL Server, an application can use the authenticationScheme connection property to indicate that it wants to connect to a database using type 4 Kerberos integrated authentication.

---

The jTDS JDBC driver for SQL Server supports Windows authentication simply using the domain property as described in the 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 Windows 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).

Feculent

Answer 3

I use jTDS on a mac (10.9).

Using this driver you need to specify the username and password like always, the only difference is that you need to specify domain=WHATEVERTHENTDOMAIN in the connection string (or connection properties if you rather).

So a sample connection string is:

jdbc:jtds:sqlserver://db_server:1433/DB_NAME;domain=NT_DOMAIN_NAME

The jTDS driver then uses NTLM to login to the specified domain with the username and password.

Mccourt

Answer 4

This is an old post but may be relevant for some people. See this other SO post that describes how to connect to a SQL Server with Windows Authentication from a Linux machine through JDBC. This will work on mac as well.

Keldon

Answer 5

jTDS is inferior to Microsoft's JDBC driver (in particular, it cannot figure out the types of parameters in a prepared statement)

Yes, you can authenticate to MS SQL Server using Active Directory authentication, as Active Directory is just Kerberos + LDAP, which are open source and implemented on Mac

Kerberos config /etc/krb5.conf :

[libdefaults]
default_realm = YOUR_REALM.NET

[realms]
YOUR_REALM.NET = {
    kdc = host.your-domain.net
}

I needed to use the fully qualified domain name of the KDC, not just the domain name

JDBC Connection String:

jdbc:sqlserver://$host;database=$db;integratedSecurity=true;authenticationScheme=JavaKerberos

If $host does not have an SPN of MSSQLSrv/$host, add serverSp=$SPN to the JDBC connection string

Drennen

Answer 6

It is not correct to say that one driver can determine the data types and another driver can't. Any driver has to look at the implied type based on the arguments passed. Both jTDS and Microsoft's driver do this. This is a limitation of the protocol - the database cannot tell the driver which type is correct, because in many queries it can't know what you intend.

In each version, jTDS and Microsoft's driver each have different issues and different advantages. The "best" choice depends on exactly which version of each you look at, and exactly what your needs are. I've had to switch back and forth as different versions come out - Microsoft breaking in a certain way, then later adding something I wanted.

Leucocyte

Answer 7

The following connection string worked for me

jdbc:jtds:sqlserver://server_name:port_name;useLOBs=false;databaseName=db_name;useNTLMv2=true;domain=domain_name;

I'm using jTDS 1.3.2 and SQuirreL SQL Client.

Uptake