I need to prove that the encryption settings we have in our app's connection string are working. Would would be the simplest way to validate that traffic from our web site to the sql server is in fact encrypted.
+20
A:
You could use something like Wireshark to view the packets at they're transmitted over the network
Glen
2009-05-25 15:03:18
+1 This is a great tool for that.
BenAlabaster
2009-05-25 15:09:49
+1, bye-bye to my late answer.
karim79
2009-05-25 15:15:51
Another +1 for Wireshark. This is exactly what I use to prove it to clients when they ask whether something is secure.
John Feminella
2009-05-25 15:25:37
Good tool, but be careful, see Jeff's blog post: http://www.codinghorror.com/blog/archives/001267.html
Nathan Koop
2009-05-25 16:33:29
On modern switched networks its handy to hang onto a small 4 port hub. You cant really get anything on wireshark unless you are in the same collision domain as the data you want to monitor. A switched network causes micro segmentation so you really need to insert yourself using the hub into the collision domain of your target.
Copas
2009-05-25 18:41:39
+1
A:
I would set the Force Protocol Encryption to true and Trust Server Certificate to true in the db connection string. The server should fail to establish a connection if it cannot provide you with an encrypted connection as requested. There is an article that covers encryption with sql server 2005 and later.
Simple test is to try a connection with and without encryption and fail when it hands out the undesired type of connection. then its up to the DBA, IT or you to configure the server to match your requirements.
MikeJ
2009-05-25 15:49:48
+1 for your answer. Contrary to the wireshark option, devising a test to prove that the unencrypted connection will be rejected also helps demonstrate that the application security requirements cannot be degraded just by changing settings on the database server side.
Pierre-Luc Simard
2009-05-25 15:55:13
Integrated security means you're using NTLM or Kerberos authentication; it doesn't mean your traffic is encrypted.
Andomar
2009-05-25 16:15:37
@andomar is correct --- It's my understanding that the creds (regardless of integrated or mixed mode) would be encrypted --- it's the rest of the traffic that I'm thinking of.
Ralph Shillington
2009-05-25 16:25:42
you are correct - the flags are not the NTLM authentication ones, but those should be used too...I have updated to show that you should set the Force Protocol Encryption and Trust Server Certificate flags in the connection string.
MikeJ
2009-05-25 19:47:03
+1
A:
You check the encrypt_option column of the sys.dm_exec_connections DMV. This way you can no only prove that is encrypted, but you can also validate in your application at start up time. To enforce the encryption you follow the methods described in this MSDN How To: Enable Encrypted Connections to the Database Engine. If either the client or the server forces encryption and a certificate is provided and the client accepts the server certificate, the connection will be encrypted. To validate that the traffic is encrypted you can use the built-in netmon.exe tool (must be installed from ad/remove system components), download the improved Microsoft Network Monitor 3.2 or other third party tools.
As an alternative the deployment site can enforce IPSec encryption.
Remus Rusanu
2009-05-25 16:26:41
A:
To guarantee that encryption is being used, you need to enable encryption on the server.
Client side encryption is not mandatory. Server side is mandatory.
When the SQL Server service starts, it will stop if it can not read the certificate or there are other obstacles. It will not accept unencrypted connections.
To answer, I used a packet sniffer the first I used encryption to check, then I just relied on the fact that server side encryption is mandatory and SQL won't start.
Keep in mind that there is a current SQL Server limitation if you enable encryption on the server. Encryption will be for all incoming connections. If you enable encryption on the client computer, all outgoing connections from that client try to make an encrypted connection to any SQL Server.
Late edit:
Use an older version of the MS JDBC client: it can't handle server side encryption...
gbn
2009-05-28 11:23:57