How to use SQL Server connection in Laravel?

Question

I got a working project made in Laravel 3 that I have to switch to MsSQL Server (not my call though, sniff...) and I don't understand the Laravel configuration on this database type...

I changed the default inside database.php to this 'default' => 'sqlsrv' then I configured the host, database, username, password in the sqlsrv array but then I get this error message:

This extension requires the Microsoft SQL Server 2012 Native Client ODBC Driver to communicate with SQL Server`

After some research I found that we need PDO of SQLSRV, which I already have as version 5.4 and in my phpinfo I get this returned pdo_sqlsrv support : enabled so it seems all correct but at the same time it seems to ask for ODBC SQLSRV connections, does I really have to use that?

I would rather connect directly from the Laravel database connection... but even if I need it and I created some ODBC connection to the server, how do I put them inside my configuration? Here is what I have in my configuration (of course the my... is replace by my real details) :

'sqlsrv' => array(
    'driver'   => 'sqlsrv',
    'host'     => 'myServerIP', 
    'database' => 'myDatabase',
    'username' => 'myUsername',
    'password' => 'myPassword',
    'prefix'   => '',
 ),

Any help would be greatly appreciated, the only thing I found close to my problem was this link from the Laravel forum: Laravel query on SQL Server but then it just stop without giving any configurations.

Reshape

Answer 1

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

Reshape

Answer 2

That's what I did to connect my Laravel app to MS SQL Server:

1. sudo apt-get update

2. 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.

3. 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

4. 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

---

5. 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

6. now restart your server (nginx or apache or php-fpm)

7. 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

Recrystallize

Your answer is very useful, but in step 5: "php -i | grep php.ini" will give you the path to cli php.ini. Is it right? shouldn't it be the php.ini used by nginx or apache? – Welsh

@Welsh step 5 will search for every php.ini file that exist!! and you choose which one you want to edit. – Recrystallize

Sorry @MahmoudZalt I understand your point, I just think the command "php -i | grep php.ini" doesn't find for every php.ini in the system, just in the loaded php cli file, so, it doesn't work as expect when you're trying to find the php.ini from apache, because php -i (info) from command line is not the same as phpinfo() from apache, and they will give you a different output based on its configuration. For example, I just ran "php -i grep php.ini" in my VPS and it doesn't return /etc/php5/apache2/php.ini Well, I could be wrong, just check it – Welsh

It was all in my freetds.conf. You saved my day, thanks. – Excommunicatory

I wish I could upvote this answer every freakin' time I have to come back here. I've used this answer half a dozen times over the last year, each time I need to setup a new server. This should probably be in the official laravel docs as a side note. – Erickaericksen

This was very helpful for me even in 2019. – Preconscious

Answer 3

For Linux, Laravel 5 and php 7.1:

1. Add connection as says here
2. sudo apt install php7.1-sybase
3. Try again :)

Brewer

Answer 4

GOT THE SOLUTION. TRY THIS!

1. Click Protocols for the Instance you created

   Refer :https://i.sstatic.net/O6Dyn.png

2. Enable the IP settings

   Refer :https://i.sstatic.net/64j11.png

3. 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.sstatic.net/ZQ7MR.png

Hope it will help. Thank you!

Thumping

Please use text to indicate what is needed. Pictures may not be readable on some screens and (in general) their content in not searcheable. – Inexact