尽管安装了驱动程序，但无法从PHP(PDO)访问SQL Server.已启用 [英] SQL Server Access from PHP (PDO) Not Working Despite Driver Installed & Enabled

问题描述

我正在尝试访问SQL Server.

更新:为响应下面Clive的评论，我还尝试过重新安装本机ODBC驱动程序，但没有更改.

我已经检查了所有可以找到的问题，一切似乎都井然有序.我的服务器详细信息:

PHP 5.4(通过Plesk管理)
SQL Server 2008 R2 SP2
Windows Server 2008 R2

最新的PDO mssql驱动程序位于EXT目录中，并在php.ini中引用:

extension=php_sqlsrv_54_nts.dll
extension=php_pdo_sqlsrv_54_nts.dll

PHPINFO()给出以下内容:

PDO Drivers: mysql, sqlite, sqlsrv
pdo_sqlsrv support: enabled
sqlsrv support:  enabled

我的连接字符串如下:

$DSN = "sqlsrv:Server=myDbServer,1433;database=my-dbname";
$PdoOptions = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$sparklyGenericPdoObj = new PDO($DSN,'myUsername','myPasswurde', $PdoOptions);

但这只是给我以下错误，该错误以回旋方式链接到我已经安装的驱动程序(我已经检查了版本号):

PHP致命错误:带有消息的未捕获异常'PDOException' 'SQLSTATE [IMSSP]:此扩展名需要Microsoft SQL Server 2012 Native Client ODBC驱动程序，用于与SQL Server通信.使用权 以下URL下载Microsoft SQL Server 2012本机 x86的客户端ODBC驱动程序: http://go.microsoft.com/fwlink/?LinkId=163712 '

在此线程上(报告与我完全相同的错误)是一个解决方案，它说您可以通过在PHPINFO中查找我上面提到的项目来确认驱动程序已安装:解决方案

尽管这是一篇相当老的文章，但我认为最好将答案放在此处而不是创建其他问题...

经过3个星期的网上研究并测试了有关php安装，php代码和驱动程序安装的许多假设，我终于找到了解决此问题的方法.

问题:

我有一个安装在WIN SERVER 2012 SP2上并在IIS下运行的应用程序. 该应用程序的开发是在WIN 7 SP1上进行的，并在32位XAMPP 3.2.2下运行.所有这些都运行PHP 5.6.

服务器上的一切工作正常，但是在XAMPP上本地执行时，我始终收到此消息:

此扩展程序需要用于SQL Server的Microsoft ODBC驱动程序11与SQL Server通信

