How to Connect CentOS Web Server to a SQL Server

I recently needed to have an internal CentOS 6.x web server (LAMP-based) talk to one of our Microsoft SQL Servers in order to dynamically update records in the local MySQL server the web server normally communicates with. It turns out that it can be a bit of an interesting task to accomplish. Today I’m going to go through a confirmed working method of setting up your CentOS 6.x based web server to talk to a Microsoft SQL Server database. I’ll also provide reusable PHP database connection handling code (in the form of connect and close functions) that can be placed in an include file.

Configuring SQL Server and Its Host

First, the SQL Server (and the appropriate database) must be configured for Windows and SQL Server Authentication. This requires a restart of the SQL Server service if changed. In addition, the server must also have TCP/IP connections enabled with a static port defined (for my example, I will be using the default of 1433), and the firewall on the host for the SQL Server must allow connections to SQL Server on the static port.

At this stage, the first thing to test from your CentOS system is whether you can telnet into the SQL Server host on port 1433. It is important at this stage to test this as you need to determine (a) whether the CentOS system is properly resolving the DNS name of the Windows system (if you are wanting to connect via hostname rather than IP address), and (b) if the Windows system is properly responding on port 1433.

Provided everything is working at this stage, we’re ready to get onto setting up the CentOS system to talk to the SQL Server.

Configuring the CentOS System

First, we will gain temporary root access to the CentOS system.

su

Enter the root user password, and change directory to our home directory.

cd ~

Next, we install the current version of the EPEL repository for CentOS 6.

rpm -Uvh http://www.muug.mb.ca/pub/epel/6/x86_64/epel-release-6-7.noarch.rpm

Then we will disable the EPEL repository from being used on regular update checks.

vi /etc/yum.repos.d/epel.repo

Look for the [epel] section, and set:

enabled=0

At this stage, we’re ready to install the components that may be missing.

yum install freetds php-odbc php-mssql --enablerepo=epel

Now that we have all of the components installed, it’s time to configure them. We’ll start with FreeTDS.

vi /etc/freetds.conf

Note: All of the configuration entries for this file within a section must be indented.

[global]
	tds version = 8.0
	port = 1433
	client charset = UTF-8
	debug flags = 0x4fff
	timeout = 180
	connect timeout = 180
	text size = 20971520

[TDS_Servername]
	host = Servername
	port = 1433
	tds version = 8.0
	encryption = request

You can unncomment “dump file = /tmp/freetds.log” in [global] section and change path if desired, and comment out unused [server] entries and configurations by prefixing the lines with a “;”.

Next, we need to change the file paths of the ODBC configuration files (one or both may not exist yet).

mv /etc/odbc.ini /usr/local/etc/
mv /etc/odbcinst.ini to /usr/local/etc/

If either file does not exist, then we need to create the missing file(s):

vi /usr/local/etc/odbc.ini
vi /usr/local/etc/odbcinst.ini

Then we can proceed to create the requisite symlinks to the files (and adjust their permissions).

cd /etc
ln -s /usr/local/etc/odbc.ini
ln -s /usr/local/etc/odbcinst.ini
chmod 0664 /usr/local/etc/odbc*.ini
cd ~

Next, we need to generate the required template files for ODBC per the UnixODBC FreeTDS documentation.
First, the data source (DSN) template:

vi tds.datasource.template
[DSNName]
Driver = FreeTDS
Description = TDS_Servername
Trace = No
Server = IPAddress
Port = 1433
TDS Version = 8.0
Database = DBName

Second, the ODBC driver template:

vi tds.driver.template
[FreeTDS]
Description	= v0.91 with protocol v8.0
Driver		= /usr/lib64/libtdsodbc.so.0

Now we will install the data source and driver by the following commands. We copy the .odbc.ini file to make the data source accessible system-wide so that the Apache user is able to access it.

odbcinst -i -d -f tds.driver.template
odbcinst -i -s -f tds.datasource.template
cp /root/.odbc.ini /etc/odbc.ini

At this stage, I strongly suggest editing your odbcinst.ini and disabling unused ODBC drivers.

vi /etc/odbcinst.ini

** Comment out unused [driver] entries and configurations by prefixing the lines with a “#”

At this stage, it is a good time to test the connectivity to the SQL Server before we make any changes to Apache or PHP. From the shell, we can run the following command:

tsql –H Hostname_OR_IP –p PortNumber –U Username

If successful, you will be prompted for the password. On successful login, we further test the connection:

USE DBName;
SELECT * FROM TableName WHERE FieldName = ‘SampleValue’;
GO

This should result in the record set being displayed in the shell. We can then exit the connection.

exit

Image4

For a second command-line connection test, we run the following command:

tsql –S Servername –U Username –D DBName

If successful, you will be prompted for the password. On successful login, we further test the connection:

SELECT * FROM TableName WHERE FieldName = ‘SampleValue’;
GO

This should result in the record set being displayed in the shell. We can then exit the connection.

exit

Image5

Configuring PHP and SELinux

Now that we have a working connection to the SQL Server, we can configure PHP for core PHP time limits and for MS SQL specific configurations. NOTE: I used high execution/input/timeout values for the purposes of testing. These are not recommended values for a live production system.

vi php.ini
max_execution_time = 600
max_input_time = 600

Image6new

mssql.connect_timout = 30
mssql.timeout = 180
mssql.textlimit = 20971520
mssql.textsize = 20971520
mssql.secure_connection = Off
** Uncomment "mssql.max_procs = -1"

Image7

Next, we execute three commands from the shell. The first two modify SELinux settings to allow Apache (and its modules — in this case PHP) to connect via the network to a remote database (these can take up to 15 seconds each). The third restarts the Apache web server.

setsebool -P httpd_can_network_connect on
setsebool -P httpd_can_network_connect_db on
/etc/init.d/httpd restart

And finally, now that we have everything configured, we can create some PHP scripts to talk to the database. I used a simple pair of functions to handle creating and closing the database connection, which are stored in a separate include file.
NOTE: Do not use the “mssql_*” PHP commands, instead use the “odbc_*” commands.

/common/database/db_connect.inc.php

Image8new

/dev/db_lookup.php

Image9new

Items for consideration (for those who have the control to implement them):
1. Keep the firewall hole for Microsoft SQL Server limited to the IP(s) that will need to access it.
2. Use a separate set of credentials (with appropriate permissions) on your Microsoft SQL Server for authentication from the web server.
3. Set your timeouts for development testing, then adjust them up or down as required (allowing for the scenario of a script which may run longer than your initial testing script). This will be very dependent upon your usage scenario. This can be tested by setting a variable at the beginning and end of the script with each set to time(), then taking the difference to get the total execution time of the script.
4. If using an include file to store the database connection handling functions (which I strongly recommend so that your database permission are not stored in your live accessible script directly), ensure that you put the environment path to your freetds.conf prior to the include call in your main code.
5. If using an include file to store the database connection handling functions, ensure that you include the file prior to entering a loop which will utilize the connection.
6. Immediately prior to calling MakeMSDBConnect() (or using odbc_connect for those wanting to write their own code and not use my functions), ensure that you put the environment variable for the TDS version so that the connection will use the appropriate version.
7. When working with databases, if you are not using hard-coded information for your database queries, ensure that you sanitize all inputs to prevent SQL injection attacks.