5

What I mean to achieve is very simple. I want to connect to an external MS SQL database from a PHP script over a secure connection. This has however proven problematic and, with three hours put in to research so far, I am at a loss.

The platform for the client is Ubuntu, which means I can not use SQLSRV. The secure connection has been tested with different clients and it works fine. I am currently using PDO and DBlib to connect to the database, which also works fine.

I was not able to find any method to force a secure connection. I have tried multiple other drivers, to no avail.

What are my options?

Edit: I am left with the following FreeTDS logs...

config.c:543:   Got a match.
config.c:565:   host = 'XXXXXXXXXX'
config.c:595:   Found host entry XXXXXXXXXX.
config.c:599:   IP addr is XXXXXXXXXX.
config.c:565:   port = '1433'
config.c:565:   encryption = 'require'
config.c:565:   check certificate hostname = 'no'
config.c:629:   UNRECOGNIZED option 'check certificate hostname' ... ignoring.
config.c:565:   ca file = 'XXXXXXXXXX.pem'
config.c:629:   UNRECOGNIZED option 'ca file' ... ignoring.
Samuel Willems
  • 301
  • 3
  • 12

2 Answers2

3

If you want to use PDO, you could set up PDO ODBC. You will need to setup the configuration files /etc/odbc.ini, /etc/odbcinst.ini and /etc/freetds/freetds.conf.

You'll also have to install unixodbc and freetds: apt-get install unixodbc tdsodbc.

You can see more info here: Connect PHP to MSSQL via PDO ODBC

EDIT: To enforce SSL in ODBC, add the Encrypt keyword and set it to true in your connection string. And setup your SQL Server to use SSL: https://support.microsoft.com/en-us/kb/316898

EDIT 2: According to the OP, adding encryption=require and check certificate hostname to freetds.config as per the following specification: http://www.freetds.org/userguide/freetdsconf.htm along with the above steps will fix the problem

Community
  • 1
  • 1
Don Rhummy
  • 24,730
  • 42
  • 175
  • 330
  • I would consider this, though I can't find any specific means to force an SSL connection for ODBC. – Samuel Willems Jan 19 '16 at 14:12
  • Isn't the Encrypt keyword available when using dblib? – Samuel Willems Jan 20 '16 at 08:35
  • I have configured `ODBC`, it works correctly. I have added the `Encrypt` keyword to the `odbc.ini`, yet it still does not encrypt the connection. It still does not throw any errors either. The server is configured to accept both encrypted and plain connections. The query I use to check the connection is `SELECT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID`. I'm pretty certain `Encrypt` is not available in the ODBC DSN, though I'm aware that it is used for `PDO_SQLSRV` – Samuel Willems Jan 20 '16 at 09:53
  • To force a secure connection one might append `encryption=require` to `freetds.config` as per the following specification: `http://www.freetds.org/userguide/freetdsconf.htm` Another useful atribute is `check certificate hostname`. Please update your answer to include all relevant information. – Samuel Willems Jan 20 '16 at 10:31
  • @SamuelWillems Did it work with `encryption=require` and `check certificate hostname`? – Don Rhummy Jan 20 '16 at 13:37
  • provided that the certificate is either valid or added as trusted root certificate. There does not seem to be an attribute to completely disable certificate verification. – Samuel Willems Jan 20 '16 at 16:31
  • @SamuelWillems I made the edit. Can you mark it as the answer? – Don Rhummy Jan 20 '16 at 16:33
1

Have you configured mssql_connect to use a secure connection? Look in your php.ini and verify the mssql.secure_connection parameter is set to on

[MSSQL]
mssql.secure_connection = On
Don Rhummy
  • 24,730
  • 42
  • 175
  • 330
  • Hey, thank you for your reply. I didn't want to use `mssql_connect` since it's deprecated. I have, however, indeed tried to change this setting and use `mssql_connect`. I've installed the certificate in `/etc/ssl/certs` as well. I was wondering how I might debug this, I currently have no idea what is going wrong. Does it not even try to connect over SSL or is it running in to a problem and defaulting to plain? – Samuel Willems Jan 19 '16 at 13:56