Odunayo Ogungbure

Posted on Aug 16, 2019 • Edited on Jan 20, 2020

Setting up a Laravel project with SQL Server AND XAMPP / WAMP on Windows

#laravel #mssql #windows

I spent a large part of yesterday trying to connect an existing Laravel project to MsSQL. I made some mistakes and used different tutorials but I finally got it setup.

Note: This article assumes you have SQL Server, xampp or wamp and laravel correctly installed on your system.

First, we create a fresh Laravel installation and when it's complete we can open the folder in our editor.

laravel new mssql

Open the database.php file in the config folder and make this change.

 /*
    |--------------------------------------------------------------------------
    | Default Database Connection Name
    |--------------------------------------------------------------------------
    |
    | Here you may specify which of the database connections below you wish
    | to use as your default connection for all database work. Of course
    | you may use many connections at once using the Database library.
    |
    */
    //from
    'default' => env('DB_CONNECTION', 'mysql'),
    //to
    'default' => env('DB_CONNECTION', 'sqlsrv'),

This is not really important as we are still going to update the .env file, but hey I just feel like doing it :).

Next, we need to update our .env file database credentials

DB_CONNECTION=sqlsrv
DB_HOST=127.0.0.1
DB_PORT=1433
DB_DATABASE=laravel
DB_USERNAME= #SQL Server username
DB_PASSWORD= #SQL Server password

Let's run our migrations using the migrate artisan command in our terminal.

 php artisan migrate