即使我已经多次安装了该驱动程序(与找到的其他php驱动程序

我尝试了其他驱动程序，并且它与ODBC驱动程序配合得很好；

$db = new PDO("odbc:Driver={SQL Server};Server=192.168.165.XXX;Database=testDB;", "userName", "passWord");

但是我发现我在PROD服务器上使用sqlsrv时没有遇到过后来的驱动程序的某些限制，所以我决定再多做一些研究，以找到在本地实现sqlsrv的方法机器...

调查:

在连接PHP之前，我尝试使用ODBC Source Administrator(ODBCSA)应用程序创建System DSN(数据源名称)，并在尝试与应用程序连接之前从那里测试连接. 我在Win7中发现的一件很奇怪的事情是，有两个不同的ODBCSA应用程序.

Odbcad32.exe文件的32位版本位于％systemdrive％\ Windows \ SysWoW64文件夹中.

Odbcad32.exe文件的64位版本位于％systemdrive％\ Windows \ System32文件夹中.

32位版本的ODBC管理器工具显示 32位系统DSN ，32位用户DSN和64位用户DSN.

ODBC管理器工具的64位版本显示 64位系统DSN ，32位用户DSN和64位用户DSN

只需确定您使用的是哪种ODBCA，请在odbcad32.exe下签入任务管理器，如果您在右边看到* 32，则表示它是32位版本.

首先，我执行了64位ODBCSA，并使用全新安装的全新ODBC驱动程序11(称为ODBC_11_64)创建了DSN，并使用ODBCSA测试了连接.一切正常.当我测试代码时，我收到了另一条错误消息:

指定的DSN包含驱动程序和应用程序之间的体系结构不匹配

然后，我尝试使用32位ODBCSA创建一个新的DSN.起初很奇怪，当我发现除了一些驱动程序名称之外没有版本号，并且我从ODBCSA收到此错误:

找不到用于SQL Server的ODBC Driver 11的安装例程.请重新安装驱动程序.

和其他消息:

在注册表中找不到组件

很明显这是一个安装问题，后来的消息指出了正确的方向，即注册表...

以管理员身份启动regedit，我发现可以在这里找到所有64位ODBC驱动程序: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI

所有32位ODBC驱动程序都可以在这里找到: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBCINST.INI

当尝试访问64位密钥(用于SQL Server的\ ODBC Driver 11)时，我能够显示键值，但是当我尝试访问32位密钥时，却收到此消息:

错误打开键，SQL Server驱动程序的ODBC驱动程序11无法打开. 错误阻止此键被打开. 详细信息:拒绝访问.

解决方案:

通过右键单击32位错误密钥，我可以访问权限界面.然后，我可以添加我的用户名(也可以添加一个组)，并授予对注册表项的完全控制权.

然后我回到使用用于SQL Server的32位ODBCSA和ODBC Driver 11创建新的DSN的过程中，测试了SUCCESS的连接！ :-)

我回到我的应用程序时，发现能够在本地计算机上使用sqlsrv访问远程MSSQL SERVER.

一直以来，驱动程序安装包有问题！我应该具有SYSTEM的安装权限，该密钥的用户和管理员都具有该驱动程序的64位注册表项所做的安装.

这是一个很长的故事，但是我认为这是值得的，因为许多人试图解决这个复杂的问题，朝着许多不同的方向努力而没有成功.这也是我对你们所有人的贡献，他们未能找到解决方案，但对我的调查有所帮助；-)

I am trying to access an SQL Server.

UPDATE: In response to Clive's comments below I have also tried reinstalling the native ODBC driver but no change.

I've checked over all the questions I can find on this and everything seems to be in order. My server details:

PHP 5.4 (Managed via Plesk)
SQL Server 2008 R2 SP2
Windows Server 2008 R2

The latest mssql drivers for PDO are in the EXT directory and referenced in php.ini:

extension=php_sqlsrv_54_nts.dll
extension=php_pdo_sqlsrv_54_nts.dll

PHPINFO() gives the following:

PDO Drivers: mysql, sqlite, sqlsrv
pdo_sqlsrv support: enabled
sqlsrv support:  enabled

My connection string looks like this:

$DSN = "sqlsrv:Server=myDbServer,1433;database=my-dbname";
$PdoOptions = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$sparklyGenericPdoObj = new PDO($DSN,'myUsername','myPasswurde', $PdoOptions);

