Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
There are a few different encryption options for SQL Server 2005
By default using SQL Native Client your login packet will be encrypted over the wire so as to not disclose your password. This encryption is supported by a self-signed & self-generated certificate that is provisioned by SQL upon server startup. If you install a mutually trusted certificate you can increase your protection against man-in-the-middle type attacks.
Server
There are controls on the server to require that clients connecting to SQL you can control this with the ForceEncryption property under the Network Configuration. When you set this any client that is not able to use an encrypted connection will fail. In the SQL Server Configuration Manager right-click on “Protocols for MSSQLSERVER” and go to properties. You have to restart SQL for the changes to take effect here.
Client
On the client there are two settings; 1) Force Protocol Encryption; this will force the client to encrypt the entire data stream 2) Trust Server Certificate; this is the switch to say if you are going to trust the self-generated server certificate.
Your safest bet here with these two settings is #4; this will require that your server uses a provisioned, trusted certificate.
Force Protocol Encryption client setting |
Trust Server Certificate client setting |
Connection string/connection attribute Encrypt/Use Encryption for Data |
Connection string/connection attribute Trust Server Certificate |
Result |
|
1. |
No |
N/A |
No (default) |
Ignored |
No encryption occurs. |
2. |
No |
N/A |
Yes |
No (default) |
Encryption occurs only if there is a verifiable server certificate, otherwise the connection attempt fails. |
3. |
No |
N/A |
Yes |
Yes |
Encryption always occurs, but may use a self-signed server certificate. |
4. |
Yes |
No |
Ignored |
Ignored |
Encryption occurs only if there is a verifiable server certificate, otherwise the connection attempt fails. |
5. |
Yes |
Yes |
No (default) |
Ignored |
Encryption always occurs, but may use a self-signed server certificate. |
6. |
Yes |
Yes |
Yes |
No (default) |
Encryption occurs only if there is a verifiable server certificate, otherwise the connection attempt fails. |
7. |
Yes |
Yes |
Yes |
Yes |
Encryption always occurs, but may use a self-signed server certificate. |
Brad Sarsfield
Comments
Anonymous
June 08, 2007
Этот пост посвящен использованию сертификата с собственной подписью (self-signedAnonymous
July 24, 2008
Is there any way to avoid 'encrypting the login packet' (if security is not a concern) while using SQL authenticaton when we use MS ODBC drivers or OLEDB Providers? Looks like -there are 3rd party drivers that are not forcing the encryption of login packet? I wonder how they do it?Anonymous
June 09, 2009
How to disable default encryption when login?Anonymous
February 19, 2010
Re: http://blogs.msdn.com/sql_protocols/archive/2005/11/10/491563.aspx Can you please add borders to the table above, since the details are lost in the right most column without visible borders. Thanks! JasonAnonymous
November 04, 2010
Yes, Jason is right...you need to add borders!Anonymous
November 04, 2010
The 2008 R2 page has a nicer table :) msdn.microsoft.com/.../ms131691.aspx