Laravel 连接到 SQL Server 2008 命名实例

Laravel connect to a SQL Server 2008 named instance

提问by kitensei

I am trying to connect an SQL server from an Ubuntu machine, everythings works great except for named instances:

我正在尝试从 Ubuntu 机器连接 SQL 服务器，除了命名实例外，一切都很好：

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'     => 'yyyy\NAMEDINSTANCE',
                '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

任何编辑器如 Management studio、DBeaver 或 Database.NET 只能通过指定INSTANCENAME连接到该实例，因此 DSN 似乎更像是 PDO 问题

I also have tried to connect to that named instance directly with tsqlwithout any more luck:

我还尝试直接连接到该命名实例，tsql但没有任何运气：

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

Here the /etc/freetds.conffile:

这里的/etc/freetds.conf文件：

[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:

和 tds 日志文件：

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...

当然，如果我尝试连接到SERVER2（这是一个非命名实例），一切都会顺利...

回答by kitensei

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)

• SQL 服务器没有侦听好的默认端口（我的不好）
• Laravel (PDO ?) 不知道如何处理（或者至少我还没有找到如何处理）命名实例，我尝试了任何可能的组合（见问题）

So I finally used a combination of FreeTDS DSN with laravel in order to connect the SQL named instance server.

所以我最终使用了 FreeTDS DSN 和 laravel 的组合来连接 SQL 命名实例服务器。

The /etc/freetds.confDSN configuration:

该/etc/freetds.confDSN配置：

[NAMED_INSTANCE]
   host = 127.0.0.1
   port = 55021

And in the laravel database adapter:

在 Laravel 数据库适配器中：

'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

这解决了我的问题，希望它也能帮助别人

回答by 120DEV

Thanks for the participation to solve this connection problem. I also encountered this problem, here is how I solved it.

感谢您参与解决此连接问题。我也遇到了这个问题，下面是我解决的方法。

For info in my case the connection with tsql works but not since Laravel (5.4)

对于我的信息，与 tsql 的连接有效，但自 Laravel (5.4) 以来就无效

One trick I took to understand is that it is not the default port (1433) that is used.

我理解的一个技巧是它不是使用的默认端口（1433）。

To find the port you must start a shell connection:

要找到端口，您必须启动 shell 连接：

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

And check in the logs the good port here is 1168

并检查日志，这里的好端口是1168

Net.c: 1059: instance port is 1168

Net.c: 1059: 实例端口为 1168

Contents of the file freetds.conf

文件freetds.conf 的内容

[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

Contents of the file : config/database.php

文件内容：config/database.php

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

Now everything is working properly.

现在一切正常。