无法通过 PHP 连接到 msSQL 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/469964/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Unable to connect to msSQL database via PHP
提问by
I am using the current code in attempt to access a msSQL 2005 db:
我正在使用当前代码尝试访问 msSQL 2005 db：
<?php
$myServer = "[server]";
$myUser = "[username]";
$myPass = "[password]";
$myDB = "[db]";
//connection to the database
$dbhandle = mssql_connect($myServer, $myUser, $myPass)
or die("Couldn't connect to SQL Server on $myServer");
//select a database to work with
$selected = mssql_select_db($myDB, $dbhandle)
or die("Couldn't open database $myDB");
//declare the SQL statement that will query the database
$query = "SELECT id, name, year ";
$query .= "FROM cars ";
$query .= "WHERE name='BMW'";
//execute the SQL query and return records
$result = mssql_query($query);
$numRows = mssql_num_rows($result);
echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>";
//display the results
while($row = mssql_fetch_array($result))
{
echo "<li>" . $row["id"] . $row["name"] . $row["year"] . "</li>";
}
//close the connection
mssql_close($dbhandle);
?>
It is returning the following:
它返回以下内容：
Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: XXXXXXX in D:\xxxxx.xxx\xxxx.php on line 16
Couldn't connect to SQL Server on XXXXXXX
What do you think the problem is?
你认为问题是什么？
回答by Dalin Seivewright
It sounds to me like one of your DLLs is the wrong version. There was an issue of some sort with the move from SQL2000 to SQL2005 that the creators of PHP didn't resolve themselves. There are a variety of posts about it here: the following link
在我看来，您的 DLL 之一版本错误。从 SQL2000 迁移到 SQL2005 存在某种问题，PHP 的创建者没有自行解决。这里有很多关于它的帖子： 以下链接
I believe the DLL is ntwdblib.dll and the version needs to be version 2000.80.194.0 at the very least. If you're running Apache or WampServer, there is an identical dll where the Apache DLLs are stored that needs to be overwritten.
我相信 DLL 是 ntwdblib.dll 并且版本至少需要是 2000.80.194.0 版本。如果您正在运行 Apache 或 WampServer，则有一个完全相同的 dll 用于存储需要覆盖的 Apache DLL。
Note: I was having this issue a few days ago and finding the correct DLLs and overwriting both allowed it to work.
注意：几天前我遇到了这个问题，找到正确的 DLL 并覆盖都允许它工作。
Also: You may need to setup remote connections. Sql Server 2005 has remote connections disabled by default. You can allow remote connections by running the SQL Surface Area Configuration utility.
另外：您可能需要设置远程连接。默认情况下，Sql Server 2005 禁用远程连接。您可以通过运行 SQL Surface Area Configuration 实用程序来允许远程连接。
回答by Greg
Try calling mssql_get_last_message()to get the last error message:
尝试调用mssql_get_last_message()以获取最后一条错误消息：
$dbhandle = mssql_connect($myServer, $myUser, $myPass)
or die("Couldn't connect to SQL Server on $myServer. Error: " . mssql_get_last_message());
回答by oabarca
stop using
停止使用
mssql_connect
mssql_connect
and start using
并开始使用
sqlsrv_connect
sqlsrv_connect
That will save you a lot of headaches. Plus, the function *mssql_connect* has been deprecated.
这将为您省去很多麻烦。另外，函数 *mssql_connect* 已被弃用。
For using sqlsrv_connect, you must download the driver and install it as an extension for PHP to recognize the sqlsrvfunctions. Download the driver from Microsoft Download Center (search for "sql server php driver"), at the time of this post the download url was: http://www.microsoft.com/en-us/download/details.aspx?id=20098
要使用 sqlsrv_connect，您必须下载驱动程序并将其安装为 PHP 的扩展以识别sqlsrv函数。从 Microsoft 下载中心下载驱动程序（搜索“sql server php driver”），在本文发布时，下载 url 为：http: //www.microsoft.com/en-us/download/details.aspx?id =20098
The right steps for installation are clearly explained by Microsoft itself at http://www.microsoft.com/en-us/download/details.aspx?id=20098
Microsoft 在http://www.microsoft.com/en-us/download/details.aspx?id=20098上清楚地解释了正确的安装步骤
- download the driver. 2. put it in the PHP ext folder. 3. update the php.ini 4. restart the server.
- 下载驱动程序。2.放在PHP ext文件夹中。3.更新php.ini 4.重启服务器。
After installing the sql server driver, then simply follow the instructions from http://www.php.net/manual/en/function.sqlsrv-connect.php. Below a quick snippet:
安装 sql 服务器驱动程序后，只需按照http://www.php.net/manual/en/function.sqlsrv-connect.php 中的说明进行操作 。下面是一个快速片段：
<?php
$serverName = "serverName\sqlexpress"; //serverName\instanceName
// Since UID and PWD are not specified in the $connectionInfo array,
// The connection will be attempted using Windows Authentication.
$connectionInfo = array( "Database"=>"dbName");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn ) {
echo "Connection established.<br />";
}else{
echo "Connection could not be established.<br />";
die( print_r( sqlsrv_errors(), true));
}
?>
Vote up!
投票！
回答by r.paul ?
Download the driver in this site http://www.microsoft.com/en-us/download/details.aspx?id=20098, then open your php.ini file and add the DLL that you download.
在这个站点下载驱动程序http://www.microsoft.com/en-us/download/details.aspx?id=20098，然后打开你的 php.ini 文件并添加你下载的 DLL。
回答by Developer
Late response, but it might help someone.
we are nearly there, it is the problem with your connection parameters,
probably you need to give servername as IP:port
迟到的回应，但它可能会帮助某人。我们快到了，这是您的连接参数的问题，可能您需要将 servername 提供为IP:port
servername::The MS SQL server. e.g. hostname:port (Linux), or hostname,port (Windows).
servername::MS SQL 服务器。例如hostname:port (Linux), or hostname,port (Windows).
Server Name should be "server\instance"
An instance is nothing more than specific port address different than 1433... so just discover the port on mssql server and then try to connect using: ip:port
服务器名称应该是“服务器\实例”一个实例只不过是与 1433 不同的特定端口地址......所以只需在 mssql 服务器上发现端口，然后尝试使用以下方法连接： ip:port
Example code that works completely fine for me::
对我来说完全正常的示例代码::
ini_set('display_errors', '1');
// $myServer = "winsrv22.somedns.co.uk:22320";//this style works as well
$servername = "123.21.47.23:22320";
$myUser = "UserName";
$myPass = 'xxxxxxxx';
$myDB = "[database]";
//connection to the database
$dbhandle = mssql_connect($servername, $myUser, $myPass)
or die("Couldn'tt connect to SQL Server on $myServer");
if($dbhandle) {
echo "Success, Connected\r\n";
} else {
echo "problem :( \r\n";
}
Hope this helps some one :) Happy coding!!
希望这对某人有所帮助:) 快乐编码！！
回答by Templar
I had some difficulties with this a few months ago, and I found that the only way to make it work was to include the instance name when specifying the server. For example:
几个月前我遇到了一些困难，我发现让它工作的唯一方法是在指定服务器时包含实例名称。例如：
$myServer = "SERVER\INSTANCENAME";
Specifying just the server would not work, even with TCP/IP enabled.
即使启用了 TCP/IP，仅指定服务器也不起作用。
回答by user2178225
First try to do something like phpinfo()on your browser, than see which databases are allowed.
首先尝试phpinfo()在浏览器上执行类似操作，然后查看允许使用哪些数据库。
If you don't see anything like mssqlthen it is not configured. So use the odbc_connect()which will be configured...your connection string will be like this:
如果你没有看到类似的东西，mssql那么它没有被配置。因此，使用odbc_connect()将配置的...您的连接字符串将是这样的：
$server = '';
$user = '';
$password = '';
$database = '';
$connection = odbc_connect("Driver={SQL Server Native Client `11.0};Server=$server;Database=$database;", $user, $password);`
If you are using mssql 2005 or 2008, then change d 11.0 to 10.0 and for other versions just change to the version of your mssql.
如果您使用的是 mssql 2005 或 2008，则将 d 11.0 更改为 10.0，对于其他版本，只需更改为您的 mssql 版本。
回答by savageguy
Invalid credentials, if your not using localhost be sure you add the server's ip to the safe list.
凭据无效，如果您不使用 localhost，请确保将服务器的 ip 添加到安全列表中。
