8

I'm trying to connect to an SQL Server from a PHP/Zend Framework application running on Ubuntu and at a remote location.

I'm trying to get the connecting application to request encryption from the SQL Server (since the default connection on 1433 is in the clear, and I don't want my credentials being sniffed).

I've configured the wildcard SSL certificate on the SQL Server, and I'm creating a PDO connection with the following DSN:

dblib:host=server-not-matching-domain.com:1433;dbname=MyDB;Encrypt=true;TrustServerCertificate=false;charset=

Taken from http://blogs.msdn.com/b/brian_swan/archive/2011/03/08/sql-server-driver-for-php-connection-options-encrypt.aspx

Since the host name doesn't match the installed certificate, I'd expect the connection to fail - but it doesn't fail.

HorusKol
  • 8,375
  • 10
  • 51
  • 92
  • When you say you have a wildcared SSL certificate, what CNs does that certificate match? I suspect that the driver may never do actual CN to hostname checks; TrustServerCertificate=false only prevents self-signed certificates. – mjec Feb 10 '17 at 14:40
  • The point is that the hostname absolutely does not match the wildcard domain, so I expect an error. If I don't get a certificate error in this scenario, I can't trust the connection when the hostname does match. – HorusKol Feb 10 '17 at 22:51
  • Interesting - it seems my question has been duplicated: http://stackoverflow.com/questions/34875958/php-connect-to-ms-sql-with-ssl - there's good answers here and there... what's the protocol? – HorusKol Feb 14 '17 at 22:19
  • Can you verify exactly what driver you are using and that the client is Ubuntu? sqlncli-11.0.1790.0 ? – ficuscr Feb 14 '17 at 22:25
  • I ask because last time I tried this [freetds](http://www.freetds.org/) was in the mix. PDO_SQLSRV is Windows only. Maybe you should be adding to freetds.conf / odbc.ini... `encryption=require` and `check certificate hostname`? – ficuscr Feb 14 '17 at 22:40
  • To be honest - my question is a couple of years old, and my need for an answer has passed as we no longer connect directly to the SQL Server but via an encrypted web api. However, if you look at the DSN, I'm using dblib and not sqlsrv as the PDO driver. I'm going to VTC this question in favour of the newer duplicate as that contains the relevant info about freetds and ODBC. – HorusKol Feb 14 '17 at 22:45
  • Possible duplicate of [PHP connect to MS SQL with SSL](http://stackoverflow.com/questions/34875958/php-connect-to-ms-sql-with-ssl) – HorusKol Feb 14 '17 at 22:45

3 Answers3

2

Might it be that Encrypt=true;TrustServerCertificate are not taken under consideration at all? Without being an expert on this please check out the documentation in PHP regarding PDO_DBLIB DSN. As written there is a special parameter in the DSN that is called secure and is currently unused. I am not sure if this is supposed to be the correct way of declaring that you want a secure connection in this DSN and it is also not helpful at all the fact that it is tagged as unused.

In addition also do the following

In php.ini make sure the following is set: mssql.secure_connection = On

Have a look if connecting via PDO_ODBC is better for what you want to achieve. For connecting via PDO_ODBC check out this example

  • @HorusKol no need mate i always do check it in case something changed or in case i forgot something. After all we are in this game because bottom line we are readers mostly... we like to read and solve. :) cheers –  Feb 13 '17 at 09:16
0

try to install AES ENCRYPTION CERTIFICATE it's the best i have used to protect credentials from being sniffed

dblib:host=server-not-matching-domain.com:1433;dbname=MyDB;Encrypt=true;TrustServerCertificate=true;charset=utf8

make the default value true of ssl crtificate in php.ini file and then procede with AES

Mohit_
  • 49
  • 1
  • 7
-3

It is a more complicated than just setting up a flag.

Refer this blog: http://www.madirish.net/214

  1. You will have to create or install ssl certificate on mysql server.

  2. in my.cnf do the below settings:

    ssl-ca=/etc/ssl/mysql/server.csr
    ssl-cert=/etc/ssl/mysql/server.cert
    ssl-key=/etc/ssl/mysql/server.key

  3. restart mysql : mysqld restart

  4. Create client keys

  5. Send those keys along with username and password.

Following stackoverflow answers explain it in detail:

Connect to remote MySQL server with SSL from PHP

PHP to MySQL SSL Connections

Hope it helps.

Community
  • 1
  • 1
Prasad Paranjape
  • 151
  • 8
  • 1
    Can you quote the relevant parts of the article? I might accept the answer if it was more than just a link that may rot – HorusKol Feb 10 '17 at 22:53
  • 3
    This question is tagged `sql-server`, indicating Microsoft SQL Server. Your answer is for MySQL, which is a different DBMS product. – Dan Guzman Feb 11 '17 at 18:49