Voice of the DBA

Author: way0utwest

  • Restoring a Certificate

    I have written about creating a certificate and backing up a certificate, and the next step is the restoration of a certificate into a server instance. This post covers the basics of restoring a certificate.

    There is no RESTORE CERTIFICATE command because the CREATE CERTIFICATE command accomplishes the same thing. It is expected that many people will get a certificate from another company that is a trusted provider, and in that case, they would create their SQL Server internal certificates from the files provided by the vendor.

    However you get your certificate, it will come as two separate files. A .cer file, which is the public key for the certificate, and the .pvk file, for the private key of the certificate. These are the same files that are were created by the makecert.exe utility. They are also the files that were created by the backup in the last post.

    To restore the certificate, you perform the same procedure as the CREATE CERTIFICATE. For my exported certificate, I’ll execute:

    create certificate MySalaryCert
 from file = N'c:\EncryptionPrimer\MySalaryCert.cer'
 with private key
  ( file = N'c:\EncryptionPrimer\MySalaryCert.pvk'
  , decryption by password = N'AReallyStr0ngK#y4You'
   );

    This will load both the public and private sides of the encryption key into SQL Server and you can view them with this DDL:

    select
  name
, certificate_id
, pvt_key_encryption_type_desc
, subject
, expiry_date
, start_date
, thumbprint
, pvt_key_last_backup_date
 from sys.certificates

    This will show you the results. There are other fields here, but these are the ones I look at to check that this is the proper key. Note that on CREATE, the last backup date is NULL, even if you created this from a file.

    cert2

    cert3

    Note that there are other uses for certificates in Windows. You can install them for IPSec, for SQL Server SSL communications, and more. If you use certificates for any purpose, be sure you can back them up, restore them, and properly manage passwords.

  • Code Scanning

    Code Review
    I think we need better tools for scanning code.

    When I started writing code, the applications I wrote for various companies would only receive manual code reviews from my peers, and then limited testing from a group of people that were usually bored and unchallenged in their jobs. More often than not, I’d be asked by people what to test, how the various parts of the application worked, and receive a basic double check on the tests I’d run, not any extensive analysis. I think a lot of people had, or even still have, a similar experience, which is one reason we have such poor security in many applications.

    These days I know there are much better tools for testing applications, and I have heard of black box scanning of static source code. I haven’t heard of too much real time scanning of the executable code by the authors of code, but I’m sure there are tools out there to help you find vulnerabilities. I saw recently there are even better tools for scanning code that combine both techniques into something being called glass-box scanning.

    Security is a problem in many applications, and it’s great to see more tools being put into place to help uncover issues before our customers do. I don’t know to what extent we are vulnerable in these ways at the database level, but I suspect that we do need better tools to help us comb through access logs, as well as double check permissioning for users and objects. When we do have security problems, they are usually large security problems because of the large amount of data that can be exposed inappropriately, yet we don’t have very mature tools and processes for monitoring and detecting problems.

    Steve Jones

    The Voice of the DBA Podcasts

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

  • High Availability Upgrades

     

    Is SQL Server 2012 worth the upgrade for you?

    SQL Server 2012 will be released soon. I have no idea of the exact date, though I suspect a peek at the SQL Server developers’ vacation schedule in Redmond might provide some clue. The announcement last year was a first half of 2012, so I do expect to see the product RTM sometime between now and July 1.

    One of the highly anticipated features in SQL Server 2012 is the Always Onfeature, which will dramatically increase the ease with which we can build databases that can not only tolerate hardware failures, but be deployed (physically) further apart, and with more flexibility in how backups can be taken. If you haven’t read about Always On, I’d urge you to start looking at this feature and see if it’s something that can help your company.

    Unlike Database Mirroring in SQL Server 2008 R2, which is limited to one secondary database, Always On will allow us to have multiple secondaries. The current mirror databases are unreadable, unless you count the kludgy database snapshot feature. In SQL Server 2012, however, we can actually use the secondary databases to query, providing some limited scale out capabilities. I think that’s fantastic, and it’s a large step forward for the SQL Server platform. I’m wondering if your company feels the same way.

    Will the read only secondaries available in SQL Server 2012 convince your company to upgrade?

    There are lots of other features in SQL Server 2012, some great advances in other parts of the platform that might be worth upgrading for, but I regularly see people struggling with scale issues. With the changes in licensing as well, these new scalability features are worth examining, and I’m wondering how important they are for your business. Important enough to justify the price of an upgrade? Let us know today.

    Steve Jones

    The Voice of the DBA Podcasts

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

  • PepsiCo Women’s Inspiration Award Winner – If I can do it, so can you…

    An inspiring story. A hard one, and one that comes from someone with talent.

    I like seeing stories like this, and I think they do give some inspiration to others. Here is someone that was dealt a hard hand in life, and turned it around, with perseverance and hard work.

    However this is clearly an exceptional person. I wish we saw more stories from people that were average, or even just slightly above average making a change in their lives, and growing. It’s more realistic, and I think it would motivate more people to actually pursue their goals, rather than looking at someone like this and thinking “I’m not that talented” and I won’t have that level of success.

    You should pursue your dreams, not be afraid of the hard work and setbacks, and roll with them..