Solved

Initial Connection to MS SQL 2012 fails everytime

Posted on 2014-02-25
13
420 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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
Can we please see the connection code that generates this message?
0
 

Author Comment

by:IT Tech
Comment Utility
$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 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 82

Accepted Solution

by:
Dave Baldwin earned 250 total points
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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 82

Expert Comment

by:Dave Baldwin
Comment Utility
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
Comment Utility
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
Comment Utility
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 108

Expert Comment

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

Expert Comment

by:Jerry_Justice
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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 shrink a transaction log file down to a reasonable size.

771 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

10 Experts available now in Live!

Get 1:1 Help Now