How to connect to named instance with ODBC Driver 17 on PHP 7?

Question

I am trying to connect to an MS SQL database that is a named instance from my linux box running PHP 7.1. I am using ODBC Driver 17 for SQL Server.

I have successfully installed the ODBC driver from Microsoft and successfully connected to databases that are not named instances. For the named instance, I have tried every combination that I can think of (with/without port, quotes, brackets, etc.)

//$host = 'mySVR-SQL'; // THIS WORKS
//$host = '10.10.10.12345'; // THIS ALSO WORKS
$host = "SERV\INSTANCE_NAME"; // THIS DOES NOT WORK
$host = "SERV\INSTANCE_NAME,1433"; // THIS DOES NOT WORK
$host = "[SERV\INSTANCE_NAME]"; // THIS DOES NOT WORK
$host = '123.12.34.56\INSTANCE_NAME'; // THIS DOES NOT WORK
$user = 'my_username';
$pass = 'my_password';
$db = 'my_database';

$conn = "odbc:DRIVER={ODBC Driver 17 for SQL Server};SERVER=$host;DATABASE=$db";

$db_c = new PDO($conn, $user, $pass);

//connection string without variables for troubleshooting
$db_c = new PDO("odbc:Driver={ODBC Driver 17 for SQL Server};Server=123.12.34.56\INSTANCE_NAME;Database=MyDatabase", "my_username", "my_password");

This is the error message I get:

SQLSTATE[HYT00] SQLDriverConnect: 0 [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired

What can I do to further troubleshoot this issue? Note that I was able to connect fine before using pdo_dblib drivers on php 5.6. But since we are upgrading to PHP 7, I figured we should use the microsoft drivers (which work fine, except for the named instance.)

This DOES work (using dblib):

$db_c = new PDO("dblib:host=123.12.34.56\INSTANCE_NAME;dbname=MyDatabase;charset=utf8", "my_username", "my_password");

user_14487033 · Answer

For a MS SQL database with a named instance and a custom port this will work:

new \PDO("sqlsrv:Server=IP_ADDRESS\INSTANCE_NAME,CUSTOM_PORT;Database=DATABASE_NAME", "my_username", "my_password");

You need to escape the backslash by using 2 backslashes. And if there is a custom port you need to include it in the connection string.

How to connect to named instance with ODBC Driver 17 on PHP 7?

1 Answers