Microsoft SQL 2008 Server Connection Problem via PHP

[Guest]

Hi,

I have been trying to connect to MS SQL server 2008 for the past few days via PHP and have got this return message:

Connection could not be established. Array ( [0] => Array ( [0] => 08001 [sqlSTATE] => 08001 [1] => -1

 => -1 [2] => [Microsoft][ODBC Driver 11 for SQL Server]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. [message] => [Microsoft][ODBC Driver 11 for SQL Server]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. ) [1] => Array ( [0] => HYT00 [sqlSTATE] => HYT00 [1] => 0 [code=auto:0] => 0 [2] => [Microsoft][ODBC Driver 11 for SQL Server]Login timeout expired [message] => [Microsoft][ODBC Driver 11 for SQL Server]Login timeout expired ) [2] => Array ( [0] => 08001 [sqlSTATE] => 08001 [1] => -1 [code=auto:0] => -1 [2] => [Microsoft][ODBC Driver 11 for SQL Server]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][ODBC Driver 11 for SQL Server]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. )

could I please have some help trying to sort this out. i have roughly 2 weeks left to get this done.

[Neil]

Hi,

I have been trying to connect to MS SQL server 2008 for the past few days via PHP and have got this return message:

Connection could not be established. Array ( [0] => Array ( [0] => 08001 [sqlSTATE] => 08001 [1] => -1

 => -1 [2] => [Microsoft][ODBC Driver 11 for SQL Server]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. [message] => [Microsoft][ODBC Driver 11 for SQL Server]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. ) [1] => Array ( [0] => HYT00 [sqlSTATE] => HYT00 [1] => 0 [code=auto:0] => 0 [2] => [Microsoft][ODBC Driver 11 for SQL Server]Login timeout expired [message] => [Microsoft][ODBC Driver 11 for SQL Server]Login timeout expired ) [2] => Array ( [0] => 08001 [sqlSTATE] => 08001 [1] => -1 [code=auto:0] => -1 [2] => [Microsoft][ODBC Driver 11 for SQL Server]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][ODBC Driver 11 for SQL Server]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. )

could I please have some help trying to sort this out. i have roughly 2 weeks left to get this done.

 

Sounds like you're not supplying credentials to login, they're invalid, or the server's not running.

[Guest]

1. Is the server running?

Is the server accepting remote connections? Is it listening on a public network interface?

Can you log into the server with your credentials locally/over ssh?

Can you log into the server with your credentials via the publicly exposed port on the SQL server itself?

What code are you using to connect to the server? Please post it, we are not goddamn clairvoyants.

[Guest]

 

1. Is the server running?
2. Is the server accepting remote connections? Is it listening on a public network interface?
3. Can you log into the server with your credentials locally/over ssh?
4. Can you log into the server with your credentials via the publicly exposed port on the SQL server itself?
5. What code are you using to connect to the server? Please post it, we are not goddamn clairvoyants.

 

1. Server is running

2. yes it can accept remote connections. I am not sure about the Public interface part but have other applications connecting to the server.

3. I am able to log into the server locally with my credetials

4. sorry i am not sure.

5. Please see below.

 

<?php$serverName = "SERV002\SQLEXPRESS"; //serverName\instanceName$connectionInfo = array( "Database"=>"YSHSDB", "UID"=>"sa", "PWD"=>"password");$conn = sqlsrv_connect( $serverName, $connectionInfo);if( $conn ) {     echo "Connection established.<br />";}else{     echo "Connection could not be established.<br />";     die( print_r( sqlsrv_errors(), true));}?>

UPDATE: Looks like it is to do with trying to access the server remotely. I am unsure how i can fix this issue. Any help would be good. 

