Загрузка...Sqlsrv connect php
sqlsrv_connect — Opens a connection to a Microsoft SQL Server database
(Информация о версии неизвестна, возможно, только в SVN)
sqlsrv_connect — Opens a connection to a Microsoft SQL Server database
Описание
Opens a connection to a Microsoft SQL Server database. By default, the connection is attempted using Windows Authentication. To connect using SQL Server Authentication, include «UID» and «PWD» in the connection options array.
Список параметров
The name of the server to which a connection is established. To connect to a specific instance, follow the server name with a backward slash and the instance name (e.g. serverNamesqlexpress).
An associative array that specifies options for connecting to the server. If values for the UID and PWD keys are not specified, the connection will be attempted using Windows Authentication. For a complete list of supported keys, see » SQLSRV Connection Options.
Возвращаемые значения
A connection resource. If a connection cannot be successfully opened, FALSE is returned.
Примеры
Пример #1 Connect using Windows Authentication.
// 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.
» ;
>else <
echo «Connection could not be established.
» ;
die( print_r ( sqlsrv_errors (), true ));
>
?>
Пример #2 Connect by specifying a user name and password.
= «serverNamesqlexpress» ; //serverNameinstanceName
$connectionInfo = array( «Database» => «dbName» , «UID» => «userName» , «PWD» => «password» );
$conn = sqlsrv_connect ( $serverName , $connectionInfo );
if( $conn ) <
echo «Connection established.
» ;
>else <
echo «Connection could not be established.
» ;
die( print_r ( sqlsrv_errors (), true ));
>
?>
Пример #3 Connect on a specifed port.
= «serverNamesqlexpress, 1542» ; //serverNameinstanceName, portNumber (default is 1433)
$connectionInfo = array( «Database» => «dbName» , «UID» => «userName» , «PWD» => «password» );
$conn = sqlsrv_connect ( $serverName , $connectionInfo );
if( $conn ) <
echo «Connection established.
» ;
>else <
echo «Connection could not be established.
» ;
die( print_r ( sqlsrv_errors (), true ));
>
?>
Примечания
By default, the sqlsrv_connect() uses connection pooling to improve connection performance. To turn off connection pooling (i.e. force a new connection on each call), set the «ConnectionPooling» option in the $connectionOptions array to 0 (or FALSE ). For more information, see » SQLSRV Connection Pooling.
The SQLSRV extension does not have a dedicated function for changing which database is connected to. The target database is specified in the $connectionOptions array that is passed to sqlsrv_connect. To change the database on an open connection, execute the following query «USE dbName» (e.g. sqlsrv_query($conn, «USE dbName»)).
Смотрите также
- sqlsrv_close() — Closes an open connection and releases resourses associated with the connection
- sqlsrv_errors() — Returns error and warning information about the last SQLSRV operation performed
- sqlsrv_query() — Prepares and executes a query.
sqlsrv_connect: Windows authentication
I have this PHP code running under IIS:
When I try to visit the page, I get the error:
[Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’.
Which is confusing, as the documentation here says «The connection will be attempted using Windows Authentication».
The flow of this is:
- User using x Windows account on workstation1 visits this website hosted on server1
- server1 executes above PHP code and tries to retrieve data from server2 using y Windows account ( y is defined in the IIS settings, under Application Pools->Identity)
- Windows account y has access to the database
The IIS pool is running under a Windows domain user «domainadm» which has access to the SOME_DB database and the server it sits on.
Why is it trying to authenticate anonymously and how can I fix this so it runs under the user the IIS pool is running as?
Checking the Authentication section in IIS, the only options I have are «Anonymous Authentication» or «ASP.NET Impersonation». No Windows authentication?
Anonymous is currently enabled, which gives the error above. If I switch to the ASP.NET one I instead get this error when visiting the page:
500 — Internal server error. There is a problem with the resource you are looking for, and it cannot be displayed. An ASP.NET setting has been detected that does not apply in Integrated managed pipeline mode.
My updated web.config:
3 Answers 3
The issue is IIS is sending the connections anonymously to SQL despite the AppPool identity you are running as. To resolve this, you have to enable anonymous connections from the web server (server1) to the SQL server (server2).
On your SQL server, create a login for server1$ and assign appropriate permissions to Some_DB under the User Mappings tab. For example, give this account db_datareader and db_datawriter rights and you should be able to connect and successfully complete CRUD operations. For the sake of testing, you may just want to grant this login db_owner rights on Some_DB temporarily to ensure SQL permissions are not at issue.
With respect to the PHP fastcgi.impersonate setting — I believe you can set this to 0/false as it isn’t going to matter in this case.
In IIS, in the authentication section, enable Windows authentication and ASP.NET Impersonation.
Those will be the only authentication types that will be enabled in IIS.
In the web.config file, you will want the following (this should solve the problem if Windows not appearing):
The reason for the identity.impersonate is that you will need it to impersonate the logged in user credentials through windows.
Once the user is authenticated, you can use anonymous authentication to access the database.
For example to prove the above works, in your database go to Security — Logins on SQL Server and Add Login.
Add: NT AUTHORITYANONYMOUS LOGON
and assign it dboOwner role to ensure it is working.
Doing the above will prove that there are no other conflicts with the way that IIS is set up — e.g. incorrect web.config entries.
Make sure the app pool is running in integrated mode and that you have Integrated Security=true in your database connection string.
You can then assign a different account if necessary e.g. your «domainadm» account a service account to run the app pool under. But there may be issues if the user is not on the same domain and other issues that could arise because of the way that the server/accounts are configured that could potentially cause this to fail. But trying the above steps will get it running so that you could diagnose those issues if necessary.
The suggestions for Windows authentication or impersonation here are not applicable to your case. Windows auth would be for the client computer «x» to auth with your front end «server1», and impersonation would be used to forward that credential «x» to SQL «server2». That would require every user using the site be given SQL access. You instead want anonymous access to your site, and for the site to manage its own access to SQL.
Problem Summary: The default user for Anonymous Auth is IUSR, regardless of what your app pool setting is. When it tries to connect to a network resource, it authenticates as «NT AUTHORITYANONYMOUS LOGIN». Obviously, that’s not very useful when trying to lock down access.
Note: The IUSR account is similar to LOCALSERVICE in the manner in which it acts anonymously on the network. The NETWORKSERVICE and LOCALSYSTEM accounts can act as the machine identity, but the IUSR account cannot because it would require an elevation of user rights. If you need the anonymous account to have rights on the network, you must create a new user account and set the user name and password manually, as you did in the past for anonymous authentication.
Solution: Go to the Authentication section, select Anonymous Authentication, click Edit. and change from «Specific User: IUSR» to «Application pool identity». That should force the site to use the account you’ve set for the app pool.
Once done, if you are using the default app pool setting of «Application Pool Identity», your application will attempt to auth with SQL as DOMAINMACHINENAME.
sqlsrv_connect
(No version information available, might only be in Git)
sqlsrv_connect — Opens a connection to a Microsoft SQL Server database
Описание
Opens a connection to a Microsoft SQL Server database. By default, the connection is attempted using Windows Authentication. To connect using SQL Server Authentication, include «UID» and «PWD» in the connection options array.
Список параметров
The name of the server to which a connection is established. To connect to a specific instance, follow the server name with a backward slash and the instance name (e.g. serverNamesqlexpress).
An associative array that specifies options for connecting to the server. If values for the UID and PWD keys are not specified, the connection will be attempted using Windows Authentication. For a complete list of supported keys, see » SQLSRV Connection Options.
Возвращаемые значения
A connection resource. If a connection cannot be successfully opened, FALSE is returned.
Примеры
Пример #1 Connect using Windows Authentication.
// 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.
» ;
>else <
echo «Connection could not be established.
» ;
die( print_r ( sqlsrv_errors (), true ));
>
?>
Пример #2 Connect by specifying a user name and password.
= «serverName\sqlexpress» ; //serverNameinstanceName
$connectionInfo = array( «Database» => «dbName» , «UID» => «userName» , «PWD» => «password» );
$conn = sqlsrv_connect ( $serverName , $connectionInfo );
if( $conn ) <
echo «Connection established.
» ;
>else <
echo «Connection could not be established.
» ;
die( print_r ( sqlsrv_errors (), true ));
>
?>
Пример #3 Connect on a specified port.
= «serverName\sqlexpress, 1542» ; //serverNameinstanceName, portNumber (default is 1433)
$connectionInfo = array( «Database» => «dbName» , «UID» => «userName» , «PWD» => «password» );
$conn = sqlsrv_connect ( $serverName , $connectionInfo );
if( $conn ) <
echo «Connection established.
» ;
>else <
echo «Connection could not be established.
» ;
die( print_r ( sqlsrv_errors (), true ));
>
?>
Примечания
By default, the sqlsrv_connect() uses connection pooling to improve connection performance. To turn off connection pooling (i.e. force a new connection on each call), set the «ConnectionPooling» option in the $connectionOptions array to 0 (or FALSE ). For more information, see » SQLSRV Connection Pooling.
The SQLSRV extension does not have a dedicated function for changing which database is connected to. The target database is specified in the $connectionOptions array that is passed to sqlsrv_connect. To change the database on an open connection, execute the following query «USE dbName» (e.g. sqlsrv_query($conn, «USE dbName»)).
Смотрите также
- sqlsrv_close() — Closes an open connection and releases resourses associated with the connection
- sqlsrv_errors() — Returns error and warning information about the last SQLSRV operation performed
- sqlsrv_query() — Prepares and executes a query
Как подключиться к Microsoft SQL Server через PHP
В этой статье мы расскажем о всех тонкостях подключения к MS SQL серверу, для работы с ним через PHP .
Мы будем рассматривать SQL Server 2014 . На ранних версиях этот процесс может немного отличаться.
Давайте определим, что мы уже имеет установленный пакет SQL Server на каком то VPS или сервере с ОС Windows (Например, заказанного у ihc.ru), либо установленный на вашем локальном компьютере для тестов.
Так же соответственно на сервере стоит PHP версии 5.2+ или у вас установлен локальный сервер, вроде OpenServer .
Теперь нужно скачать и установить PHP driver для SQL Server — вот ссылка на официальный сайт.
На данный момент там есть четыре версии драйвера 2.0, 3.0, 3.1, 3.2. Далее приведена таблица с совместимостью с PHP :
| Microsoft Drivers for PHP for SQL Server Version | Supported PHP Versions |
|---|---|
| Microsoft Drivers for PHP for SQL Server Version | Supported Windows Server |
|---|---|
