Is SSL being used?

Question

Is SSL being used?

Shanke

SSC-Addicted

Points: 494
More actions
August 10, 2010 at 7:22 am

#148432

As an administration task I need to find out if we have enabled encrypted connections between the user and the server. i.e. the sessions should be with SSL.
What I am currently doing is as follows:
I am looking at the 'ForceEncryption' under the following corresponding registry key for the respective SQL server instance:
"HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib"
If the value is '1' I assume that SSL is being used else not.
Am I correct in doing so?
Please help!
Thanks ,
Damodar Shanke
If U need things 2 b done,
then do the things that U need to be doing! :cool:

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

Did you add a certificate for Server Authentication from a trusted CA to the server and follow the steps and information outlined in the books online?

http://technet.microsoft.com/en-us/library/ms189067.aspx

Shanke SSC-Addicted Points: 494 More actions · Answer 2

Thank you for replying. 🙂

No, I have not configured the servers. It has been done by someone else. As I mentioned earlier my task was limited to figure out if SSL is enabled or not.

Also, I assume that if a certificate is not added it must be using some default certificate. (I understand that this could be a security hole.) Maybe checking for the certificate could be a next step that I could do after I confirm that SSL is enabled.

But again, is just checking for the 'ForceEncryption' reg key enough to determine that SSL is eabled or will I need to also ensure that an explicit certificate for Server Authentication has been added.

Thanks for all the help!

If U need things 2 b done,
then do the things that U need to be doing! :cool:

jncarter Ten Centuries Points: 1195 More actions · Answer 3

You are correct that sql does auto generate a certificate for use with ssl each time it starts. It is self-signed so it is not as secure as something you generate/purchase through a CA.

If you check the ForceEncryption option a restart of SQL is required. Just like it sounds all connections will be forced to use ssl.

You can verify connections are using ssl by looking at sys.dm_exec_connections. The encrypt_option will be true (encrypted) or false (not encrypted).

-Jeremy

Shanke SSC-Addicted Points: 494 More actions · Answer 4

Thank you so much to the both of you - Jeremy and Jonathan!

As per your suggestion, I think it would be a better option to look at the 'sys.dm_exec_connections' view which gives the actual run time info.

"select * from sys.dm_exec_connections where encrypt_option != 'TRUE'"

Just a passing thought - Could it be possible that even though SSL is enabled, some connections use encryption and some connection do not?

Thanks,

Damodar Shanke

If U need things 2 b done,
then do the things that U need to be doing! :cool:

jncarter Ten Centuries Points: 1195 More actions · Answer 5

SSL is always enabled. If the server option ForceEncryption is not enabled, then clients can still request to use SSL. So yes it is possible that some connections are encrypted while others are not.

Nadrek SSC-Insane Points: 20039 More actions · Answer 6

There is only one good way to see if encryption is being used; and that is to see if encryption is being used! Not if it is set up, not if it "should" be being used, not if it's logged as being used, but to see if it's actually being used.

This is a two part process:

Load up a packet sniffer!

Log in to the database and run a query in a few different ways; with a client that won't allow encryption, with a client that deliberately requests worthless (DES) or pathetic (RC2) encryption, with a client that is default, etc.

Some sniffers:

Smart Sniff

Microsoft Network Monitor 3.4

Wireshark