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:

[01000][unixODBC][Driver Manager] Can't open lib '/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2260.0' : file not found

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

suse:~ # ldd /usr/lib/libmsodbcsql-11.0.so.2260.0
        linux-vdso.so.1 (0x00007fff20978000)
        libcrypto.so.0.9.8 => not found
        libdl.so.2 => /lib64/libdl.so.2 (0x00007fcf8fb44000)
        librt.so.1 => /lib64/librt.so.1 (0x00007fcf8f93c000)
        libssl.so.0.9.8 => not found
        libuuid.so.1 => /usr/lib64/libuuid.so.1 (0x00007fcf8f736000)
        libodbcinst.so.1 => /usr/lib64/libodbcinst.so.1 (0x00007fcf8f525000)
        libkrb5.so.3 => /usr/lib64/libkrb5.so.3 (0x00007fcf8f251000)
        libgssapi_krb5.so.2 => /usr/lib64/libgssapi_krb5.so.2 (0x00007fcf8f011000)
        libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007fcf8ed0b000)
        libm.so.6 => /lib64/libm.so.6 (0x00007fcf8ea0d000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fcf8e7f6000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fcf8e5da000)
        libc.so.6 => /lib64/libc.so.6 (0x00007fcf8e22d000)
        /lib64/ld-linux-x86-64.so.2 (0x00007fcf9011f000)
        libltdl.so.7 => /usr/lib64/libltdl.so.7 (0x00007fcf8e022000)
        libk5crypto.so.3 => /usr/lib64/libk5crypto.so.3 (0x00007fcf8ddf9000)
        libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007fcf8dbf5000)
        libkrb5support.so.0 => /usr/lib64/libkrb5support.so.0 (0x00007fcf8d9ea000)
        libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007fcf8d7e6000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007fcf8d5cf000)
        libselinux.so.1 => /lib64/libselinux.so.1 (0x00007fcf8d3ae000) 

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

suse:~ # zypper in libopenssl0_9_8

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.

suse:~ # cat /etc/odbc.ini
[mydsn]
Driver = ODBC Driver 11 for SQL Server
Database = test
Server = sqlservera.base.local
Trusted_Connection = yes

And the other

suse:~ # cat /etc/odbcinst.ini
[ODBC Driver 11 for SQL Server]
Description=Microsoft ODBC Driver 11 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2260.0
Threading=1
UsageCount=1

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

login as: base\chrissy
Using keyboard-interactive authentication.
Password:
Have a lot of fun...
BASE\[email protected]:~> isql -v mydsn
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>

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:

BASE\[email protected]:~> isql -v mydsn
[S1000][unixODBC][Microsoft][ODBC Driver 11 for SQL Server]Cannot generate SSPI context
[S1000][unixODBC][Microsoft][ODBC Driver 11 for SQL Server]SSPI Provider: Server not found in Kerberos database
[ISQL]ERROR: Could not SQLConnect
BASE\[email protected]:~>

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.”

BASE\[email protected]:~> sqlcmd -D -S sqlserver -E
Sqlcmd: Error: Connection failure. ODBC Driver for SQL Server is not installed correctly. To correct this, run ODBC Driver for SQL Server Setup.

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

sqlcmd: 'M': Unknown Option. Enter '-?' for help.

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