Accessing MSSQL from Linux using PHP MSSQL_CONNECT

MSSQL_CONNECT and MSSQL in PHP on Linux

You’ve yum installed php-mssql and you’re still tearing your hair out because you can’t connect to MSSQL from Linux. In this case it’s CentOS 6.7 with PHP 5.6.23 and Apache 2.2.15.

You’re using mssql_connect in a basic test script like this one:

<?php 
#################################### 
# PHP error handling for development servers
error_reporting(E_ALL);
ini_set("display_errors", "1"); 
#####################################

// No need to state port number or instance, but in some situations it may be necessary
// To find that query MSSQL: select @@servername + '\' + @@servicename
// For different port do, e.g., 192.168.1.10:9000
$myServer = "[ip or hostname]";
$myUser = "[username]"; 
$myPass = "[password]"; 
$myDB = "[dbname]";

//connection to the database
print "########################</br>mssql_connect:</br>";
$dbhandle = mssql_connect($myServer, $myUser, $myPass) or exit("Couldn't connect to SQL Server: ".mssql_get_last_message());

//output some info from MSSQL
$version = mssql_query('SELECT @@VERSION');
$row = mssql_fetch_array($version);
echo $row[0];
// Clean up
mssql_free_result($version);
?>

Diagnosing the problem

Here’s a list of things to try, in order. You need to make sure you Linux machine can actually connect to the MSSQL box over the network and we’ll use Telnet to do that while monitoring network connections so we can see if a connection is actually being made.

Have a browser window open with a link to the test script ready to hit refresh.

Install a network connection monitor or use netstat or ss. Opinions vary. I really like tcptrack for no particular reason (you might need to install libpcap in order to install it).

# yum install libpcap
# wget http://pkgs.repoforge.org/tcptrack/tcptrack-1.4.0-1.el6.rf.x86_64.rpm
# rpm -ivh tcptrack-1.4.0-1.el6.rf.x86_64.rpm

I open two SSH windows and place them side by side so I can monitor what’s going on in one window while working in the other.

Pick an SSH window and do this:

You have to find which ethernet port you need to monitor:

# ip link show

Usually it’s going to be “eth0” – make sure to type in that zero and not an Oh. If it’s not clear which port to use, just go through the list of ports prefixed by 1:, 2:, etc

# tcptrack -i eth0 -r 10

-i is interface

-r 10 is telling it to wait 10 seconds before removing the connection from the list – sometimes it’s so fast that you can’t see the connection before it disappears.

So now you have your port monitor running in one window. Switch to the other.

Assuming your MSSQL box is on the default port:

# telnet 192.168.1.10 1433 (replace the IP with your IP or hostname)

Hopefully you see something like this in tcptrack. Note the port 1433. I hid my IP addresses for obvious reasons.

 

You should also get the following in the Telnet window, proving your Linux box can open a connection to MSSQL:

Trying 192.168.1.10...
Connected to 192.168.1.10.
...(etc)

Now try running your script in the browser window. Do you see any connections opening? I am assuming you don’t and the script is failing.

However, if you can get this far and you can connect using Telnet, then there’s a good chance the security settings on your box are disallowing Apache to open network connections.

Try this:

 

# setsebool -P httpd_can_network_connect=1

It will take a couple seconds or more to complete. You are telling SELINUX to allow Apache (httpd) to make network connections.

Run your script again while watching tcptrack. 

Hopefully you see this. Note that the connection says CLOSED. The script opened a connection and then closed it. Perfectly normal.

 

That didn’t work! Now what?!?

If you can’t connect you’ve got a number of items to now check. I’m not going to detail all of those since you should have some basic idea by now how to configure these. If you’re hosting on a third party, you may simply never be able to connect since their network policies may disallow this. Anyway, here’s a list of items to check:

1. PHP and Apache version
   1. Things can change between versions.
2. IPTables on your Linux box
   1. Check and make sure it allows outbound and inbound connections on port 1433
3. Local or hosting network
   1. Make sure that network rules allow connections between the boxes
4. MSSQL Box
   1. There are a few things to try here:
      1. Windows Firewall
         1. Check if it allows remote connections on that port/service
         2. If you can disable it entirely for testing. If you can disable it and then it works you know immediately where your problem is if the connection doesn’t work when it is enabled.
      2. SQL Server Configuration Manager
         1. SQL Server has a client/network manager that you need to configure to allow connections for different connection types and IP addresses.
         2. Try SQL Server Network Configuration > Protocols for MSSQLSERVER > Click the “IP Addresses” tab. Make sure that both “Active” and “Enabled” both say yes. For whatever reason, I’ve configured that section and missed setting both of them. Triple check this. You will need to restart SQL, so plan for that.
      3. SQL Server user
         1. Make sure your user is allowed access to the database. This won’t prevent the connection from occuring, but while you’re there you may as well.

Overall, MSSQL_CONNECT works pretty well between linux and Windows. If you’re a Windows person you have to do things differently in the Linux world if you’re not very familiar with it. Good luck!