Voice of the DBA

Tag: security

  • Password Help

    Keepass Logo
    Use Keepass, Password Safe, or some other password manager.

    In case you haven’t heard, LinkedIn passwords were hacked and posted online. If you haven’t changed your LinkedIn password since the attack, please stop reading and go do it now.

    In reading about the attack, I sent a few friends a note as well to remind them that not only should they change their passwords, but that they should not be using the same password in multiple places. With more and more services intertwined, and more logins needed, it’s no stretch to think that someone that manages to crack your password, or steal it, from one service might use it on another service. Especially banking services.

    As data professionals, it’s easy for us to preach to non-technical people the value of strong passwords, but it can be hard to get them to use them. Strong passwords are problematic for many people to remember and type, and we find that most people prefer the path of least resistance: one short password that’s easy to remember.

    This article talks about a few things that IT people can do to help educate others and make it easier for them to consider strong passwords. Inside of a company, we can try to implement more services and products that secure our systems, but if people continue to use the same password for their personal mail or favorite website, much of that might not matter.

    Ultimately I think the best thing we can do is set a good example, and choose strong passwords stored in a password manager. Once people see that it’s relatively easy to secure passwords with something like KeePass or Password Safe, they may give it a try. That may help keep our data just a little more secure.

    Steve Jones

    The Voice of the DBA Podcasts

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

  • Using a Symmetric Key

    In my Encryption Primer talk, I do demo on symmetric key use, and wanted to document it here. Encryption is a serious subject, and please do your research and education, as well as testing, before you implement it.

    This post will look at some simple encryption and decryption using symmetric keys. I showed how to create a symmetric key before, so I won’t talk about that here, but I’ll just show the code.

    Let’s set up a test table. In this case, I’m showing salary in a table, which isn’t something you want to do, but you might have this in an existing application: data you need to encrypt, but it’s stored unencrypted.

    -- create a table 
create table Employees ( 
 id int identity(1,1)
 , firstname varchar(200)
 ,lastname varchar(200)
 ,title varchar(200)
 ,salary numeric(10, 4) );
go
insert Employees 
values
  ('Steve','Jones','CEO', 5000)
 , ('Delaney','Jones','Manure Shoveler', 10)
 , ('Kendall','Jones','Window Washer', 5)
;
go

    Here I have three employees and salaries. Now I want to encrypt the salary. However I cannot just encrypt this value. Encryption creates a binary representation of the data, and that won’t fit in a varchar field. So I need to add a column.

    alter table Employees 
 add EnryptedSalary varbinary(max);
go

    Now I have a placeholder, so let’s create a key and then update the new binary column with the encrypted value.

    -- create a symmetric key
create symmetric key MySalaryProtector
 WITH ALGORITHM=AES_256
 , IDENTITY_VALUE = 'Salary Protection Key'
 , Key_SOURCE = N'Keep this phrase a secr#t' 
 ENCRYPTION BY PASSWORD = 'Us#aStrongP2ssword'
;
go 
-- open the key 
open symmetric key MySalaryProtector
 decryption by password='Us#aStrongP2ssword'
;

-- encrypt the data 
update Employees 
 set EnryptedSalary = ENCRYPTBYKEY(key_guid('MySalaryProtector'),cast(salary as nvarchar))
; 
go 
-- remove the old data 
update employees
 set salary = 0
; 
go

    Note that I open the key, which is needed. I can close it at the end, or it will close when my session ends. I don’t close it here as I usually run this demo in the course of one session.

    The encryption takes place with the ENCRYPTBYKEY function, which requires the GUID of the key. Why the GUID and not the name I don’t know, but it seems like a PIA, halfway implementation. In any case, the KEY_GUID function is used as the first parameter.

    The number needs to be cast as a character, so I do that first, and then it’s the next parameter in the function. If I look at the data, it looks like this:

    select id , firstname , lastname , title , salary , EnryptedSalary
 from Employees; go

    encryptsymm

    If you use the same identity_value and key_source, you should get the same encryption.

    To decrypt it, I do this:

    -- decrypt the data, with the casting  select id  , firstname  , lastname  , title  , Salary = cast(cast(DecryptByKey(EnryptedSalary) as nvarchar) as numeric(10,4))  , EnryptedSalary  from Employees go

    The encrypted value has a header that tells it what key is used, so as long as it’s open, this works.

  • We Need to Learn Encryption

    encryption
    We need better encryption and better tools for the future.

    More than learning encryption, we need to demand better encryption tools inside SQL Server. After reading about the issues from the STUXNET worm, it makes me worried that we will see more disruptive attacks, not just from hackers and criminals, but from the virtual vandals and bored teenagers that have more time than morals or sense. The issues from the STUXNET worm spread far beyond any territorial or national issues, and run into all sorts of industrial control systems that may receive more attention from hackers in the future. With some of the brightest hackers in the US and Israeli governments providing a template for compromising those system, I’m sure there will be no shortage of future attacks.

    That doesn’t necessarily mean there are going to be more and more database attacks in the near future, but I’m sure there is research going on into new ways to attack database or applications, either from governments, criminals, or even graduate students. At some point there will be new attacks that come out, and these vulnerabilities may result in zero-day, or even forever-day vulnerabilities.

    We can’t change the way SQL Server, or other vendor technologies, work at a base level, but we can reduce the amount of damage that’s done by not being the easy prey for attackers. In my mind, that means we should be securing our data as best we can, including encrypting communications and limiting access rights, as well as encrypting the actual data we store.

    I’m hoping that Microsoft makes PKI much easier when they release the updated Certificate Services in Windows 2012, and that we also find better private solutions for individuals that allows us to better secure our systems and make it difficult for the casual attacker to compromise our systems. I don’t know if we’ll see viable products and solutions soon, but as we distribute our systems, data, and backups to wider and wider systems, including tablets and mobile devices, we need better security more than ever.

    Steve Jones

    The Voice of the DBA Podcasts

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

  • New Security Holes

    Geek Security Holes

    One of the big advertising campaigns of 2011 was the Apple campaign that centered around Siri. This is a voice recognition feature of the iPhone 4S that allows you to manage many of your phone interactions by speaking instead of touch interactions. There are a whole series of commercials about Siri and some rather funny parodies (NSFW). I used Siri briefly on my iPhone 4 before the 4S launch and I didn’t like the interaction, but my wife loves it.

    Apparently, however, IBM doesn’t like the idea of Siri being used inside of the company. IBM’s CIO has banned the use of the voice recognition because the audio and translation are not done on the phone, but rather sent to a series of servers controlled by another company. Apple is ultimately responsible, but there are other companies who a subcontracted for pieces of the whole process. There is a potential security hole, especially with a company like IBM that is involved in research and new technical products that might compete with Apple.

    I have to admit this is a data security hole I never expected to encounter. It’s a valid concern, as your location, the content of your message, which might be an appointment or reminder to yourself, could be mined and used by Apple. As more companies use voice recognition, or even other types of interactions that might require data processing at a remote location.

    Ultimately I think that we can’t let the market work out the way that our privacy should be handled. I don’t think companies will do a good job of protecting the individuals’ or business partners’ interests. Until there is a good framework in place, anyone that uses third party libraries for processing things like voice recognition ought to be concerned over the data security that is offered as part of the service.

    Steve Jones

    The Voice of the DBA Podcasts

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