?
Solved

Initial Connection to MS SQL 2012 fails everytime

Posted on 2014-02-25
13
Medium Priority
?
441 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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 83

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 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 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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

801 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