Voice of the DBA

Tag: security

  • Creating a Symmetric Key in SQL Server

    Symmetric keys in SQL Server are recommended for encrypting data in columns. They are a good balance of security and resource usage, much better than asymmetric keys. Creating a symmetric key is fairly simple, using DDL that’s easy to understand.

    One note before I show this is that symmetric keys are deterministic when created, meaning that the same parameters run in different databases will result in the same key. That means that the same key in a different database (or instance) can decrypt data that was encrypted in your production instance. Keep control of the parameters used to create symmetric keys and secure them. That means watch out for storage of these items in source control, in installation files, upgrade scripts, etc.

    Creating a Key

    The creation DDL used is the CREATE SYMMETRIC KEY statement. This command has a number of parameters that you can change. The important ones for most people are:

    • the algorithm
    • the encryption mechanism
    • the key source
    • the identity value

    You should try to use the most secure algorithm you can, which is AES_256 in SQL Server 2012. It’s the same back to SQL Server 2005. You should avoid the RC4 algorithms, since they are not terribly secure. Even the DES ones you might avoid, but do some research to understand if you have a need to use anything less than AES_256.

    The encryption mechanism provides protection for the key. You can use a password (secure it) or you can use another key. The common way to secure the symmetric key is with an asymmetric key (or a certificate). However if you have the option to use a hardware module with the extensible key management (EKM) system, use that. You can use multiple encryption mechanisms if needed, which might be useful for separating the access to this key for different users.

    The key source provides a way to seed the key. This is a parameter you need to regenerate the key.

    The identity value is optional, but provides more a passphrase to tag a key. Useful for temporary keys.

    To actually build a key, let’s create one here using a few parameters, and securing it with a password:

    -- create a symmetric key
create symmetric key MySalaryProtector
 WITH ALGORITHM=AES_256
    , IDENTITY_VALUE = 'Salary Protection Key'
    , Key_SOURCE = N'Keep this phrase a secr#t'
  ENCRYPTION BY PASSWORD = 'Us#aStrongP2ssword';
go

    That’s it, once you have executed this, you have created a key. You can see your symmetric keys by querying sys.symmetric_keys

    SELECT * FROM sys.symmetric_keys

    sym_key_a

     

    That’s all you need to do. There’s not backup or restore of a key; if you need to recreate it, supply the same parameters and you’ll get the same key. The keys are stored in the backup of a database, so if you restore from backup, you’ll have them back as well.

    In another post, I’ll look at actually encrypting data with a key.

  • SQL Injection Tools

    I wouldn’t recommend you use any of the tools in this article for attacking anyone, but they could help you to understand the vulnerabilities in your own application. The tools cover a variety of possible ways that injection attacks can occur and most work against SQL Server.

    In this day and age, I’m amazed that SQL injection isn’t given more attention by developers, but I constantly find developers that aren’t aware of what it is, or don’t bother to incorporate techniques known to prevent injection.

    No matter how secure you think your particular site is, there might be someone with access that plays with one of these tools, or someone that gets access through another system and can then attack your application. Don’t count on outer firewall security at your perimeter.

    Learn to build applications with SQL Injection in mind and develop the habit of secure coding.

  • Creating a User without a Login – Contained Databases

    In SQL Server 2012, we have a new feature: partially contained databases. In a previous post, I showed how to enable this, and this post will look at one of the advantages of contained databases: users without logins.

    Contained Users

    One of the problems in non-contained databases is the fact that when a database is moved or restored, the login mapping to the user in the database doesn’t always transfer cleanly. Microsoft has sp_help_revlogin and sp_change_users_login to help fix this, but in a DR situation, or in a crisis, this may not work. It’s also a hassle.

    Contained users help fix this. They are users that exist within the database, and do not require a login mapping. The server level authentication will transfer to a database level authentication, if the database has the partial containment option set.

    To create a contained user, you can use the GUI, or T-SQL, both of which are easy and I’ll show them below:

    SSMS Contained User

    If you right click the Users folder (under Security) in a database, you can select the New User option.

    cdb3

    When this appears, you can then use the drop down to select a User with a Password option for a SQL Server user that is contained inside a database.

    cdb4

    The traditional user is a user with a login. Here’s the dialog from SSMS 2008, with no option for a user without a password.

    cdb8

    Back to 2012, I can enter a user name and password, and then I have a user in my database.

    cdb5

    The process for a Windows user (again, without a login) is similar. I can select a “Windows User” and then select the ellipsis by the User name and search for an AD user.

    cdb6

    This looks the same when I accept a user

    cdb7

    I can set a default schema here, and a language, but I don’t need the login.

    T-SQL

    The process with T-SQL is the same. The code for the CREATE USER command is simple:

    create user Billy with password = 'Billy2Goat$Gruff'
;

    If I want a Windows user, I can do this:

    CREATE USER [DKRSQL2012\Andy]
GO

    Note that this is domain\user syntax. Some AD tools allow the user@domain syntax, but this isn’t allowed in SQL Server 2012 for the CREATE USER command.

    You can replace the user name with a group at the database level, and the syntax is the same.

    Summary

    That’s it. It’s simple, and in another post, I’ll look at authentication.

  • Key Storage

    keys
    Hopefully your digital key storage is more organized and secure than this.

    One of the issues with encryption, perhaps the biggest issue, is the management of the keys that protect the encrypted data. I have been an advocate of keeping the backup of the keys far away from the backup of the encrypted data.I usually want them on separate media, or a separate tape, just so that a loss of my backup of the data (or the data itself), doesn’t include the key.

    However this presents a problem in a DR situation, especially over time. If I make a backup, and lose my server in a year, can I easily find the copies of the asymmetric keys or certificates? Can I easily match up the proper key with the encryption if I rotate keys periodically? There hasn’t been a great solution I’ve seen to solving this issue.

    Recently I saw a talk on security, and the speaker mentioned they kept copies of their certificates on the backup tape with the backup of the data. This person felt that since a password was needed for the certificate, that this was secure enough. Perhaps, but you still have the problem of securing that password over time as well. This week, I wanted to ask those of you that use encryption, how do you handle the issue.

    Would you store a secure asymmetric key protected with a password on your backup drive or tape?

    If so, then how do you handle the security of the password? If not, then what other solution do you have? I know key management is a struggle in many organizations, but if you have something that works for you, let us know how it works.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.