?
Solved

Initial Connection to MS SQL 2012 fails everytime

Posted on 2014-02-25
13
Medium Priority
?
447 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 111

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 111

Expert Comment

by:Ray Paseur
ID: 39887262
Can we please see the connection code that generates this message?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 1000 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 84

Accepted Solution

by:
Dave Baldwin earned 1000 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 111

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 84

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 111

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

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Course of the Month15 days, 1 hour left to enroll

840 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