Laravel connects to an instance called SQL Server 2008

Question

I am trying to connect a SQL server from an Ubuntu machine, everything works fine, except for named instances:

works

'data' => array( 'driver' => 'sqlsrv', 'host' => 'xxxx', 'port' => 1433, 'database' => 'db', 'username' => 'user', 'password' => 'pwd', 'prefix' => '', ), 

is not

  'data' => array( 'driver' => 'sqlsrv', 'host' => 'yyyy\NAMEDINSTANCE', 'port' => 1433, 'database' => 'db', 'username' => 'user', 'password' => 'pwd', 'prefix' => '', ), 

I always get 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 tried all possible combinations:

• host \ INSTANCE
• host / INSTANCE
• host \\ INSTANCE

Can someone help me?

Edit : Since I also tried without an instance name ( as indicated here ), the script continues to try 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, like management studio, DBeaver or Database.NET, can connect to this instance only with INSTANCENAME , so it seems to be a PDO problem with DSN

I also tried connecting to this named instance directly using tsql without tsql much luck:

 tsql -S SERVER -U usr -P pwd -L dbname 

Here's 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 an unnamed instance), everything goes smoothly ...

Answer 1

I finally found a solution, there were two problems:

• SQL Server did not listen on the good default port (my bad)
• Laravel (PDO?) Doesn't know how to handle (or at least I didn't find how) named instances, I tried any possible combination (see question)

So, I finally used a combination of FreeTDS DSN with laravel to connect instance instances SQL Server.

DSN configuration /etc/freetds.conf :

 [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 this helps someone too.

Answer 2

Thank you for participating in solving this connectivity issue. I also ran into this problem, this is how I solved it.

For information in my case, the connection to tsql works, but not since Laravel (5.4)

One trick that I realized is that it is not used by default ( 1433 ).

To find the port, you must start a connection to the shell:

 tsql -D DB -S "IP\INSTANCE" -U login -P pass 

And check in the logs that the good port here is 1168

Net.c: 1059: instance port - 1168

The contents of the freetds.conf file

 [global] text size = 64512 dump file = /var/log/freetds.log dump file append = yes [mssql] host = MSSQLSRV port = 1168 tds version = auto instance = IP\INSTANCE dump file = /var/log/freetds.log dump file append = yes 

File Contents: config / database.php

  'sqlsrv' => [ 'driver' => 'sqlsrv', 'host' => 'mssql', 'port' => '1168', 'database' => 'DB', 'username' => 'login', 'password' => 'pass', 'prefix' => '', ], 

Now everything is working correctly.