Error odbc_connect() SQL Error - Can't Open lib 'SQL Server'

Table of Contents

Issue
#

Ketika hendak membuka koneksi ke database Microsoft SQL Server dengan PHP muncul error seperti berikut.

PHP Warning:  odbc_connect(): SQL error: [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found, SQL state 01000 in SQLConnect in 
/home/user/public_html/tes.php on line 8

Hal tersebut kemungkinan karena driver belum ada sehingga perlu diinstall terlebih dahulu.

Solution
#

CloudLinux

Jika memakai Cloudlinux/CageFS, Install driver dengan perintah

/opt/alt/alt-php-config/install_odbc

Atur supaya user CloudLinux dapat mengakses package ODBC.

cagefsctl --addrpm unixODBC

Lalu update CageFS.

cagefsctl --force-update && cagefsctl -M

Untuk memastikan driver telah terinstall, gunakan perintah berikut.

# odbcinst -q -d
[PostgreSQL]
[MySQL]
[FreeTDS]
[MariaDB]
[ODBC Driver 17 for SQL Server]

RHEL/CentOS

Jika memakai RHEL/CentOS Anda dapat mengeksekusi script berikut.

#!/bin/sh

# add repo conf
#curl https://packages.microsoft.com/config/rhel/${dist_ver}/prod.repo > /etc/yum.repos.d/mssql-release.repo
cat << EOF > /etc/yum.repos.d/mssql-release.repo
[packages-microsoft-com-prod]
name=packages-microsoft-com-prod
baseurl=https://packages.microsoft.com/rhel/\$releasever/prod/
enabled=1
gpgcheck=1
gpgkey=https://packages.microsoft.com/keys/microsoft.asc
EOF

# install packages
ACCEPT_EULA=Y yum -y install msodbcsql17

Untuk memastikan driver telah terinstall, gunakan perintah berikut.

# odbcinst -q -d
[PostgreSQL]
[MySQL]
[FreeTDS]
[MariaDB]
[ODBC Driver 17 for SQL Server]

cPanel

Install driver menggunakan skrip berikut.

#!/bin/sh

# add repo conf
#curl https://packages.microsoft.com/config/rhel/${dist_ver}/prod.repo > /etc/yum.repos.d/mssql-release.repo
cat << EOF > /etc/yum.repos.d/mssql-release.repo
[packages-microsoft-com-prod]
name=packages-microsoft-com-prod
baseurl=https://packages.microsoft.com/rhel/\$releasever/prod/
enabled=1
gpgcheck=1
gpgkey=https://packages.microsoft.com/keys/microsoft.asc
EOF

# install packages
ACCEPT_EULA=Y yum -y install msodbcsql17

Pastikan driver telah terinstall.

# odbcinst -q -d
[PostgreSQL]
[MySQL]
[FreeTDS]
[MariaDB]
[ODBC Driver 17 for SQL Server]

Untuk menggunakan modul PHP sqlsrv dan pdo_sqlsrv, Anda perlu menginstall package unixODBC-devel agar proses kompilasi dengan PECL tidak error.

yum install unixODBC-devel

Selanjutnya install modul dengan perintah.

/usr/bin/ea-php81-pecl install sqlsrv
/usr/bin/ea-php81-pecl install pdo_sqlsrv

Restart service Apache dan PHP-FPM.

/scripts/restartsrv_httpd
/scripts/restartsrv_apache_php_fpm

Untuk memastikan modul telah terinstall.

# /opt/cpanel/ea-php74/root/usr/bin/php -i | grep -i sqlsrv
Registered PHP Streams => https, ftps, compress.zlib, php, file, glob, data, http, ftp, compress.bzip2, phar, zip, sqlsrv
PDO drivers => mysql, odbc, sqlite, sqlsrv
pdo_sqlsrv
pdo_sqlsrv support => enabled
pdo_sqlsrv.client_buffer_max_kb_size => 10240 => 10240
pdo_sqlsrv.log_severity => 0 => 0
pdo_sqlsrv.report_additional_errors => 1 => 1
pdo_sqlsrv.set_locale_info => 2 => 2
sqlsrv
sqlsrv support => enabled
sqlsrv.ClientBufferMaxKBSize => 10240 => 10240
sqlsrv.LogSeverity => 0 => 0
sqlsrv.LogSubsystems => 0 => 0
sqlsrv.SetLocaleInfo => 2 => 2
sqlsrv.WarningsReturnAsErrors => On => On

Jika memerlukan modul terinstall di versi PHP lama, Anda dapat mengecek versi modul sqlsrv dan pdo_sqlsrv yang disupport oleh versi PHP lama link berikut.

Contoh.

Untuk PHP 7.2

/usr/bin/ea-php72-pecl install pdo_sqlsrv-5.8.1

Untuk PHP 7.3

/usr/bin/ea-php73-pecl install pdo_sqlsrv-5.9.0

Untuk PHP 7.4

/usr/bin/ea-php74-pecl install pdo_sqlsrv-5.10.0

Testing

Berikut adalah skrip PHP yang dapat digunakan untuk pengetesan MSSQL.

Function sqlsrv_connect().

<?php
    $serverName = "IP Address MSSQL";

// jika menggunakan custom port
//    $serverName = "IP Address MSSQL,PORT";

$connectionInfo = array( "Database"=>"testdb", "UID"=>"myuser", "PWD"=>"strongpassword");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}

Function odbc_connect.

<?php

// Configuration Settings for connection to Database
$host = 'IP Address MSSQL';
$user = 'myuser';
$pass = 'strongpassword';
$db   = 'testdb';
$conn = "DRIVER={/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.6.1};SERVER=$host;PORT=1433;DATABASE=$db";

// Open connection
$db_connect = odbc_connect($conn, $user, $pass);

// Check for successful connection
if ($db_connect) {
    echo 'Connection established.<br />';
} else {
    echo "Connection could not be established.<br />";
    echo "ODBC Error: " . odbc_errormsg($db_connect) . "<br />";
    exit; // Stop script execution
}

// Close connection
odbc_close($db_connect);

?>