SSL Encryption Test

SSL Encryption Test

Post by xxdanbrown » Fri, 30 Sep 2005 05:34:08

Hello All,

After some (significant) effort I successfully requested and installed
a certificate on my test sql server box on my intranet.
I then selected "force encryption" on the SQL Server Network Tool.
SQL Server started up just fine indicating (as far as I knwo) that it
has found the certificate and that the certificate is acceptable.

Next step was testing: One test was connecting Query Analyzer from a
machine on the same intranet that the test SQL Server box is on which
has a copy of the certificate installed on it.
The next test was connecting query analyzer from a similar machine on
the intranet but without a certificate.

In both cases query analyzer connected successfully with no error.

Now correct me if I'm wrong here, but my understanding is that the box
without the certificate should have been unable to connect to the SQL
Server. Since it *was* able to connect and query the data it appears
that something is not working right. What am I doing wrong?

SSL Encryption Test

Post by xxdanbrown » Fri, 30 Sep 2005 23:49:10

Correction: It looks like it is actually *working*.
I sniffed the packets with force encryption on and cannot see anything
I sniffed the packets with force encryption off and I can make out
everything passing back and forth between the sql server and the

What is wierd as far as I'm concerned is how this is supposed to secure
the system if *anybody* can connect. Basically the only protection it
gives you is preventing packets from being sniffed and read, so someone
could *still* connect using SSL and run a dictionary attack trying to
guess sa if they knew what port you were on.


SSL Encryption Test

Post by Matt Neeri » Sat, 01 Oct 2005 10:52:02

There are currently 2 modes of SSL with SQL.

Client side initiated SSL encryption and server-side SSL encryption.

Server side SSL encryption is enabled via the "Force Protocol Encryption"
settting on the server, you have discovered this already.
With Server side SSL, the client does not validate the SSL certificate at
all, it just uses it to encrypt the traffic.

With client side initiated SSL encryption, the client will both verify that
it trusts the root CA of the certificate as well as validate that the target
server is properly embedded in the certificate (mutual authentication). So
client side intiated SSL is more stringent.

However, you cannot use SSL to deny users access to the server (or for
client authentication like you can with IIS), SSL is only used to encrypt
the data over the wire with SQL. But that is actually a good idea I'll
bring to the next meeting we have about future of TDS protocol, I think
this would be a nice feature to have.

Matt Neerincx [MSFT]

This posting is provided "AS IS", with no warranties, and confers no rights.

Please do not send email directly to this alias. This alias is for newsgroup
purposes only.