I have a Windows 2008 R2 Datacenter server with IIS 7.5, MS SQL 2012 Express, using PHP 5.6 trying to connect to a database I created, testDB, via Windows Authentication, but am failing to do so.
Here is my PHP I am using to connect to my SQL ServerInstance:
<?php
$serverName = "nmc-intranetintranetsql";
$connectionInfo = array("Database"=>"testDB", "UID"=>"username", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if ( $conn ) {
echo "Connection successful.<br />";
} else {
echo "Connection failed.<br />";
die( print_r( sqlsrv_errors(), true));
}
?>
When I run this I receive:
Connection failed. Array ( [0] => Array ( [0] => 28000 [SQLSTATE] => 28000 1 => 18456 [code] => 18456 2 => [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user ‘jaj’. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user ‘jaj’. ) 1 => Array ( [0] => 28000 [SQLSTATE] => 28000 1 => 18456 [code] => 18456 2 => [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user ‘jaj’. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user ‘jaj’. ) )
In MS SQL I have added the username to the Security > Logins and mapped testDB and gave dbo schema. User also has sysadmin Server Role. I can connect to testDB via CLI SQLCMD /S nmc-intranetintranetSQL /E /d testDB under the username. I can also connect to the ServerInstance via a remote SSMS session so I believe the remote connection properties are correct. I have verified the steps mentioned in the answer found here.
For IIS I have tried establishing the connection many different ways, but also unsuccessful there. So I have been assuming that is where my issue lies in the SQL login failed error. I have been changing the connection string via the IIS Manager under the NMC-Intranet Node and also the Default Web Site Node and failed there. My current connection string in web.config is what was in there by default after making changes in IIS Manger, and is as follows:
<connectionStrings>
<remove name="LocalSqlServer" />
<add connectionString="Server=nmc-intranet;Database=testDB;Integrated Security=true" name="LocalSqlServer" providerName="System.Data.SqlClient" />
</connectionStrings>
I have tried changing it to the following that I found here, and still have had no success.
<connectionStrings>
<add connectionString="Server=.SQLEXPRESS;Database=testDB;User ID=username;Password=password" name="LocalSqlServer" providerName="System.Data.SqlClient" />
</connectionStrings>
In IIS DefaultAppPool I have tried changing the Process Model Identity to the username/password there as well. I have been reading about Kerberos authentication here, but when the intranet is accessed with it enabled it prompts users for a username/password to even view the home page.
I hope I asked this question correctly as found here as previous questions were not well-received. I have done a lot of researching, but I do not know what I could be missing, and I have made so many changes and reverted them as they did not work I am just hoping for some direction of what I might need to try next. I have most likely misinterpreted something, as I do not have a complete understanding of how the connection needs to be established, and skipped a step or overlooked some information.
I am no professional here just trying to gather this all together from the internet, make it work, and hopefully learn something.
End game I am looking to have users log in to Intranet via AD, and upload files, create their own posts, create a directory of sorts that can be managed via the intranet, possibly have an IM(?), and really just the learning experience.
Thank you.
Advertisement
Answer
Solution:
If you want to connect to SQL Server using Windows authentication, remove UID and PWD connection options. When you use these options, then PHP Driver tries to connect using SQL Server authentication (I guees that jaj is the value for UID).
<?php
$serverName = "nmc-intranetintranetsql";
$connectionInfo = array(
"Database"=>"testDB"
);
$conn = sqlsrv_connect($serverName, $connectionInfo);
if ($conn === false) {
echo "Connection failed.<br />";
die( print_r( sqlsrv_errors(), true));
} else {
echo "Connection successful.<br />";
}
?>
Explanations:
The Microsoft Drivers for PHP for SQL Server can connect to SQL Server by using Windows Authentication or by using SQL Server Authentication. When you use Windows Authentication, then the Web server’s process identity or thread identity (if the Web server is using impersonation) is used to connect to the server, not an end-user’s identity. I use this script to get more information:
<?php
# ---------------------------------------
# SQL Server authentication
# ---------------------------------------
echo "SQL Server authentication"."</br>";
$server = 'serverinstance,port';
$cinfo = array(
"Database"=>'master',
"UID"=>'login',
"PWD"=>'password'
);
$conn = sqlsrv_connect($server, $cinfo);
if( $conn === false )
{
echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
exit;
}
$sql =
"SELECT 'SUSER_SNAME' AS [NAME], CONVERT(nvarchar(128), SUSER_SNAME()) AS [VALUE]".
"UNION ALL ".
"SELECT 'SUSER_NAME' AS [NAME], CONVERT(nvarchar(128), SUSER_NAME()) AS [VALUE]".
"UNION ALL ".
"SELECT 'USER_NAME' AS [NAME], CONVERT(nvarchar(128), USER_NAME()) AS [VALUE]".
"UNION ALL ".
"SELECT 'USER_ID' AS [NAME], CONVERT(nvarchar(128), USER_ID()) AS [VALUE]";
$stmt = sqlsrv_query($conn, $sql);
if( $stmt === false ) {
echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
exit;
}
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
echo $row['NAME'].": ".$row['VALUE']."</br>";
}
sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);
# ---------------------------------------
# Windows authentication
# ---------------------------------------
echo "Windows authentication"."</br>";
$server = 'serverinstance,port';
$cinfo = array(
"Database"=>'master'
);
$conn = sqlsrv_connect($server, $cinfo);
if( $conn === false )
{
echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
exit;
}
$sql =
"SELECT 'SUSER_SNAME' AS [NAME], CONVERT(nvarchar(128), SUSER_SNAME()) AS [VALUE]".
"UNION ALL ".
"SELECT 'SUSER_NAME' AS [NAME], CONVERT(nvarchar(128), SUSER_NAME()) AS [VALUE]".
"UNION ALL ".
"SELECT 'USER_NAME' AS [NAME], CONVERT(nvarchar(128), USER_NAME()) AS [VALUE]".
"UNION ALL ".
"SELECT 'USER_ID' AS [NAME], CONVERT(nvarchar(128), USER_ID()) AS [VALUE]";
$stmt = sqlsrv_query($conn, $sql);
if( $stmt === false ) {
echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
exit;
}
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
echo $row['NAME'].": ".$row['VALUE']."</br>";
}
sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);
?>