Voice of the DBA

Tag: security

  • Finding a Balance

    lego USB
    Are devices like this a problem?

    This editorial was originally published on Nov 19, 2007. It is being republished as Steve is on vacation.

    When I started in IT things were much simpler. We had smaller data sets, but hardware was larger. There just wasn’t a good way to transfer large amounts of data on 3 1/2″ floppy drives. I remember CDs coming into being and CD writers became a concern, but since few people had them and we knew who they were, it wasn’t a big problem. The zip drives created a cause for concern briefly with their 100MB capacity, but again, they were somewhat bulky and easy to spot.

    However with the growing sizes of flash drives, storage in cell phones, and the monstrous capacity of iPods and other music players, it seems that data has little chance of being contained by IT within the walls of the organization.

    I saw an interesting discussion about managing all these technology devices and the problems that come with so much storage being in reach for so many people.

    So we’re data people, we get beat up to ensure our SOX procedures work well, and we’re in the trenches. With that in mind, the poll this week is…

    Does it make sense to ban personal storage devices?

    I know it’s not practical to actually try and prevent the iPods and other devices from coming into the building. You for sure cannot take away all the cell phones from people. But does it make sense to prevent these devices from connecting to your network? Ban USB and Bluetooth; don’t purchase rewriteable drives except for admins who can be monitored. Some other ideas?

    That might not even work. Recently a report surfaced about some executives in Korea that stole nearly $2billion worth of trade secrets with USB drives and taking them to a new company.

    Think about the past experiences we”ve had. Suppose you”d locked down floppy drives and CD burners a decade ago. All of a sudden MP3 players appear and get mounted as removable drives. It”s a new twist you hadn”t considered, so you shut down all the serial, parallel, and USB ports. You get a new laptop and realize Bluetooth is now available and works just as well and your controls have been circumvented again. What do you do now? It’s a tough balance to strike. Are you trying to be a control freak and make it an hostile work environment or do you trust your employees and deal with the occasional problems that come with them?

    I’m not sure what the best solution is, but I’d venture to guess that banning the technologies won”t work. Someone will always come up with a new way to get around your controls, and more importantly, you won”t be as vigilant if the controls “appear” to work.

    I’d adopt the Counterpane approach to security, which is what security expert Bruce Schneier believes in. Assume you”ll get compromised and attacked and put systems in place to detect and respond to issues rather than trying to prevent all attacks.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are now available atsqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). We’ve upped the quality a little on the Quicktime files, so if it’s better from your side, let us know. Comments are definitely appreciated and wanted, and you can get feeds from there.

    The RSS Feed:  or now on iTunes! 

  • The Personal Checksum

    checksum
    Do you create a checksum for yourself?

    This editorial was originally published on Nov 2, 2007. It is being republished as Steve is on vacation.

    Someone sent me this article on foiling thieves in restaurants with a personal checksum. It’s an interesting idea and while it could help, I’m sure there are some smart people that will find a way around it.

    I don’t reconcile my restaurant bills that closely, but I have an idea of what I’ve spent and I think that changing of bills rarely happens. I spent about 8 years in the restaurant business cooking, waiting tables, bartending, and managing at various times. I worked in all types of restaurants from diners to nightclubs and I’ve rarely seen anyone change a tab. It does happen, usually when someone is upset about their gratuity. But the person usually will brag about it to other employees and often you can tell when a ticket is changed.

    I don’t condone that behavior. If someone doesn’t want to tip you, then they don’t have to. On the other hand, if you’re going out to a restaurant in the US, remember that those people work essentially for tips. They aren’t paid minimum wage; they’re paid less as an allowance against tips.

    I’m not looking to start a restaurant debate, but with this being a Friday, I needed a poll and here it is:

    What other useful personal checksums do you use?

    Or do you need? Or do you wish someone would develop? Do you want some type of checksum on your credit score, something to tell you if it’s been accessed or even correctly calculated? Does it make sense to have some type of calculation to determine if your mortgage is being correctly credited?

    I’m not really sure what checksum I’d be looking for. To date I’ve had fairly good luck with security in my personal data and have had no real cause for concern.

    The one interesting thing I would like is a way to get a quick checksum for the last month off my automatic toll transponder and compare that to some calculation against my bill. I can never remember how often I’ve used it during the previous month.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are now available atsqlservercentral.podshow.comto get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

  • Enable Transparent Data Encryption

    This is one of the things in my Encryption Primer presentation that I don’t demo. It’s really easy to do, and it’s rather mechanical, so I just show the image that has the steps from MSDN and leave it at that.

    However there are a few things I wanted to change, and test, so I thought I’d show my procedure on a local database. I roughly follow the MSDN article, but a few slight items.

    First, use master and create your keys and certificates.

    CREATE DATABASE TDETest
