Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Remote access to SQL Express data

Posted on 2014-04-16
4
Medium Priority
?
700 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

604 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