PHP IIS MSSQL Connection Issues

DKY

I get the following error:

Connection could not be established. Array ( [0] => Array ( [0] => IM002 [SQLSTATE] => IM002 [1] => 0

 => 0 [2] => [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified [message] => [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified ) ) [/quote]

When trying to connect to my MSSQL database with the following code:
[code=php]$serverName = "(local)";
$connectionInfo = array("UID" => "User", "PWD" => "Password", "Database"=>"DB_NAME");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn )
{
     echo "Connection established.\n";
}
else
{
     echo "Connection could not be established.\n";
     die( print_r( sqlsrv_errors(), true));
}

//-----------------------------------------------
// Perform operations with connection.
//-----------------------------------------------

/* Close the connection. */
sqlsrv_close( $conn);

Can someone help me figure this out? I can't connect for some reason.

Roger_Ramjet

OK, so you got the code form msdn and it does not work: big surprise there :rolleyes:

Now I am going to assume that you are not running it as posted: ie that you do infact replace the parameters with real database name etc. So the error message is saying that the database does not exist, but this could be because the user has not get permissions to acces the server in the first place.

DKY

My first question is since I'm running both IIS and MSSQL on the same maching I should be able to do $serverName = "localhost"; or should I do something else?

I am using a name and password and this name and password has been setup to connect but I'm unsure of any other way to check if the user can connect. Any suggestions?

Roger_Ramjet

Well, it all depends on what has been set up and what is running. The manual does tell you that if you omit this then it will connect to the default sql server instance on the local machine. So if there is only one instance running then leave it out.

Otherwise there are various ways of specifying the server instance from name through protocol,port, ip adress and named pipe.

Best to refer to the manual: http://msdn.microsoft.com/en-us/library/ms130822.aspx

DKY

Do you think the error may mean that I have to set up an odbc database connection in the adminsitrative tools/data sources (odbc)??

DKY

I tried changing
sqlsrv_connect
to
mssql_connect
and got

Fatal error: Call to undefined function mssql_connect() in C:\path\to\file.php on line 47

DKY

This little bit of code

if (function_exists('mssql_fetch_row')) {
echo "MSSQL functions are available.<br />\n";
} else {
echo "MSSQL functions are not available.<br />\n";
}

tells me that the "MSSQL functions are not available." so I must be missing something...

Weedpacket

Did you enable the mssql extension in php.ini?

Also there appear to be some threads on this exact topic in Microsoft's SQL Server forums (they have a forum specifically to support users of this driver). For example, one asserts that you need not only SQL Server running on the machine in question, but also a SQL native client.

DKY

I went to the extensions section and actually had to add the code extension=php_mssql.dll; whereas in other installations of php I'm used to just uncommenting it. I then had to create a "ext" directory in the root php folder and download a copy of the dll file to put in there.

It's almost as if php is not turning on extensions for some reason. Is there a way to test that?
Should MSSQL show up in my phpinfo screen once enabled because there's nothing in there about MSSQL at all?

Maybe I should repost in this microsoft sql server forum? Every topic I find doesn't seem to help me out as I have this dll file installed and it doesn't seem to be picking it up at all.

bradgrafelman

First off, you started the thread talking about sqlsrv_connect(), which as far as I can tell is part of a 3rd-party PHP extension. Now you're talking about mssql_connect(), which is part of the "mssql" library that comes with PHP. Which one do you want to use?

DKY wrote:
Should MSSQL show up in my phpinfo screen once enabled

Yes, below the initial table of general info about your PHP installation, there should be a table preceded by a "mssql" (unsure of case - could be all uppercase or somewhere in between) header. If you don't see this, then the mssql extension wasn't loaded successfully.

First thing to test is if your php.ini file is even being read by PHP. Locate a PHP directive in your phpinfo() printout... e.g. display_errors. Then locate this same directive in your php.ini file; do the values match? If so, try changing it and restart your webserver - does a new phpinfo() printout reflect your changes?

DKY wrote:
I then had to create a "ext" directory in the root php folder and download a copy of the dll file to put in there.

The "ext" directory, as well as other supporting DLL's that PHP/extensions need, should have been there already. If not, it sounds like you don't have a complete install of PHP - I would recommend you re-download the .zip package from php.net and extract the "ext" folder and any DLLs you find in the root of the package.

Also note that PHP extensions compiled with a specific version of PHP don't play well with other versions, so you'll want to make sure you're downloading a .zip of the same version of PHP that you have installed.

EDIT: Thread moved to Install forum.

Brian_Swan

These two blog posts should help clear up the difference between the sqlsrv and mssql extensions:
http://blogs.msdn.com/brian_swan/archive/2010/03/08/mssql-vs-sqlsrv-what-s-the-difference-part-1.aspx
http://blogs.msdn.com/brian_swan/archive/2010/03/10/mssql-vs-sqlsrv-what-s-the-difference-part-2.aspx
Based on the error in the initial post, it looks like you don't have the SQL Native Client on the machine that is running PHP (which is required). More info (and download links) here: http://msdn.microsoft.com/en-us/library/cc296170(SQL.90).aspx
Hope that helps.
-Brian