Named instances with dynamic ports, the workaround and possible implementation

[Sarke]

I've spent a couple of days struggling with why it wasn't able to connect, and I think I should share my findings and suggestions so it may help other people in a similar situation as well as the maintainers of this repo.

The current version of this repository doesn't correctly deal with named instances that are on a dynamic port (that is, not 1433), without having to manually look-up that port. The server name and instance should be enough. It's enough for jTDS and it's enough for SQL Server Management Studio. So why not this PHP extension?

I've managed to find two workarounds to this issue.

The first is to use the pdo_dblib extension instead, which uses the FreeTDS library (same one jTDS uses). I'm not sure how current it is with connecting to the latest SQL Servers though.

The second is to do what FreeTDS and SQL Server Management Studio does, and that is to send a UDP connection to port 1434 with the instance name, and the server will return the port information.

This is what should be done in the extension, but it is not even though it appears to be a recurring problem for people using this extension, such as:

#442 #888 #470

In a few of them, I see a replies like these:

Yes, it is by design. You need to provide port number to connect to a named instance. See this page. Related issues: #340, #190, #175

#442 (comment)

to connect to a named instance on a static port, you need to use port number. Please see here. You can also check the related issues: #442, #340, #190, #175.

#576 (comment)

However, I disagree, and that is just skirting the problem. If this was the case, then why doesn't the other libraries and Management Studio require the port as well? The port can change after a db server restart, so hard-coding the port is far from a desired solution.

So why not implement the correct solution?

Here's what the FreeTDS documentation states:

If you specify servername\instance as servername during login, FreeTDS will attempt to connect to specified instance. Only Microsoft SQL Server instances are supported. (This server feature was introduced with SQL Server 2000.)

For the technically curious: each SQL Server instance appears on the network as a server listening at a port. The old way — and it still works — is to designate each instance in freetds.conf as a separate server. The new "named instance" notation, if we can call it that, instead uses the server to discover the port. The library sends a UDP packet containing the instance name to the server at a well known port, port 1434. The server responds with a port number. FreeTDS then uses that number to connect in the usual way.

http://www.freetds.org/userguide/portoverride.htm

And here's a link to their implementation of the UDP request (in C):

https://github.com/FreeTDS/freetds/blob/7f6be032fbe853ccc766c885633db243e2eb599c/src/tds/net.c#L1227

Shouldn't Microsoft's own solution be able to connect to a Microsoft SQL Server as well as a open-source library can?

Finally, for those who are stuck using this extension and are not able or willing to use the pdo_dblib with FreeTBS solution, I offer this PHP workaround for discovering the port:

$server = '192.168.12.34\\Express';

// separate the host address from the isntance name
list($host, $instance) = explode('\\', $server);

$msg = chr(4) . $instance;

// make the UDP request
$socket = socket_create(AF_INET, SOCK_DGRAM, SOL_UDP);
socket_connect($socket, $host, 1434);
socket_write($socket, $msg, strlen($msg));
$buf = '';
socket_recv($socket, $buf, 2048, MSG_WAITALL);
socket_close($socket);

// format it nicely
$recv = explode(chr(0), $buf, 2);
$info = [];
foreach (array_chunk(explode(';', rtrim($recv[1], ';')), 2) as $pair)
    $info[$pair[0]] = $pair[1];

print_r($info);

$correctServer = $host . ',' . $info['tcp']; // 192.168.12.34,49161

This is basically what I am hoping the maintainers of this repo will implement in this extension. Granted I didn't include any error checking as I just put this together as a proof of concept.

This will give you something like this:

Array
(
    [ServerName] => windows-pc
    [InstanceName] => Express
    [IsClustered] => No
    [Version] => 10.50.1600.1
    [tcp] => 49161
    [np] => \\windows-pc\pipe\MSSQL$EXPRESS\sql\query
)

The tcp is what holds the port number. You also get the named pipe format in np. The port number can and probably should be cached since it does not frequently change.

I hope this post saves people the time I spent on this.

[david-puglielli]

@Sarke Thank you for your help, I'm sure other users will find it useful. Support for automatic port discovery is planned for a future release, so please stay tuned. It depends on the underlying ODBC driver - you can stay up-to-date with news on the blog.

[bjverde]

@Sarke Thank !! Your tip was central in resolving my problem.

Its work with Ubuntu 16.04, PHP 7.0
[DriverName] => libmsodbcsql-17.2.so.0.1
[DriverODBCVer] => 03.52
[DriverVer] => 17.02.0000
[ExtensionVer] => 5.3.0

My two cents contribution

/***
 * By Peter Stalman (Sarke) 
 * https://github.com/microsoft/msphpsql/issues/927
 */
function getPortSqlServer($host, $instance) {
	$msg = chr(4) . $instance;
	// make the UDP request
	$socket = socket_create(AF_INET, SOCK_DGRAM, SOL_UDP);
	socket_connect($socket, $host, 1434);
	socket_write($socket, $msg, strlen($msg));
	$buf = '';
	socket_recv($socket, $buf, 2048, MSG_WAITALL);
	socket_close($socket);


	// format it nicely
	$recv = explode(chr(0), $buf, 2);
	$info = [];
	foreach (array_chunk(explode(';', rtrim($recv[1], ';')), 2) as $pair)
		$info[$pair[0]] = $pair[1];


	//For Debug
	//var_dump($info);
	//$correctServer = $host . ',' . $info['tcp']; // 192.168.12.34,49161
	//var_dump($correctServer);
	
	return $info['tcp'];
}


function getHostPort($server, $port=null) {
	list($host, $instance) = explode('\\', $server);
	if( !empty($instance) && empty($port) ){
		$port = getPortSqlServer($host,$instance);
	}	
	if( !empty($port) ){
		$server = $server.','.$port;
	}	
	return $server;
}


$hostname = "SERVERNAME\INSTANCENAME";
$hostname = getHostPort($hostname);	
$dbname   = "myDataBase";
$username = "myUser";
$password = "myPass";

$conn = new PDO('sqlsrv:Server='.$hostname.';Database='.$dbname, $username , $password);

[david-puglielli]

The latest release of the ODBC driver, version 17.4, should fix any issues with named instances and dynamic port resolution on Linux. Instructions here.

[david-puglielli]

Closing issue as this should now be fixed. Please reopen if necessary.