New Topic

cPanel & WHM Feature Requests Topic Web Servers

Category Web Servers

Tags EasyApache4, EA4, mssql, experimental, v66

EasyApache 4 PDO_DBLIB (mssql support)

Rick Kukiela shared this idea ago

Completed

I need to be able to connect to a ms sql server database from php. The native mssql driver has been deprecated and removed in php 7.0. The only way to connect to mssql moving forward is PDO_DBLIB or PDO_ODBC. PDO_DBLIB is preferred.

This is a major issue for me. I run cpanel servers and I cannot move to EA4 until there is a way to connect to MSSQL from PHP supported. The only prerquisite for PDO_DBLIB is FreeTDS so I do not know why this is not supported yet. I cant imagine it would be too difficult to build a PDO_DBLIB package.

I like this idea 0

Best Answer

benny@cpanel.net ●

Hey all! It looks like I got my wires cross, and PDO has been available for some time. You can see them in the EA4 repo here:

[root@64 ~]# yum search php-pdo |grep ^ea-
ea-php54-php-pdo.x86_64 : A database access abstraction module for PHP
ea-php55-php-pdo.x86_64 : A database access abstraction module for PHP
ea-php56-php-pdo.x86_64 : A database access abstraction module for PHP
ea-php70-php-pdo.x86_64 : A database access abstraction module for PHP
ea-php71-php-pdo.x86_64 : A database access abstraction module for PHP
[root@64 ~]#

If you need these, feel free to install!

Replies (6)

Rick Kukiela ●

Honestly, I posted this before I realized EA4 already supports PDO_ODBC which does support MSSQL connections... So i guess this is not needed. At least for me.

URL

Mattherw Jones ●

How did you get it working? We're having trouble getting it to recognize the defined drivers.

URL

Eduardo ●

I have done this with ODBC and EasyApache 4. First install the PHP PDO_ODBC module in EA4

Then we have to install Microsoft SQL Server ODBC Driver for Linux, download available at https://www.microsoft.com/en-us/download/details.aspx?id=28160

After following the instructions to install it and restart Apache I had to edit the file /etc/odbc.ini with this config:

[MSSQLServer]
Driver = SQL Server Native Client 11.0
Description = Database access
Trace = No
Server = 123.456.789.0
Port = 1433
Database = DatabaseName

Then on the PHP script connect to the database like this:

<?php

putenv('ODBCSYSINI=/etc/');
putenv('ODBCINI=/etc/odbc.ini');

try {
$db = new PDO("odbc:MSSQLServer", $username, $password);
} catch (PDOException $exception) {
echo $exception->getMessage();
exit;
}

$q = $db->query("SELECT * from Table");
while ( $d = $q->fetchObject() ) {
var_export( $d );
}

This way I managed to make the connection to the MSSQL Server.

URL

lurkerX ●

Thank you so much Eduardo! This is a real life-saver.

If you need to enable the PHP ODBC extension on an existing configuration and have access to WHM:

1) Go to "EasyApache 4" and click on the "Customize" button for the "Currently Installed Packages" section at the top of the page.

2) Click on "PHP Extensions" and then enter "ODBC" in the search field.

3) Enable "php-odbc" for the version(s) of PHP on your server that you desire. Click "Next".

4) Scroll to the bottom of the review page and click the "Provision" button.

5) An Apache restart is probably a good idea.

I still had connectivity issues that were due to firewalls on both Linux and Windows. I found telnet to be useful for basic testing of the SQL Server's port.

For the Linux machine, I had to open up the out bound port to SQL Server (typically this is 1433).

Again in WHM:

1) Go to "ConfigServer Security & Firewall", then click on the "ConfigServer Firewall" tab.

2) Click on the "Firewall Configuration" button.

3) Locate the "Allow incoming TCP ports" section and add the port to SQL Server to the "TCP_OUT" field.

4) Scroll to the bottom of the page and click the "Change" button.

5) On the following page click the "restart csf+ifd" button.

For the Windows 2012R machine I had to add a rule to Windows Firewall:

1) Control Panel > System and Security > Windows Firewall

2) Click on "Advanced settings" on the left.

3) Right-click on "Inbound Rules" and choose "New Rule..."

