laravel8.*连接windwos sqlserver2016

2022年3月15日16:51:23

环境：

服务器centos7.9

laravel8.* php8.0  

目标数据库windows sqlserver 2016

https://github.com/Microsoft/msphpsql/releases/tag/v5.10.0

安装驱动和扩展

sudo yum install -y unixODBC-devel

先装这个，不然安装报错

sudo pecl install sqlsrv-5.10.0
sudo pecl install pdo_sqlsrv-5.10.0

备注，linux的sqlserver https://packages.microsoft.com/config/rhel/7/目前只有 2017和2019，官方其他版本需要自己去找

所以在linux 2017和windows2016 访问的api都是兼容，除非是有什么特性函数或者操作，不然基本都是一样

安装链接工具

https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017

sudo su

#Download appropriate package for the OS version
#Choose only ONE of the following, corresponding to your OS version

#Red Hat Enterprise Server 7 and Oracle Linux 7
curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo

#Red Hat Enterprise Server 8 and Oracle Linux 8
curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/mssql-release.repo

exit
sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts
sudo ACCEPT_EULA=Y yum install -y msodbcsql18
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y yum install -y mssql-tools18
echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo yum install -y unixODBC-devel

重启fpm

php -m

查看laravel使用的是pdo_sqlsrv 所以只需要安装这个

修改config/database.php

 'sqlsrv' => [
            'driver' => 'sqlsrv',
            'url' => env('DATABASE_URL'),
            'host' => env('SQLSER_HOST', 'localhost'),
            'port' => env('SQLSER_PORT', '1433'),
            'database' => env('SQLSER_DATABASE', 'forge'),
            'username' => env('SQLSER_USERNAME', 'forge'),
            'password' => env('SQLSER_PASSWORD', ''),
            'charset' => 'utf8',
            'prefix' => '',
            'prefix_indexes' => true,
            'encrypt' => 'no', // alternatively, defer to an env variable
//            'trust_server_certificate' => 'true', // alternatively, defer to an env variable
        ],

https://techcommunity.microsoft.com/t5/sql-server-blog/odbc-driver-18-0-for-sql-server-released/bc-p/3257207#M1970

ODBC Driver 18.0 for SQL Server 默认encrypt=yes

windwos的驱动的话，https://docs.microsoft.com/en-us/sql/connect/php/download-drivers-php-sql-server?view=sql-server-ver15

 Download Microsoft Drivers for PHP for SQL Server (Windows) 下载驱动

吧dll复制到php下面就可以了，php8的话应该是 x64 nts版本