Voice of the DBA

Author: way0utwest

  • Decryption and CASTing

    In my last encryption post I showed how to encrypt and decrypt data with a symmetric key. However there was a piece of the explanation I left out. If you look at that post, suppose that you ran this query after you’d encrypted the data:

    -- decrypt the data
select 
  id
,firstname
,lastname
,title
,Salary = DecryptByKey(EnryptedSalary)
,EnryptedSalary
 from Employees
go

    The results wouldn’t be what you’d expect:

    decrypt

    The binary data is returned, which isn’t rendered correctly. The salary column is the decryption, and the EncryptedSalary is the encrypted data. Note they are different.

    This stumped me for awhile when I was playing with encryption and I checked the dercryptbykey page thoroughly before I realized that the return type was varbinary and needed to be CAST.

    If I cast this back to nvarchar, I get the data:

    select 
  id
, title
, Salary = cast(DecryptByKey(EnryptedSalary) as nvarchar) 
, EnryptedSalary
 from Employees

    decrypt2

    In my example, I CAST to nvarchar, and then to numeric, mostly for clean coding. This is numeric data. Can I cast directly?

    select 
  id
, title
, Salary = cast(DecryptByKey(EnryptedSalary) as numeric(10,4))
, EnryptedSalary
 from Employees
go

    No. I get an error.

    Msg 8115, Level 16, State 6, Line 1

    Arithmetic overflow error converting varbinary to data type numeric.

    This isn’t a valid CAST, so I need to double up the CASTs as shown in the original post.

  • Fix Failures Fast

    broken hard drive
    You don’t need this kind of failure to consider replacing your hard disk.

    What makes PCs fail? Recently Microsoft Research published a research paper that looks at PC failures. The research spanned a million consumer PCs and examined failure rates and the suspected causes of those failures. The data came from the Windows Error Reporting system that runs after crashed inside of Windows, so this data doesn’t include other operating systems. It also is for fatal errors, those resulting from system crashes and doesn’t include non-fatal errors.

    It’s an interesting read, though a little boring since it’s an academic style paper. It does have a few highlights thatcaught my eye. The first is that when a failure occurs, the chance of another failure occurring soon is high. That means that disk failures predict another failure is likely in the next 30 days. Server machines might be different, but those of you with consumer grade hardware might think about an immediate backup and the purchase of a hard disk replacement if you have a fatal disk error.

    There are other highlights that over-clocking can dramatically increase CPU failure rates. It isn’t completely clear if this means a shorter lifetime, but if you’re like me, having errors that crash systems is incredibly annoying and time consuming, so I’m not sure I’d ever want a system over-clocked. There was another interesting point that said CPUs and RAM in white box (non-OEM) machines was less reliable than brand name systems. I struggle to see how a CPU would be less reliable, but that was part of the analysis. I’m not sure that would stop me from purchasing my own parts in the future, but it is something to keep in mind. Perhaps you should burn in the machine as soon as you can to allow for a parts return if you have issues.

    Laptops were more reliable, which was surprising, and also comforting since most IT pros I know use laptops. People tend to keep old memory around, which can wear out over time, so be careful about moving memory from machine to machine. There weren’t a tremendous number of surprises in the study, but it did make me appreciate my long running desktop that rarely reboots, and even more rarely crashes. I was pleased to see such a detailed analysis being undertaken by Microsoft Research using data from actual Windows systems. Hopefully there are engineers also looking at the data and improving the OS as well.

    Steve Jones

    The research paper for this editorial was highlighted in the Brent Ozar, PLF newsletter.

    The Voice of the DBA Podcasts

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

  • Colorado Wildfires

    More than a few people have asked how I’m doing with all the fires in Colorado, so I wanted to drop a quick note. I’m fine and the family is safe. We are near Parker, CO, so not very close to the fires themselves.

    We were affected slightly by the Springer Fire near Eleven Mile Canyon just west of Colorado Springs. I was supposed to attend Camp Alexander with my son as the Boy Scout summer camp a couple weeks ago. However as we arrived at the facility, it was being evacuated due to the proximity of that fire. We did go camping near Grays Peak instead, and I had a quiet, fairly unwired rest of the week at home.

    The Colorado Springs fire (Waldo Canyon) is near the Air Force Academy, about 50mi SW of me, and not very close. The High Point fire near Fort Collins is a good 80-90 miles away from me, so that’s not a problem. The only close fire was the small Elbert fire about 30-40 miles South and slightly East of the ranch, but that was quickly contained.

    All is well here, just hot and dry. Hoping for some moisture soon.

  • Security and Honesty

    Ladder of Disclosure
    I really think we need better disclosure on security issues.

    Information is power. That’s been a saying I’ve lived by as a data professional for years. That has guided me to capture additional data in applications, often data business users did not think was important initially. The power of information led me to monitor my servers, and proactively look for ways to improve performance. Using resources like SQLServerCentral allowed me to learn about what others were doing, what worked, and what didn’t. The dissemination of information has helped me to have a successful career as a DBA.

    When I see articles like this one, where companies are not disclosing the security issues they face, I worry that our industry is not advancing as quickly as it can. It’s important for us to share technical challenges and solutions among as many people as possible in technology. Our systems are complex, the sheer number of technologies is overwhelming for any one person or even company. The vulnerabilities, bugs, and attacks outweigh the technologies by far, yet our employers so often do not want to disclose any issues for fear of bad publicity.

    It’s time that this was required. Every company gets attacked, and probably most get hacked in some way. Rather than prevent that they are invulnerable, make the information public, or at least public to other IT workers. It doesn’t have to be a press release from your company, but companies should be required to disclose the problems they’ve had, the vulnerabilities they faced, and the mitigation measures. I don’t want to invite attacks, but I also think that we are building more and more poorly developer applications on top of poorly architected foundations.

    Within a reasonable time, companies ought to be forced to disclose the issues. They don’t have to fix them, but the disclosure might just encourage them to spend a little more time ensuring that their infrastructure is protected.

    Steve Jones

    The Voice of the DBA Podcasts

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