Developing Matt

My Technical Journal

SQL Server 2008 Security by don kiely

with one comment

I’m not very knowledgeable to sql server security, so I was glad to finally get to this class taught by Mr. Kiely.  This is one of the classes I took in the 24 hour free technology marathon hosted by sqlpass.  Click on the sqlpass24 tag to see the others.  I’m very grateful to sql pass for providing this training. 

Mr. Kiely is a developer who sees the database world from my perspective.  While not a fan of security I’m a fan of him.  When you listen to him you get the sense that he is just an average guy with a lot of great useful knowledge and a goofy sense of humor.

When we used to be neighbors in Alaska we used to hang out all the time.  We would talk about security after a day of sledding in the snow. 

Ok.  that’s a complete lie.

Here is the information that I caught from the course.  You should probably attend one yourself to catch what he really said, but this is what I picked up:

The big 2008 security changes that I saw

1.  Builtin\administrator is no longer automatically sysadmin
2.  In 2005 we received encryption and granular permissions.  2008 builds on it. 
3.  Encryption

ENCRYPTION

The biggest area of enhancement is encryption.  Personally, I’m not interested in encryption at all.  But as a developer I see it as my responsibility to be aware, as it can be very useful for sensitive data.  If anything else, this class made me less afraid of it.  It looks like it’s not near the overhead that it used to be and my fear of losing data due to some encryption botch-up is slowly inching itself away from reality.

SLQ 2005 introduced native data encryption at the cell level.  There wasn’t any way to encrypt the entire database.  In 2008 you can encrypt the entire database (it encrypts the file, the log, and the backup files, and thus is transparent to the application) 

With transparent data encryption you cannot attach and read the data from an encrypted database.  It queries a certificate to access physical files.    Unlike 2005 this encrypts all data as it is written to disk….  decrypted as read from disk…and encrypts and decrypts each 8k page.

Filestream data is not encrypted, by the way.

OTHER SECURITY CONSIDERATIONS

Policy based management: centralized control over configuration settings, object properties, etc across server machines and instances.  Can I just say WOW!  I had no idea that was there.  I might be mistaken, but it looks like I could allow cmd shell based upon a schedule.  CRAZY. 

Furthermore you can record any action against any object.  What?  Really?  If I wanted to find out who was eating my oreos in the middle of the night I could do it with this.  I speak in jest, but this is actually a very powerful feature.

Don, when are you going to write a security book?

Advertisements

Written by matt

September 21, 2009 at 1:35 am

Posted in Sql Server

Tagged with

One Response

Subscribe to comments with RSS.

  1. Wow. Maybe I should think about it….

    But, um, goofy?

    Don Kiely

    September 24, 2009 at 1:46 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: