Unable to insert data into MS SQL Server using PHP in IIS

Hi,

I guess that it is a permission problem. First check this by inserting this at the beginning of your php script.

echo "Server Software: ".$_SERVER['SERVER_SOFTWARE']."<br>"."Server builtin account: ".exec('whoami')."<br><br>";

Take that user that is indicated and go to IIS Manager - Application Pool. There you change the corresponding user and change from ApplicationPoolIdentity -> advanded settings.

You can see here what I changed to get access via IIS 7.5 to a certain ODBC DNS by changing the default values of ASP.NET 1.1:

You could use SQL-Server-Authentication or Windows-Authentication.

You could then use local/system. This will make your application run under NT AUTHORITY\SYSTEM, which is an existing login for the sql database by default or change it to what you prefer. This is the simple way, but naturally you have to consider security too. That then needs further steps. This is only to make it work.

See here the SQL-server-side permissions as an image.

There are some other prerequisites too, which could perhaps be of importance:

Check PDO support in phpinfo(): "sqlsrv" should be in "enable" list. Connection String: sqlsrv:Server=xxxxxxxxxxx;Database=cTest

PHP Manager in IIS 7.0 Goto PHP Manager ->PHP Extensions->Enable of Disable Extensions ->Disable List Enable your PHP_PDO_SQLSRV_5(xx)_nts.dll.

And it could - in combination with the php versionon you use - depend on the Microsoft Drivers: 3.1, to 2.0 dll files

See https://msdn.microsoft.com/en-us/library/cc296170%28v=sql.105%29.aspx

And - if you have to pass a firewall - open port 1433

But I guess that these prerequisites are all met in your environment, for if you get no php error output. IIS simply hangs in a permission problem.

OK - I read your amendment 1 The first thing is the question what user is indicated from your php script.

exec('whoami');

Tell me that please , if you would be so kind. I need that for the further explanation.

OK - I read your amendment 2 NT_AUTHORITY/iusr - ahhh OK - he belongs to IIS_IUSRS group. That is a little bit strange, that IIS 7.5 runs below IUSR. But OK let me reflect a little while. ... It is this one here

I never did that for IUSR, but let us try it out. Remember all steps for roll-back.

First go to IIS Manager, click Application Pools and please send me the screenshot of the indicated right side ( the "users"). Normally it would be ASP.NET 1.1. and not IUSR. This is highly insecure!

I need the screenshot, for something very strange seems to be set in your Application Pool identity settings.

See here my message on the try to set IUSR as identity:

I have to reflect. Please wait a moment. There is something very strange.

OK - I saw your screenshot and amendment 3

IIS uses IUSR because of PDO permissions - that is OK. So we make it from the other side of SQL Server permissions:

1 Add 'NT AUTHORITY\IUSR' user to security of SQL

2 Set the server role as public or DBcreator and set the mapping of the database(membership of db_owner) which the user can access to as well

Do you know where to make that?

If not - we can try a very simple thing in DefaultAppPool settings. I'm now out of office for two hours.

You can simply try that for the first. It could run if your database permissions include NT_AUTORITY/SYSTEM, which is normally included.

You set this in advanced settings of DefaultAppPool

This could simply make it run if the SQL-Server side has these permissions - I guess that it is so.

OK - I read your amendment 4

Now set your connection string user name and password to NULL

$conn = new PDO("sqlsrv:Server=$serverName;Database=CTest", NULL, NULL);

And change this in SQL Management:

I tried it out for SQL EXPRESS:

echo "Server Software: ".$_SERVER['SERVER_SOFTWARE']."<br>"."Server builtin account: ".exec('whoami')."<br><br>";
$serverName = "RWTH-SAP1\SQLEXPRESS,1433";
try {
    $conn = new PDO("sqlsrv:Server=$serverName\SQLEXPRESS;Database=master",NULL, NULL);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e) {
    echo $e;
    die("Error connecting to SQL Server");
}

echo "Connected to SQL Server\n";

And I tried it out for SQL Server 2008 R2

<?php
echo "Server Software: ".$_SERVER['SERVER_SOFTWARE']."<br>"."Server builtin account: ".exec('whoami')."<br><br>";
try {
        $conn = new PDO("sqlsrv:Server=RWTH-SAP1\RWTH-SAP1,1433;Database=ECC", NULL, NULL);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        echo "Connected to SQL Server\n";
    } catch (PDOException $e) {
        echo $e;
        die("Error connecting to SQL Server");
    }


?>

It works in my environment with the settings that we tried out. Can you test it with the corresponding connection string too?

$conn = new PDO("sqlsrv:Server=$serverName\xxxxxxxx;Database=xxxx",NULL, NULL);

<?php
echo "Server Software: ".$_SERVER['SERVER_SOFTWARE']."<br>"."Server builtin account: ".exec('whoami')."<br><br>";
try {
        $conn = new PDO("sqlsrv:Server=RWTH-SAP1\SQLEXPRESS;Database=", NULL, NULL);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        echo "Connected to SQL Server<br>";
        $sqlDB = "CREATE DATABASE testStackoverflow";
        $connSQL = $conn->exec($sqlDB);
        echo "Database Created<br>"; 
        $conn->query("use testStackoverflow");

        // sql to create table
        $sql = "CREATE TABLE MyGuests (
        firstname VARCHAR(30) ,
        lastname VARCHAR(30)  ,
        email VARCHAR(50)     ,
        reg_date TIMESTAMP
        );";
        // use exec() because no results are returned
        $conn->exec($sql);
        echo "Table MyGuests created successfully";
    }
catch (PDOException $e) {
        echo $e;
        die("Error connecting to SQL Server");
    }
$conn = null;
?>

Debug is ok too. So all PDO things work.

What is important too, is that you can change your PHP APP_POOL_ID, which is in my case:

_SERVER["APP_POOL_ID"]  ASP.NET 1.1  

here:

_SERVER["APP_POOL_CONFIG"]  C:\inetpub\temp\apppools\ASP.NET 1.1\ASP.NET 1.1.config   

Best regards

Axel Arnold Bangert - Herzogenrath 2016