I am trying to connect an SQL server from an Ubuntu machine, everythings works great except for named instances:
this works
'data' => array( 'driver' => 'sqlsrv', 'host' => 'xxxx', 'port' => 1433, 'database' => 'db', 'username' => 'user', 'password' => 'pwd', 'prefix' => '', ),
this doesn’t
'data' => array( 'driver' => 'sqlsrv', 'host' => 'yyyyNAMEDINSTANCE', 'port' => 1433, 'database' => 'db', 'username' => 'user', 'password' => 'pwd', 'prefix' => '', ),
I always end up with this error:
exception 'PDOException' with message 'SQLSTATE[HY000] Unknown host machine name (severity 2)' in /var/www/public/my.api/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:47
I have tried every possible combination:
- host INSTANCE
- host / INSTANCE
- host \ INSTANCE
Can someone help me ?
Edit: Because I have also tried without instance name (as stated here), the script keep trying to connect until I get this error:
exception 'PDOException' with message 'SQLSTATE[HY000] Unable to connect: Adaptive Server is unavailable or does not exist (severity 9)' in /var/www/public/my.api/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:47
Any editor as Management studio, DBeaver or Database.NET can connect to that instance only by specifying the INSTANCENAME, so it seems more a PDO problem witht the DSN
I also have tried to connect to that named instance directly with tsql
without any more luck:
tsql -S SERVER -U usr -P pwd -L dbname
Here the /etc/freetds.conf
file:
[global] tds version = 8.0 client charset = UTF-8 port = 1433 dump file = /tmp/freetds.log dump file append = yes text size = 64512 [SERVER] host = ip port = 1433 instance = instance_name [SERVER2] host = ip port = 1433
And the tds log file:
log.c:196:Starting log file for FreeTDS 0.91 on 2015-03-19 15:35:46 with debug flags 0x4fff. iconv.c:330:tds_iconv_open(0xc163a0, UTF-8) iconv.c:187:local name for ISO-8859-1 is ISO-8859-1 iconv.c:187:local name for UTF-8 is UTF-8 iconv.c:187:local name for UCS-2LE is UCS-2LE iconv.c:187:local name for UCS-2BE is UCS-2BE iconv.c:349:setting up conversions for client charset "UTF-8" iconv.c:351:preparing iconv for "UTF-8" <-> "UCS-2LE" conversion iconv.c:391:preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion iconv.c:394:tds_iconv_open: done net.c:205:Connecting to 195.70.16.92 port 1433 (TDS version 7.1) net.c:270:tds_open_socket: connect(2) returned "Operation now in progress" net.c:306:getsockopt(2) reported: Connection timed out net.c:316:tds_open_socket() failed util.c:331:tdserror(0xc16140, 0xc163a0, 20009, 110) util.c:361:tdserror: client library returned TDS_INT_CANCEL(2) util.c:384:tdserror: returning TDS_INT_CANCEL(2) mem.c:615:tds_free_all_results()
Of course, if I try to connect to SERVER2 (which is a non named instance) everything goes smoothly…
Advertisement
Answer
I finally found a solution, there were two problems :
- The SQL server wasn’t listening on the good default port (my bad)
- Laravel (PDO ?) doesn’t know how to handle (or at least I haven’t found how) named instances, I have tried any possible combination (see Question)
So I finally used a combination of FreeTDS DSN with laravel in order to connect the SQL named instance server.
The /etc/freetds.conf DSN configuration:
[NAMED_INSTANCE] host = 127.0.0.1 port = 55021
And in the laravel database adapter:
'webcmd' => array( 'driver' => 'sqlsrv', 'host' => 'NAMED_INSTANCE', 'database' => 'db', 'username' => 'usr', 'password' => 'pwd', 'prefix' => '', ),
And that solved my problem, hope it’ll help someone too