Laravel连接到SQL Server 2008命名实例 [英] Laravel connect to a SQL Server 2008 named instance
问题描述
我正在尝试从Ubuntu计算机上连接SQL Server,除命名实例外,其他一切工作正常:
这有效
'data'=>大批('驱动程序'=>'sqlsrv','主机'=>'xxxx','端口'=>1433,'数据库'=>'D b','用户名'=>'用户','password'=>'pwd','前缀'=>'',),
这不是
'data'=>大批('驱动程序'=>'sqlsrv','主机'=>"yyyy \ NAMEDINSTANCE",'端口'=>1433,'数据库'=>'D b','用户名'=>'用户','password'=>'pwd','前缀'=>'',),
我总是会遇到此错误:
在/var/www/public/my.api/vendor/laravel/framework/src/Illuminate/Database/连接器/Connector.php:47我尝试了所有可能的组合:
- 托管 \ 实例
- 托管/实例
- 托管 \\ 实例
有人可以帮助我吗?
修改:因为我也尝试过不使用实例名称(如此处所述, ),所以该脚本继续尝试连接,直到出现此错误:
消息为"SQLSTATE [HY000]"的 异常"PDOException"无法连接:/var/www/public/my.api/vendor/laravel/中的Adaptive Server不可用或不存在(严重性9)framework/src/Illuminate/Database/Connectors/Connector.php:47
作为Management Studio,DBeaver或Database.NET的任何编辑者都只能通过指定 INSTANCENAME 来连接到该实例,因此似乎DSN似乎是一个PDO问题
我还尝试使用 tsql
直接连接到该命名实例,而没有更多的运气:
<代码> tsql -S SERVER -U usr -P pwd -L dbname
这是/etc/freetds.conf
文件:
[global]tds版本= 8.0客户端字符集= UTF-8端口= 1433转储文件=/tmp/freetds.log转储文件追加=是文字大小= 64512[服务器]主机= ip端口= 1433实例= instance_name[SERVER2]主机= ip端口= 1433
和tds日志文件:
log.c:196:FreeTDS 0.91的启动日志文件在2015-03-19 15:35:46带有调试标志0x4fff.iconv.c:330:tds_iconv_open(0xc163a0,UTF-8)iconv.c:187:ISO-8859-1的本地名称是ISO-8859-1iconv.c:187:UTF-8的本地名称是UTF-8iconv.c:187:UCS-2LE的本地名称为UCS-2LEiconv.c:187:UCS-2BE的本地名称为UCS-2BEiconv.c:349:设置客户端字符集"UTF-8"的转换iconv.c:351:为"UTF-8"准备iconv-"UCS-2LE"转换iconv.c:391:为"ISO-8859-1"准备iconv-"UCS-2LE"转换iconv.c:394:tds_iconv_open:已完成net.c:205:连接到195.70.16.92端口1433(TDS版本7.1)net.c:270:tds_open_socket:connect(2)返回正在进行操作"net.c:306:getsockopt(2)报告:连接超时net.c:316:tds_open_socket()失败util.c:331:tdserror(0xc16140,0xc163a0,20009,110)util.c:361:tdserror:客户端库返回TDS_INT_CANCEL(2)util.c:384:tdserror:返回TDS_INT_CANCEL(2)mem.c:615:tds_free_all_results()
当然,如果我尝试连接到 SERVER2 (这是一个未命名的实例),一切都会顺利进行...
我终于找到了解决方案,有两个问题:
- SQL服务器未在正常的默认端口(我不好)上监听
- Laravel(PDO?)不知道如何处理(或至少我没有找到)命名实例,我尝试了任何可能的组合(请参阅问题)
因此,我最终将FreeTDS DSN与laravel结合使用,以连接SQL命名实例服务器.
/etc/freetds.conf DSN配置:
[NAMED_INSTANCE]主机= 127.0.0.1端口= 55021
在laravel数据库适配器中:
'webcmd'=>大批('驱动程序'=>'sqlsrv','主机'=>"NAMED_INSTANCE",'数据库'=>'D b','用户名'=>'usr','password'=>'pwd','前缀'=>'',),
那解决了我的问题,希望它也能帮助到某人
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' => '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
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...
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
这篇关于Laravel连接到SQL Server 2008命名实例的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!