Connect Laravel to Microsoft SQL

Change the default driver from mysql to

 'default' => 'sqlsrv',

In sqlsrv array provide the correct details to you sql IP

  'sqlsrv' => array(
   'driver' => 'sqlsrv',
   'host' => 'IP.Adress.Of.Host.Machine', // Provide IP address here
   'database' => 'database',
   'username' => 'root',
   'password' => '',
   'prefix' => '',
  ),

You can also addition key values to array if you like.

Let's see if this works out :)

if i want to access any table or so... do i simply use eloquent then?

i'm currently doing another project. i'll let you know asap if it works :)

Once the database connection is established, you will be able to use eloquent normally as you do with mysql :)

Hi there, I have been trying to sort out this issue for past week and no luck so far.. I developed the database locally using mysql and now trying to move the application to SQL SERVER 2012 at work and i am getting PDO Exception (Driver not found). Could some one help please ??

@caramelsolid:

it would be helpful to post this in an own discussion with some code snippets of your own application so that the people here on the forum can help you. if you spam another thread about something different, then no one can really help you!

@caramelsolid you need the PDO driver for sqlsrv http://php.net/manual/en/ref.pdo-sqlsrv.php

Thanks @maximebeaudoin for your help. However, I installed them drivers on the windows that i am running on parallel desktop on my IMAC. Its really frustrating because i have never tried to connect to a SQL Server before. I even tried the OBDC Driver but there is no step by step instructions as how to do it properly. If you do know of any step by step instructions then please let me know. I would really appreciate as its driving me crazy :(

@Houbsi sorry mate didnt mean to spam your discussion. As the topic was already getting discussed here thats why posted. Hope you dont mind

@abhimanyusharma003 SQLSTATE[HY000]: General error: 208 General SQL Server error: Check messages from the SQL Server [208] (severity 16) [(null)] (SQL: select * from [TABLENAME])

this is what i'm getting right now... the connection works but i can't get any information of the database. even if i try it with db::raw

I just had to set this up last week, and this answer by @Rufhausen worked well. https://laracasts.com/discuss/channels/general-discussion/sqlsrv-driver-on-linux/replies/14216 Make sure you select "No" when the package script asks if you want to replace your config files.

The only problem I'm having yet with SQL server is that the dates are coming back in the format: "Mar 30 2006 08:55:00:000AM" instead of the typical Y-M-D H:M:S

@caramelsolid PHP versions up to 5.5 work with SQL Server on Windows

5.5 drivers were only released recently (yes, Microsoft are slow with the updates!!!). http://www.microsoft.com/en-gb/download/details.aspx?id=20098

In my case I needed to add the php_pdo_sqlsrv_55_nts.dll file to my PHP extension list. (PDO for 5.5). If you get any PHP errors (especially about missing libraries), Google the message - sometimes you might be missing some DLLs that come bundled with .NET packages which you can download separately. I never got the ODBC libraries to work, so I had to wait for MS to catch up.

I have installed Laravel 5 on a Mac and I am trying to connect to a SQL Server on Windows. To make the connection via Laravel to the SQL server I also installed FreeTDS with UnixODBC. In php.ini I have activated php_odbc.so and mssql.so. Which of the two do I need to use (php odbc or mssql) to be able to use it in Laravel 5? When I set the driver to mssql I get a driver not found pdo exception. I also tried disabling one of the two libraries to be shure they won't conflict eachother.

phpinfo() to see your mssql driver is install in you php

I have developed a REST api using laravel , api is hosted on macbook and I want to access MS SQL server running on another system. I have made these changes in my project's "database.php" file.

'default' => env('DB_CONNECTION', 'sqlsrv'),

'sqlsrv' => array( 'driver' => 'sqlsrv', 'host' => 'IP.Adress.Of.Host.Machine', // Provide IP address here 'database' => 'database', 'username' => 'root', 'password' => '', 'prefix' => '', ),

When I try to execute a query, I get this error ,"PDOException in could not find driver". I have no clue about how to make MS Sql Server accessible from my macbook.

Hi @Mehdavi

you have to install the driver.

Here are good articles: http://onefinepub.com/2013/03/ms-sql-odbc-ubuntu/ http://onefinepub.com/2014/03/installing-unixodbc-2-3-2-higher-ubuntu-12-04-lts/

After this is installed... everything should work fine.

But on MAC OS i have no driver package found yet. You should use a VM with vagrant :)

this worked for me

in the .env

DB_CONNECTION=sqlsrv DB_HOST=pcname\servername

in database.php

'sqlsrv' => [ 'driver' => 'sqlsrv', 'host' => 'pcname\servername', 'database' =>'mydb', ],

**pcname\servername--this name is in the top right corner on the sql server 2014 by the object explorer

NB.first i tried to connect to sql server 2014 with php with a connection string --i google this-- connect.php

I want to connect with MS Access database. please suggest me what should i change for connecting with MS Access database.. @abhimanyusharma003

Hi, I am new to Laravel and working on Laravel 5.4 version. I am creating an small CRUD application. My database is on Sql Server.

Now, when I am doing new registration, Laravel throwing an error given below:

"SQLSTATE[28000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Login failed for user 'SYSDEV\ABC'"

while I am login in computer with an account 'SYSDEV\DEF'.

On My system (SYSDEV), I have two user accounts

1. ABC 2.DEF

I have also checked all the needed Laravel configuration files and everything is fine. I don't know why the laravel trying to make connection with another user account? Is there can be another reason?

Please anyone tell me How can I fix this issue?

@dhiru027 Are your SQL Server users configureed using 'Windows Authentication' or 'SQL Server Authentication'??

Don't even bother trying to use 'Windows Authentication' its not designed for this senario at all, you want to be using 'SQL Server Authentication'. Create a new user, set the 'Default Database', 'Default Language' & on your 'User Mapping' make sure the user has at least 'db_datareader, db_datawriter, db_ddladmin & public'.

your .env should have the following (obviously change the details):

DB_CONNECTION=sqlsrv

DB_HOST=192.168.1.100\instance (specify instance if not default)

DB_PORT=1433

DB_DATABASE=Laravel_sqldb (the name of the database)

DB_USERNAME=mynewuser

DB_PASSWORD=newuserpassword

your database.php should have the following (just copy & paste)

'sqlsrv' => [

'driver' => 'sqlsrv',

'host' => env('DB_HOST', 'localhost'),

'database' => env('DB_DATABASE', 'forge'),

'username' => env('DB_USERNAME', 'forge'),

'password' => env('DB_PASSWORD', ''),

'charset' => 'utf8',

'prefix' => '',

If your still struggeling I'll post some screen shots to help....

Hi, I recently have a project that need to pull data from a MSSQL of a Accounting solution (vendor based). There is a very confusing part about the prefix that I accidentally find out its not needed to put.

Here are my solutions that worked for me. I'm on Windows based machine, Windows 10 Pro. Running on WAMP. For some reason, the the MSSQL strictly requires ODBC driver on top of sqlsvr driver. I'm on PHP 5.6 at the time of this writing.

Note that my deployment is in CentOS based machine, so the driver for MSSQL need to be researched and installed. I'm not sure does the ODBC driver needed to be pair with sqlsrv driver in CentOS.

First get the necessary files from

Microsoft Drivers for PHP for SQL Server

Refer PHP version here

Microsoft® ODBC Driver 11 for SQL Server

.env files

DB_HOST={ip address} or {DDNS should be able to work}
DB_DATABASE={database name}
DB_PORT= Okay, for this you need to refer to the MSSQL for best reference. Try to look it in regedit HKEY_LOCAL_MACHINE/SOFTWARE/MICROSOFT/Microsoft SQL Server/{instance_name}/MSSQL Server/Super SocketNetlib/TCP

Refer here

Refer here 2

config/database.php

'driver' => 'sqlsrv'
'host' => env('DB_HOST'),
'database=>env('DB_DATABASE'),
'port' => env('DB_PORT'),
'charset' => 'utf8',
'prefix' => '',

Here prefix is the one that confuses me, I checked with Microsoft Access and connect via ODBC, it shows that every table has a prefix of 'pbo_' in-front of the table name (MySQL term). E.g.: pbo_itemcollections

However, if I put a 'pbo' as 'prefix', it just keep returning

Illuminate\Database\QueryException: SQLSTATE[42S02]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name

putting an empty prefix seems to fix for me.

I am new to Laravel.

I have installed all the required extensions

extension=php_sqlsrv_73_ts.dll

extension=php_pdo_sqlsrv_73_ts.dll

And I can see in php info sql_serve

When I write in my controller :

$serverName = "DESKTOP-E8EJSV8\MJMSSQLSEVRER"; //serverName\instanceName
$connectionInfo = array( "Database"=>"AdventureWorks2017", "UID"=>"sa", "PWD"=>"admin1234");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
        echo "Connection established.<br />";
}else{
        echo "Connection could not be established.<br />";
        die( print_r( sqlsrv_errors(), true));
}

I get Connection established.

But When I do:

$users = DB::table('BusinessEntityID')->get();

I get:

SQLSTATE[42S02]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name '[BusinessEntityID]'. (SQL: select * from [[BusinessEntityID]]])

