Post reply

Encryption Is Not Being Forced for All Connections

  • March 21, 2006 at 1:23 pm

    #93446

    I am trying to setup a secure SQL 2005 server. One of the things I want to do is force all connections to the server to be encrypted. I can get an encrypted connection, but I can also connect without encryption -- which I want to stop.

    I succesfully created a certificate on the server (using makecert.exe) using the SQL Service account. I setup the instance in SQL Server Configuration Manager to use the certificate I created, and I set the ForceEncryption flag to yes. I then restart the SQL Service. I also exported the certificate out of the Personal store and into the Trusted Root Certification Authority on the server. All of this was done with the service account (an admin). I also added the exported certificate to my client's Trusted Root Certification Authority.

    Again, I'm able to connect using encryption. I determine this by, on the client, right-clicking the server name in ManStud, selecting Properties, then View connection properties. However, I can still connect to the server even wth the Encrypt option unchecked. I thought the ForceEncryption flag is supposed to prevent that. BTW, I'm connecting as a sys admin via Windows authentication.

    Does anyone know how I can force every single connection to be encrypted, causing any non-encrypted attempts to fail? Thank you!

  • March 21, 2006 at 1:30 pm

    #627967

    BTW, it's running on Windows Server 2003 SP1. There is no Certification Authority running.

    Could this have anything to do with the certificate being self-signed?

  • March 22, 2006 at 3:22 am

    #628035

    Make sure named pipes is disabled.

    Try the connection from a machine that does not have the certificate installed.

  • March 22, 2006 at 8:08 am

    #628108

    Named pipes is disabled. The only two libraries enabled are TCP/IP and shared memory.

    My fellow DBA was able to connect to it via ManStud and QA without the certificate installed.

  • March 22, 2006 at 12:58 pm

    #628207

    I may be out of my realm, since I know SQL2000 encryption and do not know how much it has changed for 2005, so keep that in mind as you read this.

    For 2000, once I set it to force encryption all connections had to be encrypted. The ONLY way to verify this, I found, was to sniff the packets and see if there was plain text visible. The client, even when connected with an encrypted connection, did not flag it as such on the client side. This may also be the case with 2005, but I cannot say for sure at this time.

    Chris

  • March 22, 2006 at 1:04 pm

    #628209

    I'll have to give it a try. Now off to find a packet sniffer! Thanks.

  • March 24, 2006 at 7:34 am

    #628614

    I downloaded the Ethereal packet sniffer (looks like a great product, BTW) and gave it a shot. I don't know networking very well, so I'm not sure I was looking at the right stuff. But I saw clear text in the packets when I removed the certificate and disabled ForceEncryption. After I reenabled the cert and ForceEncryption, I saw what I think are encrypted packets. Well, let's just say I didn't see any clear text. This was true regardless of whether I checked the Encrypt Connection box and whether or not ManStud said encryption was on. Also, I tried it from QA and it looks like it was encrypted as well.

    So it looks like the ForceEncryption is working just fine. Thanks for the tip, Chris!

  • March 24, 2006 at 7:38 am

    #628616

    When I did it I ran the same query twice, one with encryption and once without, and I was able to compare similar packets in the sniffer, and see that one looked like garbage while I could read the other. You'll have to look through several non-encrypted packets to see it, but you'll see a pattern.

    Chris

  • March 24, 2006 at 7:41 am

    #628620

    Exactly. I found a select * from AdventureWorks.dbo.DatabaseError was a good test. It's been a pain in the rear, but this whole process is really teaching me a lot about encryption and network protocols. Kinda fun, too. :>

    Thanks!

  • May 10, 2007 at 8:01 am

    #705496

    any luck with forcing all connections to be encrypted ?

    I'm still able to connect without encryption; our sniffer shows the querytext-dump very nice readable.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 10 posts - 1 through 9 (of 9 total)

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