PHP v5.6 PDO on MS SQL Server 2014

Question

Issue

I am currently unable to connect to my database using PDO in PHP.

Specs

PHP version 5.6.9
Database: Microsoft SQL Server 2014 on 2008 R2
From phpinfo(): PDO drivers: mysql, odbc, sqlite
Using IIS 6.1

What I've tried

I have just installed the drivers in the SQLSRV32.exe file from Microsoft (from this link: https://www.microsoft.com/en-us/download/details.aspx?id=20098) to my PHP extensions folder, however I still cannot seem to connect to my database. (And yes, I restarted IIS)

I can get the connection to work with older connection methods.

My Code

My current PHP code for PDO connection is as follows:

try {
    $this->pdo = new PDO('odbc:host='.$this->hostname.':'.$this->port.';dbname='.$this->database,$this->username,$this->password);
} catch (PDOException $e) {
    echo 'Failed to get DB handle: '.$e->getMessage().'<br/>';
    exit;
}

The above code is inside my MyPDO class constructor so that when I instantiate my class, it is connected $pdo = new MyPDO();.

My Code's Result

The issue is that I am getting this error as output:

Failed to get DB handle: SQLSTATE[IM002] SQLDriverConnect: 0 [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Any help resolving this issue is greatly appreciated!

It looks like you're inadvertently concatenating the username and password into the dsn (as part of the dbname) rather than passing them in as additional parameters; it should probably be : new PDO('odbc:host='.$this->hostname.':'.$this->port.';dbname='.$this->database, $this->username, $this->password); — CD001
– CD001, Commented May 18, 2016 at 15:53
My apologies, I updated my post with the correct code. I am still experiencing this issue — ctwheels
– ctwheels, Commented May 18, 2016 at 16:00
Ok, the basic question, do you want to use ODBC? If so, have you defined this DSN? (I haven't done this since Windows 2000 days, but there used to be a control panel for defining them. One tab for user-specific ones, and another tab for system-wide ones.) Once it's defined you want to do something like this: new PDO("odbc:dsn_name", $user, $pass); — miken32
– miken32, Commented May 18, 2016 at 17:24

PiKos · Accepted Answer · 2016-05-18 17:15:50Z

2

Double check your php.ini and confirm that you have lines:
```
extension=php_sqlsrv_56_nts.dll
extension=php_pdo_sqlsrv_56_nts.dll 
```
For PDO only php_pdo_sqlsrv_56_nts.dll is mandatory.
In phpinfo() in PDO Drivers sections you should have: sqlsrv

Use PDO_SQLSRV DSN rather than odbc, for example:

$this->pdo = new PDO( "sqlsrv:server=$hostname,$port;Database=$dbname", $username, $password);

or if you have full datasource name (ex. localhost\SQLEXPRESS)

$this->pdo = new PDO( "sqlsrv:server=$serverName;Database=$dbname", $username, $password);

If you receive could not find driver exception it means that sqlsrv extensions are not loaded.

answered May 18, 2016 at 17:15

PiKos

1,3841 gold badge16 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ctwheels Over a year ago

This worked for me. Thank you very much. I was missing extension=php_pdo_sqlsrv_56_nts.dll in the php.ini file and changed my PDO call to your second code chunk using the full datasource name

Collectives™ on Stack Overflow

PHP v5.6 PDO on MS SQL Server 2014

Issue

Specs

What I've tried

My Code

My Code's Result

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Issue

Specs

What I've tried

My Code

My Code's Result

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related