May 17, 2018 at 9:45 am
Some MSDN documentation when explaining how to setup SSL encryption for SQL Server, include a step similar to the following:
".. On the client computer, use the Certificates snap-in to install either the root certificate or the exported certificate file.."
In my circumstance the certificate has been created by the internal corporate CA and the clients will be on the same corporate domain as the server. I've been testing connectivity between my workstation and a development SQL Server instance where I've installed the certificate and enabled ForceEncryption in protocol properties. I can apparently connect using SSMS without enabling "Encrypt connection" or "Trust server certificate" options and without installing the certificate locally. I've also queried sys.dm_exec_connections on the server and used Microsoft Message Analyzer to examine network packets and confirmed encryption is enabled for the session and the network packets are in fact encrypted.
So, just to confirm, if the client trusts the certificate's root authority, is it still necessary to install the certificate on the client to support full server authentication and channel encryption? Also, based on the experience of those who have implemented SSL in an internal corporate environment, under what conditions would client connectivity potentially fail as a result of enabling SSL on the server?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 18, 2018 at 3:03 am
Is the certificate installed on SQL Server ? If yes, then your connections will be encrypted since there is a verifiable server certificate.
Also it would be good to disable the old TLS protocols not intended for use.
Hari Mindi
http://www.dba-datascience.com
May 18, 2018 at 5:59 am
From my understanding, the certificate contains the clients public key. This is why the client does not need to have a client certificate installed on the local machine. You are essentially giving the client everything they need to function under the new secure connection.
But, I've always wondered how secure this really is. I've never really dealt with Microsoft certificates before in this manner. If the server is essentially defining the public key for the client, then the public key itself can be compromised once it's sent to the client right? I mean, you have to first send without encryption in order to receive encryption? Then any further communication between client and server is encrypted.
As there is such thing as a client certificate, wouldn't that be the ultimate goal here? To both have a server and client certificate where you are not sending the public key to the client, they just already have it?
May 18, 2018 at 10:08 am
xsevensinzx - Friday, May 18, 2018 5:59 AMFrom my understanding, the certificate contains the clients public key. This is why the client does not need to have a client certificate installed on the local machine. You are essentially giving the client everything they need to function under the new secure connection.But, I've always wondered how secure this really is. I've never really dealt with Microsoft certificates before in this manner. If the server is essentially defining the public key for the client, then the public key itself can be compromised once it's sent to the client right? I mean, you have to first send without encryption in order to receive encryption? Then any further communication between client and server is encrypted.
As there is such thing as a client certificate, wouldn't that be the ultimate goal here? To both have a server and client certificate where you are not sending the public key to the client, they just already have it?
SSL is a two-way encrypted channel based on mutual trust. The temporary encryption key is signed by the client key, but is NOT the key used for the data transfer (they can't all use the same public key, ortherwise anyone who's ever accessed your connection would be able to read every connection ever against that server). If you're doing it blindly with no local certs, there is a bit of chatter where each side has to validate that they in fact trust the other wise enough to exchange data, at which point a temporary encryption key is established and you move on.
Loading the client cert avoid the initial chatter.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 18, 2018 at 11:24 am
My understanding is that the client only needs to have installed the root certificate of the Certificate Authority, and then the client can validate any server certificate issued by that the CA. For example, Windows comes bundled with the root certificates of dozens of external 3rd party certificate providers like VeriSign or Network Solutions, which is how SSL over https can be established with public websites. In a corporate setting that hosts it's own CA (like my scenario), the root certificate of the internal CA is installed when the workstation or application server is provisioned. In that case, an internal client can establish SSL connections to an internal database server without having to pre-install the certificate issued to that server. This would seem to be consistent with my testing, but correct me if I'm wrong about the concepts or technicals of how this stuff works.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 18, 2018 at 1:17 pm
Eric M Russell - Friday, May 18, 2018 11:24 AMMy understanding is that the client only needs to have installed the root certificate of the Certificate Authority, and then the client can validate any server certificate issued by that the CA. For example, Windows comes bundled with the root certificates of dozens of external 3rd party certificate providers like VeriSign or Network Solutions, which is how SSL over https can be established with public websites. In a corporate setting that hosts it's own CA (like my scenario), the root certificate of the internal CA is installed when the workstation or application server is provisioned. In that case, an internal client can establish SSL connections to an internal database server without having to pre-install the certificate issued to that server. This would seem to be consistent with my testing, but correct me if I'm wrong about the concepts or technicals of how this stuff works.
That's correct - the children of a common CA can validate/trust the other children without needing anything else installed. That said - pretty sure the parent cert has nothing to do with the actual key used to encrypt the communication once the trust has been set up (so the channel is still encrypted point to point from other peer machines not involved with the communication).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 18, 2018 at 2:49 pm
Matt Miller (4) - Friday, May 18, 2018 1:17 PMEric M Russell - Friday, May 18, 2018 11:24 AMMy understanding is that the client only needs to have installed the root certificate of the Certificate Authority, and then the client can validate any server certificate issued by that the CA. For example, Windows comes bundled with the root certificates of dozens of external 3rd party certificate providers like VeriSign or Network Solutions, which is how SSL over https can be established with public websites. In a corporate setting that hosts it's own CA (like my scenario), the root certificate of the internal CA is installed when the workstation or application server is provisioned. In that case, an internal client can establish SSL connections to an internal database server without having to pre-install the certificate issued to that server. This would seem to be consistent with my testing, but correct me if I'm wrong about the concepts or technicals of how this stuff works.That's correct - the children of a common CA can validate/trust the other children without needing anything else installed. That said - pretty sure the parent cert has nothing to do with the actual key used to encrypt the communication once the trust has been set up (so the channel is still encrypted point to point from other peer machines not involved with the communication).
The CA generated certificate may be only required for server validation, as protection against spoofing. I've read that setting ForceEncrypt=YES on client or server without having installed signed certificate on server will still enable full channel SSL encryption by using a default self signed certificate that SQL Server creates itself.
In my case, I want both authentication and channel encryption, and I want it without configuring or installing anything additional on the client, which it seems can be achieved so long as client machines already have CA root certificate pre-installed.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 18, 2018 at 3:31 pm
Eric M Russell - Friday, May 18, 2018 2:49 PMMatt Miller (4) - Friday, May 18, 2018 1:17 PMEric M Russell - Friday, May 18, 2018 11:24 AMMy understanding is that the client only needs to have installed the root certificate of the Certificate Authority, and then the client can validate any server certificate issued by that the CA. For example, Windows comes bundled with the root certificates of dozens of external 3rd party certificate providers like VeriSign or Network Solutions, which is how SSL over https can be established with public websites. In a corporate setting that hosts it's own CA (like my scenario), the root certificate of the internal CA is installed when the workstation or application server is provisioned. In that case, an internal client can establish SSL connections to an internal database server without having to pre-install the certificate issued to that server. This would seem to be consistent with my testing, but correct me if I'm wrong about the concepts or technicals of how this stuff works.That's correct - the children of a common CA can validate/trust the other children without needing anything else installed. That said - pretty sure the parent cert has nothing to do with the actual key used to encrypt the communication once the trust has been set up (so the channel is still encrypted point to point from other peer machines not involved with the communication).
The CA generated certificate may be only required for server validation, as protection against spoofing. I've read that setting ForceEncrypt=YES on client or server without having installed signed certificate on server will still enable full channel SSL encryption by using a default self signed certificate that SQL Server creates itself.
In my case, I want both authentication and channel encryption, and I want it without configuring or installing anything additional on the client, which it seems can be achieved so long as client machines already have CA root certificate pre-installed.
Yeah, what I was talking about was mainly for client spoofing. Bare in mind, the only reason I think about this is I'm currently trying to build a UDP server from scratch and also find a way to secure the packets (UDP is connection-less) with the client to prevent bad players from trying to spoof the client and cheat. 😀
May 21, 2018 at 7:08 am
Eric M Russell - Friday, May 18, 2018 2:49 PMMatt Miller (4) - Friday, May 18, 2018 1:17 PMEric M Russell - Friday, May 18, 2018 11:24 AMMy understanding is that the client only needs to have installed the root certificate of the Certificate Authority, and then the client can validate any server certificate issued by that the CA. For example, Windows comes bundled with the root certificates of dozens of external 3rd party certificate providers like VeriSign or Network Solutions, which is how SSL over https can be established with public websites. In a corporate setting that hosts it's own CA (like my scenario), the root certificate of the internal CA is installed when the workstation or application server is provisioned. In that case, an internal client can establish SSL connections to an internal database server without having to pre-install the certificate issued to that server. This would seem to be consistent with my testing, but correct me if I'm wrong about the concepts or technicals of how this stuff works.That's correct - the children of a common CA can validate/trust the other children without needing anything else installed. That said - pretty sure the parent cert has nothing to do with the actual key used to encrypt the communication once the trust has been set up (so the channel is still encrypted point to point from other peer machines not involved with the communication).
The CA generated certificate may be only required for server validation, as protection against spoofing. I've read that setting ForceEncrypt=YES on client or server without having installed signed certificate on server will still enable full channel SSL encryption by using a default self signed certificate that SQL Server creates itself.
In my case, I want both authentication and channel encryption, and I want it without configuring or installing anything additional on the client, which it seems can be achieved so long as client machines already have CA root certificate pre-installed.
However, if you use a self-signed certificate, you will need to export and install the self-signed cert from the server on the clients, or it won't connect.
As Eric pointed out, the above information I posted is *WRONG,* please disregard it.
https://docs.microsoft.com/en-us/sql/connect/jdbc/connecting-with-ssl-encryption?view=sql-server-2017
May 21, 2018 at 10:30 am
jasona.work - Monday, May 21, 2018 7:08 AMEric M Russell - Friday, May 18, 2018 2:49 PMThe CA generated certificate may be only required for server validation, as protection against spoofing. I've read that setting ForceEncrypt=YES on client or server without having installed signed certificate on server will still enable full channel SSL encryption by using a default self signed certificate that SQL Server creates itself.In my case, I want both authentication and channel encryption, and I want it without configuring or installing anything additional on the client, which it seems can be achieved so long as client machines already have CA root certificate pre-installed.
However, if you use a self-signed certificate, you will need to export and install the self-signed cert from the server on the clients, or it won't connect.
When I said self-signed certificate, perhaps I'm just talking about an un-signed certificate. Let's assume we have a server with no signed certificate. SQL Server will generate a default self-signed (un-signed?) certificate the last time it started up. This certificate will only be used for the purpose of channel encryption, not server validation.
Now, let's assume the client attempts to connect using the following the optional property ENCRYPT = YES in connection string. Because the client will by default attempt to validate the certificate, the connection will fail with the error: "The certificate chain was issued by an authority that is not trusted".
However, if the client connects using both ENCRYPT = YES and TrustServerCertificate = TRUE, the connect will succeed, because the certificate validation step is bypassed (the server is explicitly trusted by the client). In this case, the server's identity has not been validated, but the connection itself WILL be SSL encrypted, and the certificate does NOT need to be installed on the client.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply