mssql_connect() odbc_connect() problems

Atron_Seige · Nov 25, 2004

Hi

I am testing a web site on my PC: WinXP, SQL 2k, PHP5, Apache 2.

Connecting to local database using the mssql commands is working 100%, trying to get to a remote user is an issue...

The remote is Win XP, SQL 2k.

I have read the other posts about changing the remote user to SQL Authentication, done that, same problem.

I have tried ODBC, same problem...

Either I get the "SQL Server does not exist or access denied., SQL state 08001 in SQLConnect " error, or the
"Data source name not found and no default driver specified" or some other errors...

I have tried various methods of connecting to the db, various lines of code, but the errors seem to all be the same.

Is there a special user that I need to create on the SQL server side? I am trying to log in with the "sa" users, so I know that is not the problem. The password is 100% correct (it is empty).

Please help, and thank you in advance.

Atron Seige

tsinka · Nov 25, 2004

Hi,

please post the PHP code (the part where you try to connect to the SQL server)

Thomas

Atron_Seige · Nov 26, 2004

these are not the real values
$strServerName = "servername";
$strUsername = "username";
$strPassword = "password";
$strDatabaseName = "[databasename]";
$strODBCConName = "ODBCConnectionname";

$connection_string = "DRIVER={SQL Server};SERVER=$strServerName ;DATABASE=$strDatabaseName ";

if ($conn = odbc_connect( $connection_string, $strUsername , $strPassword ))
{
echo "Connected ";
$sql = "SELECT * FROM con_Contact";
$exc = odbc_exec($conn,$sql);
if($exc)
{
echo "selection completed ";
while($row = odbc_fetch_object($exc))
{
echo $row->con_Name.' '.$row->con_Surname . " ";
echo "line ";
}
}
}

other attempt

if ($conn = odbc_connect($strODBCConName, $strUsername, strPassword))
{
echo "Connected ";
$sql = "SELECT * FROM con_Contact";
$exc = odbc_exec($conn,$sql);
if($exc)
{
echo "selection completed ";
while($row = odbc_fetch_object($exc))
{
echo $row->con_Name.' '.$row->con_Surname . " ";
echo "line ";
}
}
}

antoher try...
$connMSSQL = mssql_connect($strServerName, $strUsername, $strPassword) or die("Could not connect.");

I have tried a few other combinations, but it all seems out to kill my sanity... If you can see a bug, plz tell me, if you know anywhere that I can get a tutorial (for free) I will be even happier.

I have the php cfm file, and I have searched the php.net site as well.

Thanks in advance for any help

Atron Seige

tsinka · Nov 26, 2004

Which version of the ntwdblib.dll is installed on the web server ?

Additionally, read the user contributed note of
guilherme_cruz at uol dot com dot br

in the manual

Thomas

Atron_Seige · Nov 26, 2004

Hi

Thanks for the quick response!

I am running version 2000.2.8.0. Is that the latests and greatest?

I will try the code from the other site in a few seconds.

Thanks for the help, I will report on the success or failures...

Atron Seige

tsinka · Nov 26, 2004

That version should be sufficient.

Thomas

Atron_Seige · Nov 26, 2004

I am feeling like a right idiot now...

in the article that you said that I should read, he refers to the following:
"1. Add a new Alias choosing Named Pipes

Type in the ‘Alias’ field (anything you want): CONN1
Erase ‘Server field’ and type exactly: SERVER_NAME\INSTANCE_NAME"

What is the instance name??

"The field ‘Pipe Name’ should be: \SERVER_NAME\pipe\MSSQL$INSTANCE_NAME\sql\query"

Not too sure about that statement either...

Thanks.

Atron seige

Atron_Seige · Nov 26, 2004

<?php
while (php.code == "working")
{
me.dancing = true;
me.praise( tsinka);
}
?>

^_

Thanks tsinka, i figured out that the instance part is not required, you just need to set up the named pipe pointing to the server and all seems to work 100%.

Now to test it from my local web server to remote SQL server.

Thanks for the help, I hope that during my time on this forum I will be able to help others as fast and efficiently as you helped me.

Atron Seige

Atron_Seige · Nov 26, 2004

Ok, remote is not working...

Should i set up the alias on the web server or on the sql server?

I have tried setting up the connection on the web server and the SQL server.

Cant even connect to it via SQL! WTF?!?! I can ping the system fine...

I can get to it in SQL by using the IP, but the Name doesnt seem to want to resolve, and using an IP in the Alias doensnt make any changes either

I need vodka 2 do this stuff

Atron Seige

tsinka · Nov 26, 2004

Ok ... too bad, I played around a little bit with my local local->remote installation.

