sqlsrv driver on Linux?

Ubuntu is based off Debian so any commands or packages normally work across both.

SQL Server is by Microsoft right? Think you have to compile it yourself on Linux.

According to http://php.net/manual/en/sqlsrv.installation.php it works only in windows server-like things.

Edit:

Check this https://launchpad.net/ubuntu/+source/freetds

That's the PHP side of it, not the driver? You can compile it on Linux

Yeah, that is obvious. He needs to refactor the code to use this freedts lib, as sqlsrv depends on windows ddl's.

Edit: lol, or perhaps not. I have not used it

Yeah, thinking it's something like this: http://www.microsoft.com/en-gb/download/details.aspx?id=28160

Been a while since I've thought about Windows shit servers

A limiting factor right now is of course that I am on ServerPilot, which handles mostly for what PHP supports. They have a list of extensions that their official builds of PHP (4,5,6) supports:

https://serverpilot.io/community/articles/available-php-extensions.html

I see "odbc" in there, but no mssql (which would be the alternative), sadly...

I really fricken want to get rid of the Windows server we have for serving this application.

If php.net is correct, then you cannot use sqlsrv functions on ubuntu :(

I can't comment on the ServerPilot aspect of this, but I've recently had to deal with setting up Homestead to work with MSSQL Server and it came down to doing the following:

sudo apt-get install php5-mssql

Update /etc/freetds/freetds.conf to read as follows:

[global]  
# TDS protocol version
; tds version = 4.2
tds version = 8.0
client charset = UTF-8

Add to php.ini:

mssql.charset = "UTF-8"

Rufhausen, will that work with Laravel? I keep "sqlsrv" as driver name or change to "mssql"? Reason I ask is that using "mssql", Laravel says it's not a supported driver (as per the docs).

CedNet, yes. At least it's working for me. I haven't changed anything in database.php aside from adding the credentials:

  'sqlsrv' => array(
   'driver' => 'sqlsrv',
            'host' => getenv('MS_DB_HOST'),
            'database' => getenv('MS_DB_DATABASE'),
            'username' => getenv('MS_DB_USERNAME'),
            'password' => getenv('MS_DB_PASSWORD'),
   'prefix' => '',
  ),

Alright, then ServerPilot is out the door.

What PHP version are you using? I'd like to go up to 5.5.

I'm running 5.6.2 on homestead and on our Test server (ubuntu 12.04.5).

I just tried to install mssql on the ServerPilot handled server and it won't work as PECL stopped supporting mssql as of 5.4. Great.

When you say Homestead btw, you mean it's via Forge? With Forge I can't add a server at the location where I host my servers (in Sweden). Or can Forge add "any" custom server? (freshly installed)

When I refer to Homestead, I'm just referring to my local dev environment running Laravel Homestead with some extras added: https://gist.github.com/rufhausen/b0b053ec39e9e8d90f86 I don't know anything about ServerPilot, but can you just bypass it and log in to the shell directly and install via apt-get, or do u not have root access?

Ah, gotcha.

ServerPilot serves you a secured server ready for adding what they call "apps" (home directories for domains). It does it all pretty well.

They have their own way of serving PHP. Any app on the server can have any PHP version.

So installing php5-mssql won't make mssql available in the app directories.

I installed it as per your previous post. Set up all the configs. Restarted php. No difference, still complaining over missing sqlsrv driver. Darnit

I can only assume that ServerPilot overwrites any customizations that you are making. Bummer. Here's a screenshot of phpinfo on my homestead env to at least prove I'm not crazy. :) https://cloudup.com/cKokf-eln_I

I'm not completely certain on Linux being more of a FreeBSD fan myself.

For my FreeBSD installs (both in production and within Vagrant/VirtualPC in dev) I install and configure something called FreeTDS which then allows usage of the normal mssql pdo driver for php/laravel.

http://www.freetds.org

For the config file (/usr/local/etc/freetds.conf in my case)

[<server name name you will use in laravel database conf>]
       host = <ip address of your sqlsrv>
       port = 14330
       tds version = 7.0 (sql 2003 in my case, may need to change for different versions)

