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

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] [SQL Server的ODBC驱动程序11] [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] [SQL Server的ODBC驱动程序11]命名管道提供程序：无法打开与SQL Server的连接[53]。，SQL状态08001在SQLConnect中的C：\ Users ...“

Not sure what I'm doing wrong.

不知道我做错了什么。

my_odbc is 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).

my_odbc是另一台计算机上的SQL Server（2008）。我没有该数据库的管理员权限，因此我无法在此端更改任何内容（例如启用SQL Server身份验证）。
I am running Windows 7 and using PHP Version 5.6.12

我正在运行Windows 7并使用PHP 5.6.12版
phpinfo() indicates that ODBC Support is enabled as well as pdo_sqlsrv support

phpinfo（）表示启用了ODBC支持以及pdo_sqlsrv支持

6 个解决方案

#1

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数据库的其他boss软件）：

$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

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

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

#2

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....

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

#3

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 local it doesn't have any authorisation to access your remote SQL 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(...));"

#4

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
检查是否ping
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.
如果未启用，则启用TCP / IP连接SQL Server配置。为此，请打开SQL Server配置管理器 - > SQL Server网络配置 - > SQL Server的协议 - > TCP / IP（设置为启用）。重新启动SQL服务。
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 SQL and save.
打开端口，执行此操作Windows防火墙设置 - >例外 - >添加端口（名称：SQL;端口：1433; TCP）然后从例外勾选SQL并保存。
Enable running browser services.SQL Server Configuration Manager -> SQL Server Services - > SQL Server Browser set to running.
启用运行浏览器服务.SQL Server配置管理器 - > SQL Server服务 - > SQL Server Browser设置为运行。
defining Port in connection string
在连接字符串中定义端口
add instance name with machine name if more than one instance is being used
如果正在使用多个实例，请使用计算机名称添加实例名称

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-errors的连接

sql server provider named pipes provider error

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

Have a look at these links too

也看看这些链接

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

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

#5

Look at the regedit HKLM/SOFTWARE/ODBC

查看注册表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

然而，像服务器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.

然后，您必须通过ODBC驱动程序11替换SQL Server的SQL Server Native Client 11.0。

#6

-1

Enable in IIS on Manager PHP o PDO_ODBC:

在IIS上启用PHP o PDO_ODBC：

I have authenticated by Windows with following PHP statement:

我已通过Windows验证以下PHP语句：

$ 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.

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

#1