Initial Connection to MS SQL 2012 fails everytime

How do you know it fails?  Is there a message or code set that you can show us?  What is the time-penalty for just connecting twice?  Not that I would call that a solution, but it might be a viable workaround... at least until we can isolate the problem.

The error is "Connection to MS SQL could not be established."  It just means that the user would have to refresh the screen again.

Can we please see the connection code that generates this message?

$serverName = "192.168.xx.xx";
$uid = "uid";
$pwd = "pwd";
$databaseName = "database";
   
$connectionInfo = array( "UID"=>$uid,  
 "PWD"=>$pwd,  
 "Database"=>$databaseName,
 "ReturnDatesAsStrings"=>true);   

/* Connect using SQL Server Authentication. */
$conn = sqlsrv_connect( $serverName, $connectionInfo) 
 or die("Connection to MS SQL could not be established.");

Select allOpen in new window

I did a trace in SQL and I don't even see the initial attempt, I do see the secondary attempt though.  Not sure what that would indicate.

Here is the results:

array(3) { [0]=> array(6) { [0]=> string(5) "08001" ["SQLSTATE"]=> string(5) "08001" [1]=> int(5) ["code"]=> int(5) [2]=> string(111) "[Microsoft][SQL Server Native Client 11.0]Named Pipes Provider: Could not open a connection to SQL Server [5]. " ["message"]=> string(111) "[Microsoft][SQL Server Native Client 11.0]Named Pipes Provider: Could not open a connection to SQL Server [5]. " } [1]=> array(6) { [0]=> string(5) "HYT00" ["SQLSTATE"]=> string(5) "HYT00" [1]=> int(0) ["code"]=> int(0) [2]=> string(63) "[Microsoft][SQL Server Native Client 11.0]Login timeout expired" ["message"]=> string(63) "[Microsoft][SQL Server Native Client 11.0]Login timeout expired" } [2]=> array(6) { [0]=> string(5) "08001" ["SQLSTATE"]=> string(5) "08001" [1]=> int(5) ["code"]=> int(5) [2]=> string(330) "[Microsoft][SQL Server Native Client 11.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"]=> string(330) "[Microsoft][SQL Server Native Client 11.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." } } array(2) { [0]=> array(6) { [0]=> string(5) "01000" ["SQLSTATE"]=> string(5) "01000" [1]=> int(5701) ["code"]=> int(5701) [2]=> string(92) "[Microsoft][SQL Server Native Client 11.0][SQL Server]Changed database context to 'databasepos'." ["message"]=> string(92) "[Microsoft][SQL Server Native Client 11.0][SQL Server]Changed database context to 'databasepos'." } [1]=> array(6) { [0]=> string(5) "01000" ["SQLSTATE"]=> string(5) "01000" [1]=> int(5703) ["code"]=> int(5703) [2]=> string(93) "[Microsoft][SQL Server Native Client 11.0][SQL Server]Changed language setting to us_english." ["message"]=> string(93) "[Microsoft][SQL Server Native Client 11.0][SQL Server]Changed language setting to us_english." } } Test Complete

Select allOpen in new window

Reformatted for readability.

// FIRST CONNECT
array(3) { 
[0]=> array(6) { 
      [0]=> string(5) "08001" 
      ["SQLSTATE"]=> string(5) "08001" 
      [1]=> int(5) 
      ["code"]=> int(5) 
      [2]=> string(111) "[Microsoft][SQL Server Native Client 11.0]Named Pipes Provider: Could not open a connection to SQL Server [5]. " 
      ["message"]=> string(111) "[Microsoft][SQL Server Native Client 11.0]Named Pipes Provider: Could not open a connection to SQL Server [5]. " 
      } 
[1]=> array(6) { 
      [0]=> string(5) "HYT00" 
      ["SQLSTATE"]=> string(5) "HYT00" 
      [1]=> int(0) 
      ["code"]=> int(0) 
      [2]=> string(63) "[Microsoft][SQL Server Native Client 11.0]Login timeout expired" 
      ["message"]=> string(63) "[Microsoft][SQL Server Native Client 11.0]Login timeout expired" 
      } 
[2]=> array(6) { 
      [0]=> string(5) "08001" 
      ["SQLSTATE"]=> string(5) "08001" 
      [1]=> int(5) 
      ["code"]=> int(5) 
      [2]=> string(330) "[Microsoft][SQL Server Native Client 11.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"]=> string(330) "[Microsoft][SQL Server Native Client 11.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." } 
      } 

// SECOND CONNECT
array(2) { 
[0]=> array(6) { 
      [0]=> string(5) "01000" 
      ["SQLSTATE"]=> string(5) "01000" 
      [1]=> int(5701) 
      ["code"]=> int(5701) 
      [2]=> string(92) "[Microsoft][SQL Server Native Client 11.0][SQL Server]Changed database context to 'poswest'." 
      ["message"]=> string(92) "[Microsoft][SQL Server Native Client 11.0][SQL Server]Changed database context to 'poswest'." 
      } 
[1]=> array(6) { 
      [0]=> string(5) "01000" 
      ["SQLSTATE"]=> string(5) "01000" 
      [1]=> int(5703) 
      ["code"]=> int(5703) 
      [2]=> string(93) "[Microsoft][SQL Server Native Client 11.0][SQL Server]Changed language setting to us_english." 
      ["message"]=> string(93) "[Microsoft][SQL Server Native Client 11.0][SQL Server]Changed language setting to us_english." 
      } 
} 
Test Complete

Select allOpen in new window

At least we've got some error codes and messages.  You might try searching for those things and seeing if any of the online postings at microsoft.com sound credible.

Can you run queries after the second attempt to connect?  Some of the posts I found suggested that SQLSTATE 01000 can be ignored.

On my 4 SQL Servers, I always use SQL Auth with TCP/IP connections instead of Named Pipes and I don't have these problems.

Unfortunately the one program was designed by a programmer who isn't here anymore and I can't change his program. :(  His program works ok, it just seems to be the connection string on the TCP connection I'm doing.  I'm going to try a few more things and let everyone know.

Thanks for your help so far!!
Zack

Thanks all!  I moved over the web server to the database server and it seems to be working a lot better.  I still don't have a conclusion as to what solved it, but thanks for all your help!

Thanks for the points and thanks for using EE.  Glad you've got it working, ~Ray

If you still want an answer it may be that you have "AutoClose" property set on the database level.

SQL Server will automatically shut down a database temporarily when this property is set.  When the first connection request hits that database, it has to "wake up" the database.

Very often, this first connection will time out with an error "connot connect".

By the time you try it again, it will be awake enough to respond to your program.

Check that setting in "Database/Properties/Options"