Re: Encryption

From: Neil Pike (neilpike@compuserve.com)
Date: 04/24/03 

Date: Thu, 24 Apr 2003 21:28:18 +0100
From: Neil Pike <neilpike@compuserve.com>

 Sandy,

 See the below taken from chapter 10 of the SQL Server resource kit
 
> Actually he wants documentation about EFS and how it works when
> encrypting SQL databases. Basically, he wants me to prove my case. So
> if you know of any whitepapers or documentation that describes the
> processs and could point me in that direction I would certainly
> appreciate it.

Encryption
Network Encryption Using SSL/TLS
SQL Server 2000 now automatically supports encryption of data and other network
traffic as it travels between the client and server systems on a network. The
encryption strength depends on the encryption capabilities authorized by the
certificate installed for SQL Server and the cryptographic capabilities of the
client and the server.

The certificate selected for SQL Server must be assigned to the name of the
server, in the form of the fully qualified Domain Name System (DNS) server name
(for example, SQLServer.Redmond.corp.Microsoft.com). The certificate must be
valid for server authentication. Log in to SQL Server as the SQL Server service
account, obtain the certificate (from either an internal certificate authority
or a trusted third-party provider), and then install it on the server in the
location suggested when you import the certificate.

Login Packet Encryption
During any login attempt, if a certificate is present on the server and is
usable (that is, it is valid for server authentication and has the DNS name of
the computer as the subject name in the certificate) all login-related packets
will be encrypted. This happens automatically, and as long as the certificate
is installed, no additional server configuration is required.

Client-Requested Encryption
The client can request encryption of all data traffic to SQL Server. This
option is set using the Client Network Utility (using the Force Protocol
Encryption option), and it applies to all outbound connections from that
computer. The Client-Requested Encryption option also prevents access to SQL
Server 7.0 and earlier, as well as any SQL Server 2000 server that does not
have a valid certificate.

You can also set this option programmatically with the Encrypt=yes option in
the connection string of your OLE DB or ODBC connection to a database server.

Server-Requested Encryption
Encryption can be required on the server if the database administrator requests
it. This option is set using the Server Network Utility (using the Force
Protocol Encryption option). Setting the Server-Requested Encryption option
guarantees that all network traffic to SQL Server will be encrypted. If a
client is unable to negotiate encryption with SQL Server, the connection will
be terminated.

Encrypted File System Support on Windows 2000
SQL Server 2000 works on Windows 2000 if you use Encrypting File System (EFS),
which is part of Windows 2000, to protect data files.

You must encrypt the files using the service account of SQL Server, and if you
change to service account, you must re-encrypt the files using the new account.
(To do this, unencrypt the files, change the service account for SQL Server
services, and then re-encrypt the files with the new service account.) If you
do not update the encryption when you change the service account, SQL Server
may not be able to start, because it will not be able to decrypt the files that
were encrypted with the previous service account's credentials.

 Neil Pike MVP/MCSE. Protech Computing Ltd
 Reply here - no email
 SQL FAQ (484 entries) see
 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq

Relevant Pages

  • RE: Help Newbie..Upload file from SQL Server
    ... Enable SSL Encryption for SQL Server 2000 with Microsoft Management ... Steps to Use to Install a Certificate on a Server with Microsoft Management ... Steps to Enable Encryption for a Specific Client ...
    (microsoft.public.sqlserver.programming)
  • Encrypting off-site with certificates public key
    ... I thought it would be wise to use a certificate encryption scheme to allow ... Then the data is written into a varbinarycolumn on the central server ... For some reason the public key is generating a different algorithm on .NET ...
    (microsoft.public.sqlserver.security)
  • Re: Policy settings tool and username tokens (not x509)
    ... the wse setup tool is telling me "I'm making you give me an x509 server cert ... to do any encryption, something isn't right. ... > Having both client and server tokens allows the default WSE client to ... > require that the CN name in the certificate match the name of the ...
    (microsoft.public.dotnet.framework.webservices.enhancements)
  • Re: SQL-Server startet nicht ...
    ... Because connection encryption is required, ... You should verify that the certificate is ... Check the SQL Server error log and the Windows event logs for information ...
    (microsoft.public.de.sqlserver)
  • Re: How to (completely) turn off protocol encryption in SQL Server? (the checkbox in server network
    ... Even with encryption switched off, the login handshake will still be ... CA certificate still present which I guess would be the cause of what you ... Jasper Smith (SQL Server MVP) ...
    (microsoft.public.sqlserver.security)