Connecting via PHP to SQL Server using a Windows account

Question

I am trying to connect using PHP to SQL Server on another machine. I found two ways to do this. Either with odbc_connect or sqlsrvr connect.

$connection = odbc_connect("Driver={SQL Server Native Client 10.0};Server=$serverName;Database=$db;", 'user', 'pass'); 

or

  $conn = sqlsrv_connect($serverName, array('UID' => '', 'PWD' => '')); 

The connection works if I try to connect to an SQL account. Unfortunately, I am unable to contact using Windows Authentication.

So far I have tried the following: used a work pass, added Trusted_Connection = yes; or Integrated Security = SSPI ;, I also tried combining this with fastcgi.impersonate = 1 or 0.

When I use my user directly and pass, I get an login-dependent error, and when I try to authenticate Windows with sspi, I get the following error: Login failed for user "NT AUTHORITY \ ANONYMOUS LOGON".

I searched a lot for the problem, but could not find a solution. However, some people have said that php is not using a Network Service account, and that this could be a problem.

Does anyone know how I can fix this or perhaps give me an edge?

Thanks in advance.

Answer 1

basically the problem is that the identifier that is used to connect to the server will always be the identifier of the process in which PHP is running

This link can help you.

Answer 2

I managed to establish a reliable connection to a remote SQL server with PHP running on a separate IIS server using:

 $connectionInfo = array('Database' => $dbname); $con = sqlsrv_connect($servername,$connectionInfo); 

In php.ini:

 fastcgi.impersonate = 0 mssql.secure_connection = On 

In IIS for my PHP site:

 Anonymous Authentication = Disabled Windows Authentication = Enabled Application Pool settings for site: Integrated, Network Service 

SQL Server database protection for $ dbname database:

 create login: domainname\iisservername$ map domainname\iisservername$ to db_datareader privilege for $dbname database 

Note. It is important to know the meaning of $ in iisservername. With the above settings, IIS will establish a reliable connection to the SQL server without the need to store user and password information in your PHP file. Locally, the permissions used by the application pool are Network Service. Remotely, these permissions pass through the network as a user domain name \ iisservername $.

Answer 3

For PHP on IIS 7.5 to connect without a username and password using SSPI authentication. I had to do the following:

In my additional settings for the IIS application pool, I installed it to use the domain \ username user account for the user who I needed PHP to connect to.

In my specific IIS website, I changed the basic settings for connecting as the same domain \ username.

In my PHP code, I could connect using $conn = sqlsrv_connect($dbhost, array("Database" => $dbname, "UID" => "", "PWD" => ""));

Other notes: fastcgi.impersonate = 1 And I only have: Anonymous authentication enabled *

Answer 4

Have you tried $conn = sqlsrv_connect($serverName);

An array is optional. Authentication is the default. See MSDN Article

IIS must have Windows authentication and anonymous access disabled.

Answer 5

• Open the IIS Console
• Configure your application pool id with the right user, which must have sufficient permissions in your SQL Server + database
• Choose your "specific site"
• Open the Authentication Settings
• Right-click Anonymous Authentication and click Modify
• The last time you select the option: Application pool identifier (radio button) and click OK to apply the changes.

Answer 6

1. Open the IIS Console
2. Go to your website
3. Open the Authentication settings. Right-click on Anonymous Authentication and click Edit. Lastly, select the option: Application pool identifier (switch) and click OK to apply the changes.
4. Go to "Application Pools" and get the name of the application pool.
5. Open SSMS (SQL Management Studio), log into the appropriate database and select "Security."
6. Add a new user, fill in only the name and save. Username must be: IIS APPPOOL \ {YOUR_APP_POOL_NAME}

Good luck