Hi All,
I have a SSL certificate installed on my SQL Server 2005. There is a default certificate as well which was created by the database itself.
I have configured the database to use my own certificate and have also set the ForceEncryption to Yes on the server.
Now, I am expecting that if my ASP.NET code on a different machine tries to connect to the database without having the root certificate installed on client's certificate store, I should get an error. But, I am not getting an error.
But, I do get the following error when I set Encrypt = Yes
"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"
and, If I set Encrypt=No, I do not get any error again.
My understanding was that if server forces encryption then client should definitely have the root certificate. Is that incorrect understanding?
Also, I want to know about what will happen if ForceEncryption = No on the server and Encrypt = Yes in the connection string.
Could you please help me understand the results of various options like this?
Additionally, with the server still Forcing Encryption, if I do install the root and intermediate root on my client then I get the following error with Encrypt = Yes.
"ERROR: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The requested name is valid and was found in the database, but it does not have the correct associated data being resolved for.)"
The Encrypt = No setting again does not give any error.
Thanks and Regards,
-Ajay Kumar Suri
Hi Ajay,
The validation of the server's certificate will only occur if encryption is forced on the client-side. Take a look at this posting for more info: http://blogs.msdn.com/dataaccess/archive/2005/08/05/448401.aspx
Il-Sung.
|||Hi,
Thanks very much for your reply.
Could you please verify if my understanding now is correct for SQL Server 2005.
Server : Encryption On (Self Signed Certificate)
Client : Encryption off or not specified.
The channel is encrypted.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption off or not specified. Root Certificate not installed on client.
The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption off or not specified. Root Certificate is installed on client.
The channel is encrypted using the server SSL certificate.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption On.
Will result in problem unless Trust Server Certificate is specified or client has the root certificate.
Server : Encryption Off.
Client : Encryption On.
Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.
Server : Encryption Off.
Client : Encryption Off.
No encryption.
I hope I have understood it correctly.
Please advise.
Best Regards,
-Ajay Kumar Suri
|||Hi Ajay,
Not quite. I've added my comments in blue. The key thing to understand is that client will only try to validate the server's certificate if encryption is requested from the client side. Otherwise, no certificate validation is performed so there's no need to use Trust Server Certificate, even for a self-signed cert.
Server : Encryption On (Self Signed Certificate)
Client : Encryption off or not specified.
The channel is encrypted.
> Correct.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption off or not specified. Root Certificate not installed on client.
The channel is encrypted using the Self Signed SQL Server 2005 certificate instead.
> Incorrect. The channel is encrypted using the provisioned certficiate, not the self-signed cert.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption off or not specified. Root Certificate is installed on client.
The channel is encrypted using the server SSL certificate.
> Correct.
Server : Encryption On (Certificate Issued by a Trusted Authority)
Client : Encryption On.
Will result in problem unless Trust Server Certificate is specified or client has the root certificate.
> Correct.
Server : Encryption Off.
Client : Encryption On.
Will result in problem unless Trust Server Certificate is specified. This would use the self created certificate from the server.
> Depends if the server has a proper certificate provisioned or not. If not, then a self-signed cert would be used and you are correct that Trust Server Certificate must be specified.
Server : Encryption Off.
Client : Encryption Off.
No encryption.
> Correct, although the login packet will always be encrypted.
Hope this clears things up.
Il-Sung.
|||Hi Sung,
Thanks very much for your comments.
It does clear a lot of things for me.
Best Regards,
-Ajay Kumar Suri
No comments:
Post a Comment