Solved

Initial Connection to MS SQL 2012 fails everytime

Posted on 2014-02-25
13
428 Views
Last Modified: 2014-03-05
Hi All,

   I'm having an issue with MS SQL 2012 and when PHP connects, it fails automatically on the first attempt, but immediately connects on the second.  Can anyone help me with this?  I googled and found dynamic IP ports which I disabled and restarted the services, but still no luck.

Thanks - Zack
0
Comment
Question by:IT Tech
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39887190
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.
0
 

Author Comment

by:IT Tech
ID: 39887223
The error is "Connection to MS SQL could not be established."  It just means that the user would have to refresh the screen again.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39887262
Can we please see the connection code that generates this message?
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:IT Tech
ID: 39887269
$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.");

Open 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.
0
 
LVL 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
ID: 39887295
Let's give it a try like this and see if we can get a clue about the exact error condition.

$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); 
var_dump( sqlsrv_errors() );

$conn = sqlsrv_connect($serverName, $connectionInfo); 
var_dump( sqlsrv_errors() );

die("Test Complete");

Open in new window

0
 

Author Comment

by:IT Tech
ID: 39887300
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

Open in new window

0
 
LVL 83

Accepted Solution

by:
Dave Baldwin earned 250 total points
ID: 39887334
The error says you are using 'Named Pipes'.  I would go to the config for the SQL Native Client and enable TCP/IP connections and see if that works better.  They also have to be enabled on the server.  That is not the same as setting the IP address.  There is a specific checkbox for enabling remote TCP/IP connections.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39887345
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

Open 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.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39887539
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.
0
 

Author Comment

by:IT Tech
ID: 39887795
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
0
 

Author Closing Comment

by:IT Tech
ID: 39892403
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!
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39892516
Thanks for the points and thanks for using EE.  Glad you've got it working, ~Ray
0
 
LVL 3

Expert Comment

by:Jerry_Justice
ID: 39907960
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"
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days socially coordinated efforts have turned into a critical requirement for enterprises.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now