Laravel SQL Server: SQLSTATE[HY000]: [unixODBC][Microsoft][ODBC Driver 13 for SQL Server]Protocol error in TDS stream

Question

I am attempting to run a raw SQL query from a remote server. At this point I am unaware of what SQL Server version this is running on, but believe it to be 2008. I am attempting to connect using Laravel 5.5. I just want a positive connection at this point. Can anyone direct me as to what the issue may be?

I have also tried putting DB::raw after 'select' in the string. Here is my query string:

public function index()
{
    $query = DB::connection('sqlsrv')->select('
        SELECT "tProject"."ProjectName", "tProject"."CreatedDate", "tProject"."ProjectNumber", "tProjectEstByItem"."Qty", "tService"."ServiceCode", "tService"."Description", "tProjectItemRollup"."HoursApproved", "tProjectItemRollup"."Hours", "tProjectEstByItem"."Gross", "tProjectStatus"."ProjectStatus", "vw_flat_Project_Properties"."ClientPO", "tProjectEstByItem"."COGross", "tProjectEstByItem"."COQty", "tProject"."EstExpenses", "tProject"."EstLabor", "tProject"."ApprovedCOExpense" 
        FROM   (((("Workamajig"."dbo"."tProject" "tProject" 
        INNER JOIN "Workamajig"."dbo"."tProjectItemRollup" "tProjectItemRollup" 
        ON "tProject"."ProjectKey"="tProjectItemRollup"."ProjectKey") 
        INNER JOIN "Workamajig"."dbo"."tProjectStatus" "tProjectStatus" 
        ON "tProject"."ProjectStatusKey"="tProjectStatus"."ProjectStatusKey") 
        INNER JOIN "Workamajig"."dbo"."vw_flat_Project_Properties" "vw_flat_Project_Properties" 
        ON "tProject"."ProjectNumber"="vw_flat_Project_Properties"."ProjectNumber") 
        LEFT OUTER JOIN "Workamajig"."dbo"."tProjectEstByItem" "tProjectEstByItem" 
        ON ("tProjectItemRollup"."ProjectKey"="tProjectEstByItem"."ProjectKey") 
        AND ("tProjectItemRollup"."EntityKey"="tProjectEstByItem"."EntityKey")) 
        INNER JOIN "Workamajig"."dbo"."tService" "tService" 
        ON "tProjectItemRollup"."EntityKey"="tService"."ServiceKey" 
        WHERE  "tProject"."CreatedDate">={ts "2017-06-26 00:00:00"} 
        AND "tProject"."ProjectNumber" 
        LIKE "bigl%" 
        ORDER BY "tProject"."ProjectNumber", "tService"."Description" ');

    dd($query);
}

Answer 1

I know this post was a long time ago but will still attempt to solve it for future readers.

First of all you need to check if you have the pdo_sqlsrv extension installed on your localserver(whether wamp or xampp). You can accomplish this on xampp by visiting the url "http://localhost/dashboard/phpinfo.php" and search for "pdo_sqlsrv", its usually below the "pdo_sqlite" extension. If you don't see the pdo_sqlsrv extension then follow this post Connecting php 7.2 to MS SQL using sqlsrv.

Next thing is to go to your .env file and set

DB_CONNECTION=sqlsrv also

DB_PORT=1433,

then set DB_HOST,DB_DATABASE,DB_USERNAME and DB_PASSWORD to the correct credentials.

If you installed MS SQL on your local pc make sure the Database Engine uses the SQL Server Authentication and not Windows Authentication, and also fix the TCP/IP port to 1433 by following this link to see how its done

SQLSTATE[08001]: [Microsoft][ODBC Driver 13 for SQL Server]TCP Provider: No connection could be made because the target machine actively refused it.

Restart the Laravel server