14 april 2014

How to Connect CentOS Web Server to a SQL Server

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

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

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

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

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

/dev/db_lookup.php

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.
http://net-ict.be/

Geen opmerkingen:

Een reactie plaatsen

Opmerking: alleen leden van deze blog kunnen een reactie plaatsen.