Voice of the DBA

Tag: security

  • Mobile Password Protection

    mobile phone lock screen
    Please put a password on your smartphone.

    I recommend people that have smartphones install a password on their phones. Not that it provides a lot of protection, but it does limit the access that a casual phone-picker-upper gets if they take the phone as a target of opportunity. I’d like to think that the average smartphone thief would wipe, or throw away your phone rather than mess with the password, and move on to easier targets.

    However thieves that target your phone as a different story, and for them, getting past a four digit lock on your phone might be trivial. There are firms that help police get past codes, and I’m sure there are plenty of instructions and utilities around that a thief with decent computer skills could use. It’s also likely that there are more than a few utilities available to duplicate this functionality.

    As more and more of our customers use mobile devices, including tablets, to access systems, we need to be more conscious of security on these devices. Passwords are a start, but remote wipe or disabling are also important. However the thing you might want to invest in most as a data professional is monitoring and analysis of the access to your database. If you can’t completely secure your database, the next best thing is detecting security breaches.

    Steve Jones

    The Voice of the DBA Podcasts

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

  • Security Outside the Database

    SQL Injection on nametag
    SQL Injection shouldn’t be this easy

    It’s 2012.

    We’ve known about SQL Injection for years, we’ve known about the issues with high privilege admin accounts for decades, and poor configuration has been an issue ever since we first started networking two computing devices together. Yet these supposedly well known issues are still problems for databases in many companies.

    We have a lot of work to do in the database with regards to security. Auditing, tracking, configuring security, these are all challenges, and while SQL Server is getting better with the enhancements they have in SQL Server 2012, there is still work to do on the platform. I do think that Microsoft needs some help and guidance from us here as well, as we implement new features, find problems with using, scaling, or just understanding them, and I hope you will try out the new features and then submit feedback on Connect and write about your experiences.

    However the security implemented in the database is often circumvented by poor practices outside the database. There’s little  excuse for developers not to understand SQL Injection and code around it. There’s no excuse for frameworks and sample code to show code that allows SQL Injection. As far as privileged accounts go, that’s a bit of laziness on the part of admins. The modern OSs go a good job of allowing you to elevate from a non-privileged account. So even if you want to be lazy and give more access to developers or non-admins, give them an audited, second account that can be used to track activity. I’d prefer that we educate admins more and work closer with each other to ensure work gets done quickly, but maintaining a separation of duties, if for no other reason than to have a second pair of eyes examine changes for issues.

    As far as networking issues go, we have to demand more education from networking people. The days of connecting machines to a hub and worrying about IP addressing and the correct gateways are gone. Networking is much more complicated, and firewall administrators need to better understand their systems.

    Education is the key to making the systems work better, as it is in so many parts of life. Demand (if you can) or ask (if you can’t demand) that your fellow workers learn to work with their platforms better and improve the security throughout your entire system.

    Steve Jones

    The Voice of the DBA Podcasts

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

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