Here is my database.php file

'sqlsrv' => [
    'driver' => 'sqlsrv',
    'host' => env('DB_HOST', 'DESKTOP-E8EJSV8\MJMSSQLSEVRER'),
    'database' => env('DB_DATABASE', 'AdventureWorks2017'),
    'username' => env('DB_USERNAME', 'sa'),
    'password' => env('DB_PASSWORD', 'admin1234'),
    'charset' => 'utf8',
    'prefix' => '',
    'prefix_indexes' => true,
],

And the Table exists in my DB.

Here is my env file

APP_NAME=Laravel
APP_ENV=local
APP_KEY=base64:5NiRxIzF14YgZkMGB403LBeWAH+IpOYtt4okTw5iAio=
APP_DEBUG=true
APP_URL=http://localhost

LOG_CHANNEL=stack

BROADCAST_DRIVER=log
CACHE_DRIVER=file
QUEUE_CONNECTION=sync
SESSION_DRIVER=file
SESSION_LIFETIME=120

REDIS_HOST=127.0.0.1
REDIS_PASSWORD=null
REDIS_PORT=6379

MAIL_DRIVER=smtp
MAIL_HOST=smtp.mailtrap.io
MAIL_PORT=2525
MAIL_USERNAME=null
MAIL_PASSWORD=null
MAIL_ENCRYPTION=null

AWS_ACCESS_KEY_ID=
AWS_SECRET_ACCESS_KEY=
AWS_DEFAULT_REGION=us-east-1
AWS_BUCKET=

PUSHER_APP_ID=
PUSHER_APP_KEY=
PUSHER_APP_SECRET=
PUSHER_APP_CLUSTER=mt1

MIX_PUSHER_APP_KEY="${PUSHER_APP_KEY}"
MIX_PUSHER_APP_CLUSTER="${PUSHER_APP_CLUSTER}"

I found the dev.to article helpful : https://dev.to/mr_steelze/setting-up-a-laravel-project-with-sql-server-and-xampp-wamp-on-windows-3n7k