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.

Answer 1

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