So to fix this i have moved my xamplite to the server hosting the SQL server. However, As this may not be an option at my work place ( very high chance it won't) how do i go about setting all this up so that if xampp is installed on another machine it can access the SQL Server?

[Guest]

So to fix this i have moved my xamplite to the server hosting the SQL server. However, As this may not be an option at my work place ( very high chance it won't) how do i go about setting all this up so that if xampp is installed on another machine it can access the SQL Server?

 

Never used Microsoft SQL server; possibly someone else on the forum can provide detailed assistance. Otherwise you're best off reading the documentation; that's how I learned to set up MySQL.

[Neil]

What happens if you ping "SERV002" from your CLI vs pinging "\\SERV002" ?

[d3limit3r]

In SQL Server Management Studio, right click on the server name on the left and then click on the properties option. With the Server Properties window open, select the Connections page and ensure that the 'Allow remote connections to this server' checkbox is enabled. If it wasn't already enabled, click OK and then proceed to restart the SQL Server by right clicking on the server name once again and then selecting the restart option.

 

If you are still unable to connect after performing the first change, open the SQL Server Configuration Manager. Once the Configuration Manager window is open, expand the SQL Server Network Configuration section on the left and then select 'Protocols for MSSQLSERVER ' (where MSSQLSERVER is the name of your SQL Server instance). In the Right-hand pane, ensure that TCP/IP is enabled. If it isn't enabled, right-click on it, and select the Enable option and then proceed to restart the SQL Server in the same fashion that was described above. 

[Guest]

Probably need to make sure the firewall is letting the connections through as well.

[Guest]

What happens if you ping "SERV002" from your CLI vs pinging "\\SERV002" ?

  

I can ping the server and access files off it from a share.

In SQL Server Management Studio, right click on the server name on the left and then click on the properties option. With the Server Properties window open, select the Connections page and ensure that the 'Allow remote connections to this server' checkbox is enabled. If it wasn't already enabled, click OK and then proceed to restart the SQL Server by right clicking on the server name once again and then selecting the restart option.

 

If you are still unable to connect after performing the first change, open the SQL Server Configuration Manager. Once the Configuration Manager window is open, expand the SQL Server Network Configuration section on the left and then select 'Protocols for MSSQLSERVER ' (where MSSQLSERVER is the name of your SQL Server instance). In the Right-hand pane, ensure that TCP/IP is enabled. If it isn't enabled, right-click on it, and select the Enable option and then proceed to restart the SQL Server in the same fashion that was described above.

I will check the second option you mentioned when I am on my PC next.

[santsys]

Firewall, services running, and making sure SQL is accepting remote connections to the correct IP address... By default SQL Server only accepts connections via 127.0.0.1...

 

To check the connections open SQL Server Configuration Manager, select SQL Server Network Configuration the Protocols for MSSQLSERVER, then select TCP/IP properties, then navigate to the IP Addresses tab and make sure Active and Enabled are set to Yes for the IP Address assigned to your server.

 

 

 

More information here, https://msdn.microsoft.com/en-us/ms174212.aspx

[Guest]

Firewall, services running, and making sure SQL is accepting remote connections to the correct IP address... By default SQL Server only accepts connections via 127.0.0.1...

 

To check the connections open SQL Server Configuration Manager, select SQL Server Network Configuration the Protocols for MSSQLSERVER, then select TCP/IP properties, then navigate to the IP Addresses tab and make sure Active and Enabled are set to Yes for the IP Address assigned to your server.

 

 

 

More information here, https://msdn.microsoft.com/en-us/ms174212.aspx

I have set those settings and i am still having no luck. My machine is only showing IP1 and IP2 should it be listing more than that? I will also check with the actual server at work on Wednesday when i am back in to see what their settings are since that will be the final server that it is pulling the information from.

[santsys]

I have set those settings and i am still having no luck. My machine is only showing IP1 and IP2 should it be listing more than that? I will also check with the actual server at work on Wednesday when i am back in to see what their settings are since that will be the final server that it is pulling the information from.

 

It should show the number of IP Addresses in your system. So if there are only 2 addresses assigned, then only the two will show up. If all of that is still not working, you're going to want to double check your ports (make sure you are using the standard ports, 1433). Make sure the firewall is open. Make sure your network isn't restricting traffic, if you're in a corporate environment they may have network level port blocking or something going on. You can also validate your connection string (try using the IP to connect)...  You can also test using SQL Server Management Studio from a remote computer to see if that connects... Maybe you're applications connection string has something messed up.

[Guest]

It should show the number of IP Addresses in your system. So if there are only 2 addresses assigned, then only the two will show up. If all of that is still not working, you're going to want to double check your ports (make sure you are using the standard ports, 1433). Make sure the firewall is open. Make sure your network isn't restricting traffic, if you're in a corporate environment they may have network level port blocking or something going on. You can also validate your connection string (try using the IP to connect)...  You can also test using SQL Server Management Studio from a remote computer to see if that connects... Maybe you're applications connection string has something messed up.

I think it might connect at work then, I just need to update the obdc software on the server end because I was able import the tables from the actual server into my virtual server. hmm I will keep you updated. I will try it first think tomorrow morning.

cheers

 

 

Update:

Hey @santsys

I have checked those settings and enabled what ever needed to be enabled and still can't grab the data remotely. Any other options i should look at?