4

I am trying to set up my SQL Server 2008 R2 instance so that it only accepts SSL connections. My understanding is that once this is set up, any client machine should need to have a certificate installed in order to connect.

I have followed the instructions on http://msdn.microsoft.com/en-us/library/ms191192(v=sql.105).aspx and done a lot of reading on a lot of other sites as well. I have created a certificate, selected it in the SQL Server Configuration Manager (Network Configuration/Protocols/Properties), and I have set 'Force Encryption' to true. I restarted the service running my instance.

After doing all this, when I try to connect to my instance remotely, I succeed. Normally I like succeeding but in this case I haven't installed a certificate my development machine would recognize (this is where my instance is) and when I look at the connection properties, it says the connection is not encrypted. If I choose to try an encrypted connection from the client (selecting this in SQL Server Management Studio's Options in the connection box), I get the certificate problem I expect.

So my questions are: am I right that if things are set up correctly the connection from the client should fail (because it lacks an appropriate certificate) without my having to configure anything on the client? And is there something obvious that I might be missing? Some other place where I have to change a property, maybe? I am hoping that someone who has done this before will have some ideas.

By the way, in creating my cert I followed SQL Dude's instructions at http://thesqldude.com/tag/makecert/. The cert's name is my dev machine's full qualified domain name.

Improve this question

1 Answer 1

Reset to default
6

Note that when you force encryption on the server side, the connection will be encrypted by using ssl. You can use any certficate for this. Your connection will be encrypted, However the client will not check if the identity of the SQL Server is in fact valid. Without this check you could be open for a man in the middle attack.

If you choose force encryption on the client side, a full validity check will be performed. That way not only is the connection encrypted, also the client knows that the server it is connection to, is in fact the server mentioned in the certificate because it can trust that the certificate has not been tampered with.

For this to work, the certificate used on the SQL Server must be issued by a certificate authority that the client trust. This can be any well known certificate authority or for example when you have a windows domain and a CA in that domain, you can have that CA issue one. You then have to import the root certificate of that CA into all clients and that will work too.

to check if your connection is encrypted use the following:

SELECT session_id,encrypt_option
FROM sys.dm_exec_connections
Improve this answer
6
  • session_d was not a column, according to my SSMS, but running the command you referenced without it gives several 'true' results. But I am more interested forcing the client to have a cert. Is this not possible without also doing some configuration on the client?
    – Ralph
    Dec 21, 2013 at 15:37
  • Oh, sorry, should have been session_id (fixed it). What is your main goal? Making sure that nobody can read the data that goes over the wire? Or do you also want to make sure that when a client that is connecting to ServerA is really connection to ServerA and not to a ServerB that is posing to be ServerA? If you want that, you would need to configure the client to force encryption by setting the appropriate property in the connection string. But then you have to be sure that the certificate that you installed on the server is issued by a authority that is trusted by the client.
    – Edward Dortland
    Dec 21, 2013 at 16:48
  • You can accomplish that by for example getting a certificate from a well-known certificate authority or if you have a CA in your Domain you can have that one issue a certificate and then you import the Root certificate of your domain CA on your clients.
    – Edward Dortland
    Dec 21, 2013 at 16:51
  • In addition to encrypting the traffic, I was hoping I could actually use this to verify the identity of the client machine. There is only one client that should ever access the production version of this database. The fourth paragraph of the MSDN article I cited gave me the impression that if I set up an SSL connection, an attempted client connection would fail without the proper certificate. But based on what you have written and what I am seeing, this does not seem to be the case unless the client machine is specifically set up for an SSL connection.
    – Ralph
    Dec 21, 2013 at 22:01
  • I think you have answered my question but if anyone could point me in the right direction for what I'm looking for, that would be great.
    – Ralph
    Dec 21, 2013 at 22:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.