?
Solved

Remote access to SQL Express data

Posted on 2014-04-16
4
Medium Priority
?
692 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
[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
  • 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 84

Accepted Solution

by:
Dave Baldwin earned 1500 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 84

Expert Comment

by:Dave Baldwin
ID: 40005362
You're welcome, glad to help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

765 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