How to Connect CentOS Web Server to a SQL Server

Posted on October 15, 2012 by Peter Washburn in SQL Server with 0 Comments

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.

Sponsored

Configuring the CentOS System

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

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

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

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

Look for the [epel] section, and set:

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

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

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

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).

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

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

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

Second, the ODBC driver template:

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.

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

** 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:

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

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

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

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

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

Sponsored

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.

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.

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.

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.

Sponsored

Tagged with ,