I believe FreeTDS gets installed automatically on Ubuntu when you use apt-get to install php5-mssql.

I tried this on my ubuntu 14.04 LTS desktop and it works!

You can't use any of the sqlsrv components. Use mssql instead.

➜ sudo apt-get install php5-mssql
➜ sudo php5enmod mssql
➜ sudo service apache2 restart

Okay, I'm testing a new approach using an ODBC connection type. I found this Laravel package: https://github.com/ccovey/odbc-driver It's 4.1 only, but there's a package that ups it to 4.x and it loads fine.

Now the only problem is that the SQL query generated via the PDO ODBC driver is causing an error:

SQLSTATE[HY010]: Function sequence error: 0 [Microsoft][ODBC Driver Manager] Fel i funktionssekvens (SQLExecute[0] at ext\pdo_odbc\odbc_stmt.c:254) (SQL: select count(*) as aggregate from x where x)

By any chance someone here know why this occurs? There's no difference in the SQL queries. Just a driver change from sqlsrv to odbc.

Btw, the exact generated query (by laravel debug output) works fine via SQL Server management studio. No issue at all.

Okay, appears certain queries simply don't work. Some do. I think I must wait for official ODBC support before I can move this application over to Linux.

Thanks all for your help!

This is similar to the behavior I experienced before making the charset changes (to utf-8) in freetds.conf and php.ini for mssql. Some queries worked and others just bonked. Maybe there are comparable changes you can make for your setup to remove the errors.

Hi, i work a lot with SQL Server, and now using Laravel was pain in the butt because i started to use Vagrant to create a virtual environment with Ubuntu.

First, as @keevitaja posted, you must install php5-mssql, this makes Laravel use the dblib driver instead of sqlsrv.

You can see this in the Illuminate\Database\Connectors\SqlServerConnector class.

After installing this package, you must edit the /etc/freetds/freetds.conf file adding this under the global section:

[global]
    tds version = 8.0
    client charset = UTF-8

This lets the driver encode all the data in utf-8 and avoid the strange characters in data.

Finally you should create a locales.conf file within /etc/freetds to allow correct parsing of dates with SQL Server, i have this configuration and has worked well with DateTime, DateTime2, SmallDateTime and Date data types:

[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

[es_ES]
    date format = %b %d %Y %I:%M%p
    language = spanish
    charset = iso_1

[pt_BR]
    date format = %d/%m/%Y %H:%M
    language = Portuguese
    charset = iso_1

[it_IT]
    date format = %d/%m/%Y %H:%M
    language = Italiano
    charset = iso_1

After that restart your php-fpm or if you are not using fpm, restart nginx or apache.

Hope this helps!.

@uxweb, thank you! I seriously just lost most of a day trying to figure out why my dates from sqlserver would not import directly into mysql.

@Rufhausen thanks for those installation instructions, they made life much easier

Really interesting post.

I get PDOException driver not found in production.

I local environment i have the following:

'sqlsrv' => array(
            'driver'   => 'sqlsrv',
            'host'     => 'localhost',
            'database' => 'techno',
            'username' => 'sa',
            'password' => '1234',
            'prefix'   => '',
        ),

and i use php_sqlsrv_54_ts.dll and php_sqlsrv_54.dll. But these are not suppoted in production Centos Linux VPS.

So the only thing that must me done in production is the configuration in /etc/freetds/freetds.conf file? Any ideas?

This thread helped me get set up with SQL Server by configuring freetds.conf and locales.conf. Thanks! The only issue now is that the dates being stored in my database are always 12 hours behind. Is this a configuration issue with the timezone?

@noleafclover614 If you are using Homestead or any *nix OS, then you can check if your timezone is set accordingly with the date command:

vagrant@homestead:~$ date

Sat Mar 21 05:28:15 UTC 2015

If you have UTC, then that's why you are getting the dates 12 hours behind.

You can set the right timezone by running this command:

sudo dpkg-reconfigure tzdata

And after that you will get the right date and time.

@louposk Are you using windows for development and linux for production?, Can you describe with more detail your scenario?