SQL Server Connection timing out

29 Maret 2010 20:15

Just installed PHP/IIS on a fresh 2008 server installation. However, I'm having trouble connecting to my SQL2005 database server. I have good connectivity, and can connect via the Management Studio. Substituting the IP address for the hostname has no effect.

This is the error I get:

Unable to connect.



Array
(
    [0] => Array
        (
            [0] => 08001
            [SQLSTATE] => 08001
            [1] => 2
            [code] => 2
            [2] => [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [2]. 
            [message] => [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [2]. 
        )

    [1] => Array
        (
            [0] => HYT00
            [SQLSTATE] => HYT00
            [1] => 0
            [code] => 0
            [2] => [Microsoft][SQL Server Native Client 10.0]Login timeout expired
            [message] => [Microsoft][SQL Server Native Client 10.0]Login timeout expired
        )

    [2] => Array
        (
            [0] => 08001
            [SQLSTATE] => 08001
            [1] => 2
            [code] => 2
            [2] => [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
            [message] => [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
        )

)

My code is as follows:

<html><body>

<?php
$server = "dbserver"

;
echo $server;
$connectionInfo = array("Database"
=>"database"
,"UID"
=>"sa"
,"PWD"
=>"dbpassword"
);

/* Connect using SQL Server Authentication. */


$conn = sqlsrv_connect( $serverName, $connectionInfo);
if

( $conn === false

 )
{
     echo "Unable to connect.</br>"

;
     die( print_r( sqlsrv_errors(), true

));
}

sqlsrv_close( $conn);
?>

</body></html>

Any help? I'm genuinely stumped.

Semua Balasan

30 Maret 2010 0:07

Penjawab Pertanyaan

0
I am a bit confused by your code. Is your server name stored in “$server” or “$serverName”?

If $server is the PHP variable storing the SQL Server name, then your connection call should be:

$conn = sqlsrv_connect($server, $connectionInfo);

Thanks,

Serban
Serban Iliescu - MSFT
- Ditandai sebagai Jawaban oleh Serban Iliescu - MSFTEditor 08 April 2010 19:22
- Balas
- Kutip
11 Mei 2010 19:03

0
Is the connection to a specific instance? Include instance name for connection to an instance. Also, the port number may be included.
```
$server = "dbserver\instanceName"


$server = "dbserver, 1521"
```
- Balas
- Kutip
20 Agustus 2012 7:16

0

Could you please tell what is wroing here?

$connectionInfo = array( "UID"=>'aws_iis_production',
"PWD"=>'Q1w2e3r4t5',
"Database"=>"AWS_IIS_Production");

/* Connect using SQL Server Authentication. */
$conn = sqlsrv_connect('192.168.3.86, 1433', $connectionInfo);

Having: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server"

IP connections on this IP and port are enabled in SQL 2008 Config manager.

Thank you
-- Partner at Deep Shift Labs. We are building Nerrvana - Selenium cloud at your fingertips and Startyco - pluggable web apps for start-ups.
- Balas
- Kutip
20 Agustus 2012 9:31

0

Igor

Could you try connecting with SQL Server Management Studio, from your PHP computer, using "192.168.3.86, 1433" as the server name?

Rob
- Balas
- Kutip
20 Agustus 2012 9:37

0

This is extreme :) I wish it would be a 10M tool to do it. Did not want to install Studio on PHP/IIS system. But if no other option will have to.

BTW is there anything similar but lightweight?

Thank you

-- Partner at Deep Shift Labs. We are building Nerrvana - Selenium cloud at your fingertips and Startyco - pluggable web apps for start-ups.
- Balas
- Kutip
20 Agustus 2012 10:03

1
Igor

It's not extreme, it's ordinary. You can install Management Studio, run your tests then uninstall it again. It's not a large program, and it's *not* going to solve your problem:

Running the test on Management Studio will determine whether there is something not working with the PHP driver. If your test succeeds, then it's a PHP problem. If it fails, then it's probably your network, e.g. a firewall issue, or you have selected the wrong port.

Rob

Edit:

You can also try connecting using the ODBC tools in Control Panel - just create an ODBC connection for SQL Server Native Client, enter your server details, and click the button to test the connection.
- Diedit oleh Robert Johnson 20 Agustus 2012 10:07 other option
- Balas
- Kutip
20 Agustus 2012 14:14

1

Igor

It's not extreme, it's ordinary. You can install Management Studio, run your tests then uninstall it again. It's not a large program, and it's *not* going to solve your problem:

Running the test on Management Studio will determine whether there is something not working with the PHP driver. If your test succeeds, then it's a PHP problem. If it fails, then it's probably your network, e.g. a firewall issue, or you have selected the wrong port.

Rob

Edit:

You can also try connecting using the ODBC tools in Control Panel - just create an ODBC connection for SQL Server Native Client, enter your server details, and click the button to test the connection.

This forum does not alarm for post updates. I missed your update :(

I still can't get used to loading 230Mb SQLEXPRWT_x64_ENU.exe to just test connection. You were right. When you do not install and configure every day as sysadmin or support guy you loose these skills. I found that I can ping from SQL2008 box IIS box but not the other way around. So I just added a plain rule to WF allowing TCP over 1433 and it I connected both from MMS and PHP.

Eventually I did two things on VM I received from a client:

1. Enabled TCP in Connection Manager and changed IP from 10. to 192.168. network. They use 10. network in the office and it stays there after VM was brought to another network

2. Added TCP 1433 allowed from outside

Good article on how to install just MMS:

http://stackoverflow.com/questions/3536023/how-to-install-sql-server-management-studio-2008-component-only

Thank you Robert. What is 'ICAP' next to your name btw?
-- Partner at Deep Shift Labs. We are building Nerrvana - Selenium cloud at your fingertips and Startyco - pluggable web apps for start-ups.
- Balas
- Kutip
20 Agustus 2012 14:19

1

Since there is no snippet on how to connect to SQL server NOT LOCALLY over TCP/IP

<?php
/* Specify the server and connection string attributes. */

$connectionInfo = array( "UID"=>'aws_iis_production',
                         "PWD"=>'Q1cxcxcx4t5',
                         "Database"=>"AWS_IIS_Production");

/* Connect using SQL Server Authentication. */
$conn = sqlsrv_connect('192.168.3.86, 1433', $connectionInfo);
if( $conn === false )
{
     echo "Unable to connect.</br>";
     die( print_r( sqlsrv_errors(), true));
}

/* Query SQL Server for the login of the user accessing the
database. */
$tsql = "SELECT CONVERT(varchar(32), SUSER_SNAME())";
$stmt = sqlsrv_query( $conn, $tsql);
if( $stmt === false )
{
     echo "Error in executing query.</br>";
     die( print_r( sqlsrv_errors(), true));
}

/* Retrieve and display the results of the query. */
$row = sqlsrv_fetch_array($stmt);
echo "User login: ".$row[0]."</br>";

/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>

Since we use ADODB - http://adodb.sourceforge.net/ now I am back to my tests written about 7 years ago to test both ADODB and MSSQL driver on handling long strings and old dates. This time it will ADODB 'mssqlnative' driver under test.

-- Partner at Deep Shift Labs. We are building Nerrvana - Selenium cloud at your fingertips and Startyco - pluggable web apps for start-ups.
- Balas
- Kutip
20 Agustus 2012 15:10

0
Thanks Igor

So updating the Windows firewall fixed the problem, thanks for the updates.

In retrospect it would have been better advice to start with the Control Panel/ODBC connection test. I agree with you on a lot of your points:
1. SSMS *is* a large app. I remember I installed it without any problems from here: http://www.microsoft.com/en-us/download/details.aspx?id=22985 but I had forgotten how large it was!
2. It's undesirable to spoil a clean server installation by installing apps you won't need.
'ICAP' is the name of the company I work for at the moment.
Rob
- Balas
- Kutip
20 Agustus 2012 20:49

1

Better to use SQLCMD instead...? :)

Cheers,

Jonathan
This posting is provided 'AS IS' with no warranties, and confers no rights.
- Balas
- Kutip
21 Agustus 2012 11:04

1
Thanks Jonathan - good idea.

SQLCMD is downloadable by finding "Microsoft® SQL Server ... Command Line Utilities" on the feature pack page for the desired SQL Server version, e.g. for 2008 R2, the page is here:
- http://www.microsoft.com/en-us/download/details.aspx?id=16978#SQLCMD
Much less trouble than installing Management Studio for testing a connection!
Rob
- Ditandai sebagai Jawaban oleh Jonathan GuerinMicrosoft Employee 21 Agustus 2012 17:55
- Balas
- Kutip