Using Windows Authentication to Connect to SQL Server from Linux

Oct 22, 2013 · 4 min read ·

Share on:

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.

 1suse:~ # ldd /usr/lib/libmsodbcsql-11.0.so.2260.0
 2        linux-vdso.so.1 (0x00007fff20978000)
 3        libcrypto.so.0.9.8 => not found
 4        libdl.so.2 => /lib64/libdl.so.2 (0x00007fcf8fb44000)
 5        librt.so.1 => /lib64/librt.so.1 (0x00007fcf8f93c000)
 6        libssl.so.0.9.8 => not found
 7        libuuid.so.1 => /usr/lib64/libuuid.so.1 (0x00007fcf8f736000)
 8        libodbcinst.so.1 => /usr/lib64/libodbcinst.so.1 (0x00007fcf8f525000)
 9        libkrb5.so.3 => /usr/lib64/libkrb5.so.3 (0x00007fcf8f251000)
10        libgssapi_krb5.so.2 => /usr/lib64/libgssapi_krb5.so.2 (0x00007fcf8f011000)
11        libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007fcf8ed0b000)
12        libm.so.6 => /lib64/libm.so.6 (0x00007fcf8ea0d000)
13        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fcf8e7f6000)
14        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fcf8e5da000)
15        libc.so.6 => /lib64/libc.so.6 (0x00007fcf8e22d000)
16        /lib64/ld-linux-x86-64.so.2 (0x00007fcf9011f000)
17        libltdl.so.7 => /usr/lib64/libltdl.so.7 (0x00007fcf8e022000)
18        libk5crypto.so.3 => /usr/lib64/libk5crypto.so.3 (0x00007fcf8ddf9000)
19        libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007fcf8dbf5000)
20        libkrb5support.so.0 => /usr/lib64/libkrb5support.so.0 (0x00007fcf8d9ea000)
21        libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007fcf8d7e6000)
22        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007fcf8d5cf000)
23        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:

1suse:~ # 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.

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

And the other

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

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

 1login as: base\chrissy
 2Using keyboard-interactive authentication.
 3Password:
 4Have a lot of fun...
 5BASE\chrissy@suse:~> isql -v mydsn
 6+---------------------------------------+
 7| Connected! |
 8| |
 9| sql-statement |
10| help [tablename] |
11| quit |
12| |
13+---------------------------------------+
14SQL>

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:

1BASE\chrissy@suse:~> isql -v mydsn
2[S1000][unixODBC][Microsoft][ODBC Driver 11 for SQL Server]Cannot generate SSPI context
3[S1000][unixODBC][Microsoft][ODBC Driver 11 for SQL Server]SSPI Provider: Server not found in Kerberos database
4[ISQL]ERROR: Could not SQLConnect
5BASE\chrissy@suse:~>

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

1BASE\sqlserver@suse:~> sqlcmd -D -S sqlserver -E
2Sqlcmd: 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.

1sqlcmd: '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:

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.