Microsoft ODBC Driver for SQL Server on Linux – by the SAP HANA Academy
One of the topics that we are working on is SAP HANA smart data access (SDA) [SAP HANA Smart Data Access – YouTube].
Configuring SDA involves the following activities
- Install an ODBC driver on the SAP HANA server
- Create an ODBC data source (for remote data sources that require an ODBC Driver Manager)
- Create a remote data source (using SQL or SAP HANA studio)
- Create virtual tables and use them in calculation views, etc.
As of SPS 11, the following remote data sources are supported:
- IBM DB2 for Linux UNIX and Windows
- IBM Netezza
- Microsoft SQL Server 2012
- Oracle Database 12c
- Teradata Database
- Apache Hadoop (Simba Apache Hive ODBC)
- Apache Spark
In the SAP HANA Administration Guide, prerequisites and procedures are documented for each supported data source, but the information is intended as a simple guide and you will need ‘to consult the original driver documentation provided by the driver manufacturer for more detailed information‘.
In this series of blogs, I will provide more detailed information about how perform activity 1. and 2,; that is, installing and configuring ODBC on the SAP HANA server.
The topic of this blog is the installation and configuration of the Microsoft ODBC driver for SQL Server on Linux.
In the video tutorial below, I will show you in less than 10 minutes how this can be done.
If you would like to have more detailed information, please read on.
Supported ODBC Driver Configurations
At the time of writing, there are two ODBC drivers for SQL Server available for the Linux (and Windows) platform: version 11 and 13 (Preview).
|Microsoft ODBC driver for SQL Server on Linux||SQL Server||OS (64-bit)||unixODBC||SAP HANA Smart Data Access support|
|Version 13 (Preview)||2016, 2014, 2012, 2008, 2005||RHEL 7, SLES 12||2.3.1||Not supported|
|Version 11||2014, 2012, 2008, 2005||RHEL 5, 6; SLES 11||2.3.0||SQL Server 2012|
For SAP HANA smart data access, the only supported configuration is Microsoft ODBC Driver 11 in combination with SQL Server 2012. Supported means that this combination has been validated by SAP development. It does not mean that the other combinations do not work; they probably work just fine. However, if you run into trouble, you will be informed to switch to a supported configuration.
Information about supported configurations is normally provided in the SAP Product Availability Matrix on the SAP Support Portal, however, so far only ASE and IQ are listed. For the full list of supported remote data sources, see SAP Note 1868209 – SAP HANA Smart Data Access: Central Note.
On the Windows platform, the ODBC driver manager is bundled together with the operating system but on UNIX and Linux this is not the case so you will have to install one.
The unixODBC project is open source. Both SUSE Linux Enterprise Server (SLES) and Red HatEnterprise Linux (RHEL) provide a supported version of unixODBC bundled with the operating system (RPM package). However, Microsoft does not support these bundled unixODBC packages for the Microsoft ODBC Driver Version 11 so you will need to compile release 2.3.0 from the source code. This is described below.
|unixODBC||Release Date||OS (64-bit)||Microsoft ODBC Driver|
|2.3.1||11.2001||RHEL 7, SLES 12||Version 13 (Preview)|
First, you will need to validate that certain OS packages are installed and if not, install them (System Requirements).
This concerns packages like the GNU C Library (glibc), GNU Standard C++ library (libstdc++), the GNU Compiler Collection (GCC) to name a few, without which you will not get very far compiling software. Also, as the Microsoft ODBC Driver supports integrated security, Kerberos and OpenSSL libraries are required.
Installing the Driver Manager
Next, you will need to download and build the source for the unixODBC driver manager (Installing the Driver Manager).
- Connect as root
- Download and extract the Microsoft driver
- Run the script build_dm.sh to download, extract, build, and install the unixODBC Driver Manager
The build script performs the installation with the following configuration:
# export CPPFLAGS=”-DSIZEOF_LONG_INT=8″
# ./configure –prefix=/usr –libdir=/usr/lib64 –sysconfdir=/etc –enable-gui=no –enable-drivers=no –enable-iconv –with-iconv-char-enc=UTF8 –with-iconv-ucode-enc=UTF16LE”
# make install
Note the PREFIX, LIBDIR and SYSCONFDIR directives. This will put the unixODBC driver manager executables (odbcinst, isql), the shared object driver files, and the system configuration files (odbcinst.ini and odbc.ini for system data sources) all in standard locations. With this configuration, there is no need to set the environment variables PATH, LD_LIBRARY_PATH and ODBCINSTINI for the login shell.
Installing the Microsoft ODBC Driver
Next, we can install the ODBC driver [Installing the Microsoft ODBC Driver 11 for SQL Server on Linux].
Take a look again at the output of the build_dm.sh (print screen above). Note the passage:
PLEASE NOTE THAT THIS WILL POTENTIALLY INSTALL THE NEW DRIVER MANAGER OVER ANY
EXISTING UNIXODBC DRIVER MANAGER. IF YOU HAVE ANOTHER COPY OF UNIXODBC INSTALLED,
THIS MAY POTENTIALLY OVERWRITE THAT COPY.
For this reason, you might want to make a backup of the driver configuration file (odbcinst.ini) before you run the installation script.
- Make a backup of odbcinst.ini
- Run install.sh –install
The script will register the Microsoft driver with the unixODBC driver manager. You can verify this with the odbcinst utility:
odbcinst -q -d -n “ODBC Driver 11 for SQL Server”
Should the install have overwritten any any previous configuration, you either need to register the drivers with the driver manager again or, and this might be easier, restore the odbcinst.ini file and manually add the Microsoft driver.
For this, create a template file (for example, mssql.odbcinst.ini.template) with the following lines:
[ODBC Driver 11 for SQL Server]
Description=Microsoft ODBC Driver 11 for SQL Server
Then register the driver with the driver manager using the command:
odbcinst -i -d -f mssql.odbcinst.ini.template
Create the data source and test the connection
Finally, we can register a data source with the driver manager. For this, create a template file and save it as mssql.odbc.ini.template.
You can give the data source any name. Here MSSQLTest is used, but for production systems, using the database name might be more sensible (spaces are allowed for the data source name).
Driver = name of the driver in odbcinst.ini or the full path to driver file
Description = optional
Server = host (FQDN); protocol and port are optional, if omitted tcp and 1433 will be used.
Database = database name (defaults to Master)
Driver = ODBC Driver 11 for SQL Server
Description = SQL Server 2012 test instance
; Server = [protocol:]server[,port]
; Server = tcp:mo-9e919a5cc.mo.sap.corp,1433
Server = mo-9e919a5cc.mo.sap.corp
Database = AdventureWorks2012
Register the DSN with the driver manager as System DSN using the odbcinst utility:
odbcinst -i -s -l -f mssql.odbc.ini.template
odbcinst -q -s -l -n “MSSQLTest”
isql -v “MSSQLTest” <username> <password>
The -v [erbose] flag can useful in case the connection fails, as it will tell you, for example, that your password is incorrect. For more troubleshooting, see below.
System or User Data Source
It is up to you, of course, whether to register the data source as a system data source or a user data source. As the SAP HANA server typically is a dedicated database system, using only system data sources has two advantages:
- Single location of data source definitions
- Persistence for system updates
With the data sources defined in a single location, debugging connectivity issues is simplified, particularly when multiple drivers are used.
With the data sources defined outside of the SAP HANA installation directory, you avoid that your odbc.ini will be removed when you uninstall or update your system.
To register the DSN with the driver manager as User DSN using the odbcinst utility, connect with your user account and execute:
odbcinst -i -s -h -f mssql.odbc.ini.template
The difference is the -h (home) flag and not – l (local).
odbcinst -q -s -h -n “MSSQLTest”
Test connection (same as when connecting to a system data source):
isql -v “MSSQLTest” <username> <password>
Note that when no user data source is defined, odbcinst will return a SQLGetPrivateProfileString message.
Before you test your connection, it is always a good idea to validate the input.
For the driver, use the “ls” command to verify that the path to the driver is correct.
For the data source, use the “ping” command to verify that the server is up and use “telnet” to verify that the port can be reached (1433 for SQL Server is the default but other ports may have been configured; check with the database administrator).
If you misspell the data source name, the [unixODBC] [Driver Manager] will inform you that the
Data source name not found, and no default driver specified
If you make mistakes with the user name or password, the driver manager will not complain but the isql tool will forward the message of the database server.
If the database server cannot be reached, for example, because it is not running, or because the port is blocked, isql will also inform you by forwarding the message from the database server. Note that the message will depend on the database server used. The information we get back from SQL Server is much more user-friendly then DB2, for example.
If the driver manager cannot find the driver file, it will return a ‘file not found’ message. There could be a mistake in the path to driver file.
SAP HANA Academy Playlists (YouTube)
Microsoft Developer Network (MSDN)
Thank you for watching
You can view more free online videos and hands-on use cases to help you answer the What, How and Why questions about SAP HANA and the SAP HANA Cloud Platform on the SAP HANA Academy at youtube.com/saphanaacademy, follow us on Twitter @saphanaacademy., or connect with us on LinkedIn.