CODEWITHSUNDEEP
phpsql-serveramazon-web-servicesamazon-ec2php-7
HartleySan
HartleySan

Reputation: 7830

How to do I connect to a SQL Server instance from PHP 7 on an AWS Amazon Linux EC2 instance?

I have Apache 2.4 and PHP 7 installed and working fine on an Amazon Linux AMI on AWS. From PHP, I can connect to MySQL DBs just fine, but now I need to connect to a SQL Server DB.

How do I do this? What's the PHP code I need to write and do I need to install additional PHP packages/modules to do this? I already have PDO installed and tried to use it, but the following code throws the following error:

$dsn = 'sqlsrv:dbname=db-name;host=aws-endpoint.rds.amazonaws.com';
$user = 'user-name';
$password = 'password';
$dbh = new PDO($dsn, $user, $password);

Error:

PHP Fatal error:  Uncaught PDOException: could not find driver in /var/www/html/php-script-name.php

Edit: Adding to my question a bit, I ran a yum search pdo from the EC2 instance and got the following:

Loaded plugins: priorities, update-motd, upgrade-helper
============================================================================================================= N/S matched: pdo ==============================================================================================================
php-ZendFramework-Db-Adapter-Pdo.noarch : Zend Framework database adapter for PDO
php-ZendFramework-Db-Adapter-Pdo-Mssql.noarch : Zend Framework database adapter for MS SQL PDO
php-ZendFramework-Db-Adapter-Pdo-Mysql.noarch : Zend Framework database adapter for MySQL PDO
php-ZendFramework-Db-Adapter-Pdo-Pgsql.noarch : Zend Framework database adapter for PgSQL PDO
php70-pdo-dblib.x86_64 : PDO driver Microsoft SQL Server and Sybase databases
php71-pdo-dblib.x86_64 : PDO driver Microsoft SQL Server and Sybase databases
php-pdo.x86_64 : A database access abstraction module for PHP applications
php54-pdo.x86_64 : A database access abstraction module for PHP applications
php55-pdo.x86_64 : A database access abstraction module for PHP applications
php56-pdo.x86_64 : A database access abstraction module for PHP applications
php70-pdo.x86_64 : A database access abstraction module for PHP applications
php71-pdo.x86_64 : A database access abstraction module for PHP applications

  Name and summary matches only, use "search all" for everything.

It seems like php70-pdo-dblib.x86_64 is the plugin I want, so I ran yum install on it and my phpinfo now looks like the following:

enter image description here

I did restart Apache as well, but I still get the error noted above with the code noted above.

As such, I do not think that this is a duplicate of the question linked in the comments section.

Upvotes: 1

Views: 3571

Answers (3)

user7983224
user7983224

Reputation:

Follow the official guide from Microsoft when you get to the point of installing unixODBC refer to this page to build an RPM and install it:

https://github.com/EreMaijala/unixODBC

Upvotes: 0

HartleySan
HartleySan

Reputation: 7830

I figured it out. You do in fact have to have php70-pdo-dblib installed and restart the server. However, my $dbn string was off. Specifically, sqlsrv needed to be changed to dblib and I added the port number :1433 onto the end of the host part.

As such, the following code worked:

$dsn = 'dblib:host=aws-endpoint.rds.amazonaws.com:1433;dbname=db-name;';
$user = 'user-name';
$password = 'password';

try {

  $dbh = new PDO($dsn, $user, $password);

  $result = $dbh->query("SELECT * 
    FROM table-name;");

  foreach ($result as $row) {
    echo '<pre>';
      print_r($row);
    echo '</pre>';
  }

} catch (PDOException $e) {

  echo 'Connection failed: ' . $e->getMessage();

}

Upvotes: 1

John Hanley
John Hanley

Reputation: 81444

You need to install the Microsoft SQL PHP driver.

Follow the instructions on this page:

MSPHPSQL

Upvotes: 0

Related Questions