Solved

Remote access to SQL Express data

Posted on 2014-04-16
4
670 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
Comment Utility
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 82

Accepted Solution

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

Expert Comment

by:Dave Baldwin
Comment Utility
You're welcome, glad to help.
0

Featured Post

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

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

772 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

15 Experts available now in Live!

Get 1:1 Help Now