How to connect to SQL server using sqlsrv in CentOS 7.6?
I'm setting up a new server and trying to connect to Microsoft SQL Server Express 2014's database using "sqlsrv" extension/module with PHP 7.3 running in CentOS 7.6, but failed. However, with the same method in XAMPP, it does work!
I have installed php with the drivers according to the instructions from Microsoft page: https://docs.microsoft.com/en-us/sql/connect/php/installation-tutorial-linux-mac?view=sql-server-2017
In step 3, first two methods were not working, so I have installed PHP driver from the Remi repo instead:
[~]$ sudo yum install php-sqlsrv
And here is the list of installed packages:
$ sudo yum list installed *php*
Installed Packages
php.x86_64 7.3.4-1.el7.remi @remi-php73
php-cli.x86_64 7.3.4-1.el7.remi @remi-php73
php-common.x86_64 7.3.4-1.el7.remi @remi-php73
php-devel.x86_64 7.3.4-1.el7.remi @remi-php73
php-fedora-autoloader.noarch 1.0.0-1.el7 @epel
php-json.x86_64 7.3.4-1.el7.remi @remi-php73
php-pdo.x86_64 7.3.4-1.el7.remi @remi-php73
php-pear.noarch 1:1.10.9-2.el7.remi @remi-php73
php-process.x86_64 7.3.4-1.el7.remi @remi-php73
php-sqlsrv.x86_64 5.6.1-1.el7.remi.7.3 @remi-php73
php-xml.x86_64 7.3.4-1.el7.remi @remi-php73
$ sudo yum list installed unixODBC*
Installed Packages
unixODBC.x86_64 2.3.7-1.rh @packages-microsoft-com-prod
unixODBC-devel.x86_64 2.3.7-1.rh @packages-microsoft-com-prod
$ sudo yum list installed msodbcsql*
Installed Packages
msodbcsql.x86_64 13.1.9.2-1 @packages-microsoft-com-prod
msodbcsql17.x86_64 17.3.1.1-1 @packages-microsoft-com-prod
Although, I'm not using named instances, but according to this FAQ: https://github.com/Microsoft/msphpsql/wiki/FAQ#connect-to-named-instances
I can connect to the Microsoft SQL server with the ODBC driver independent of the PHP driver. By using "isql":
[~]$ isql -v MSSQLTest uid password
Some similar questions I have gone through are:
https://github.com/Microsoft/msphpsql/issues/703
https://github.com/Microsoft/msphpsql/issues/679
https://github.com/Microsoft/msphpsql/issues/470
But when using sqlsrv, nothing works.
My PHP connection code(working in XAMPP):
// xx.xx.xx.xx = My Public IP
// yyyy = My Port
$serverName = "xx.xx.xx.xx,yyyy"; //serverName\instanceName
$connectionInfo = array( "Database"=>"MyDB", "UID"=>"MyUID", "PWD"=>"MyPWD", "CharacterSet" => "UTF-8");
$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));
}
// ...Query part
Output:
Connection could not be established.
Array ( [0] => Array ( [0] => HYT00 [SQLSTATE] => HYT00 [1] => 0 [code] => 0 [2] => [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired [message] => [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired ) [1] => Array ( [0] => 08001 [SQLSTATE] => 08001 [1] => 10013 [code] => 10013 [2] => [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x271D [message] => [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x271D ) [2] => Array ( [0] => 08001 [SQLSTATE] => 08001 [1] => 10013 [code] => 10013 [2] => [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [message] => [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. ) )
How to get the "Connection established."?
Updated based on Remi Collet's answer:
$ sqlcmd -S mypublicip,myport -U user -P secret -Q "SELECT @@version"
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 14393: )
$ php -r ' echo"+ Connection:\n"; $conn = sqlsrv_connect("mypublicip,myport", array("UID" => "user", "PWD" => "secret")); if ($conn) { echo"+ Query: \n"; $query = sqlsrv_query($conn, "SELECT @@version"); if ($query) { echo"+ Result:\n"; print_r($row = sqlsrv_fetch_array($query, SQLSRV_FETCH_NUMERIC)); } } '
+ Connection:
+ Query:
+ Result:
Array
(
[0] => Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 14393: )
)
As you can see, both sqlcmd and running PHP scripts in command line are working!
But after uploading the same PHP scripts via FTP, and access from browser, here is the output:
+ Connection:
Just one line. Then, I have updated php-sqlsrv to 5.6.1-2.el7.remi.7.3
And, SELinux configuration has been done:
$ sudo setsebool -P httpd_can_network_connect_db 1
This is the values from "getsebool":
httpd_anon_write --> off
httpd_builtin_scripting --> on
httpd_can_check_spam --> off
httpd_can_connect_ftp --> off
httpd_can_connect_ldap --> off
httpd_can_connect_mythtv --> off
httpd_can_connect_zabbix --> off
httpd_can_network_connect --> off
httpd_can_network_connect_cobbler --> off
httpd_can_network_connect_db --> on
httpd_can_network_memcache --> off
httpd_can_network_relay --> off
httpd_can_sendmail --> off
httpd_dbus_avahi --> off
httpd_dbus_sssd --> off
httpd_dontaudit_search_dirs --> off
httpd_enable_cgi --> on
httpd_enable_ftp_server --> off
httpd_enable_homedirs --> on
httpd_execmem --> off
httpd_graceful_shutdown --> on
httpd_manage_ipa --> off
httpd_mod_auth_ntlm_winbind --> off
httpd_mod_auth_pam --> off
httpd_read_user_content --> off
httpd_run_ipa --> off
httpd_run_preupgrade --> off
httpd_run_stickshift --> off
httpd_serve_cobbler_files --> off
httpd_setrlimit --> off
httpd_ssi_exec --> off
httpd_sys_script_anon_write --> off
httpd_tmp_exec --> off
httpd_tty_comm --> off
httpd_unified --> on
httpd_use_cifs --> off
httpd_use_fusefs --> off
httpd_use_gpg --> off
httpd_use_nfs --> off
httpd_use_openstack --> off
httpd_use_sasl --> off
httpd_verify_dns --> off
Unlucky, still get the same results as when using version 5.6.1-1.el7.remi.7.3
Notice: I still have two versions of Microsoft ODBC Driver for SQL Server, 13 and 17(I'm not sure if this cause the problem, and no idea for removing the old one)
P.S. I have not tried other methods, since I'm only interested in sqlsrv extention by now, so the others might work!
Last updated:
Cause: The value of "httpd_can_network_connect" in SELinux configuration was set to "off".
Resolve Solution: Turn on "httpd_can_network_connect" value, by this command.
$ sudo setsebool -P httpd_can_network_connect 1
Then have httpd restart one time:
$ sudo systemctl restart httpd
Check the result, and now, everything is working like a charm!
Answer
Sorry, but using all examples from Microsoft SQL Server from PHP, I cannot reproduce your issue. Connection works.
- check that server is ready to server client connections
- check firewall configuration (at least 1433 should be allowed, check with netstat of used ports)
- check from command line using sqlcmd command (from mssql-tools package)
- check from command line using a simple PHP script
- check SElinux configuration (httpd_can_network_connect)
P.S. notice I have push a small update of php-sqlsrv which properly pull msodbcsql17 instead of msodbcsql.