SQL Server Client Tools, Linux and Integrated Authentication in a Few Easy Steps

Mar 14, 2016 · 4 min read  ·
Share on:

Back in 2013, I wrote about "Using Windows Authentication to Connect to SQL Server from Linux". Considering how convoluted using Linux can get, doing so was surprisingly straightforward then and it's even easier now.

Want to see a webcast about this topic? Aaron Nelson and I recorded a session for the SQL PASS PowerShell Virtual Chapter meeting "SQL Server's sqlcmd on Linux using Windows Authentication" on March 22, 2016.

Scope

This post is intended to show a simplified way to login to SQL Server from your Linux machine using Windows credentials. It covers

  1. Installing SUSE Linux
  2. Updating the server's hostname
  3. Joining the Active Directory domain
  4. Installing the prerequisites for SQL Client Tools
  5. Downloading and unzipping the SQL Client Tools
  6. Installing the ODBC Driver that contains sqlcmd and bcp
  7. Logging in as a Windows user and executing sqlcmd
  8. Partying

"Hey, installing Linux can't be counted as one step!" True, however, this can all be done in 5 commands if your server is already setup.

zypper install glibc libuuid1 krb5 openssl unixODBC wget https://download.microsoft.com/download/7/3/1/7318E2AD-E7C5-4301-AEFA-A15EDEDACFB4/msodbcsql-13.0.0.0.tar.gz tar -xvzf msodbcsql-13.0.0.0.tar.gz sudo ./msodbcsql-13.0.0.0/install.sh install --accept-license sqlcmd -S sqlserver2014a.base.local -Q "SELECT name FROM sys.databases"

If it's not, however, this post will walk you through the whole process. Note that this demo uses SUSE Linux and its amazingly simplified setup tool, YaST.

If you'd prefer using RedHat or seeing what goes on behind the scenes, check out this awesome article, "Execute queries on a Microsoft SQL server from the Linux CLI with ODBC and Kerberos authentication" by Jensd.

Step 1: Install OpenSUSE with most of the defaults

Download OpenSUSE Leap 42.1 NET install and then install, keeping all of the defaults (Next, Next, Next, Next, really.) Well, except two. Don't install a GUI and do enable SSH.

install-1

Step 2: Prepare to join AD by updating your hostname

Assuming your DHCP server hands out your domain's DNS servers, fire up yast by typing sudo yast. Then go to System -> Network Settings -> Hostname / DNS. Enter in your desired server name, and update the DNS stuff.

install-4

Tab to OK, and select.

Step 3: Join the Windows domain

In YaST, go to Network Services -> Windows Domain Membership. Enter your domain name and select all of the check boxes at the top. Tab to OK and enter, then enter your domain admin credentials when prompted.

isntall-smb

Once you've successfully joined the domain, manually restart all of the impacted services. Kidding, just go ahead and reboot by typing sudo reboot.

Step 4: Install the prerequisites for SQL Client Tools/the ODBC driver

This is easy enough, just use zypper to install some prereqs.

zypper install glibc libuuid1 krb5 openssl unixODBC

Note that the README for Microsoft ODBC Driver for SQL Server on Linux says you gotta recompile unixODBC with the proper character set, but for this proof of concept, we won't.

Step 5: Download and unzip the SQL Client Tools tarball from Microsoft

Download the Microsoft ODBC Driver 13 and 11 Previews for SQL Server - SUSE Linux by executing the following.

wget https://download.microsoft.com/download/7/3/1/7318E2AD-E7C5-4301-AEFA-A15EDEDACFB4/msodbcsql-13.0.0.0.tar.gz tar -xvzf msodbcsql-13.0.0.0.tar.gz

Want to use a different distro? Check out the System Requirements for Red Hat and Ubuntu.

Step 6: Install the ODBC Driver that contains sqlcmd and bcp

Download the Microsoft ODBC Driver 13 and 11 Previews for SQL Server - SUSE Linux by executing the following, and entering your password when prompted:

cd msodbcsql-13.0.0.0/ sudo ./install.sh install --accept-license

isntall-3

Step 7: Login as your Windows domain user and execute sqlcmd

This is where the magic happens.

sqlcmd -S sqlserver2014a.base.local -Q "SELECT name FROM sys.databases"

sqlcmd

If there's a failure, ensure your Windows account has access to the SQL Server by using SQL Server Management Studio to verify. If you're wondering how to connect to SUSE remotely, my preferred SSH client is PuTTY. You can see your server's IP address by typing sudo ifconfig from the command line.

Step 8: Party

Now you can pour yourself a nice cup of tea or some Belgian beer, and party knowing you've just accomplished so much in so little time.

4765486

Note, if you don't want to login to the Linux box as a Windows User, you can still use integrated authentication! Check out the aforementioned article, "Execute queries on a Microsoft SQL server from the Linux CLI with ODBC and Kerberos authentication", and do a Find for kinit.

Also, here are some other pretty awesome links on this subject: