Installing and Configuring the Microsoft SQL Server Driver for unixODBC on Red Hat Enterprise Linux 7.2

General Procedure Up Front

1.  Install unixODBC in RHEL 7.2

$ sudo dnf install unixODBC

2.  Download and install the Microsoft ODBC Driver 13 (Preview) for SQL Server on Linux

These instructions are actually good enough.

3.  Create and configure the /etc/odbc.ini (or ~/.odbc.ini for per-user connections) file(s).

Here’s where all the troublesome components of the process occur, so here’s the most important stuff I know:

At a minimum, the DSNs you define should include the following definitions (in template form below):

[MSSQLTest]
Driver = ODBC Driver 13 for SQL Server
Server = [protocol:]server[,port]
Database = database_name_here
#If using Kerberos authentication:
Trusted_Connection = Yes
ServerSPN = MSSQLSvc/server.fq.dn:server
#If using SSL encryption:
Encryption = Yes
#If using SSL and not importing the server certificate into your certificate store:
TrustServerCertificate = Yes

If you’re familiar with ODBC DSN stanzas, you’ll probably notice the lack of a Port value.  And that’s because Microsoft’s ODBC driver does not support that value, so don’t try to use it.

You might also notice that there are no credentials specified in the DSN stanza.  If you’re not using Kerberos authentication, you might further think “Well, I’m definitely going to do that in my users’ per-user files in ~/.odbc.ini so that they don’t have to keep entering credentials in their connection strings.”  You will further think “WHY THE F*&@ IS IT SAYING THIS WHEN I TRY TO CONNECT:”

