PDO_SQLSRV DSN

(PECL pdo_sqlsrv >= 2.0.1)

PDO_SQLSRV DSN — Connecting to MS SQL Server and SQL Azure databases

说明

The PDO_SQLSRV Data Source Name (DSN) is composed of the following elements:

DSN prefix: The DSN prefix is sqlsrv:.
APP: The application name used in tracing.
ConnectionPooling: Specifies whether the connection is assigned from a connection pool (1 or true) or not (0 or false).
Database: The name of the database.
Encrypt: Specifies whether the communication with SQL Server is encrypted (1 or true) or unencrypted (0 or false).
Failover_Partner: Specifies the server and instance of the database's mirror (if enabled and configured) to use when the primary server is unavailable.
LoginTimeout: Specifies the number of seconds to wait before failing the connection attempt.
MultipleActiveResultSets: Disables or explicitly enables support for multiple active Result sets (MARS).
QuotedId: Specifies whether to use SQL-92 rules for quoted identifiers (1 or true) or to use legacy Transact-SQL rules (0 or false).
Server: The name of the database server.
TraceFile: Specifies the path for the file used for trace data.
TraceOn: Specifies whether ODBC tracing is enabled (1 or true) or disabled (0 or false) for the connection being established.
TransactionIsolation: Specifies the transaction isolation level. The accepted values for this option are PDO::SQLSRV_TXN_READ_UNCOMMITTED, PDO::SQLSRV_TXN_READ_COMMITTED, PDO::SQLSRV_TXN_REPEATABLE_READ, PDO::SQLSRV_TXN_SNAPSHOT, and PDO::SQLSRV_TXN_SERIALIZABLE.
TrustServerCertificate: Specifies whether the client should trust (1 or true) or reject (0 or false) a self-signed server certificate.
WSID: Specifies the name of the computer for tracing.

范例

示例 #1 PDO_SQLSRV DSN examples

The following example shows how to connecto to a specified MS SQL Server database:

$c = new PDO("sqlsrv:Server=localhost;Database=testdb", "UserName", "Password");

The following example shows how to connect to a MS SQL Server database on a specified port:

$c = new PDO("sqlsrv:Server=localhost,1521;Database=testdb", "UserName", "Password");

The following example shows how to connecto to a SQL Azure database with server ID 12345abcde. Note that when you connect to SQL Azure with PDO, your username will be UserName@12345abcde (UserName@ServerId).

$c = new PDO("sqlsrv:Server=12345abcde.database.windows.net;Database=testdb", "UserName@12345abcde", "Password");

add a note

User Contributed Notes 4 notes

down

Daniel Klein ¶

4 years ago


This is the code that worked for me. I had to specify both the host name [localhost] and the server instance name [SQLEXPRESS], separating them with a double backslash before it would work.

<?php
$conn = new PDO('sqlsrv:Server=localhost\\SQLEXPRESS;Database=MyDatabase', 'MyUsername', 'MyPassword');
?>

down

david at nospam-rm-this dot functionalchaos dot net ¶

8 years ago


I found with MS-SQL Server 2008 on Windows Server 2008 R2 that I needed to use the MSSQL Server name. The servers hostname or IP address would not work ( with or without port, and/or SQL instance name, etc.) After trying several combinations, this is what I found worked in my configuration:

host-ip: 10.4.2.50
host-name: mssqlhost.mydomain.org
mssql-server-name: foo-sql
mssql-instance-name: MSSQLSERVER
database-name: mydb

<?php

$dbh = new PDO("sqlsrv:Server=foo-sql,1433;Database=mydb", $user , $pass);

?>

The above worked with and without port (,1433). I also noticed adding the instance name (MSSQLSERVER) after the server name with a slash (\), as in "Server=foo-sql\MSSQLSERVER" caused a failure to connect.

down

-2

support at spam eaccounts trap net ¶

8 years ago


I suspect the problem with "Server=foo-sql\MSSQLSERVER" was you didn't escape the backslash, with a backslash.

If hostname worked, than IP would work as well given a hostname resolves to an IP unless your DNS was mapping to the wrong IP which would be a simple issue.

I suspect you are running your web services on the same host as mssql?  If so, confirm that your mssql server is set to bind to IP addresses (if you want access from the outside of that box).  If you are only able to connect via a name (as in a NetBIOS or Active Directory name), it's likely you are connecting to a "socket" or named pipe.

down

-51

alasdair at angryloner dot com dot au ¶

8 years ago


Adding the backslash solved the problem for me.

Here's my functioning localdb connection string:

new PDO("sqlsrv:Server=(localdb)\\v11.0 ; Database = my_db ; AttachDBFilename = C:\Users\user\my_db.mdf", "", "");

add a note

官方地址：https://www.php.net/manual/en/ref.pdo-sqlsrv.connection.php

有任何技术问题请点击这里网站运营推广招聘

IT PHP 编程语言 Linux 开发编程科技 Elasticsearch HTML/CSS/XML 网络数据库 JAVA 面试 NoSQL C/C++ Golang Git 算法操作系统正则表达式 Redis 互联网 MySql JavaScript 运维软件国际架构设计 Mac OS TCP/IP Excel Vim Windows Socket Oracle VR MongoDB Python 运营 MemCache 商业硬件电子娱乐设计 nginx 摄影游戏 WordPress HTTP 团建数码电器

php7 安装fileinfo扩展 preg_split — 通过一个正则表达式分隔字符串 php位值,解决 PHP 中 usort 在值相同时改变原始位置的问题 [PHP] inet_pton/inet_ntop IP地址转换函数 [鸟哥]PHP_INT_MIN 和 -9223372036854775808 PHP 8.1 ADORecordSet对象 adodb手册 opcache预加载 Composer的Packagist资源 array_multisort() 多字段排序 PHPMailer设置utf8 PHPMailer字符集CharSet mysql面试题 PHP json解析（json_decode）页面工具 [原创]诡异的php-fpm超时问题 php中几种注释规范支持php7的性能采集扩展纯php实现DES以及TripleDES加密算法 php 修改图片大小 PHP mkdir()写出来的权限与mode值不符合

略微加速

PHP官方手册 - 互联网笔记

PDO_SQLSRV DSN

说明

范例

User Contributed Notes 4 notes