none
Remote Connection on sqlsrv with PHP

    Question

  • Hey, how can i establish a remote connection to a webserver using SQL Server driver for PHP ? I must use the IP address of the computer (that is serving) along with the server name right ?
    Friday, September 10, 2010 3:35 PM

Answers

  • Ashay has given you the answer.

    I would like to add some extra information and examples for various configurations I have encountered.

    The default SQL Server instance will use port 1433.

    So supposing you have server MY_SERVER on IP address 10.20.30.40, and it has a default SQL Server instance, you can connect using:

    sqlsrv_connect('10.20.30.40', array('UID'=>'me', 'PWD'=>'pwd', ...)); // port number not required

    Your server might not use the default port of 1433, or it might have several SQL Server instances...  if so, read on...

    If the server is configured as instances (e.g. MY_SERVER\SQL1, MY_SERVER\SQL2), each SQL Server instance will have a different port number - this port number is chosen by whoever sets the servers up, and is usually a fixed number (it can also be dynamic, see below *) .  You can only connect with the sqlsrv driver when the instance is configured to use a fixed port number.

    So you might be given an IP address and a port by your DBA, and connect like this - e.g. suppose instance MY_SERVER\SQL1 has been set up on port 4444:

    sqlsrv_connect('10.20.30.40,4444', array('UID'=>'me', 'PWD'=>'pwd', ...));

    Or perhaps you might have a DNS address:

    sqlsrv_connect('my_server.jp.domain.com,4444', array('UID'=>'me', 'PWD'=>'pwd', ...));

    Whichever port you require, make sure it's open on the server and not blocked by your own firewall.

    * When the IP address is dynamic, you need to query the host for SQL Server instances and their port numbers.  I have never needed to do it, but it requires an API like the ODBC call 'SQLBrowseConnect', and port 1434 (browse service) must be open, i.e. not blocked by your firewall.

    Monday, September 13, 2010 4:59 PM

All replies

  • By remote connection, I presume that the web server and database server are different machines/computers. The SQL Server PHP driver needs to be installed on the web server in the \ext folder of the PHP version you are using.

    Assuming you are set up for SQL Authentication, you can use either the database server name ("mydbserver") or IP address (1.1.1.1) - the exact syntax depends on whether you care using SQLSRV or PDO_SQLSRV driver. In the first case, the "mydbserver" will resolve to an IP address for the routing required, the IP address takes you straight to it.


    Ashay Chaudhary
    SQL Server Driver for PHP (http://blogs.msdn.com/sqlphp)
    All information provided "as-is" and without warranty.
    Friday, September 10, 2010 10:48 PM
  • Ashay has given you the answer.

    I would like to add some extra information and examples for various configurations I have encountered.

    The default SQL Server instance will use port 1433.

    So supposing you have server MY_SERVER on IP address 10.20.30.40, and it has a default SQL Server instance, you can connect using:

    sqlsrv_connect('10.20.30.40', array('UID'=>'me', 'PWD'=>'pwd', ...)); // port number not required

    Your server might not use the default port of 1433, or it might have several SQL Server instances...  if so, read on...

    If the server is configured as instances (e.g. MY_SERVER\SQL1, MY_SERVER\SQL2), each SQL Server instance will have a different port number - this port number is chosen by whoever sets the servers up, and is usually a fixed number (it can also be dynamic, see below *) .  You can only connect with the sqlsrv driver when the instance is configured to use a fixed port number.

    So you might be given an IP address and a port by your DBA, and connect like this - e.g. suppose instance MY_SERVER\SQL1 has been set up on port 4444:

    sqlsrv_connect('10.20.30.40,4444', array('UID'=>'me', 'PWD'=>'pwd', ...));

    Or perhaps you might have a DNS address:

    sqlsrv_connect('my_server.jp.domain.com,4444', array('UID'=>'me', 'PWD'=>'pwd', ...));

    Whichever port you require, make sure it's open on the server and not blocked by your own firewall.

    * When the IP address is dynamic, you need to query the host for SQL Server instances and their port numbers.  I have never needed to do it, but it requires an API like the ODBC call 'SQLBrowseConnect', and port 1434 (browse service) must be open, i.e. not blocked by your firewall.

    Monday, September 13, 2010 4:59 PM
  • Robert has provided a much better answer.

    Question: does it make sense for the driver to provide a discovery mechanism? Either a very simple one (that uses SQLBrowseConnect() to be called before making the connection), or a fancy one (some way to describe intent when making the connection).


    Ashay Chaudhary
    SQL Server Driver for PHP (http://blogs.msdn.com/sqlphp)
    All information provided "as-is" and without warranty.
    Wednesday, September 15, 2010 5:59 PM
  • Hi Ashay

    This might be a good discussion topic... so far I have not needed to use discovery, but I have used it in the past, and I'm certain that the requirement will come up in the future.

    So I would like to see something like sqlsrv_browse_connect added to the api...

     

    Monday, September 20, 2010 10:28 AM
  • Sounds good, I'll start one on this topic later today.
    Ashay Chaudhary
    SQL Server Driver for PHP (http://blogs.msdn.com/sqlphp)
    All information provided "as-is" and without warranty.
    Monday, September 20, 2010 2:01 PM