4) Choose the "Port" radio-button and click on the "Next" button.

5) In the "Specific local ports" field, enter the port for your SQL Server.

6) Use the "Allow the connection" radio-button and click the "Next" button.

7) Chose the checkbox(es) for when the rule is applied and click the "Next" button.

8) Give this rule a name and optional description and click the "Finish" button.

9) Then find the rule from the list of Inbound Rules and right-click on it and choose "Properties".

10) Click on the "Scope" tab from the Properties dialog.

11) In the "Remote IP Address" section, click on the "These IP addresses" radio button and then enter the IP address from the Linux machine via the "Add" button.

That's what worked for me. I am no security expert, so please use your own judgement.

Thank you so much Eduardo! This is a real life-saver.

If you need to enable the PHP ODBC extension on an existing configuration and have access to WHM:

1) Go to "EasyApache 4" and click on the "Customize" button for the "Currently Installed Packages" section at the top of the page.

2) Click on "PHP Extensions" and then enter "ODBC" in the search field.

3) Enable "php-odbc" for the version(s) of PHP on your server that you desire. Click "Next".

4) Scroll to the bottom of the review page and click the "Provision" button.

5) An Apache restart is probably a good idea.

I still had connectivity issues that were due to firewalls on both Linux and Windows. I found telnet to be useful for basic testing of the SQL Server's port.

For the Linux machine, I had to open up the out bound port to SQL Server (typically this is 1433).

Again in WHM:

1) Go to "ConfigServer Security & Firewall", then click on the "ConfigServer Firewall" tab.

2) Click on the "Firewall Configuration" button.

3) Locate the "Allow incoming TCP ports" section and add the port to SQL Server to the "TCP_OUT" field.

4) Scroll to the bottom of the page and click the "Change" button.

5) On the following page click the "restart csf+ifd" button.

For the Windows 2012R machine I had to add a rule to Windows Firewall:

1) Control Panel > System and Security > Windows Firewall

2) Click on "Advanced settings" on the left.

3) Right-click on "Inbound Rules" and choose "New Rule..."

4) Choose the "Port" radio-button and click on the "Next" button.

5) In the "Specific local ports" field, enter the port for your SQL Server.

6) Use the "Allow the connection" radio-button and click the "Next" button.

7) Chose the checkbox(es) for when the rule is applied and click the "Next" button.

8) Give this rule a name and optional description and click the "Finish" button.

9) Then find the rule from the list of Inbound Rules and right-click on it and choose "Properties".

10) Click on the "Scope" tab from the Properties dialog.

11) In the "Remote IP Address" section, click on the "These IP addresses" radio button and then enter the IP address from the Linux machine via the "Add" button.

That's what worked for me. I am no security expert, so please use your own judgement.

URL

cPanelJacob ●

Howdy,

I've built out FreeTDS for EasyApache 4, and I've enabled MSSQL support in PHP5. Since PHP7 doesn't support MSSQL, you'd have to use PDO or the like for that.

As this hasn't been through the full team processes and releases yet, I've only built them out on build.opensuse.org, here:

https://build.opensuse.org/project/show/home:Jperkster:ea-freetds

If you would, please check out the RPMs and test them out, see how they work. Feedback here is most appreciated so that we can get everything done quickly to get it into the mainline EA4.

Thanks for your patience!

URL

benny@cpanel.net ●

We'll be pushing an update to the production EasyApache 4 repo that includes this RPM on April 18th. We'll be back to update again when that goes live!

URL

benny@cpanel.net ● ●

Hey all! It looks like I got my wires cross, and PDO has been available for some time. You can see them in the EA4 repo here:

[root@64 ~]# yum search php-pdo |grep ^ea-
ea-php54-php-pdo.x86_64 : A database access abstraction module for PHP
ea-php55-php-pdo.x86_64 : A database access abstraction module for PHP
ea-php56-php-pdo.x86_64 : A database access abstraction module for PHP
ea-php70-php-pdo.x86_64 : A database access abstraction module for PHP
ea-php71-php-pdo.x86_64 : A database access abstraction module for PHP
[root@64 ~]#

If you need these, feel free to install!

URL