Try this one:

On the web server open the SQL Server Client Network Utility. Change the settings of the Server Alias to the remote server to:

TCP/IP => type in the server name/ip, check the box "Dynamically determine port" and use the name in "Server alias" as server name in the mssql_connect string, not the hostname. Always use the alias name.

On the remote server in the properties of the instance there's an option to allow remote connections using RPC. Is that checkbox checked in your case ?

Please post the info you get when you switch to the "DB-Library Options" tab in the SQL Server Client Network Utility.

Thomas

Atron_Seige · Nov 26, 2004

File name : c:/windows/system32/ntwdblib.dll
Version 7.00.839 .... umm... that is not right?! It said it was version 2000 something!!! Fuck...

OK.... so you found the bug... I am sorry, I really thought that the version number that is shown on Explorer properties is the right value, now I saw where to look... :$

I will reboot and try with the new dll..

Thanks for the help.

Atron Seige

tsinka · Nov 26, 2004

This is what I have:

File name: C:\WINDOWS\SYSTEM32\NTWDBLIB.DLL
Version: 8.00.194
Date: 06.08.2000
Size: 274.489 Bytes

Automatic ANSI to OEM conversion: checked
Use international settings: checked

Thomas

Atron_Seige · Nov 26, 2004

I have that same version now, and I still get the "Not associated with a trusted SQL Server connection" errors. sniff

I have tried the TCP/IP connections, it wont work, not even in query analyser. Named Pipes go thru on Query Analyser, but I get the above message when I am using it in PHP.

The RPC is checked on SQL, all the other checkboxes you mentioned are checked...

I have tried both mssql and odbc objects...

/me gets very sad ....

I am going to spend the weekend at the office at this rate!

Any other ideas? I am going to go through the php.net list to see if they mention something...

Atron Seige

tsinka · Nov 26, 2004

One last idea (taken from the PHP manual) ... I'm running out of ideas

Check if

mssql.secure_connection = On

is set in php.ini (I think the default is Off) and switch it to Off (then restart the web server)

Seems like you can't connect with sa if that setting is set to on.

EDIT: Look at the mssql_connect page in the manual. There are some other user contributed notes that might be helpful.

EDIT2: Try to connect with another user than sa.

Thomas

Atron_Seige · Nov 26, 2004

I just checked that... Did change the error message from
Login failed for user '(null)'
to Login failed for user 'sa'

I have asked anyone that seems to know how to switch on a pc... still no luck.

What PHP are you running? I am on 5, I have been recommended to go back to 4.3.9.

Question 2: When you run phpinfo, what does your msssql library version say? Mine still says 7.0, even though I copied the new version to system32, php and apache2... strange.

I really appreciate your help.

Thank you

Atron Seige

tsinka · Nov 26, 2004

Hmmm .... I just installed the client libs/apps on my local PC (including the Enterprise manager). With the enterprise manager I registered with a remote SQL Server.

I have no problems connecting to the remote server (pipes and TCP/IP) ... but I'm running xampp (http://www.xampp.org) on my local PC which seems to be some kind of chrooted/encapsulated because it doesn't copy anything to any other directory than the installation directory during installation. I switched between PHP4 and PHP5 (thats just one click with xampp) and have no problems at all ... btw ... while writing this I remember that PHP comes with it's own version of ntwdblib.dll.
I think that in the manual someone wrote that he could only connect to the SQL server using the dll coming with PHP.

Are both servers in the same Windows domain (in a domain at all).

About that version mismatch in phpinfo. How/where did you install Apache/PHP (directories/setup...) ?

Thomas

Atron_Seige · Nov 29, 2004

After spending friday night installing Windowns 2000 on the test machine, with SQL 2000... All the service packs, all the reboots...

Spent satuday installing PHP 4.3.9 on Apache 2 on the ol'laptop. Spent 2 much time swearing at the horrible errors...
Spent time drinking...

Sunday the setup was complete and all was working 100%

ODBC, MSSQL, everything! W00T

What I learnt:
INSTALL SQL 2000 WITH SQL AUTHENTICATION, DO NOT TRY TO CHANGE IT LATER ON, AS IT DOESNT SEEM TO HAVE ANY EFFECT

Thanks for all the help.

Atron Seige

mssql_connect() odbc_connect() problems

AAtron_Seige

Ttsinka

AAtron_Seige

Ttsinka

AAtron_Seige

Ttsinka

AAtron_Seige

AAtron_Seige

AAtron_Seige

Ttsinka

AAtron_Seige

Ttsinka

AAtron_Seige

Ttsinka

AAtron_Seige

Ttsinka

AAtron_Seige