SQL Server 2012 : Encryption Support (part 1) - Encrypting Data on the Move

1/22/2014 12:33:48 AM

Let’s face it: data that is stored in databases is interesting not only to us as users but to many others in the world whom you might not know or trust. With the increased leverage of the power of relational databases by businesses and consumers, database vendors are under increasing pressure to provide more security-related features.

Other than locking down access to SQL Server databases, administrators and developers can provide another layer of protection against the bad guys, and that is encryption. At a high level, encryption takes interesting information, such as your credit card number, and translates it into a binary representation that can be understood only by authorized parties. Data can be encrypted for use in transit, such as when you are passing your password back to a web server, or it can be stored in an encrypted format (on the file system or in a database, for example).

Data in SQL Server is encrypted using encryption keys. These keys can be either symmetric or asymmetric, and there are pros and cons to using either one. With symmetric key encryption, both the sender and the receiver of the data have the same key. The sender encrypts the data using the key and an encryption algorithm. When the data reaches the recipient, it is decrypted using the same encryption algorithm and key. The main benefit of this approach is better encryption and decryption performance compared to using asymmetric keys. The problem with symmetric key encryption comes into play when you consider what happens when someone else somehow gets hold of your symmetric key. Because you are encrypting with just one key, anyone who has that key can decrypt the data.

Asymmetric key encryption uses two keys, a public key and a private key. The sender encrypts the data using the recipient’s public key, which is freely obtainable by anyone. The security comes in when the recipient receives the data; the recipient decrypts it via his or her private key. The public key in this case cannot decrypt the data. Thus, the private key is the valuable asset when using asymmetric encryption.

The other concept that arises in encryption discussions is certificates. Certificates are basically asymmetric keys that contain extra metadata. This metadata includes information such as an expiration period and the certificate authority that issued the certificate. Certificates can be created by anyone, and in some circumstances, you want to make sure that the sender or recipient of the data is actually who he or she says. This is where certificate authorities come into play. These companies act as a mediator between the sender and receiver. After you pay a nominal fee and they conduct an identity check, they provide you with a certificate that they have signed. When you use this signed certificate to send data, the recipient can validate the certificate with the certificate authority, and because both of you trust the certificate authority, it’s safe to assume that the message was in fact signed by the sender.

There is another type of certificate called a self-signed certificate, which anyone can create. In some cases, it is acceptable to use a self-signed certificate. SQL Server automatically creates a self-signed certificate the first time it starts. This certificate is used to encrypt the connection during SQL Server authentication.

1. Encrypting Data on the Move

All connection login requests made to SQL Server are encrypted if the client is using the SQL Server Native Access Client application programming interfaces (APIs). This is a huge improvement, because in SQL Server 2000 and earlier versions, if a user wanted to authenticate using SQL Server Authentication, the user name and password were sent in clear text across the wire. SQL Server 2005 and higher can automatically encrypt the login packet information via the self-signed certificate it created the first time the service started.

Login packets are not all that’s encrypted in the connection. The entire connection itself can optionally be encrypted for the lifetime of the connection. The request for an encrypted channel can be forced by the server (so that all connections are encrypted by default), or the request can be made by the client making the connection. It is recommended that administrators use a real certificate rather than the self-signed certification because of potential “man-in-the-middle” attacks.

To force encryption on the server, launch the SQL Server Configuration Manager tool (available in the Configuration Tools folder of the SQL Server 2012 Program Files menu item). This tool is used for managing the protocols and services of SQL Server. Expand SQL Server Network Configuration, right-click Protocols for MSSQLSERVER, and select Properties to open the Protocols for MSSQLSERVER Properties dialog box, shown in Figure 1.

Protocols for MSSQLSERVER Properties dialog box.

Figure 1. Protocols for MSSQLSERVER Properties dialog box.

In this dialog, the Flags tab allows you to force encryption on the server and the Certificate tab allows you to select a certificate that is already installed on the local machine to be used by SQL Server to encrypt the data. If you force encryption but do not select a certificate, SQL Server uses its self-signed certificate to encrypt the data. Remember that the SQL Server self-signed certificate is not considered trusted by the client connections. For clients to be able to use the SQL Server self-signed certificate, they must set this option in the Properties dialog box of the SQL Native Client 11.0 Configuration node in the same Configuration Manager tool. This dialog box is shown in Figure 2.

SQL Native Client 11.0 Configuration Properties dialog box.

Figure 2. SQL Native Client 11.0 Configuration Properties dialog box.

This dialog box offers two options: The Force Protocol Encryption option forces clients to always make encryption connections. The second option is what needs to be set if the client wants to make encrypted connections to SQL Server and have it leverage the SQL Server self-signed certificate.

  •  SQL Server 2012 : Authentication and Authorization (part 2) - User-Schema Separation,Execution Context
  •  SQL Server 2012 : Authentication and Authorization (part 1) - How Clients Establish a Connection, Password Policies
  •  SQL Server 2012 : SQL Server Security Overview
  •  SQL Server 2012 : Working with Transactions - Transactions in SQL CLR (CLR Integration)
  •  SQL Server 2012 : Distributed Transactions (part 4) - Using a Resource Manager in a Successful Transaction
  •  SQL Server 2012 : Distributed Transactions (part 3) - Distributed Transactions in the .NET Framework - Writing Your Own Resource Manager
  •  SQL Server 2012 : Distributed Transactions (part 2) - Distributed Transactions in the .NET Framework
  •  SQL Server 2012 : Distributed Transactions (part 1) - Distributed Transaction Terminology, Rules and Methods of Enlistment
  •  Protecting SQL Server Data : TRANSPARENT DATA ENCRYPTION (part 6) - Reversing the Implementation of TDE
  •  Protecting SQL Server Data : TRANSPARENT DATA ENCRYPTION (part 5) - Verifying TDE - Verification through Backup and Recovery
    Video tutorials
    - How To Install Windows 8 On VMware Workstation 9

    - How To Install Windows 8

    - How To Install Windows Server 2012

    - How To Disable Windows 8 Metro UI

    - How To Change Account Picture In Windows 8

    - How To Unlock Administrator Account in Windows 8

    - How To Restart, Log Off And Shutdown Windows 8

    - How To Login To Skype Using A Microsoft Account

    - How To Enable Aero Glass Effect In Windows 8

    - How To Disable Windows Update in Windows 8

    - How To Disable Windows 8 Metro UI

    - How To Add Widgets To Windows 8 Lock Screen
    programming4us programming4us
    Top 10
    Free Mobile And Desktop Apps For Accessing Restricted Websites
    MASERATI QUATTROPORTE; DIESEL : Lure of Italian limos
    TOYOTA CAMRY 2; 2.5 : Camry now more comely
    KIA SORENTO 2.2CRDi : Fuel-sipping slugger
    How To Setup, Password Protect & Encrypt Wireless Internet Connection
    Emulate And Run iPad Apps On Windows, Mac OS X & Linux With iPadian
    Backup & Restore Game Progress From Any Game With SaveGameProgress
    Generate A Facebook Timeline Cover Using A Free App
    New App for Women ‘Remix’ Offers Fashion Advice & Style Tips
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th