Installing ODBC driver 13 for MSSQL Server in Amazon Linux on EC2 instance

Question

Problem:

Need to access and execute the MSSQL Stored Procedure from PHP code.

Version details:

Local development machine - Windows 10 with XAMPP with PHP 7

Production system - Amazon AWS AMI Linux with PHP 5.3.2

What I require:

Want to use odbc connection to access the MSSQL on my LOCAL as well as LIVE server.

What I tried for LIVE server:

Wanted to make sure that the LIVE server is compatible to packages and am able to install the required packages correctly.

Hence, started with the following steps:

1) Looked in the link below where I found that we need to use ODBC for faster data access: https://stackoverflow.com/a/31105561

2) Tried to install following ODBC driver on Amazon Linux as mentioned below:

(Assuming that RHEL 6 installer would work on Amazon Linux too. Please let me know in case am incorrect)

• Microsoft ODBC Driver 13.1 for SQL Server - Windows + Linux

sudo su curl packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo

exit

sudo yum remove unixODBC #to avoid conflicts

sudo ACCEPT_EULA=Y yum install msodbcsql-13.0.1.0-1 mssql-tools

But installer fails showing below error:

Error: msodbcsql conflicts with unixODBC-utf16-2.3.1-1.x86_64
Error: Package: msodbcsql-13.1.4.0-1.x86_64 (packages-microsoft-com-prod)
           Requires: unixODBC >= 2.3.1
           Available: unixODBC-2.2.14-14.7.amzn1.i686 (amzn-main)
               unixODBC = 2.2.14-14.7.amzn1

3) Hence, tried to install the unixODBC using http://www.unixodbc.org/

- Downloaded the package via curl
- gunzip unixODBC*.tar.gz
- tar xvf unixODBC*.tar

Then, used the following commands to install the same:

./configure
make
make install 

4) Restarted httpd service and again tried step 2 but installer failed with same error mentioned in point 2.

What I tried for LOCAL machine:

1. Downloaded the .msi file for Microsoft ODBC Driver 13.1 for SQL Server - Windows + Linux
2. Installed the same on my machine and restarted xampp apache. But am still getting Uncaught PDOException: could not find driver

UPDATE: Resolved the issue in local system by adding the extension dll in php.ini and restarted the server.

Could anyone please let me know what have I missed in LIVE configuration?

Answer 1

Approach to solution

As the direct link for installation was not working, had downloaded rpm files for offline installation using below lines:

sudo mkdir /tmp/RedHat6Packages

sudo curl https://packages.microsoft.com/rhel/6/prod/msodbcsql-13.1.4.0-1.x86_64.rpm > /tmp/RedHat6Packages/msodbcsql-13.1.4.0-1.x86_64.rpm

sudo curl https://packages.microsoft.com/rhel/6/prod/mssql-tools-14.0.4.0-1.x86_64.rpm > /tmp/RedHat6Packages/mssql-tools-14.0.4.0-1.x86_64.rpm

Tried to install offline with following commands: cd /tmp/RedHat6Packages/ sudo yum localinstall msodbcsql-13.1.4.0-1.x86_64.rpm sudo yum localinstall mssql-tools-14.0.4.0-1.x86_64.rpm

But still was getting the same error: error: Failed dependencies: libodbcinst.so.2()(64bit) is needed by msodbcsql-13.1.4.0-1.x86_64 unixODBC >= 2.3.1 is needed by msodbcsql-13.1.4.0-1.x86_64 Found command to check the packages: sudo yum provides /usr/lib64/libodbcinst.so.2

This showed following warning:

Loaded plugins: priorities, update-motd, upgrade-helper
2 packages excluded due to repository priority protections
unixODBC-2.2.14-14.7.amzn1.x86_64 : A complete ODBC driver manager for Linux
Repo        : amzn-main
Matched from:
Filename    : /usr/lib64/libodbcinst.so.2    

unixODBC-utf16-2.3.1-1.x86_64 : ODBC driver manager with UTF16 character conversion enabled by default
Repo        : packages-microsoft-com-prod
Matched from:
Filename    : /usr/lib64/libodbcinst.so.2

JUMP directly to solution

Checked the below mentioned link:

What does that mean: packages excluded due to repository priority protections

Disabled priorities by editing /etc/yum/pluginconf.d/priorities.conf and set enabled = 0

Was able to install the driver using the same commands mentioned: cd /tmp/RedHat6Packages/ sudo yum localinstall msodbcsql-13.1.4.0-1.x86_64.rpm sudo yum localinstall mssql-tools-14.0.4.0-1.x86_64.rpm

Still unable to figure out where are extension files but have created other question (Next steps after installation of MSSQL ODBC 13.1 on Amazon EC2 Linux) as purpose of this question is solved.

As a newbie to linux (esp. who have migrated from windows), it was really difficult to find the above solution. Hope it helps someone!!

Answer 2

Here are the commands which should work for Amazon Linux AMI:

curl https://packages.microsoft.com/config/rhel/7/prod.repo | sudo tee /etc/yum.repos.d/msprod.repo1
sudo yum remove unixODBC-utf16 unixODBC-utf16-devel
sudo yum info unixODBC*
sudo yum install mssql-tools unixODBC-devel
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' | tee -a ~/.bash_profile && source ~/.bash_profile

Source: Install sqlcmd and bcp the SQL Server command-line tools on Linux.

---

Otherwise, try to install older version which is not conflicting as per this post, e.g.

sudo ACCEPT_EULA=Y yum install msodbcsql-13.0.1.0-1 mssql-tools-14.0.2.0-1