PDO_SQLSRV DSN

(Информация о версии неизвестна, возможно, только в SVN)

PDO_SQLSRV DSNПодключение к базам данных MS SQL Server и SQL Azure

Описание

Строка источника данных (Data Source Name, DSN) для PDO_SQLSRV состоит из следующих элементов:

Префикс DSN

Префикс DSN равен sqlsrv:.

APP

Имя приложения, используемое при трассировке.

ConnectionPooling

Определяет, берется ли соединение из пула соединений (1 или TRUE) или нет (0 или FALSE).

Database

Имя базы данных.

Encrypt

Определяет, будут ли шифроваться данные коммуникации с SQL Server (1 или TRUE) или не будут (0 или FALSE).

Failover_Partner

Определяет сервер и экземпляр зеркала базы данных (если включено и настроено) в случае недоступности первичного сервера.

LoginTimeout

Определяет время ожидания подключения (в секундах).

MultipleActiveResultSets

Отключает или явно включает поддержку функции Multiple Active Result Sets (MARS) - возвращение нескольких результирующих наборов.

QuotedId

Определяет, использовать для заключения в кавычки идентификаторов стандарт SQL-92 (1 или TRUE) или правила, задаваемые Transact-SQL (0 или FALSE).

Server

Имя сервера базы данных.

TraceFile

Определяет путь до файла, используемого для данных трассировки.

TraceOn

Определяет, включена ли для создаваемого соединения функция трассировки ODBC (1 или TRUE) или отключена (0 или FALSE).

TransactionIsolation

Определяет уровень изоляции транзакций. Допустимые значения данной опции - PDO::SQLSRV_TXN_READ_UNCOMMITTED, PDO::SQLSRV_TXN_READ_COMMITTED, PDO::SQLSRV_TXN_REPEATABLE_READ, PDO::SQLSRV_TXN_SNAPSHOT и PDO::SQLSRV_TXN_SERIALIZABLE.

TrustServerCertificate

Определяет, должен ли клиент принимать (1 или TRUE) или отклонять (0 or FALSE) самозаверенные (self-signed) сертификаты сервера.

WSID

Определяет имя компьютера для трассировки.

Примеры

Пример #1 Примеры PDO_SQLSRV DSN

Следующий пример показывает, как подключаться к определенной базе данных MS SQL Server:

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

Следующий пример показывает, как подключаться к базе данных MS SQL Server по определенному порту:

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

Следующий пример показывает, как подключаться к базе данных SQL Azure с идентификатором сервера 12345abcde. Примечание: при соединении к SQL Azure с помощью PDO, имя пользователя будет равно UserName@12345abcde (UserName@ServerId).

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

add a note add a note

User Contributed Notes 5 notes

up
5
support at spam eaccounts trap net
3 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.
up
1
Daniel Klein
6 months 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');
?>
up
1
php at zootal dot com
1 month ago
I finally discovered that sqlsrv and pdo_sqlsrv are not the same thing, and work differently, and have different requirements. As near as I can tell, sqlsrv requires the odbc driver from Microsoft. pdo_sqlsrv does not, it uses pdo. This caused me a lot of grief until I figured this out, as most of the discussion for pdo_sqlsrv seems to relate to sqlsrv, not pdo_sqlsrv. They are not the same.

pecl install sqlsrv

This will download and build the module for sqlsrv-4.3.0.tgz. This will not work without the microsoft ODBC driver, which apparently needs to be downloaded and installed separately. I was never able to get this to work as my distro is not one of the supported distros the Micorosoft builds the ODBC driver for.

pecl install pdo_sqlsrv

This downloads and installs the module for pdo_sqlsrv-4.3.0.tgz. This does NOT require the Microsoft ODBC driver. Why? Because it does not use ODBC, it uses PDO to connect to mssql server.

To make a connection using pdo_sqlsrv (without the Microsoft ODBC driver):

$dbh = new PDO ("dblib:host=<ip address>;dbname=db","user_id","password");

Note that we are using "dblib:host", NOT "sqlsrv:host".

So to clarify: In my case, I'm using PHP 7.2 and I want to connect to a Microsoft sql server database. I'm using Slackware 14.2 64 bit, and Microsoft does not build an ODBC driver that works (at least I could not get it to work) for this distro.

1) Install unixODBC. I had to download the source and ./configure|make|make install, as the version that came with the package manager did not work. Don't be afraid to go outside of your package manager and install unixODBC from scratch.
2) When you configure PHP, be sure to include --with-pdo-odbc=unixODBC
3) After you install PHP, use PECL to download and install pdo_sqlsrv. Do NOT install sqlsrv. And be sure to add extension-pdo_sqlsrv to php.ini if necessary, the PECL installer doesn't always do this.

Now you should be able to to do this:

$dbh = new PDO ("dblib:host=<ip address>;dbname=db","user_id","password");

When I do the steps above, it works great. YMMV. I welcome comments from anyone that knows more about this process.
up
0
david at nospam-rm-this dot functionalchaos dot net
4 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.
up
-6
alasdair at angryloner dot com dot au
3 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", "", "");
To Top