12

I need to prove that the encryption settings we have in our app's connection string are working.

What would be the simplest way to validate that traffic from our web site to the SQL Server is in fact encrypted?

sharptooth
  • 167,383
  • 100
  • 513
  • 979
Ralph Shillington
  • 20,718
  • 23
  • 91
  • 154

5 Answers5

27

You could use something like Wireshark to view the packets at they're transmitted over the network

Glen
  • 21,816
  • 3
  • 61
  • 76
  • 1
    Another +1 for Wireshark. This is exactly what I use to prove it to clients when they ask whether something is secure. – John Feminella May 25 '09 at 15:25
  • Good tool, but be careful, see Jeff's blog post: http://www.codinghorror.com/blog/archives/001267.html – Nathan Koop May 25 '09 at 16:33
  • 2
    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 May 25 '09 at 18:41
13

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
  • 288,378
  • 40
  • 442
  • 569
  • 2
    +1 for the encrypt_option as a simple quick check. As a copy-and-paste one liner, use: `SELECT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID`. Doesn't prove your deployed app is using encryption of course, but confirms the SQL Server _can_. – hlascelles Dec 17 '13 at 08:53
  • @hlascelles: `encrypt_option` is an after-the-fact value. If it says is encrypted, it means the app *does* use encrypted traffic. On that session, at least (usually on all other too). – Remus Rusanu Dec 17 '13 at 09:02
  • You are dead right. Sorry, I was coming at it from an admin point of view: testing a SQL Server deploy from a workstation (eg checking certificates). As @Remus says, add the line above to your deployed code for a runtime check (using the WHERE clause to ensure it is your actual connection). – hlascelles Dec 17 '13 at 09:20
  • 1
    By default, this does not work for non-sysadmins. Accessing the dm_exec_connections view requires VIEW SERVER STATE permission on the server, which is not granted by default. Do you know of an alternative where all logins can access (by default) ? – William Jan 25 '17 at 16:05
  • @Wiliam use [module signing](https://technet.microsoft.com/en-us/library/ms345102%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396) – Remus Rusanu Jan 25 '17 at 17:47
  • Keep in mind that sys.dm_exec_connection's encrypt_option column shows the _current_ encryption status for the connection. It starts out as false, then changes to true before TDS’s login phase if either the entire connection or just the first TDS login packet will be encrypted. In the case of the latter, once that packet has been processed, encrypt_option changes back to false. So, by itself, the fact that this column shows true doesn’t guarantee that the connection will be encrypted for its duration. – Ben Gribaudo Sep 22 '17 at 14:09
7

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
  • 14,430
  • 21
  • 71
  • 87
  • 2
    +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 May 25 '09 at 15:55
  • 2
    Integrated security means you're using NTLM or Kerberos authentication; it doesn't mean your traffic is encrypted. – Andomar May 25 '09 at 16:15
  • @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 May 25 '09 at 16:25
  • 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 May 25 '09 at 19:47
  • 1
    Keep in mind that setting Trust Server Certificate = true will allow for MITM attacks. If that's something you want to protect against, then set it to false and get an SSL certificate for the SQL server. Detailed instructions are available here: https://thesqldude.com/2012/04/21/setting-up-ssl-encryption-for-sql-server-using-certificates-issues-tips-tricks/ – Mike Aug 02 '17 at 00:53
1

There is another much underrated tool from Microsoft itself: 'Microsoft Network Monitor'. Basically this is very similar to wireshark with the exception that some specific MS protocols have better parser and visualisation support than wireshark itself and obviously it would only run under windows ;-).

The tool is quite old and looks abandoned (havn't seen a newer release so far) but still does an good job and the grammar for defining new protocols is quite neat/interesting - so this still possess a lot of power for the future. mnm 3.4 about dialog

Analysis Example - Recording is filtered for TDS - so the other packets are discared mostly:

Example Session for TDS (MSSQL)

This is also true for sql server connections. The MNM can even visualize the resultsets going over the wire - quite neat. Nonetheless wireshark as mentioned above would be sufficient to validate encryption and applied certificates on the wire itself. Means it can understand the TDS-Protocoll fully.

Handling TLS

Also with an extension (so called experts) 'NmDecrypt' and the right certificates (including private keys) - it is possible to decrypt protocolls - quite nice for TDS which uses TLS INSIDE of TDS - no wonder - no one has really implemented that yet as a fully supported protocoll for wireshark ;)

Links for the tools:

dalini
  • 176
  • 1
  • 9
0

To guarantee that encryption is being used, you need to enable the force encryption option 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.

For SQL 2000, KB 276553

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.

A KB search for SQL 2005

Late edit:

Use an older version of the MS JDBC client: it can't handle server side encryption...

Ben Gribaudo
  • 5,057
  • 1
  • 40
  • 75
gbn
  • 422,506
  • 82
  • 585
  • 676
  • By "you need to enable encryption on the server," do you mean "you need to enable the require encryption connections setting on the server"? If that setting is not set to true, the server will allow both unencrypted and first-login-packet-only-encrypted connections. – Ben Gribaudo Sep 22 '17 at 13:58
  • @BenGribaudo Once the checkbox is enabled in the SQL Server Config, then the server accepts only encrypted connections. Server side encryption is mandatory when this is checked. All my servers have this. – gbn Sep 22 '17 at 14:07