We should get the error below. This means we need to download Microsoft drivers for PHP for SQL Server. (If your migrations did run successfully, then there's no further setup to do).

 Illuminate\Database\QueryException: could not find driver 
(SQL: select * from sysobjects where type = 'U' and name = migrations)

You can download the appropriate drivers from Microsoft store or this GitHub page. I used the latter so I can easily download the drivers for my PHP version(Windows-7.2.zip). (I'm using PHP 7.2).

Extract the contents of the zip file and copy the php_pdo_sqlsrv_7x_ts.dll and php_sqlsrv_7x_ts.dll file.

On WAMPP

Paste the files in C:\wamp64\bin\php\php7.x\ext
Open the php.ini file in C:\wamp64\bin\php\php7.x folder and add the following lines

 extension=php_pdo_sqlsrv_7x_ts.dll
 extension=php_sqlsrv_7x_ts.dll

Open the php.ini file in C:\wamp64\bin\apache\apache2.4.xx\bin folder and add the following lines

 extension=php_pdo_sqlsrv_7x_ts.dll
 extension=php_sqlsrv_7x_ts.dll

Restart wampp server.

On XAMP

Paste the files in C:\xampp\php\ext
Open the php.ini file in C:\xampp\php folder and add the following lines

 extension=php_pdo_sqlsrv_7x_ts.dll
 extension=php_sqlsrv_7x_ts.dll

Restart xamp server.

Run the migration command in our terminal.

 php artisan migrate

Now we should be able to run our migrations.

Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table (0.01 seconds)
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table (0.01 seconds)

Bonus: (I'm on SQL Server 2012 by the way) Using unsignedBigInteger() will throw an error, instead use bigInteger(). More on this here.

UPDATE: You might need to download Microsoft ODBC Driver to fix this - Illuminate\Database\QueryException : SQLSTATE[IMSSP]: This extension requires the Microsoft ODBC Driver for SQL Server to communicate with SQL Server. Access the following URL to download the ODBC Driver for SQL Server for x64: https://go.microsoft.com/fwlink/?LinkId=163712

That’s All Folks!
Happy Coding 🖖

Top comments (12)

maygallegos • Feb 14 '20 • Edited

Hola, me marca un error

Illuminate\Database\QueryException : SQLSTATE[08001]: [Microsoft][ODBC Driver 17 for SQL Server]Proveedor de TCP: No se puede establecer una conexión ya que el equipo de destino denegó expresamente dicha conexión.
(SQL: select * from sysobjects where type = 'U' and name = migrations)

at C:\xampp\htdocs\admin-lte-example\vendor\laravel\framework\src\Illuminate\Database\Connection.php:669
665| // If an exception occurs when attempting to run a query, we'll format the error
666| // message to include the bindings with SQL, which will make this exception a
667| // lot more helpful to the developer instead of just the database's errors.
668| catch (Exception $e) {

669| throw new QueryException(
670| $query, $this->prepareBindings($bindings), $e
671| );
672| }
673|

Exception trace:

1 PDOException::("SQLSTATE[08001]: [Microsoft][ODBC Driver 17 for SQL Server]Proveedor de TCP: No se puede establecer una conexión ya que el equipo de destino denegó expresamente dicha conexión.
")
C:\xampp\htdocs\admin-lte-example\vendor\laravel\framework\src\Illuminate\Database\Connectors\Connector.php:70

2 PDO::__construct("sqlsrv:Server=127.0.0.1,1433;Database=laravel", "userSQL", "passSQL", [])
C:\xampp\htdocs\admin-lte-example\vendor\laravel\framework\src\Illuminate\Database\Connectors\Connector.php:70

Please use the argument -v to see more details.

Tyler • Feb 23 '22

do you mind help taking a look, i had this error

Illuminate\Database\QueryException

could not find driver (SQL: select * from information_schema.tables where table_schema = forge and table_name = migrations and table_type = 'BASE TABLE')

at C:\Users\User\Documents\tiidelab\mds_backend\morningsc\manager-master\vendor\laravel\framework\src\Illuminate\Database\Connection.php:712
708▕ // If an exception occurs when attempting to run a query, we'll format the error
709▕ // message to include the bindings with SQL, which will make this exception a
710▕ // lot more helpful to the developer instead of just the database's errors.
711▕ catch (Exception $e) {
➜ 712▕ throw new QueryException(
713▕ $query, $this->prepareBindings($bindings), $e
714▕ );
715▕ }
716▕ }

1 C:\Users\User\Documents\tiidelab\mds_backend\morningsc\manager-master\vendor\laravel\framework\src\Illuminate\Database\Connectors\Connector.php:70
PDOException::("could not find driver")

2 C:\Users\User\Documents\tiidelab\mds_backend\morningsc\manager-master\vendor\laravel\framework\src\Illuminate\Database\Connectors\Connector.php:70
PDO::__construct()
Bugsnag Warning: Couldn't notify. cURL error 60: SSL certificate problem: unable to get local issuer certificate (see curl.haxx.se/libcurl/c/libcurl-err...
) for notify.bugsnag.com

Shailendra • Jan 1 '20

This worked like a charm! Thanks a lot!

Rafael Alvarez • Nov 27 '19

729/5000
Hello, nice to greet you, I have a system made in Laravel 6 (migrated from Laravel 4) and MySQL but I must also connect it with SQL Server, there I only have to use a stored procedure to store some records in another system, as I should make the call from the controller? I have used the following but all give error
DB :: connection ('sqlsrv') -> SELECT ('SET NOCOUNT ON; CALL SP_APIRserve Service2?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, ? ', $ arrangement);
and
DB :: connection ('sqlsrv') -> insert ('SET NOCOUNT ON; EXECUTE SP_APIRserve Service2?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, ? ', $ arrangement);
and
DB :: connection ('sqlsrv') -> insert ('EXECUTE SP_APIRserveService2?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?', $ arrangement);
my email rjalvarez85@gmail.com

Odunayo Ogungbure • Nov 27 '19

I think what you have to do is

DB::connection()->statement()

Aghwotu Ovuoke • Mar 3 '20

Thank you so much. This really helped me.

Odunayo Ogungbure • Nov 8 '20

Glad it did 🤗

Demabio • Nov 2 '20

This came in so handy Sholley.Many thanks

Odunayo Ogungbure • Nov 8 '20

🥰

Endersonfs • Feb 16 '22 • Edited

I need help, i have this error.

PHP Warning: Module "pdo_sqlsrv" is already loaded in Unknown on line 0

Warning: Module "pdo_sqlsrv" is already loaded in Unknown on line 0

Illuminate\Database\QueryException

SQLSTATE[08001]: [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: No connection could be made because the target machine actively refused it.
(SQL: select * from sys.sysobjects where id = object_id(migrations) and xtype in ('U', 'V'))

at C:\Users\ender\OneDrive\Documents\laravel con sql server\projectapp\vendor\laravel\framework\src\Illuminate\Database\Connection.php:712
708▕ // If an exception occurs when attempting to run a query, we'll format the error
709▕ // message to include the bindings with SQL, which will make this exception a
710▕ // lot more helpful to the developer instead of just the database's errors.
711▕ catch (Exception $e) {
➜ 712▕ throw new QueryException(
713▕ $query, $this->prepareBindings($bindings), $e
714▕ );
715▕ }
716▕ }

1 C:\Users\ender\OneDrive\Documents\laravel con sql server\projectapp\vendor\laravel\framework\src\Illuminate\Database\Connectors\Connector.php:70
PDOException::("SQLSTATE[08001]: [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: No connection could be made because the target machine actively refused it.
")

2 C:\Users\ender\OneDrive\Documents\laravel con sql server\projectapp\vendor\laravel\framework\src\Illuminate\Database\Connectors\Connector.php:70
PDO::__construct("sqlsrv:Server=localhost,1433;Database=coindev", "coindev", "coindev@2022.34", [])