Voice of the DBA

Tag: sql server

  • You need to run DBCC CheckDB regularly

    You never know when you’ll encounter corruption. It can happen at any time, usually due to some sort of hardware problem or driver issue. Corruptions don’t disappear and you can’t necessarily

    Corruptions can be caused by numerous factors, and it isn’t something you can predict. However sooner or later, you’ll get corruption on one of your instances. I don’t know which one, whether it’s the finance production instance or the vacation tracker development database, but it will happen somewhere.

    Since corruption can flow through to backups, and can exist in your system for some time, you could end up losing lots of data. Your best defense is to run DBCC on a regular basis and ensure that you catch corruption or problems as early as possible.

    My recommendations in order. If you can’t do #1, do #2, or #3, but try to get something in place.

    1. Run DBCC on every database, every day – If you can, run this on all your servers. I know it’s a large request for some of you, but if you can do it, do it.
    2. Run DBCC on every database on another instance, restoring from last night’s backup – If you can’t run DBCC on your main server, offload it onto another one.
    3. Run DBCC every day on some database, rotating so that all databases are covered – Do this so that at least every week or two you have a DBCC run on your production databases. You can do a random sample of some sort, but hit all of them as often as possible.
    4. Work on a your resume or CV – If management won’t let you run DBCC checks, sooner or later you’ll have an issue, take some blame, and need a new job. Be prepared for that, if for nothing else.

     

    sqlbackuproOne of the recommendations to minimize impact from CHECKDB operations is to run them on another server, using an automated process. SQL Backup Pro 7 from Red Gate makes this very easy to do with automated backup file copy operations, restores, and CHECKDB execution.

  • 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 Backup 7

    sqlbackuproWhat more could you add to a SQL Server backup product? It seems that many software products, including Red Gate’s SQL Backup Pro, have been able to handle the things most DBAs care about for some time: compression and encryption. Today my company has released a new version of SQL Backup, version 7, which does add a couple of very nice features.

    One of the problems that exists in many database configuration is the lack of verification of the backups, and a lack of consistency checking for corruption. Both of these are fairly rare events, but when they do strike, they can propagate through backups for weeks, months, or even years at times. When a disaster does strike, this can result in a tremendous amount of lost data for an organization.

    SQL Backup Pro 7 helps to ensure your backups can be verified and checked for issues with the addition of two great new features: scheduled restores and automated verification checks.

    You can see a walkthrough of the features from my colleague, Grant Fritchey, on the Red Gate website. Grant shows how you can automate the restores, to another server, and run your DBCC checks.

    Run these Checks

    When corruption strikes, you may not be able to actually recover data. It could be too late, so it’s important that you regularly run DBCC checks. However it can be a performance issue on your production server since DBCC is resource intensive. The solution is to offload these checks to another instance, but for many DBAs, the scripting required to ensure this runs every day is rather complicated. You can do it any number of ways (T-SQL, PowerShell, VBScript, etc), but it’s important that you do it.

    The addition of this feature to Backup Pro is something myself, Brad McGehee, Grant Fritchey, and others have been requesting for some time. The ease with which you can set this up means there is no reason not to run your DBCC on almost every backup file.

  • 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.