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:
[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\[email protected]:~> 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\[email protected]:~> 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\[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."
1BASE\[email protected]:~> 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.
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.