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

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.

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        install-2

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.

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.

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:

isntall-3

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

This is where the magic happens.

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:

Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Posted in Active Directory, Linux, SQL Server

Leave a Reply

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

*