;
GO
USE master
;
GO
CREATE MASTER KEY
 ENCRYPTION BY PASSWORD = 'AReallyStr0ngP@ssword'
;
go
CREATE CERTIFICATE SteveCert
 WITH SUBJECT = 'My DEK Certificate'
;
go
USE TDETest
;
GO
CREATE DATABASE ENCRYPTION KEY
 WITH ALGORITHM = AES_128
 ENCRYPTION BY SERVER CERTIFICATE SteveCert
;
GO

    I created a test database here for another process, and this is roughly the setup. However before I enable the encryption, here’s what I recommend you do:

    USE master
;
go
BACKUP CERTIFICATE SteveCert
TO FILE = 'c:\SQLBackup\SteveCert'
WITH PRIVATE KEY 
(
    FILE = 'c:\SQLBackup\SteveCertPrivateKeyFile',
    ENCRYPTION BY PASSWORD = 'R@ndomP3ssW0rd'
);
go

    Encryption is serious stuff. If you lose this certificate from a server crash, you are definitely not going to be able to open your database or recover your data. Gone is gone, and data loss means data loss here.

    Back up your certificate.

    Quick question: do you know where your backup of the certificate is?

    Once this is done, you can continue on:

    USE TDETest
;
go
ALTER DATABASE TDETest
SET ENCRYPTION ON;
GO

    The encryption is quick on this new, small database.

    Now let’s see if this worked. We’ll add data and make a backup.

    CREATE TABLE MyTable( LogData VARCHAR(MAX))
;
INSERT MyTable SELECT 'This is an encrypted database'
;
GO
BACKUP DATABASE TDETest
 TO DISK='tdetest.bak'
;

    If I go to my backup location and look for this backup, I can open it in an editor.

    encrypt2

    It’s random gibberish. If I run a search for data in my table:

    encrypt1

    I get no results

    encrypt3

    Don’t think this is valid? Run this below and re-search this backup for the string. You’ll find it. This is one thing encryption protects you from.

    CREATE DATABASE NoTDE
;
GO
USE NoTDE
;
GO
CREATE TABLE MyTable( LogData VARCHAR(MAX))
;
INSERT MyTable SELECT 'This is an encrypted database'
;
GO
BACKUP DATABASE NoTDE
 TO DISK='notde.bak'
;

    The database is encrypted, but anything I do with the database doesn’t require code changes, hence the “transparent” nomenclature.

    The value of this is debatable, but I think it’s not a bad feature to implement if you have Enterprise Edition and you need this protection for PCI, HIPAA, or some other regulation.

  • Make a Backup First

    Mat Honan
    Are you protecting your digital life?

    I was reading in detail about the Apple / Amazon hack that targeted a Gizmodo writer.  A hacker used a few techniques to get from his Amazon account to his Apple account, his GMail, Twitter, and more. His iPhone, iPad, and Macbook were remotely wiped, and he ended up losing quite a bit of data that wasn’t backed up. That’s a horrible situation, and I know every time I’ve lost a picture or document at home, I regret not making another backup.

    The situation caused some discussion and comments on Twitter about the various things you should do to prevent this type of issue. Updating your security, choosing better passwords, and other ideas are great, but the number one thing you need to do is make a backup of your data. No matter what happens to your security or even hardware, if you have a second copy of your data somewhere else, you can recover from the situation.

    While listening to a Brent Ozar, PLF webinar recently, I heard this interview question: what is the first thing you do on a new server you’ve never worked with? The answer is ensure it’s being backed up. Not run a backup, since you can cause problems, but make sure there is a backup plan in place, or get one started as soon as possible. Why? Because if you have a backup, you can recover.

    I think this is the number one priority for any data professional, and perhaps for anyone using a computer. I know I have backups of my home machines run to a Windows Home Server. I take a backup of my Macbook regularly, and leave a copy at home when I’m traveling. I also carry backups of my VMs on separate disks, in a separate suitcase when I travel. I know that if I have one of those copies of my data, I can recover from almost anything.

    What’s the saddest part of this story? There wasn’t any intent (allegedly) to target the writer’s work or his data. The hacked just wanted to use his cool Twitter account, @mat.

    Steve Jones

    The Voice of the DBA Podcasts

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