locked
Wordpress ans SQL Server 2008 R2 - named instance RRS feed

  • Question

  • I am trying to install Wordpress and access a SQL Server 2008 R2 database.  The database I am trying to access is a named instance, and I am using the port number ot access it (ex: 127.0.0.1,1526) I have identified that the conenction can be made via SQL management tools, using the username/password combination I have.

    I walked through this technet article:

    http://social.technet.microsoft.com/Forums/en-US/sqldriverforphp/thread/9305eb95-a2b9-4076-9635-1b5b81997d31

    Which was helpful - however, I am still getting the dreaded "Error establishing a database connection".

    I am running Windows Server 2008, IIS7 adn PHP 5.3 (I just installed this)

    I cannot figure out a way to make the error message more descriptive, to dig deeper.  Any directions to dig would be extremely helpful.

    Friday, March 23, 2012 7:18 PM

Answers

  • Hi jjborland,

    After analyzing the Network Monitor tace, I found the following information:
    Tcp: [SynReTransmit #8833]Flags=......S., SrcPort=63475, DstPort=1526, PayloadLen=0, Seq=2923606475, Ack=0, Win=8192 (  ) = 8192

    It means the client(the web server in this case) can not connect to the SQL Server machine using TCP port 1526.

    In addition, with a remote assistance, I noticed the ODBC connection from the web server to the SQL Server is failed too. So, finally it seems to be the port 1526 is blocked between the web server and the SQL Server.

    Thanks,
    Jinchun Chen


    Jinchun Chen
    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff AT microsoft.com(Please replace AT with @)

    • Marked as answer by jjborland Friday, April 6, 2012 7:05 PM
    Friday, April 6, 2012 4:27 PM

All replies

  • Hi jjborland,

    Could you please double check the connection definition in case there are any blank or unnecessary characters existing? Also, please pay attention to the file extension of wp-config file which should be ‘.php’.

    If the error persists, please elaborate a bit more about how the error occurs.


    Stephanie Lv

    TechNet Community Support

    Monday, March 26, 2012 6:18 AM
  • I've doubled checked that there are no extraneous spaces, etc. The spot wat which I recieve the error message is when I try to run the database portion of the config file creation.

    http://$wordpressweburl$/wp-content/mu-plugins/wp-db-abstraction/setup-config.php

    This open a screen that requests the database name, server instance name, username and password.

    It then returns a screen that just says "Error establishing a database connection".  The rest of the message is as such:

    "This either means that the username and password information in your wp-config.php file is incorrect or we can't contact the database server at 65.182.196.226,1526. This could mean your host's database server is down.

    • Are you sure you have the correct username and password?
    • Are you sure that you have typed the correct hostname?
    • Are you sure that the database server is running?

    If you're unsure what these terms mean you should probably contact your host. If you still need help you can always visit the WordPress Support Forums."

    part of my problem is the vagueness of this error.  I can't figure out how to get more detailed informaiont.

    Monday, March 26, 2012 2:32 PM
  • Are you using SQLAuth, or Windows Auth when you log in via SSMS?

    Cheers,

    Jonathan


    This posting is provided 'AS IS' with no warranties, and confers no rights.

    Monday, March 26, 2012 6:20 PM
  • SQLAuth
    Monday, March 26, 2012 8:34 PM
  • Have you tried connecting with a basic PHP script to verify there is no problem with the PHP setup? Are you using a default instance, or a named instance for your SQL Server?

    It would be useful if you just did a basic connection from a small PHP script so that we can see if the driver is raising any errors...

    Thanks,

    Jonathan


    This posting is provided 'AS IS' with no warranties, and confers no rights.

    Monday, March 26, 2012 8:59 PM
  • Yes, it would.  However, I know nothing about PHP - so I am having difficulty finding a script that will work.  Coudl you point me in a direction?
    Monday, March 26, 2012 9:43 PM
  • Could you please try the code sample supplied in this help document?

    http://msdn.microsoft.com/en-us/library/cc296161%28v=sql.90%29.aspx

    <?php
    /*
    Connect to the local server using Windows Authentication and specify
    the AdventureWorks database as the database in use. To connect using
    SQL Server Authentication, set values for the "UID" and "PWD"
     attributes in the $connectionInfo parameter. For example:
    $connectionInfo = array("UID" => $uid, "PWD" => $pwd, "Database"=>"AdventureWorks");
    */
    $serverName = "(local)";
    $connectionInfo = array( "Database"=>"AdventureWorks");
    $conn = sqlsrv_connect( $serverName, $connectionInfo);
    
    if( $conn )
    {
         echo "Connection established.\n";
    }
    else
    {
         echo "Connection could not be established.\n";
         die( print_r( sqlsrv_errors(), true));
    }
    
    //-----------------------------------------------
    // Perform operations with connection.
    //-----------------------------------------------
    
    /* Close the connection. */
    sqlsrv_close( $conn);
    ?>

    Thanks!

    Jonathan


    This posting is provided 'AS IS' with no warranties, and confers no rights.

    Monday, March 26, 2012 10:57 PM
  • Ok, so now I get this jumble.....

    Connection could not be established. Array ( [0] => Array ( [0] => 08001 [SQLSTATE] => 08001 [1] => 10060 [code] => 10060 [2] => [Microsoft][SQL Server Native Client 11.0]TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. [message] => [Microsoft][SQL Server Native Client 11.0]TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. ) [1] => Array ( [0] => HYT00 [SQLSTATE] => HYT00 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Server Native Client 11.0]Login timeout expired [message] => [Microsoft][SQL Server Native Client 11.0]Login timeout expired ) [2] => Array ( [0] => 08001 [SQLSTATE] => 08001 [1] => 10060 [code] => 10060 [2] => [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] => [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. ) )

    I do know that the databsae is capable of being connected to from the web server, as I am connecting to a database on the same instance using asp.net.

    Monday, March 26, 2012 11:18 PM
  • What connection options and string did you use for your PHP script?

    What connection options did you use for SQLCMD?

    What connections options did you use for ASP.NET?

    Thanks!

    Jonathan


    This posting is provided 'AS IS' with no warranties, and confers no rights.

    Monday, March 26, 2012 11:21 PM
  • PHP connection (in the script)

    $serverName = "65.182.196.226,1526";
    $connectionInfo = array("UID" => "blahblah", "PWD" => "moreofthe same", "Database"=>"theDBIneed");
    $conn = sqlsrv_connect( $serverName, $connectionInfo);

    In SQL Management tools, I used 65.182.196.226,1526 as the name of the database engine, and used the same username password combination as the script.

    In asp.net:

        <add key="ConnectionString" value="server=65.182.196.226,1526;database=the dBINeed;uid=blahblah;password=moreofhesame;" />

    Monday, March 26, 2012 11:27 PM
  • Did you use the SQL Management tools on the database server or on your web server?

    You might have a firewall blocking port 1526 which would not stop Management Studio connecting if it is run from the same SQL Server computer.

    Also SQL Server has a setting to allow or not allow remote connections - however, you did not receive a message that indicates that, so maybe it's a firewall problem.

    To be briefer, make sure you can connect to server "65.182.196.226,1526" using SQL Server Management Studio on your web server computer that runs PHP.  That will help find out what's wrong.


    Rob

    Tuesday, March 27, 2012 11:08 AM
  • It's not the firewall.  I can connect to the DB form outside the server, ad am connecting from the webserver to the DB server using asp.net and the port 1526.
    Tuesday, March 27, 2012 11:37 AM
  • There are some more things you could test:

    1. Try to create an ODBC data source (from Windows control panel), selecting driver "SQL Server Native Client 11.0", and using your settings that worked in ASP, and make a connection from the driver manager.
    2. Run your ASP code that makes the connection.
      After making your ASP connection (and while the connection is alive), open a console window on your web server and run the command "netstat -b", to see how ASP has connected to the server.  If it's not on the list, it must have connected (somehow) using another protocol.

    Rob

    Tuesday, March 27, 2012 12:30 PM
  • jjborland,

    Did Robert's troubleshooting steps help you discover the cause?

    Thanks,

    Jonathan


    This posting is provided 'AS IS' with no warranties, and confers no rights.

    Tuesday, April 3, 2012 5:38 PM
  • No, unfortunately. I was able to conenct using ODBC, and I as able to see the connection using optino 2.

    I am still stumped.

    Tuesday, April 3, 2012 5:51 PM
  • 2 more ideas:

    1. I know you said your port was open - just to eliminate the possibility, do you have a local firewall that filters connections based on the application name or file name, or user account? 

    2. Try to run Jonathan's connection script from the PHP command line:

      E.g.

      • Open a console window
      • Save Jonathan's script to a folder, e.g. C:\Tests\connect.php
      • Run PHP - this assumes PHP is in C:\Program Files\PHP, change it to your own location:
        "C:\Program Files\PHP\PHP.exe" "c:\tests\connect.php"


      When you run like this, it will run with the same permissions you used to create the ODBC connection.


    Rob


    Tuesday, April 3, 2012 6:41 PM
  • Hi jjborland,

    I can reproduce your issue in a particular scenario. In order to install the WordPress, we need to create an empty database(e.g. "WordPress") in SQL Server 2008 R2 as prerequirement. In case the login we use in the installation does not have the permissions to access the database(e.g. "WordPress"), we will get the error as you mentioned in your first reply.

    So, to fix the issue, please follow these steps:
    1. Open the SQL Server Management Studio(SSMS)
    2. Connect to the database engine with the login used in the installation of WordPress
     Server Name: 127.0.0.1,1526 or 65.182.196.226,1526
     Authentication: SQL Server Authentication
     Login: the UID used in the installation
     Password: the PWD used in the installation
    3. Check if you are available to open the database for WordPress.
    4. If not, please open SSMS, and connect to the same engine with the administration account(or other account that has System Role)
    5. Create a new user for the WordPress database with the login(used for the installation)
    6. Now, install the WordPress again, all will be fine now.

    One more thing to be confirmed, did you get "Cannot open database "wordPress" requested by the login" from the following code?
    PHP connection (in the script)
    $serverName = "65.182.196.226,1526";
     $connectionInfo = array("UID" => "blahblah", "PWD" => "moreofthe same", "Database"=>"theDBIneed");
     $conn = sqlsrv_connect( $serverName, $connectionInfo);

    If you have any more questions, please feel free to ask.

    Thanks,
    Jinchun Chen


    Jinchun Chen
    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff AT microsoft.com(Please replace AT with @)

    Wednesday, April 4, 2012 6:36 AM
  • That's a great bit of research Jinchun, but according to this thread, the connection failed when running the sample script from Jonathan, with the error: "TCP Provider: A connection attempt failed ...".


    Rob

    Wednesday, April 4, 2012 11:21 AM
  • Thanks Jinchun.  But Robert is correct.  I am able to connect using SSMS and build tables, etc.  The user is set as dbOwner at this time, so there is no issue there.

    And as Robert pointed out, when using the script, I am getting a SQL timeout error.

    Wednesday, April 4, 2012 12:23 PM
  • Thank you Robert for pointing me that out.

    @jjborland, how about using serverName\instanceName as the host server?
    Please note, while using instanceName, we need to ensure the UDP 1434 port is opened from Firewall and SQL Server Browser service is running.

    Thanks,
    Jinchun Chen


    Jinchun Chen
    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff AT microsoft.com(Please replace AT with @)

    Wednesday, April 4, 2012 1:50 PM
  • 2 more ideas:

    1. I know you said your port was open - just to eliminate the possibility, do you have a local firewall that filters connections based on the application name or file name, or user account? 

    2. Try to run Jonathan's connection script from the PHP command line:

      E.g.

      • Open a console window
      • Save Jonathan's script to a folder, e.g. C:\Tests\connect.php
      • Run PHP - this assumes PHP is in C:\Program Files\PHP, change it to your own location:
        "C:\Program Files\PHP\PHP.exe" "c:\tests\connect.php"


      When you run like this, it will run with the same permissions you used to create the ODBC connection.


    Rob


    jjborland,

    Did running the script from the command line make any difference?

    Cheers,

    Jonathan


    This posting is provided 'AS IS' with no warranties, and confers no rights.

    Wednesday, April 4, 2012 5:24 PM
  • Running it this way gives me an error message of:

    "This application has failed to start because php5ts.dll was not found.  Re-installing the application may fix this problem."

    I did not attempt reinstallation of PHP, as the dll in question was not in the installation package.

    Wednesday, April 4, 2012 8:58 PM
  • Ahh - which version of PHP did you install? If you wish to use it with IIS, you should be using the Non-Thread Safe (NTS) version:
    http://www.php.net/manual/en/install.windows.iis7.php

    Thanks,

    Jonathan


    This posting is provided 'AS IS' with no warranties, and confers no rights.

    Wednesday, April 4, 2012 11:21 PM
  • VC9 x86 Non Thread Safe (2012-Feb-29 21:16:09)

    Thursday, April 5, 2012 3:59 AM
  • Hi jjborland,

    Could you please use the Network Monitor to monitor what happens during the connection? You can save the trace file and send it to me for analysisig.

    Thanks,
    Jinchun Chen


    Jinchun Chen
    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff AT microsoft.com(Please replace AT with @)

    Thursday, April 5, 2012 4:39 AM
  • Excuse my ignorance.  How do I send said file?
    Thursday, April 5, 2012 11:29 AM
  • You can use Skydrive: https://skydrive-df.live.com/

    or Dropbox: https://www.dropbox.com/

    Or, if you prefer, please email it to jguerin /a t/ microsoft /d o t/ com and I will forward it to Jinchun.

    Thanks,

    Jonathan


    This posting is provided 'AS IS' with no warranties, and confers no rights.

    Thursday, April 5, 2012 8:56 PM
  • Hi jjborland,

    Thank you for your comprehesion. I will analyze the trace file soon.

    Anyway, is it possible to have a remote assistance? It would be more effective. If it is fine, could you please send me your mail address to  tnmff AT microsoft.com(Please replace AT with @)?

    Thanks,
    Jinchun Che


    Jinchun Chen
    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff AT microsoft.com(Please replace AT with @)

    Friday, April 6, 2012 1:47 PM
  • Hi jjborland,

    After analyzing the Network Monitor tace, I found the following information:
    Tcp: [SynReTransmit #8833]Flags=......S., SrcPort=63475, DstPort=1526, PayloadLen=0, Seq=2923606475, Ack=0, Win=8192 (  ) = 8192

    It means the client(the web server in this case) can not connect to the SQL Server machine using TCP port 1526.

    In addition, with a remote assistance, I noticed the ODBC connection from the web server to the SQL Server is failed too. So, finally it seems to be the port 1526 is blocked between the web server and the SQL Server.

    Thanks,
    Jinchun Chen


    Jinchun Chen
    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff AT microsoft.com(Please replace AT with @)

    • Marked as answer by jjborland Friday, April 6, 2012 7:05 PM
    Friday, April 6, 2012 4:27 PM
  • I just had a look at that network log too.

    IIS - maybe the ASP connection that works - is connecting to 65.182.196.226 on port 1433.

    Are you sure 1526 is the right port?  Is 1433 a different instance to the one you want?

    If 1526 is definitely the right port, then it is probably your firewall.  Otherwise try 1433.

    Earlier up the thread you stated that you succeeded in making an ODBC connection from Control Panel, but Jinchun could not connect.


    Rob

    Friday, April 6, 2012 4:36 PM
  • Thanks for all your help.  apparently somewhere along the line the hosting company decided to close that port again.

    I've now got connectivity, and Wordpress running. 

    Friday, April 6, 2012 7:05 PM
  • Glad you got it sorted out. :)

    Cheers,

    Jonathan


    This posting is provided 'AS IS' with no warranties, and confers no rights.

    Saturday, April 7, 2012 12:16 AM
  • Amazing.

    When I said:

    1. To be briefer, make sure you can connect to server "65.182.196.226,1526" using SQL Server Management Studio on your web server computer that runs PHP.  That will help find out what's wrong.
    2. Try to create an ODBC data source (from Windows control panel), selecting driver "SQL Server Native Client 11.0", and using your settings that worked in ASP, and make a connection from the driver manager.
    3. Run your ASP code that makes the connection.
      After making your ASP connection (and while the connection is alive), open a console window on your web server and run the command "netstat -b", to see how ASP has connected to the server.  If it's not on the list, it must have connected (somehow) using another protocol.

    Your hosting company must have re-opened port 1526 at the exact moment you ran these tests, and then closed it again straight afterwards.


    Rob

    Saturday, April 7, 2012 12:32 AM
  • I love the sarcasm.

    However, when you said to do those things, I had already tested those - when I first installed the named instance.  Those were the first tests I ran to make sure I could connect between the two.

    then PHP failed.  So I instaled different drivers, PHP still failed - I was able to connect between my office computer and the named instance, so I never suspected that they would close that port between those two servers without closing it for all traffic.

    Saturday, April 7, 2012 11:28 AM