How to install Microsoft ODBC driver

Issue

• How to install Microsoft ODBC driver?
• How-to establish a connection with MSSQL?

Environment

• CloudLinux 

Solution

There are two ways to establish a connection with MSSQL:

DBLIB

1. Configure alt-freeTDS:

   Add to /opt/alt/freetds11/etc/freetds.conf the following details about MSSQL:

   [my_mssql]
   host = %IP%
   port = %PORT%

2. Enable module for user pdo_dblib:

   # selectorctl -u %USERNAME% -v %PHP_VERSION% -e pdo_dblib

3. Use the following code to establish connection with MSSQL:

   $dbh = new PDO("dblib:host=my_mssql;dbname=%DB_NAME%", "%DB_USER%", P.S: "%DB_PASS%");

Alternative way (without updating /opt/alt/freetds11/etc/freetds.conf) is to use the full connection string

$dbh = new PDO("dblib:host=%IP%;port=%PORT%;dbname=%DB_NAME%", "%DB_USER%", "%DB_PASS%");

Configure ODBC

1. In /etc/odbcinst.ini add FreeTDS driver details:

   [FreeTDS]
   Description = MS SQL database access with Free TDS  
   Driver      = /opt/alt/freetds/usr/lib64/libtdsodbc.so  
   Setup       = /usr/lib64/libtdsS.so  
   UsageCount  = 1

   Note: For i386/i686 use "lib" instead of "lib64" in paths
   
   

2. In /etc/odbc.ini add details about MSSQL server:

   [mssql_odbc]
   Description    = MSSQL Server  
   Driver         = FreeTDS  //should have the same name as driver in /etc/odbcinst.ini  
   Database       = %DB_NAME%  
   Server         = %IP%  
   Port           = %PORT%

   By adding a new section like this to your odbc.ini file, you can configure connections to multiple databases on the same server. 
   Each section should contain the relevant connection details for each database you want to connect to.

3. Update CageFS:

   # cagefsctl --update-etc

4. Enable PHP extensions:

   # selectorctl -u %USERNAME% -v %PHP_VERSION% -e pdo_odbc,odbc

   The following should be added into the connection script:

   $dbh = new PDO("odbc:mssql_odbc", "sa", "%DB_USER%", "%DB_PASS%");

5. Perform the following:

   # /opt/alt/alt-php-config/install_odbc
   # cagefsctl --force-update  
   # cagefsctl -M

6. Check if /etc/odbcinst.ini is available under the user:
   

   # su -s /bin/bash - username
   Last login: Sat Apr  6 16:39:52 +04 2024 on pts/1
   [~]$ ls -la /etc/odbcinst.ini
   ls: cannot access '/etc/odbcinst.ini': No such file or directory

   In such a case, please add the related package unixODBCto the CageFS:
   

   # cagefsctl --addrpm unixODBC
   # cagefsctl --force-update
   # cagefsctl -M