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.
Improve this question
2
  • What errors have you received? Commented Jan 19, 2016 at 15:08
  • None, I have queried the server to check if the connection was encrypted, it was not. I would LOVE some errors by now. Commented Jan 20, 2016 at 8:53

2 Answers 2

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

I would consider this, though I can't find any specific means to force an SSL connection for ODBC.
Isn't the Encrypt keyword available when using dblib?
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
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.
@SamuelWillems Did it work with encryption=require and check certificate hostname?
|
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
Improve this answer

1 Comment

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.