Summary:
- sqlsrv is the PHP driver for Windows environments, mssql is for Linux.
- The mssql driver and functions used to exist in PHP on Windows but are now deprecated.
- Connection settings/syntax used with the sqlsrv driver may not work with mssql. We had to change the hostname from
"hostname,\port_number";to"hostname:port_number";
Lesson # 1: Develop in an environment similar/the same as to your production environment.
Lesson # 2: It’s the little things.
Lesson # 3: Coding is better with two brains and sets of eyes.
I’ve been working on an app with a really smart fellow. The app is being built to run on Amazon Web Service (AWS) connecting to a SQL Server instance. Of course, the way that requires the least initial setup is to build and test locally. I do that on windows (though definitely migrating to linux after this) with WAMP.
After attempting to connect to the db in the cloud through PHP on WAMP, I realized that I needed the sqlsrv PHP extension to access the db. I read that PHP’s mssql driver was deprecated (more on that later). After that, development went fine and dandy.
Finally, it came time to send this puppy to the EC2. Nothing happened when we uploaded and ran it. We turned on error reporting. After running it again, it told us that the sqlsrv_connect() function didn’t exist. That’s easy, I thought. That just means that we need to install the sqlsrv driver on the instance, and we’ll be on our way.
After searching around for a few hours for the linux sqlsrv driver, we came the realization that the mssql driver is NOT deprecated on linux. It’s clearly in the docs, and we were able to see that once we came to that realization.
(Microsoft just released a SQL Server driver (version 1.0) for linux. The setup seems a little complex and it appears to only have official support for RedHat and SUSE – see end of this post for links).
We changed our sqlsrv_* function calls to mssql_*. We reloaded the app. So, problem solved? Not exactly. At this point, we were staring at a far more ambiguous db connection error.
After trying dozens of solutions, my colleague suggested changing the name of our host in the connection settings. Silly, I thought. The connection was working fine during weeks of testing. How could that be wrong?
Luckily, I was wrong. That was the solution. We could go to bed (it was about 3am). We didn’t have to try anything else!
The problem:
With the sqlsrv driver, we connected to our db with
"hostname,\port_number";
Apparently, that’s a no-no with mssql. Instead of using the above, we ended up using
"hostname:port_number";
My colleague realized there might be a problem when he tried to ping the db from the EC2 instance in the terminal with the first connection string. When he tried the second, he got a response.
Info. on MS SQL Server ODBC driver for Linux:
- MS TechNet about ODBC: http://technet.microsoft.com/en-us/library/hh568454.aspx
- MS page on download and install (only has instructions for redhat): http://www.microsoft.com/en-us/download/details.aspx?id=28160
- MS page for SUSE Linux: http://www.microsoft.com/en-us/download/details.aspx?id=34687
- Blog post with instructions on installing ODBC driver in Linux: http://blog.nhaslam.com/2011/12/12/sql-server-odbc-on-linux/ (looks like he is not working from command line)
- Another tut for redhat: http://blog.quentinrousseau.fr/index.php/2012/07/how-to-install-microsoft-sql-server-odbc-driver-1-0-for-linux/