November 26, 2008 at 1:28 am
Hi Folks,
I am trying to encrypt a database instance using SSL forced encryption flag. I have set this to yes and setup the certificate tab to point to the right certificate which I requested from a CA. I export the root CA certificate to import it to my personal store on the database client so that it would trust the certificate. Originally I was getting errors that the certificate wasn't trusted but I realize the certificate was a machine cert and I had the sql server service setup as a domain account so what I did was change the service account to local system. I can now get a connection when I choose the encrypted option in SSMS and when I set up a datasource.
But...
When I look at a packet sniffer I cannot see any SSL packets and when I try on another client machine that does not have a certificate I get a connection to the instance (which I would have thought would have failed because of forced encryption being yes). When I look in the sql server log I see that certificate was loaded for encryption.
Why can I not see SSL packet and why can I get a connection when I have no cert on a client?
November 27, 2008 at 12:22 am
Hi bodhilove,
Forgive me for being pedantic, but you're not encrypting the database instance, you're encrypting client connections to the database instance- big difference. Anyway, to your questions:
Which "forced encryption flag" did you set? There are 2 - one on the server network config and one on the client network config (see http://support.microsoft.com/kb/316898 for more info). If you only set the flag on the first of your clients that would explain why the second client could still connect to the server without SSL.
- Setting the flag on the client forces it to use SSL to connect to ANY SQL Server, but doesn't affect other clients' ability to connect to the server.
- Setting the flag on the server forces ALL clients to use SSL to connect to that server.
As to you packet sniffer results I can't say for sure - what exactly were your sniffing parameters? Remember you're not looking for HTTPS (tcp/443) traffic, you're looking for standard SQL Server (tcp/1433) traffic. It still uses the standard TCP port, SSL doesn't change this.
Also, if your SQL Server was originally using a domain account as the service account I'd probably change it back, and simply log onto the server as that account and import the root cert into its Personal store. Avoids having to use Local System which is not network-capable and overly permissive on the local machine.
Regards,
Jacob
November 27, 2008 at 8:10 pm
thank you Jacob... its great to get some help on this cos I really struggling. I am relieved that it does not use 443 (which I though it would) because netstat -a shows up nothing. I am trying to encrypt the channel and not the db itself.
Have you set this up yourself in the past?
November 28, 2008 at 12:29 am
seemes to be working now to some degree
It seems no matter whether I have Force Encryption On or Off I am getting SSL packets in Microsoft Network Monitor when I connect from a client using SSMS. I have got my certificates set up between the 2 machines this occurs with. Also when I get a SSMS connection from the server with a totally different SQL Server that does have a server certificate installed I get SSL packets in Net mon. When I use machines that do not have certificates on either server and client I get no SSL packet. The content type of the TLS RecordType packets are application data and continued application data.
I thought the Forced Encryption property is supposed to determined if SSL is used not whether the server certificate is installed. Is that right?
November 30, 2008 at 3:55 pm
Hello bodhilove,
No, I haven't actually set this up myself. SQL Server always encrypts the login credential exchange though it leaves the data stream unencrypted unless you request SSL encryption - you should be seeing this as an SSL exchange just before the normal TDS data stream.
If you don't have a certificate installed on the server it'll generate a self-signed cert when SQL Server starts up, and use that for the login credential encryption. It should also use it for data stream encryption if the client requests it and has the "Trust Server Certificate" option set to True (as it the client won't otherwise trust a cert signed by the server).
Regards,
Jacob
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply