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
主持人\ 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 tsql
without any more luck:
我也尝试使用tsql直接连接到该命名实例而不再运气:
tsql -S SERVER -U usr -P pwd -L dbname
Here the /etc/freetds.conf
file:
这里是/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(这是一个非命名实例),一切顺利......
2 个解决方案
#1
7
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.conf DSN configuration:
/etc/freetds.conf DSN配置:
[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
这解决了我的问题,希望它也会帮助别人
#2
0
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.
现在一切正常。
#1
7
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.conf DSN configuration:
/etc/freetds.conf DSN配置:
[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
这解决了我的问题,希望它也会帮助别人
#2
0
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.
现在一切正常。