使用 PHP、ODBC 和 Windows 身份验证连接到 SQL Server

Connecting to SQL Server using PHP, ODBC, and Windows authentication

提问by Compysaurus

I'm trying to use PHP to connect to an ODBC data source using Windows authentication. I can connect just fine to the server in SQL Server so I know it's running. When I try to run the command

我正在尝试使用 PHP 连接到使用 Windows 身份验证的 ODBC 数据源。我可以很好地连接到 SQL Server 中的服务器，所以我知道它正在运行。当我尝试运行命令时

$link = odbc_connect("my_odbc","",""); 

I get the error:

我收到错误：

"Warning: odbc_connect(): SQL error:
[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]
Login failed for user ''., SQL state 28000 in SQLConnect in C:\Users..."

“警告：odbc_connect()：SQL 错误：
[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]
用户''登录失败。，C:\Users 中 SQLConnect 中的 SQL 状态 28000...”

I tried:

我试过：

$link = odbc_connect("Driver={ODBC Driver 11 for SQL Server};   
        Server='my_odbc';Integrated Security=SSPI","","");

Which returned the message:

返回消息：

Warning: odbc_connect(): SQL error:
[Microsoft][ODBC Driver 11 for SQL Server]
Named Pipes Provider: Could not open a connection to SQL Server [53]. , SQL state 08001 in SQLConnect in C:\Users..."

警告：odbc_connect()：SQL 错误：
[Microsoft][ODBC Driver 11 for SQL Server]
命名管道提供程序：无法打开到 SQL Server [53] 的连接。, C:\Users 中的 SQLConnect 中的 SQL 状态为 08001..."

Not sure what I'm doing wrong.

不知道我做错了什么。

• my_odbcis a SQL Server (2008) on a different machine. I don't have admin privileges on that database so I can't change anything on that end (such as enabling SQL Server authentication).

• I am running Windows 7 and using PHP Version 5.6.12

• phpinfo()indicates that ODBC Support is enabled as well as pdo_sqlsrvsupport

• my_odbc是另一台机器上的 SQL Server (2008)。我没有该数据库的管理员权限，因此我无法更改任何内容（例如启用 SQL Server 身份验证）。

• 我正在运行 Windows 7 并使用 PHP 版本 5.6.12

• phpinfo()表示已启用 ODBC 支持以及pdo_sqlsrv支持

回答by Jonathan Parent Lévesque

Maybe you should try PDO (the performance difference isn't that great) with SQLsrv plugin (this what I'm using to connect to my other boss' software which use SQL Server 2008 database):

也许您应该使用 SQLsrv 插件尝试 PDO（性能差异不是很大）（这是我用来连接到使用 SQL Server 2008 数据库的其他老板的软件的工具）：

$connection = new PDO("sqlsrv:Server=" . $this->sourceServer . ";Database=" . $this->sourceDB, $this->sourceUser, $this->sourcePW);
$connection->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

You can download the plugin here

你可以在这里下载插件

https://www.microsoft.com/en-ca/download/details.aspx?id=36434

https://www.microsoft.com/en-ca/download/details.aspx?id=36434

In Xampp, you must copy the dll in that folder:

在 Xampp 中，您必须复制该文件夹中的 dll：

C:\xampp\php\ext

And you must add that line to your php.ini

您必须将该行添加到您的 php.ini

extension = php_pdo_sqlsrv_56_ts.dll

Note: Don't forget to restart your server so it can take in account the new php.ini file.

注意：不要忘记重新启动您的服务器，以便它可以考虑新的 php.ini 文件。

Let me know if it works for you

请让我知道这对你有没有用

回答by ourmandave

Someone had the same error message and asked and answered his own question here.

有人有同样的错误信息，并在这里提出并回答了他自己的问题。

To quote...

报价...

I′m sorry for the troubles.

我很抱歉给您带来麻烦。

The problem was, that I was using SQL Server Native Client 11.0 as driver. I switched it to SQL Server and now it works :/

问题是，我使用 SQL Server Native Client 11.0 作为驱动程序。我将它切换到 SQL Server，现在它可以工作了：/

Hopefully this at least helps someone, being in a similar problem....

希望这至少可以帮助遇到类似问题的人......

回答by timclutton

A significant omission from your question is how you are running PHP and with which credentials.

您的问题中的一个重要遗漏是您如何运行 PHP 以及使用哪些凭据。

Assuming you are using IIS or Apache (or another web server), then your PHP process is probably running under the local system account:

假设您使用的是 IIS 或 Apache（或其他 Web 服务器），那么您的 PHP 进程可能正在本地系统帐户下运行：

Since this account is localit doesn't have any authorisation to access your remoteSQL Server.

由于此帐户是本地帐户，因此它无权访问远程SQL Server。

You could alter the IIS/Apache service to run with the credentials (yours?) that are authorised to connect to SQL Server, but be aware this might cause other permissions issues with the service as well as being a problem if you change your password in the future.

您可以更改 IIS/Apache 服务以使用授权连接到 SQL Server 的凭据（您的？）运行，但请注意，这可能会导致该服务出现其他权限问题，并且如果您在未来。

Try running a test script from the command line (which should run with your credentials by default) to determine where the problem is:

尝试从命令行运行测试脚本（默认情况下应使用您的凭据运行）以确定问题出在哪里：

php -r "var_dump(odbc_connect(...));"

回答by aimme

This error is a General Error that arises due to unable to establish connect to the server for some reasons. Its important to know what server are you using with PHP and the application used with it. Example Apache, Xampp or Wamp etc.

此错误是由于某些原因无法与服务器建立连接而引起的一般错误。了解您在 PHP 中使用的服务器以及与它一起使用的应用程序很重要。例如 Apache、Xampp 或 Wamp 等。

Here are somethings you could try like.

这里有一些你可以尝试的东西。

Let me brief, below are the reference links.

让我简单介绍一下，以下是参考链接。

• Check whether it pings
• Enable TCP/IP connection SQL Server Configuration if not enabled. To do so, Open SQL Server Configuration Manager -> SQL Server Network Configuration->Protocols for SQL server->TCP/IP(set to enabled). Restart SQL services.
• Enable Remote Connection from server
• Open the Port, To do so Windows Firewall Settings-> Exceptions -> add a Port (Name:SQL;Port:1433;TCP) then from Exceptions tick SQLand save.
• Enable running browser services.SQL Server Configuration Manager -> SQL Server Services - > SQL Server Browserset to running.
• defining Port in connection string
• add instance name with machine name if more than one instance is being used

• 检查是否能ping通
• 如果未启用，则启用 TCP/IP 连接 SQL Server 配置。为此，打开SQL Server Configuration Manager -> SQL Server Network Configuration->Protocols for SQL server->TCP/IP(set to enabled). 重新启动 SQL 服务。
• 启用来自服务器的远程连接
• 打开端口，这样做Windows Firewall Settings-> Exceptions -> add a Port (Name:SQL;Port:1433;TCP) then from Exceptions tick SQL并保存。
• 启用正在运行的浏览器服务。SQL Server Configuration Manager -> SQL Server Services - > SQL Server Browser设置为运行。
• 在连接字符串中定义端口
• 如果正在使用多个实例，请添加带有机器名称的实例名称

sample

样本

$user = 'username';
$pass = 'password';
//Use the machine name and instance if multiple instances are used
$server = 'serverName\instanceName';
//Define Port
$port='Port=1433';
$database = 'database';

$connection_string = "DRIVER={ODBC Driver 11 for SQL Server};SERVER=$server;$port;DATABASE=$database";
$conn = odbc_connect($connection_string,$user,$pass);

here are useful links that i found regarding the issue

这是我发现的有关该问题的有用链接

resolving could not open a connection to sql server-errors

解决无法打开与 sql server 错误的连接

sql server provider named pipes provider error

sql server 提供程序命名管道提供程序错误

Have a look at these links too

也看看这些链接

Named Pipes Provider: Could not open a connection to SQL Server [53]

命名管道提供程序：无法打开与 SQL Server 的连接 [53]

回答by Emre Karata?o?lu

Look at the regedit HKLM/SOFTWARE/ODBC

查看regedit HKLM/SOFTWARE/ODBC

what is your folder name ?

你的文件夹名称是什么？

If there is a "SQL Server Native Client 11.0" then you have to write your php code like

如果有“SQL Server Native Client 11.0”，那么你必须像这样编写你的php代码

odbc_connect("Driver={SQL Server Native Client 11.0};Server=ip;Database=db;", "user", "pass");

However some of the server like server 2008 will be record this odbc like

然而，一些像 server 2008 这样的服务器会像这样记录这个 odbc

ODBC Driver 11 for SQL Server

Then you have to replace SQL Server Native Client 11.0 by ODBC Driver 11 for SQL Server.

然后，您必须将 SQL Server Native Client 11.0 替换为 ODBC Driver 11 for SQL Server。

回答by Luis D

Enable in IIS on Manager PHP o PDO_ODBC:

在 Manager PHP o PDO_ODBC 上的 IIS 中启用：

I have authenticated by Windows with following PHP statement:

我已使用以下 PHP 语句通过 Windows 进行身份验证：

$ Conn = new PDO ("odbc: Driver = {SQL Server}; Server = JAMILI-PC \ SQLEXPRESS; null; null");

$ Conn = new PDO("odbc: Driver = {SQL Server}; Server = JAMILI-PC\SQLEXPRESS; null; null");

I am using Windows 2008.

我正在使用 Windows 2008。

I hope it solves your problem.

我希望它能解决你的问题。