我正在尝试从 Ubuntu 机器连接 SQL 服务器,除了命名实例外,一切都很好:
这行得通
'data' => array(
'driver' => 'sqlsrv',
'host' => 'xxxx',
'port' => 1433,
'database' => 'db',
'username' => 'user',
'password' => 'pwd',
'prefix' => '',
),
这不行
'data' => array(
'driver' => 'sqlsrv',
'host' => 'yyyy'NAMEDINSTANCE',
'port' => 1433,
'database' => 'db',
'username' => 'user',
'password' => 'pwd',
'prefix' => '',
),
我总是以此错误结束:
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
我已经尝试了所有可能的组合:
- 主机 '' 实例
- 主机/实例
- 主机 '''' 实例
有人可以帮助我吗?
编辑:因为我也尝试过没有实例名称(如此处所述(,所以脚本会一直尝试连接,直到出现此错误:
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
任何编辑器,如管理工作室、DBeaver 或 Database.NET,只能通过指定 INSTANCENAME 连接到该实例,因此 DSN 似乎更像是 PDO 问题
我还尝试使用tsql
直接连接到该命名实例,但运气不佳:
tsql -S SERVER -U usr -P pwd -L dbname
这里是/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
和 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()
当然,如果我尝试连接到SERVER2(这是一个未命名的实例(,一切都会顺利进行......
我终于找到了解决方案,有两个问题:
- SQL 服务器没有侦听良好的默认端口(我的坏端口(
- Laravel(PDO?(不知道如何处理(或者至少我没有找到如何(命名实例,我已经尝试了任何可能的组合(请参阅问题(
因此,我最终将FreeTDS DSN与laravel结合使用,以连接SQL命名实例服务器。
/etc/freetds.conf DSN 配置:
[NAMED_INSTANCE]
host = 127.0.0.1
port = 55021
在 laravel 数据库适配器中:
'webcmd' => array(
'driver' => 'sqlsrv',
'host' => 'NAMED_INSTANCE',
'database' => 'db',
'username' => 'usr',
'password' => 'pwd',
'prefix' => '',
),
这解决了我的问题,希望它也能帮助某人
我遇到了这个问题,但我解决了它。我正在使用docker,我安装了最新版本的laravel和curl(curl -s https://laravel.build/example-app | bash(,这个安装包含php8.0,laravel 8.0和Ubuntu 21.04。修改 Dockerfile 并添加以下库:
nano unixodbc unixodbc-dev freetds-common freetds-dev tdsodbc freetds-bin php8.0-sybase
最后一个(php8.0-sybase(很重要,因为它将允许您执行迁移。
此外,将以下内容添加到文件中:
RUN echo "[sqlserver] ' n '
host = IPSERVER ' n '
port = 1433 ' n '
tds version = 7.3 ">> /etc/freetds/freetds.conf
由于我想连接到MS sql server 2008,我把tds版本= 7.3,根据FREETDS文档。(https://www.freetds.org/userguide/ChoosingTdsProtocol.html(
然后在 laravel .env 文件中修改与数据库的连接:
DB_CONNECTION = sqlsrv
DB_HOST = sqlserver // the same one that was configured in the freetds.conf file
DB_PORT = 1433
DB_DATABASE = databasename
DB_USERNAME = username
DB_PASSWORD = password
并在 Laravel 数据库.php 文件中,更改默认连接:
'default' => env ('DB_CONNECTION', 'sqlsrv'),
仅此而已。
微软驱动程序从未为我工作过
感谢您参与解决此连接问题。我也遇到了这个问题,这是我如何解决的。
有关我的信息,与tsql的连接有效,但自Laravel(5.4(以来就不工作了
我理解的一个技巧是它不是使用的默认端口 (1433(。
要查找端口,您必须启动外壳连接:
tsql -D DB -S "IP'INSTANCE" -U login -P pass
并检查日志,这里的好端口是1168
Net.c:1059:实例端口为 1168
文件的内容 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
文件内容:配置/数据库.php
'sqlsrv' => [
'driver' => 'sqlsrv',
'host' => 'mssql',
'port' => '1168',
'database' => 'DB',
'username' => 'login',
'password' => 'pass',
'prefix' => '',
],
现在一切正常。