Connecting to SQL Server from macOS using Windows Authentication with a different domain/account

In the old days, the only place you’d find a Mac in a large organisation was in the design team – they’d be self-supported and incompatibility issues with the rest of the Windows org would be an unavoidable fact of life. Nowadays, MacBooks are becoming increasingly common in large orgs as an option for all staff. As Mac adoption increases, and with new developers starting their first job on Macs, some very niche issues – the sort that would affect less than 0.01% of the workforce, who were probably expert enough to deal with it – have become common problems affecting entire teams. I’ll describe a workaround for one such issue here.

Why?

A large organisation using Active Directory often uses a separate domain for each division of their organisation, and likely also separate domains for development and production resources & environments. As orgs get better at integrating and sharing resources between teams, we increasingly have scenarios where you can’t just log out & in with another ID – rather you need to simultaneously connect to services in different domains, e.g. when comparing data across departments.

Can’t you just use Windows?

Windows has a straightforward way to stay logged in as one user/domain while presenting different credentials depending on the service you’re connecting to: Credentials Manager. If you have access to a Windows machine or VM in your org, you can stop reading here!

What’s the equivalent for Mac users in Active Directory orgs? I’m not aware of anything as easy as Windows Credentials Manager on macOS – you could try going into the terminal and using kinit to get a Kerberos ticket authenticating as your other userID/domain, but depending on the app you’re using, this may still get ignored in favour of your primary credentials (this was my experience e.g. in Azure Data Studio).

But if you’re just seeking to connect to SQL Server, don’t fret – there’s no need to get exceptions to avoid Windows Authentication and use SQL logins! – rather you can connect in a few simple steps by using a different SQL client:

Connecting to SQL Server from macOS with Windows Authentication as a different user

  1. Install a SQL client that supports JDBC drivers, such as DBeaver or SQuirreL.
  2. Download the jTDS JDBC driver for SQL Server and put it in a convenient location (optional depending on your SQL client, e.g. DBeaver will download it for you).
  3. Add a new SQL Server connection in your SQL connection, but specify the use of the jTDS JDBC driver instead of the standard Microsoft one.
  4. Enter the username and password of the user you want to authenticate as, without the domain prefix (this comes later), and select ‘NTLM’ as your authentication type.
  5. In the driver properties, specify the following parameters:
    DOMAIN = <set this to the domain of the user you're authenticating as>
    USENTLMV2 = true
  6. You’re in!

Hope this helps you avoid some headache and get to your data quicker. If you find an easier solution for this issue, please let me know in the comments!

Photo by Caspar Camille Rubin on Unsplash

Leave a comment