Voice of the DBA

Tag: encryption

  • Symmetric Keys

    One of the encryption options in SQL Server is symmetric key encryption. This is the type of encryption most people think about when they consider encrypting data. In symmetric key encryption, we use a key to encrypt data, and then also use a key to decrypt data.

    The key used for encryption and decryption is the same in Symmetric Key Encryption. This is why we call this symmetric. Just like with a house lock,

    image

    the key that locks (encrypts) also unlocks (decrypts). This is a picture of my front door and the lock uses a single key.

    In SQL Server, we create a symmetric key and use that to encrypt data and also decrypt it. Here’s a simple example:

    DECLARE @plain VARCHAR(200), @cipher VARBINARY(5000), @decrypt VARCHAR(200)

SELECT @plain = 'This is the plain text.'

-- encrypt
SELECT @cipher = ENCRYPTBYKEY(key_guid('MyFirstSymKey'),cast(@plain as NVARCHAR(200)));

SELECT 'Plain' = @plain
     , 'Cipher' = @cipher

-- decrypt
SELECT @decrypt = CAST( DECRYPTBYKEY(@cipher) AS nVARCHAR(200))

SELECT 'Plain' = @plain
     , 'Cipher' = @cipher
     , 'decrypt' = @decrypt

     

    That’s it. If you run it, you see the original text, the encrypted text, and the decrypted text.

     

    symkey1

     

    In another post, I’ll go into more options that are available for symmetric key encryption.

  • Encrypt Everything

    Security is a problem with so many of our applications and systems. There are numerous ways that we handle access and protection of data, often with access rights or encryption (or both) being applied to data in order to limit who can access the data. However neither of these systems is perfect for a variety of reasons and no matter how we configure our security, it seems there are always issues.

    Yahoo announced recently that they are trying to improve their security by encrypting all data that moves between their data centers. Other large internet companies do this, though not all. However, this doesn’t necessarily mean that your data is much more secure than it was before. This should make it more difficult to access data while it is transiting networks outside of Yahoo’s control, but there are still potential issues. Just as with TDE, any legitimate user inside a data center that has access to the LAN or systems inside the data center can still potentially read the data.

    I’m not picking on Yahoo here as the same issues might exist with Microsoft, Google, or any service provider that encrypts data between its facilities. This system also suffers from the potential compromise of the keys used to encrypt traffic if any employee were to sell, disclose, or lose a copy of them on laptop.

    However this is a good start, and it does mean that the NSA or any other organization that looks to read data in transit must work harder to access your data if it’s encrypted. I think it’s such a good idea that I think we ought to start encrypting all traffic by default. LAN, WAN, whatever. We’ve had tremendous advances in hardware and I’d argue that most of us have more powerful hardware than we need. If we decided t take the hit to encrypt all traffic now, we’d become used to the overhead and we’d have better security overall.

    I’d love to encrypt all data on disk, but I know people get nervous about losing data. A good start, however, would be to ensure all data in transit is protected.

    Steve Jones

    Video and Audio versions

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    Follow Steve Jones on Twitter to find links and database related items and announcements.
    Steve Jones Windows Media Video ( 19.2MB) feed

    MP4 iPod Video ( 22.4MB) feed

    MP3 Audio ( 4.6MB) feed

    Feeds are available at iTunes and Mevio

    To submit an article, rant or editorial,
    log in to the Contribution Center

  • Encrypting T-SQL Code

    This editorial was originally published on April 10, 2009. It is being re-run as Steve is on vacation.

    I recently engaged in a discussion with someone that was building an application on SQL Server. This person had a bunch of SQL code that was being put in stored procedures and then being sent to client sites. The developers were worried about clients modifying their code and wanted to send “secure updates” to the client by encrypting the stored procedures and giving the clients the encrypted text.

    Apart from the hassles of getting this to work, I asked by would they bother. There are decryption routines available and this isn’t meant to be a secure way to hide your code. Heck, even application code can be decompiled, and if they’re likely to mess with the code, they likely have the skills to get the source.

    So for this Friday’s poll, I’m wondering about how you feel about encrypting code in SQL Server. I want to know what you think.

    Is there a point?

    Is there a reason to encrypt stored procedure code? After all, there are many, many vendors that sell applications built on SQL Server, with stored procedures.  Most of that code isn’t encrypted and it’s usually not a problem. Most customers don’t mess with the code and there are usually prohibitions written into support agreements.

    Personally I don’t think there are many great ideas, and likely very, very few in the database space, that are worth securing. Someone doesn’t buy a software package so the can learn how you wrote it. Most of them buy software because it solves a problem and saves them time. If you can deliver a well performing, and good looking application, no one cares about the code.

    But I’m curious what the rest of you think, both end users and software developers. Is there really a good reason to worry about encrypting your code?

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are available atsqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.

    You can also follow Steve Jones on Twitter:

    Overall RSS Feed:  or now on iTunes! 

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

  • DevConnections Slides and Code

    I had a great time at DevConnections this week, It was a fairly quick trip for me, with lots of other events, but a good time. I presented two sessions and both went well. I had some good questions from the audience and people seemed to enjoy the talks. In order to make it easy to find the slides and code, I’m including them here:

    Encryption in SQL Server

    Searching Office Documents in SQL Server 2012

    If you have any questions, please feel free to contact me.