Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Cannot Select DB using mssql

authentication mssql

  • Please log in to reply
8 replies to this topic

#1 newphpcoder

newphpcoder

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 527 posts

Posted 12 October 2010 - 12:03 AM

Good day!

I change my database from mysql to mssql, so I need to change the connection code in my php. I’m not familiar about the codes using mssql. Now cannot connect to my database.
I used the following:
php 5.1.4
IIS 6.0
Microsoft SQL Server 2005 Express Edition
SQL Server Management Studio Express
Here is my code:

<?php
//session_start();
//session_regenerate_id();

//if($_SESSION['loggedin']){
//the user is already logged in, lets redirect them to the other page
// header("Location:company.php");
//}


$server = "ISM\SQLEXPRESS";
//$db = "dbtest";
$connectOptions = array("Database" => "dbtest");
//Because UID and PWD are not specified in the connection option, the connection is made with Windows Authentication.
$conn = mssql_connect($server) or die("Could not connect");


$selected = mssql_select_db($connectOptions)or die("Cannot select DB");


$department = $_POST['department'];
$username = $_POST['username'];

$sql=mssql_query("SELECT `Department`, `Username` FROM `tbluser` WHERE `Department` = '{$department}' AND Username = '{$username}'") or die(mssql_min_error_severity());
$ct = mssql_num_rows($sql);

if($ct == 1) {
// im guessing this means that the user is valid.
$_SESSION['loggedin'] = true; // now that the user is valid we change the session value.
$row = mssql_fetch_assoc($sql);

//$_SESSION['username'] = $row['Username'] ;
//$_SESSION['department'] = $row['Department'];

$Departments=array('aa', 'bb', 'cc', 'dd', 'ee', 'ff', 'gg', 'hh', 'ii', 'jj');

if (in_array($row['Department'], $Departments)){
header ('Location:company.php');
}else{
echo "Incorrect Username or Department";
header ('Location:index.php');
}
}
?>


And when I run this code the result is:

Cannot select DB

Thank you in advance
  • 0

#2 Alexander

Alexander

    YOL9

  • Moderator
  • 3963 posts
  • Location:Vancouver, Eh! Cleverness: 200
  • Programming Language:C, C++, PHP, Assembly

Posted 12 October 2010 - 12:10 AM

Try:
$server = 'ISM\SQLEXPRESS';
$conn = mssql_connect($server) or die('Could not connect');

$selected = mssql_select_db('dbtest', $conn) or die('Cannot select DB');

  • 0

All new problems require investigation, and so if errors are problems, try to learn as much as you can and report back.


#3 newphpcoder

newphpcoder

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 527 posts

Posted 12 October 2010 - 06:26 PM

Try:

$server = 'ISM\SQLEXPRESS';
$conn = mssql_connect($server) or die('Could not connect');

$selected = mssql_select_db('dbtest', $conn) or die('Cannot select DB');

I try the code you suggested and the result is:

Cannot select DB

and I try another code like this:

$db = "dbtest";
$selected = mssql_select_db($db) or die('MSSQL error' . mssql_get_last_message());



and the output is:
MSSQL error.The several principal "[domain name]\IUSR_[computer name]" is not able to access the databse "database name" under the current security context.

Honestly, I have no idea regaeding this error.
  • 0

#4 Alexander

Alexander

    YOL9

  • Moderator
  • 3963 posts
  • Location:Vancouver, Eh! Cleverness: 200
  • Programming Language:C, C++, PHP, Assembly

Posted 12 October 2010 - 06:44 PM

You do not have permission to browse the database, is the server running under an administrative account?
  • 0

All new problems require investigation, and so if errors are problems, try to learn as much as you can and report back.


#5 newphpcoder

newphpcoder

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 527 posts

Posted 13 October 2010 - 12:37 AM

You do not have permission to browse the database, is the server running under an administrative account?

Yes
  • 0

#6 newphpcoder

newphpcoder

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 527 posts

Posted 13 October 2010 - 12:38 AM

You do not have permission to browse the database, is the server running under an administrative account?

I try a new code:


$db = "dbtest";
$selected = mssql_select_db($db) or die('MSSQL error' . mssql_get_last_message());


and the output is:
MSSQL error.The several principal "[domain name]\IUSR_[computer name]" is not able to access the databse "database name" under the current security context.

Then I research about this error and I find a step to fix the error, but I can't follow the steps because I can't find what they say in the steps.

here is the step:
To fix this SQL Server Management Studio 2008 (maybe in 2005 too), please do the following:

1.Open SQL Server Management Studio 2008
2.Connect to the database server as normal
3.Press F7 to open the "Object Explorer Details" window or click "View" >> "Object Explorer Details"
4.In the object explorer details window expand by double click on "Databases"
5.Right click on the column header and deselect "Collation" from the menu that will pop up
6.From the object explorer, refresh the "Databases"

I don't know what the appearance is if I am already in the Object Explorer Details view. And I can’t see the column header and also the Collation.
  • 0

#7 newphpcoder

newphpcoder

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 527 posts

Posted 14 October 2010 - 06:41 PM

I try to configure the sql server I do the ff:

1. I open the SQL Server Management Studio Express
2. I connect using Windows Authentication
3. Under Databases folder where my database was there.
4. I click the plus sign on Databases
5. Then my dbtest was appearing and I click the plus sign.
6. On Security folder I click the plus sign
7. Then the Users folder was appear
8. I right click on the DOMAIN\SQLServer2005MSSQLUser$COMPUTERNAME$SQLEXPRESS.
9. On the General Tab-> Ownded Schemas I try to check the db_accessadmin and also I accidentall check the checkbox of db_owner and db_securityadmin. And Role Members I check also the db_accessadmin.

After that I reboot the server.

Then i run again my webpage.

And the result is:
Cannot select DB

Before I configure my sql server the output of my codes is:

MSSQL error.The several principal "[domain name]\IUSR_[computer name]" is not able to access the databse "database name" under the current security context.

After that I configure again my sql server to uncheck the checkboxes that I checked before. And I don't know why on the Owned Schema the checkboxex that I check was hiding so I can edit it, but on the Role members I uncheck the db_accessadmin.

I don't have any idea to fix my error: I really needed to fix it but I don't know what to do and I'm a little bit scared to configure the sql server, because I am new user of it.
:(
  • 0

#8 Alexander

Alexander

    YOL9

  • Moderator
  • 3963 posts
  • Location:Vancouver, Eh! Cleverness: 200
  • Programming Language:C, C++, PHP, Assembly

Posted 14 October 2010 - 06:53 PM

I had not worked with MS related functions yet, but I had seen this:

- Make sure you have set up a user with "SQL SERVER authentication" who has rights to connect.

If a user has not been granted permission to connect to the database, then the user cannot select one, thus the error you might be having.
  • 0

All new problems require investigation, and so if errors are problems, try to learn as much as you can and report back.


#9 newphpcoder

newphpcoder

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 527 posts

Posted 14 October 2010 - 09:56 PM

I had not worked with MS related functions yet, but I had seen this:

If a user has not been granted permission to connect to the database, then the user cannot select one, thus the error you might be having.


My authentication mode thai I used is mixed mode authentication. I can access my database in SQL Server Management Studio Express, but when I rty to connect using databse I can' access.
  • 0





Also tagged with one or more of these keywords: authentication, mssql

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download