Using Windows Authentication to Connect to SQL Server from Linux

One of the things I love most about SuSE is how well it integrates with Active Directory. Joining a domain is easy when using yast. First I ensured that the Linux server’s DNS server is pointed to my DC, then yast -> Network Services -> Windows Domain Membership.

Note that if “Windows Domain Membership” does not appear in yast, you will have to
install yast’s samba client module by executing zypper in yast2-samba-client
.

Once I successfully joined my domain, I downloaded Microsoft ODBC Driver 11 for SQL Server – SUSE and ran the installation as directed. Something I really love about this ODBC driver for Linux is that I found it it also comes with sqlcmd and bcp. SQL Server data import/export from Linux? Whaaat!

Now, when I initially attempted to run isql, I received the following error:

After confirming that the file existed, I ran ldd to check for required libraries that may be missing.

Looks like I don’t have the right version of libcrypto and libssl installed. Let’s fix that:

Now that we’ve got all the required libraries, we need to modify /etc/odbc.ini and /etc/odbcinst.ini. Here, I’ll create a DSN entry called mydsn and use it to connect to the test database on sqlservera.base.local.

And the other

So now I login using a Windows Domain account that has privleges to the SQL Server and the test database.

It works! I’m genuinely surprised that it was so easy. Now to attempt to connect to my AlwaysOn Availability Group. I changed the server from sqlservera.base.local to sqlserver.base.local and attempted to connect:

Well, damn. Maybe I’m missing something? I browse ODBC Driver on Linux Support for High Availability, Disaster Recovery, Welcome to the Microsoft ODBC Driver 11 for SQL Server on Linux and the fabulous guide Securing Access to SQL Server from Linux with Kerberos, and using the information I found, I first try taking Microsoft’s advice and connect sqlcmd using the -E option, which explicitly directs sqlcmd to “Use trusted connection, integrated authentication.”

Fail. Next, I try sqlcmd with the -M option to connect using the MultiSubnetFailover option.

I then try to add the MultiSubnetFailover to the odbc.ini options and nothing. Maybe it’s my Kerberos config? I enter a whole bunch of stuff using setspn, then confirm everything is fine using Microsoft Kerberos Configuration Manager for SQL Server and still nothing. I’m thinking that this may be a bug.

Update 3/8/2016

When I ran sqlcmd in the new version of the driver (v13), it worked swimmingly. Bask in the glory:

linuxsql

Also check out Execute queries on a Microsoft SQL server from the Linux CLI with ODBC and Kerberos Authentication for updated info, and how to get a ticket without logging in as a Windows user.

Posted in Active Directory, Linux, Security, SQL Server
2 comments on “Using Windows Authentication to Connect to SQL Server from Linux
  1. Jonathan Lewis says:

    Did you ever get a solution to this problem? I’m having the same issue and have taken the steps you described above.

  2. Jonathan Lewis says:

    I got it working. Interestingly, all I had to do was modify /etc/odbc.ini and change the Server entry from the IP address to the FQDN.

    The “Server not found in Kerberos database” error went away and everything is working great now :)

Leave a Reply

Your email address will not be published. Required fields are marked *

*