SQL Server – Non-SSL Connections with Force Encryption Enabled

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

Ok this is what i've referenced thesqldude.com/tag/makecert for creating self signed certificate. JIST :

1) MAKECERT tool used for preparing certificate with command mentioned in link above –

FYR : makecert -r -pe -n "CN=cs020.classic.classicinformatics.net" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

Where "cs020.classic.classicinformatics.net" is my system FQDN.

2) Then open mmc and added console to view certificate, And it was automatically restored in the certificate store under "personal" 3)Then opened SCM and view properties of SQL SERVER protocal.

FYI: My system hostname was "cs020" and its in domain "classic.classicinformatics.com". If it was in workgroup then creating certifcate with "CN=cs020" would have worked, but i check my fullcomputer name and it was stating something else i.e. "cs020.classic.classicinformatics.net" , so there was it, i've used the above command and got my results.