Solved

Remote access to SQL Express data

Posted on 2014-04-16
4
673 Views
Last Modified: 2014-04-16
I have code to connect to a SQLexpress database in house via PHP code and vb.net code. Works Fine.
When I run the same code on a remote server over an IPSec tunnel, I can't connect with PHP.
I have verified that:
- SQLExpress is checked for allow remote access
- I have browser turned on
- I have IP2 configured with Local IP, port 1433 and enabled
- I have IPAll TCP dynamic ports blank and TCP port to 1433
- the servername resolves and I have tried <ip>\sqlExpress as well as <server>\SQLExpress for my connection name.

- I have loaded SQL management studio on my remote computer and it DOES connect
- running my vb.NET connects fine.

But, I can't connect to the database via PHP.
The server is on IIS and PHP 5.3. I'm using the downloaded MS SQLSRV drivers for PHP.
[PHP_SQLSRV_53_NTS]
extension=php_sqlsrv_53_nts.dll


//Code I'm running
<?php
$serverName = "<server>/sqlexpress";
$connectionInfo = array( "Database"=>"myDatabase", "UID"=>"aUser", "PWD"=>"aPassword");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
     die( print_r( sqlsrv_errors(), true));
}

/* Set up a query to select an invalid column name. */
$sql = "SELECT aField FROM myDBFTable";

/* Execution of the query will fail because of the bad column name. */
$stmt = sqlsrv_query( $conn, $sql );
if( $stmt === false ) {
    if( ($errors = sqlsrv_errors() ) != null) {
        foreach( $errors as $error ) {
            echo "SQLSTATE: ".$error[ 'SQLSTATE']."<br />";
            echo "code: ".$error[ 'code']."<br />";
            echo "message: ".$error[ 'message']."<br />";
        }
    }
}
?>

//Error returned is
Fail in Select from search:
SQLSTATE: IMSSP
 code: -14
 message: An invalid parameter was passed to sqlsrv_query.

google says it's because remote access isn't allowed. But VB.net code and SQL management server says otherwise.  

Please advise.
0
Comment
Question by:davebird
  • 2
  • 2
4 Comments
 

Author Comment

by:davebird
ID: 40005030
Should have previewed better.  The remark line
/* Execution of the query will fail because of the bad column name. */ should be ignored.
The column in my query is valid. I copied and pasted my code and didn't remove that line. Sorry.
0
 
LVL 83

Accepted Solution

by:
Dave Baldwin earned 500 total points
ID: 40005102
TCP should work on port 1433 (since you have it set up) but connecting to an 'instance name' like <server>\SQLExpress requires the sqlbrowser.exe service running on port 1434.  Have you installed the SQL Native Client on the remote computer?  It is required for the 'sqlsrv' driver to work.  It also must be configured to use 'tcp/ip' connections.
0
 

Author Closing Comment

by:davebird
ID: 40005262
Thank you.  I added the SQL management to the remote Server (not my remote workstation) and it did not connect.  I do not have DNS that resolves to the data server on THAT server.  Added the entry and all is well. Thank you for your response and help.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40005362
You're welcome, glad to help.
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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.

920 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

13 Experts available now in Live!

Get 1:1 Help Now