Correct connection to Microsoft SQL Server databases requires installation of ODBC drivers. This article describes instructions to set up these drivers.
The gcc and make programs must be preinstalled:
On Debian-based distribution kits:
sudo apt-get install tar gcc make g++
On RedHat-based distribution kits:
sudo yum install tar gcc make
If Microsoft SQL Server ODBC Driver 1.0 for Linux was installed earlier, delete it and install Microsoft ODBC Driver 11 for SQL Server (the driver is available at https://www.microsoft.com/en-us/download/details.aspx?id=36437). This driver version requires the use of lunixODBC 2.3.0 or later.
Installing of driver manager is described in the Installing the Driver Manager article.
NOTE. It is not recommended to use the SQL_WCHART_CONVERT flag on building unixODBC.
After unpacking an archive with the driver and before installing, make changes to the build_dm.sh and install.sh files.
In the build_dm.sh file. Set the req_proc="unknown" value (only for Astra Linux and Debian).
In the install.sh file. Set the req_proc="unknown" value (only for Astra Linux and Debian).
If the driver is being installed to Debian-based distribution kit (Ubuntu, Astra Linux, and so on), replace the string in the install.sh file:
req_libs=( glibc e2fsprogs krb5-libs openssl )
with:
req_libs=( libc6 e2fsprogs libkrb5-3 openssl )
Then make changes to the "check_required_libs" function. This function must look as follows:
function check_required_libs
{
log "Checking that required libraries are installed"
for lib in ${req_libs[@]}
do
local present=$(dpkg-query -l "$lib" | grep "$lib" 2>/dev/null)
echo "$present" >> "$log_file"
if [ "$present" == "" ]; then
echo "The $lib library was not found installed in the dpkg database."
echo "See README for which libraries are required for the $driver_name."
return 1;
fi
done
return 0;
}
Replace -xvzf with -xvf in the build_dm.sh file.
Download the ODBC installer at: ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.0.tar.gz.
Execute the command:
bash build_dm.sh --download-url=file://unixODBC-2.3.0.tar.gz
After this a message appears:
>>Run the command 'cd /tmp/unixODBC<time marker>/unixODBC-2.3.0; make install' to install the driver manager.
Install the package:
cd /tmp/unixODBC<time marker>/unixODBC-2.3.0
sudo make install
cd -
If commands are executed via the sudo user, change the user to root with the following command to go to the directory:
sudo -i
Then go to the folder and execute the command:
cd /tmp/unixODBC<time marker>/unixODBC-2.3.0
make install
After this log out of the root user by pressing CTRL+D.
NOTE. The sudo make install command can be replaced with sudo checkinstall, if the checkinstall program is installed in OS.
Save changes after that. Check if the driver can be installed in the system by executing the command:
bash install.sh verify
The information about check results is displayed. If there are no problems during installation, the message looks as follows:
Starting install for Microsoft ODBC Driver 11 for SQL Server
Checking for 64 bit Linux compatible OS ..................................... OK
Checking required libs are installed ........................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 installed ............................. OK
unixODBC Driver Manager configuration correct .............................. OK*
Microsoft ODBC Driver 11 for SQL Server already installed ............ NOT FOUND
If errors occurred on checking, the error message is written into a log file, for example:
See /tmp/msodbcsql.30762.3652.5941/install.log for more information about installation failures.
All errors must be resolved before proceeding. After the errors are resolved, execute a command to install driver:
bash install.sh install --accept-license
To create a symbol link for the platform, execute the commands:
cd /opt/foresight/pp9.0-biserver/bin
sudo ln -s
/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0 libmsodbcsql-11.0.so
NOTE. Library location may differ from the one specified in the command.
Before getting started with the driver, make sure that there are no issues related to shared libraries. To do this, execute the command:
ldd /opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0 | grep not
NOTE. Library location may differ from the one specified in the command.
If all required libraries are present, continue with driver registration.
If after executing the command the list is formed, for example:
libcrypto.so.10 => not found
libssl.so.10 => not found
Execute the following commands:
cd /usr/lib/x86_64-linux-gnu
sudo apt-get install libssl-dev
sudo ln -s libcrypto.so.1.0.0 libcrypto.so.10
sudo ln -s libssl.so.1.0.0 libssl.so.10
In Ubuntu, symbol links differ:
cd /usr/lib/x86_64-linux-gnu
sudo apt-get install libssl-dev
sudo ln -s /lib/x86_64-linux-gnu/libcrypto.so.1.0.0 libcrypto.so.10
sudo ln -s /lib/x86_64-linux-gnu/libssl.so.1.0.0 libssl.so.10
After this check again all driver dependencies, and if there are no issues, continue with registration.
After ODBC execution is completed, the record about driver is added to the odbcinst.ini file. If required, the driver can be registered manually.
Execute the command to register the driver:
odbcinst -i -d -f mssql.driver.template
where the mssql.driver.template file contains parameters required for driver work. For example:
[ODBC Driver 11 for SQL Server]
Description = Microsoft ODBC Driver 11 for SQL Server
Driver = /opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
Threading = 1
IconvEncoding = UTF16LE
DSN = MSSQL_DSN
To check if the driver is installed correctly, execute the following command:
odbcinst -q -d -n "ODBC Driver 11 for SQL Server"
If the driver is installed correctly, all driver parameters with their values are displayed.
Before working with the driver, check connection settings. First, try using connection by means of telnet:
telnet 192.168.1.1 1433
In case of connection error, the message appears:
telnet: Unable to connect to remote host: Connection refused
To resolve errors at this stage, contact the system administrator.
In case of successful connection, the message appears:
Connected to 192.168.1.1.
Next, check server connection by means of data source parameters. To do this, execute the following command:
isql -v <DATA_SOURCE_NAME> <USER>@<HOST> <PASSWORD>
In case of successful connection to server, the message appears:
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
It is also available to execute queries to database.
In case of errors the message appears:
[ISQL]ERROR: Could not SQLConnect
To resolve errors at this stage, contact the DBMS administrator.
See also: