SQL Server SSL connection - What am I missing?

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.

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

StackExchange Database Administrators Q#55421, answer score: 6