Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why can't I connect to my mssql database using PHP?

I've done so much research trying to get my PHP code hosted on IIS to connect to my MSSQL database. I just can't seem to figure out the issue. Has anyone come across this before?

<?php

$serverName = 'AEGIS-PC\SQLEXPRESS';
$connectionInfo=array('Database'=>'tttb_db');

$con = sqlsrv_connect($serverName, $connectionInfo);
if($con){
    echo 'Connection established<br />';
}
else {
    echo 'Connection failed<br />';
    die(print_r(sqlsrv_errors(), TRUE));
}

?>

Update, we have a new error:

Connection failed Array ( [0] => Array ( [0] => 28000 [SQLSTATE] => 28000 [1] => 18456 [code] => 18456 [2] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Login failed for user 'NT AUTHORITY\IUSR'. [message] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Login failed for user 'NT AUTHORITY\IUSR'. ) [1] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 4060 [code] => 4060 [2] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Cannot open database "tttb_db" requested by the login. The login failed. [message] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Cannot open database "tttb_db" requested by the login. The login failed. ) [2] => Array ( [0] => 28000 [SQLSTATE] => 28000 [1] => 18456 [code] => 18456 [2] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Login failed for user 'NT AUTHORITY\IUSR'. [message] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Login failed for user 'NT AUTHORITY\IUSR'. ) [3] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 4060 [code] => 4060 [2] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Cannot open database "tttb_db" requested by the login. The login failed. [message] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Cannot open database "tttb_db" requested by the login. The login failed. ) )

Our SQL server is using Windows Authentication and our server name is AEGIS-PC\SQLEXPRESS with the user name and password blocks are greyed out. I can not think of reason our login would be failing. What are we doing wrong?

like image 459
Benschneider06 Avatar asked Apr 14 '15 19:04

Benschneider06


People also ask

Can I use MS SQL with PHP?

Yes, you can. It depends on which version of PHP you're using, but if you're using PHP5+ you can use Microsoft's SQL Server Driver for PHP. Make sure you use version 2, which gives you the PDO functionality as well as the procedural style.

Why SQL Server is not connecting?

The following common scenarios can cause connectivity problems: Incorrect IP address for the Server field. Make sure that the IP address matches the entry in the SQL Server error log file. Incorrect server name in the Server field.

How do I connect to an mssql database?

Connect to a SQL Server instanceStart SQL Server Management Studio. The first time you run SSMS, the Connect to Server window opens. If it doesn't open, you can open it manually by selecting Object Explorer > Connect > Database Engine. For Server type, select Database Engine (usually the default option).


2 Answers

There are a few things that could cause such problems:

1.) Your modules aren't loaded because its VC9 instead if VC11. Check which compiler version your system use and install the correct driver.

2.) Check your PHP Version and use the correct driver for your PHP-Version your can check that in your phpinfo().

3.) Don't forget to install the MSSQL Native Client otherwise you can't connect to your database that is the problem what I have every time.

Your code looks good and if your get the error message that sqlsrv_connect isn't found that is a signal that the module is not loaded.

https://www.microsoft.com/en-us/download/details.aspx?id=20098

like image 112
René Höhle Avatar answered Sep 30 '22 11:09

René Höhle


It's just a simple solution I found to mine. checkout http://blog.sqlauthority.com/2009/08/20/sql-server-fix-error-cannot-open-database-requested-by-the-login-the-login-failed-login-failed-for-user-nt-authoritynetwork-service/

it's just a few steps.

  1. Go to SQL Server >> Security >> Logins and right click on NT AUTHORITY\NETWORK SERVICE and select Properties
  2. In newly opened screen of Login Properties, go to the “User Mapping” tab. Then, on the “User Mapping” tab, select the desired database – especially the database for which this error message is displayed. On the lower screen, check the role db_owner. Click OK.

this should fix your problem

like image 24
micheal__og Avatar answered Sep 30 '22 12:09

micheal__og