How to use SQL Server connection in Laravel?
GOT THE SOLUTION. TRY THIS!
Click Protocols for the Instance you created
Refer :https://i.stack.imgur.com/O6Dyn.png
Enable the IP settings
Refer :https://i.stack.imgur.com/64j11.png
Right click on the IP settings>properties>IP Addresses>IP All>IP Port: Set it to 1433 as default SQL Server database port.
Refer: https://i.stack.imgur.com/ZQ7MR.png
Hope it will help. Thank you!
For Linux, Laravel 5 and php 7.1:
- Add connection as says here
sudo apt install php7.1-sybase- Try again :)
Ahh after a lot more research, I found out that my driver of SQL Server Native Client 10.0 was out of date and not sufficient to connect to a MsSQL Server 2008 with PDO, you need to have at least version 11.0 in order to connect to an SQL Server 2005+
If you want to validate your driver version, you can go through Control Panel >> Administrative Tools >> Data Sources (ODBC) and then click on the Drivers tab to find out which SQL Server Native Client you have already installed. If you want to update your driver, depending on your OS configuration you may choose the proper link below...
SQL Server 2012 Native Client
x86 (32 bits) Package http://go.microsoft.com/fwlink/?LinkID=239647&clcid=0x409
x64 (64 bits) Package http://go.microsoft.com/fwlink/?LinkID=239648&clcid=0x409
That's what I did to connect my Laravel app to MS SQL Server:
sudo apt-get update
sudo apt-get install php5-sybase
if you try to install php5-mssql it will install php5-sybase anyway, it is “Sybase / MS SQL Server module for php5”.
this will make Laravel use the dblib driver instead of sqlsrv.
check out this file for reference Illuminate\Database\Connectors\SqlServerConnector.php.
- open this file: /etc/freetds/freetds.conf
under [global] section and under tds version = 4.2
add tds version = 8.0 (without ; semi column at the beginning of the line)
and add client charset = UTF-8 just under the above line (without ; semi column at the beginning of the line)
This lets the driver encode all the data in utf-8 and avoid the strange characters in data
the file will look like this:
[global]
# TDS protocol version
; tds version = 4.2
tds version = 8.0
client charset = UTF-8
- create file locales.conf in this directory /etc/freetds and past the following inside it: (this allow correct parsing of dates with SQL Server).
[default]
date format = %Y-%m-%d %I:%M:%S.%z
[en_US]
date format = %b %e %Y %I:%M:%S:%z%p
language = us_english
charset = iso_1
- edit the php.ini file
first find the file with php -i | grep php.ini
then open the file and search for mssql.charset (it will probably be like disabled like this ; mssql.charset = "ISO-8859-1" )
make it mssql.charset = "UTF-8" <<make sure you remove the ; semi column to enable it>>
save and exit
now restart your server (nginx or apache or php-fpm)
of course you know that you need to set the database config to use sqlsrv by default
'default' => 'sqlsrv',and add your credentials.
OTHER METHODS:
you can use ODBC driver to connect
here’s a nice package https://github.com/ccovey/odbc-driver