$ isql -v My_Busted_DSN
[28000][unixODBC][Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Login failed for user ''.
[ISQL]ERROR: Could not SQLConnect

“I DEFINED THE F$(*&@! USER AND PASSWORD IN THE DSN,” you will perhaps angrily think.

And the reason for this behavior is that the Microsoft ODBC driver does not support User and Password values in .odbc.ini files for the sake of security.  You’ll note those values are absent from their table of permitted connection string values (which is actually an incomplete list, as it is lacking the very important ServerSPN value often required for Kerberos authentication, as we’ll see below), but they sure don’t make it very obvious.  And, of course, a properly secured user file is a fairly reasonable way to store passwords if you can’t use Kerberos (if the user can’t put the creds in an ~/.odbc.ini file, I guarantee you the user will put them in an equally-easily-compromised script if a regular job needs database access), but fine.  Whatever.  The point is, that’s why it’s happening.

So you have to include user and password values in your connection strings if that’s what you have to do.  Now, that being said, you might also run across the following problem:

$ isql -v My_StillBusted_DSN EXMPLDOM\\Example_Domain_User exampleDomainUserPassword
[28000][unixODBC][Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Login failed for user 'EXMPLDOM\Example_Domain_User'.
[ISQL]ERROR: Could not SQLConnect

(The double backslash is intentional; since the backslash is an escape character in bash, one must escape the backslash with a backslash to render a literal backslash in the domain\user string, as verified by the error output which properly renders the string)

“But..” you might sputter, “Those creds are legit.  I can even use kinit to prove it by authenticating with Kerberos against my DC.  I can even go to the damn SQL Server and log in with those same creds.  I copied and pasted the password, even.  WTF!?!??!”

Well, I just don’t even know yet.  I was going to work on that one further, but basically, my DBA showed me the logs on the SQL Server to which I was attempting to connect and it shows that the SQL Server system is receiving the credentials exactly as I type them, but it doesn’t recognize that they refer to a domain account.  Instead, it interprets them as a reference to a local SQL account which is actually named “EXMPLDOM\Example_Domain_User” and so it looks for one, fails to find it, and refuses the connection attempt.

When I hit this issue (on which I spent the bulk of my time since I didn’t have access to my DBAs or the SQL Server logs when I was trying to get this working), I opted for Kerberos, and I’m betting you will too given that you’re almost certainly running Active Directory if you’re attempting to log into a SQL Server instance with a domain account.  So that brings us to Kerberos, where you may well see the following:

$ isql -v My_Hopeless_DSN
[S1000][unixODBC][Microsoft][ODBC Driver 13 for SQL Server]SSPI Provider: Server not found in Kerberos database
[S1000][unixODBC][Microsoft][ODBC Driver 13 for SQL Server]Cannot generate SSPI context
[ISQL]ERROR: Could not SQLConnect

And if you don’t really understand Kerberos, that’s going to be a thinker for you.  If you see it, you’ve obviously already got yourself a Kerberos TGT, ’cause if you didn’t, you’d see this:

$ isql -v Why_God_Why
[S1000][unixODBC][Microsoft][ODBC Driver 13 for SQL Server]SSPI Provider: No Kerberos credentials available
[S1000][unixODBC][Microsoft][ODBC Driver 13 for SQL Server]Cannot generate SSPI context
[ISQL]ERROR: Could not SQLConnect

And if that’s what you see, then you need to get yourself a TGT, which means installing the krb5-workstation package (to test, at least, since you could simply use Kerberos authentication to connect to your RHEL system through SSH and, if those same creds offer the TGT you need, that should be all you need to do) and using `kinit username` to authenticate against your KDC as the appropriate user (make sure you’ve already configured your /etc/krb5.conf file appropriately for your environment so that your authentication attempt knows where the KDC is and whatnot).  Once you successfully do that, you can use `klist` to see a list of your TGTs.  Then, retry the connection and you may see the first Kerberos-related error described above.

If you see THAT error (Server not found in Kerberos database), it’s almost certainly because you failed to properly identify the ServerSPN value in your DSN.  Now, I can’t tell you what the SPN for your SQL Server database server will be with certainty, but by default (and unless your AD admins are really weird, that’s likely what it is), the string is as described in the template file above.  The only values you should have to replace are the server.fq.dn (with the FQDN of your SQL Server) and the “server” string after the colon with the short name of the server.

Once you properly specify this information, in my experience, Kerberos authentication works like a dream for domain accounts, and since I couldn’t figure out how to get domain account credentials to work without Kerberos (as explained above), that’s just dandy.

Some More Good Info You May Need

So, you see, that was about 5 hours of work that shouldn’t have had to happen.  If my SQL Server DBAs had been in while I was trying to get this to work, I would’ve probably had it done in an hour, but they were out and I was stubborn and that was stupid.

Anyway, allow me to answer some more general questions you may have about ODBC and the unixODBC package.  Documentation on the Interwebs is a bit opaque here.

What is the unixODBC package in Red Hat Enterprise Linux 7.2?

Basically, it’s a driver manager.  First, ODBC is a standard which governs how drivers written to provide access to database management systems should function so that developers of a large variety of software packages can rely on the methods defined by this standard when writing their software for use with a large variety of drivers (and therefore a large variety of data sources).

So, the unixODBC package you will download from RHN is not a driver, itself, but it allows you to manage the ODBC drivers you will install on your system to allow easy access to a variety of data sources.  You’ll use it to identify and catalogue installed drivers (in /etc/odbcinst.ini) and you’ll then create a list of data sources (DSNs) which will join databases with their appropriate drivers and authentication mechanisms used to access the databases (in /etc/odbc.ini or, per-user in /home/whoever/.odbc.ini).

It’s all very convenient and awesome when set up appropriately.

So that means the Microsoft ODBC Driver for SQL Server on Linux is…

The ODBC driver you need to allow native applications (like RODBC!) to connect to your Microsoft SQL Server instances!

Sweet, so how do I install it all in RHEL 7.2?

My God, do I ever know how to do that.  After 6 hours of working with the damn thing, I dare say I now know just about everything I could probably ever need to know about it.

First, you’ll notice that Microsoft provides a bunch of scary-sounding instructions for installing the unixODBC driver manager, itself.  You can see they’re instructing you to build and install from source a tarball of the unixODBC driver version 2.3.1 to which they specifically link.  The reason they are doing this is that, as they state,

“UnixODBC-2.3.2+ are not supported with this release of the Microsoft ODBC Driver 13 for SQL Server.”

Fortunately for us, RHEL 7.2 happens to have stuck with unixODBC 2.3.1 (the currently available package for RHEL 7.2 on RHN at the time of writing is unixODBC-2.3.1-11.el7).  And that’s great, because that means you don’t have to install some code from source that won’t ever see an update unless you monitor for it and manually compile it yourself.  And that would be really, really annoying in an environment with a lot of systems and soforth.

So you can simply execute `dnf install unixODBC` and you’ve got the driver manager you need.

Groovy

Indeed.  That’s about all for today.  Yeesh.

Advertisements
This entry was posted in Information Technology and tagged , , . Bookmark the permalink.

6 Responses to Installing and Configuring the Microsoft SQL Server Driver for unixODBC on Red Hat Enterprise Linux 7.2

  1. Ben says:

    Great, but what if I’m trying to connect to DB programmatically, particularlyC++.
    I have just downloaded and installed ODBC 11 driver for Linux CentOS.
    I have added the section defining the connection into odbc.ini and checked with isql.
    Now I am trying to connect to SQL Server using it’s IPv4 address.
    But SQLConnectW and SQLDriverConnectW fail both.
    For SQLDriverConnectW I use the following Con Str:
    DSN=;UID=:PWD=;
    With same params isql works just fine (isql -v
    What’s wrong?
    What should I write to use SQLConnectW and SQLDriverConnectW ?
    Can it work wit SQL security at all?

  2. Alexei says:

    Thanks for the writeup on this. I ran into the same problem (using RODBC as well). The solution I used was to create a service account on SQL Server and explicitly define the username and password when initializing the connection.

    Not sure what rights you need to be able to read the errorlog, but you can run
    EXEC sp_readerrorlog 0, 1, ‘Login failed’
    on SQL Server to see failed logins. It’s not immediately obvious, but if you’re connecting to the server normally with an active directory account, the active directory group would be the one with the rights, not the individual user. So while it’s stupid that the driver doesn’t understand Active Directory, it kind of makes sense.

    • Yeah, that’s what we did, too; but one would suspect Microsoft’s own ODBC driver would accept AD credentials… I’d wager we’re just not doing it right, but it’s hard to say given the incomplete documentation from Microsoft.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s