But that just gives me the following error which links - in a roundabout way - to the drivers I already have installed (I've checked the version numbers):

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[IMSSP]: This extension requires the Microsoft SQL Server 2012 Native Client ODBC Driver to communicate with SQL Server. Access the following URL to download the Microsoft SQL Server 2012 Native Client ODBC driver for x86: http://go.microsoft.com/fwlink/?LinkId=163712'

On this thread (reporting exactly the same error as me) is a solution which says that you can confirm the driver is installed by looking for the items I've mentioned above in PHPINFO: Not getting connected to Sql Server 2012!

Any help would be much appreciated!

解决方案

Although it is a fairly old post, I've decided that it would be better to place my answer here instead of creating an other question...

after 3 weeks of research all over the Web and testing a lot of hypothesis around php installation, php code and drivers installation, I finally found the solution to this question.

THE PROBLEM:

I have an app that is installed on WIN SERVER 2012 SP2 and running under IIS. The development of this app is made on a WIN 7 SP1 and running under 32-bit XAMPP 3.2.2. All of these are running PHP 5.6.

Everything is working fine on the server but I keep having this message when executed locally on XAMPP:

This extension requires the Microsoft ODBC Driver 11 for SQL Server to communicate with SQL Server

Even though I've installed this driver many times (with other php drivers found here, configure my php.ini to load php_sqlsrv_56_ts.dll and php_pdo_sqlsrv_56_ts.dll), it keeps returning me the same message...

The PDO command I've used to connect to distant MSSQL Server was:

$db = new PDO("sqlsrv:server=192.168.165.XXX;Database=testDB;", "userName", "passWord");

I have tried other drivers and it worked fairly well with ODBC driver;

$db = new PDO("odbc:Driver={SQL Server};Server=192.168.165.XXX;Database=testDB;", "userName", "passWord");

but I've found that there was some limitations with the later driver that I didn't experience with sqlsrv on the PROD server so I decided to push a little bit more my research to find a way to implement sqlsrv on my local machine...

THE INVESTIGATION:

Before connecting with PHP, I've tried to create System DSN (Data Source Name), with the ODBC Source Administrator (ODBCSA) application and test connection from there before trying to connect with my application. Something pretty strange I have discovered with Win7, is that there are two different ODBCSA applications.

The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.

The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.

The 32-bit version of the ODBC Administrator tool displays 32-bit system DSNs, 32-bit user DSNs, and 64-bit user DSNs.

The 64-bit version of the ODBC Administrator tool displays 64-bit system DSNs, 32-bit user DSNs, and 64-bit user DSNs

Just to make sure what ODBCA your are using, check in Task manager under odbcad32.exe, if you see *32 on its right, that means its the 32-bit version.

At first, I executed the 64-bit ODBCSA, created DSN using the brand newly installed ODBC Driver 11, called ODBC_11_64 and test the connection using the ODBCSA. All worked fine. When I tested my code I received an other error message:

The specified DSN contains an architecture mismatch between the Driver and Application

Ok then, I tried to create an new DSN using the 32-bit ODBCSA. It was strange at first, when I've discovered that there was no version number aside some driver names and I received this error from the ODBCSA:

The setup routines for the ODBC Driver 11 for SQL Server could not be found. Please reinstall the driver.

and an other message:

Component not found in the registry

It was obvious that there was an installation problem and the later message point me in the right direction, the Registry...

Starting regedit as admin, I've found that all 64-bit ODBC drivers can be found here: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI

All 32-bit ODBC drivers can be found here: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBCINST.INI

While trying to access the 64-bit key (\ODBC Driver 11 for SQL Server), I was able to display key values but when I tried to access the 32-bit key, I received this message:

Error Opening Key, ODBC Driver 11 for SQL Server Driver cannot be opened. An error is preventing this key from being opened. Details: Access is denied.

THE SOLUTION:

By right clicking on the 32-bit faulty key I was able to access permission interface. I was then able to add my username (I could also add a group) and granted full Control on the registry key.

I then came back to creating an new DSN using the 32-bit ODBCSA and ODBC Driver 11 for SQL Server, tested the connection with SUCCESS! :-)

I when back to my application and found that I was able to access the distant MSSQL SERVER using sqlsrv, on my local machine.

All along it was the driver installation package that was faulty! I should had install permissions to SYSTEM, users and administrators to the key has it did with the 64-bit registry key for this driver.

It was a pretty long story but I thought it was worth it, since a lot of persons tried to solve this complex problem, going in many different directions without success. Its also my contribution to all of you, who failed to find the solution but helped me in my investigation ;-)

这篇关于尽管安装了驱动程序，但无法从PHP(PDO)访问SQL Server.已启用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！