php and obc_connect issue

[bplogan]

Hello,

 

I am new to php and MSSQL.  I have php 5 running on a windows 2000 box.

I added a DSN to the SQL server database I am trying to connect to using admin tools and Data sources in control panel.

 

Now I'm not sure how to connect to the database via php.  I found the following code using odbc_connect

 

$dsn="issueTracker";

$username="";

$password="";

$sqlconnect=odbc_connect($dsn,$username,$password);

$sqlquery="SELECT * FROM status;";

$process=odbc_exec($sqlconnect, $sqlquery);

 

I am not entering anything for username and password as I setup the DSN using NT Authentication.

I did set mssql.secure_connection = on in the php.ini file.

 

I get the following error on the page:

 

Warning: odbc_connect() [function.odbc-connect]: SQL error: [Microsoft][ODBC SQL Server Driver]

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'., SQL state 28000 in SQLConnect

 

Any help would be awesome!

[rhodesa]

the webserver is running as an anonymous system account...it doesn't have the same access as the user that is logged in. you need to add a user using the MSSQL Management Studio and user that user/pass to login to the MSSQL server with PHP

[bplogan]

the webserver is running as an anonymous system account...it doesn't have the same access as the user that is logged in. you need to add a user using the MSSQL Management Studio and user that user/pass to login to the MSSQL server with PHP

 

Thanks for the reply.  So I am going to create the user and pass on the MSSQL server.  Then should I change the NT Authentication in the php.ini file back to off?

[rhodesa]

um...not sure...i've never used that before

 

i will also say though...on the SQL server, under Properties -> Security, make sure the Server Authentication is set to "SQL Server and Windows Authentication mode". if it's not, then change it, and reboot the system

[BMurtagh]

Hi,

 

I came across your post and wanted to include some extra information and resources that might help you a bit more. I saw in your code that you're using odbc_connect() do you have to do it this way? PHP4 & PHP5 have many built-in MSSQL functions similar to those of MySQL. I would check the link I've included below and focus on mssql_connect(), mssql_close(), etc.

 

PHP MSSQL Reference Link: http://us.php.net/manual/en/ref.mssql.php

 

Also, as mentioned by rhodesa, the MSSQL user should be created from within Management Studio (or have your hosting provider setup a login for you) and use that to connect to your database using SQL authentication.

 

I'm including a connect sample to help guide through the process:

 

<?php

$host='<dbservername>'; /* The host/ip of your MSSQL server */
$dbuser='<sqlusername>'; /* The SQL DB user you setup in Management Studio */
$dbpasswd='<sqlpassword>'; /* The password associated with the new SQL user */


$db = mssql_connect ($host,$dbusername,$dbpassword) or die("Error connecting to database");; // Attempt to connect to the db


?>

[bplogan]

Hi,

 

I came across your post and wanted to include some extra information and resources that might help you a bit more. I saw in your code that you're using odbc_connect() do you have to do it this way? PHP4 & PHP5 have many built-in MSSQL functions similar to those of MySQL. I would check the link I've included below and focus on mssql_connect(), mssql_close(), etc.

 

PHP MSSQL Reference Link: http://us.php.net/manual/en/ref.mssql.php

 

Also, as mentioned by rhodesa, the MSSQL user should be created from within Management Studio (or have your hosting provider setup a login for you) and use that to connect to your database using SQL authentication.

 

I'm including a connect sample to help guide through the process:

 

<?php

$host='<dbservername>'; /* The host/ip of your MSSQL server */
$dbuser='<sqlusername>'; /* The SQL DB user you setup in Management Studio */
$dbpasswd='<sqlpassword>'; /* The password associated with the new SQL user */


$db = mssql_connect ($host,$dbusername,$dbpassword) or die("Error connecting to database");; // Attempt to connect to the db


?>

 

Hi there, thanks for reply, I tried what you suggested and get the following error:

 

Warning: mssql_connect() [function.mssql-connect]: message: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

 

code:

<?php

$host='IASSQLDEV'; /* The host/ip of your MSSQL server */

$dbuser='user'; /* The SQL DB user you setup in Management Studio */

$dbpasswd='password'; /* The password associated with the new SQL user */

 

 

$db = mssql_connect ($host,$dbusername,$dbpassword) or die("Error connecting to database");; // Attempt to connect to

 

?>

[BMurtagh]

Have you gone through the process of creating a local MSSQL user? Is the SQL hosted locally as the webpages?

 

Based off the error, it looks like its still trying to connect using as an anonymous user.

[rhodesa]

did you get this working? in BMurtagh's post, the variables don't line up. it should be:

<?php

$host='<dbservername>'; /* The host/ip of your MSSQL server */
$dbuser='<sqlusername>'; /* The SQL DB user you setup in Management Studio */
$dbpasswd='<sqlpassword>'; /* The password associated with the new SQL user */

$db = mssql_connect ($host,$dbuser,$dbpasswd) or die("Error connecting to database");; // Attempt to connect to the db
?>