Voice of the DBA

Tag: security

  • New Connect Item – TDE

    Does the certificate matter for TDE? Apparently not as I found a number of people discussing the fact that the expiration date for certificates is not checked for TDE restores. So if your certificate expires, you can still restore the TDE backup.

    However the documentation doesn’t mention this, and I think it ought to be clarified, so I submitted this Connect item. Feel free to vote if you agree:

    https://connect.microsoft.com/SQLServer/feedback/details/677365/doc-clarify-the-expiration-date-field-of-certificates-impact-on-tde

    Whether the expiration date should matter is another debate. I think it should, but I need to really think about the pros and cons of this.

  • HASHBYTES – A T-SQL Function

    Someone was asking if the HASHBYTES function was a good one to use in T-SQL as far as performance goes.. I wish I had a good reference for the function, but the best one I had on SQLServerCentral was this piece on using it to load a data warehouse. I also wrote an editorial on it not working with strings beyond 8k, which seems to be a bug, or a lack of resources devoted to ensuring string functions work with varchar(max).

    The HASHBYTES function returns a hash of an input string. A hash is essential a calculation based on the values of the input, and two inputs that are the same, ought to produce the same hash. One catch with this function is that you provide the algorithm used, which can be one of these:

    • MD2
    • MD4
    • MD5
    • SHA
    • SHA1

    Each of these produces different output, returning a varbinary(max) value. As an example, suppose I hash “Steve Jones”

    SELECT HASHBYTES('MD2', 'Steve Jones') 'MD2'
UNION
SELECT HASHBYTES('MD4', 'Steve Jones') 'MD4'
UNION
SELECT HASHBYTES('MD5', 'Steve Jones') 'MD5'
UNION
SELECT HASHBYTES('SHA', 'Steve Jones') 'SHA'
UNION
SELECT HASHBYTES('SHA', 'Steve Jones') 'SHA1'

    The results look like this:

    MD2

    ———————————————-

    0x27851A666BFCB4A35F971DD742CDA15F

    0x2E978DE4841B1F3651A8DF4B2D2CF5F5C624A76B

    0x75931813C7EAAEAB3CD1D8D621935903

    0x979AC597C05CA6DE3A88C31A456D1125

    As you can see, there’s a different hash for the same value using different algorithms. However if I were to compare the same string to itself, I can easily tell if something has changed. If the hashes aren’t the same, there’s a difference. I’m not sure this is a great use, but the more obvious use is that I can hash a password and then have the user enter their own version, hash it, and compare the results. In this way, the system never needs to know the value.

    Just make sure you use the same algorithm Winking smile

  • Regular Audit Analysis

    Do you regularly review audit data?

    I was reading over a digital supplement that I received from Dark Reading recently, which details some of the issues in the Epsilon, Gawker Media, and a few other data breaches. It was light on details, but there were some nuggets of knowledge in there about how these attack occurred. Some were sophisticated, and some were insider attacks, but the advice given to help protect your data was all similar: limit access, watch for injection, audit, and monitor.

    I know that over the last decade as I’ve run SQLServerCentral, the topic of security and auditing has grown in importance. More and more people are implementing auditing functions in their applications and slowly tightening security where they can. There is a lot of work to do, and a lot more education that needs to be spread to a wider audience, but the trend is positive.

    However one thing in the article caught me eye, and it had me wondering how many people are going beyond the basics. For those of you that have auditing built into your application or database, I have a question this week:

    Do you regularly analyze the audit data to look for abnormal trends or access?

    All the data in the world doesn’t have any value if it’s not used. In a security context audit data isn’t all that useful if it’s only examined when an incident is discovered. The real value in auditing data is the ability to uncover problems before they occur. Looking for inappropriate access, unusual access for a particular individual or application, or even repeated attempts to gain access can help prevent a data breach.

    After all, catching the criminal later doesn’t necessarily mean you’ve “recovered” the data. Unlike physical objects, data can easily be copied and spread in way that prevents it’s complete recovery.

    Steve Jones

    The Voice of the DBA Podcasts

  • More Regulation Coming?

    Is more regulation coming for data handling and storage?

    Recently Citibank had hackers access a large number of credit card numbers and account details for their customers. It was all over the news, but there was a great piece in the New York Times (registration required) about how this seems to be a nagging problem. We’ve had data breaches before, and banks have had security issues, but they don’t seem to be learning. A number of government officials in the article are quoted as seeking new regulations for data security.

    As data professionals, we are often charged with ensuring data is safe, yet accessible to authorized individuals. That responsibility can be hard to manage, especially when our management often doesn’t want to increase our budgets, or accept more restrictive ways of authenticating users or even slower access. I certainly don’t envy the IT folks working at banks and healthcare companies, and am not sure I’d even want to take a job in those industries in the future.

    I don’t think that more regulation is necessarily the answer, at least not direct regulation. Technology changes so quickly, systems are implemented in diverse ways, and direct regulation often leads to rules that exist to help some vendor profit, not provide better security.

    Whether we get new regulation or not, I think that most of us need to learn more about what security methods work well in our systems, the implications of picking a specific type of encryption, and in which ways we can be audit our systems to detect issues. In the days of limited budgets and apathy from management, a little education is the best way to improve the security of your systems.

    Steve Jones

    The Voice of the DBA Podcasts