2

I am very new with encryption in SQL Server so I am stumbling over things that require prior experience. I have done my googling, but I need reassurance so here we go:

Scenario

I have a SQL Server Project (*.sqlproj) where my team and I put all of our:

  • schema related changes
  • post deployment scripts
  • seed scripts

Best practices?

However I have run into a best practices situation:

  1. Should I store my MASTER KEY in my SQL Project?
    • I'm leaning towards no because of the password.
  2. Should I store my CERTIFICATE in my SQL Project?
    • Seems okay to me
  3. Should I store my SYMMETRIC KEY in my SQL Project?
    • Seems okay to me

Overall

  1. Should these sensitive items be a part of my SQL Server Project and checked into source control?
    • It is usually best practice to NOT store anything with a password in Source Control.
  2. Does the Master Key qualify for what I am talking about?

On a side note - I am not storing column level seed data passwords in source control. I have already decided this would be a bad idea.

Example for context

I am pulling these examples from Microsoft's examples here.

USE AdventureWorks2012;  
GO  

-- 1. Should this be stored in my SQL Server Project? The password concerns me.
CREATE MASTER KEY ENCRYPTION BY   
PASSWORD = '<some strong password>'; 
GO

-- 2. Should this be stored in my SQL Server Project? Seems harmless.
CREATE CERTIFICATE HumanResources037  
   WITH SUBJECT = 'Employee Social Security Numbers';  
GO  

-- 3. Should this be stored in my SQL Server Project? Seems harmless.  
CREATE SYMMETRIC KEY SSN_Key_01  
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE HumanResources037; 
  • 2
    Who are you trying to protect the data from? Developers? sysadmins? hackers? All of the above? – Aaron Bertrand Oct 4 '18 at 19:02
  • I am not sure how to answer your question. If you are talking about the master key I don't know. I guess anyone who shouldn't see it. If we are talking about the storage of plain text in DB rows then anyone who shouldn't have access. – dyslexicanaboko Oct 4 '18 at 19:04
  • @AaronBertrand I had a chat with one of my DBAs and we agreed it is okay to save this key in the project because you need the key and the data to do any real damage. I'm just weary is all, but after talking it through it seems logical enough to be safe. – dyslexicanaboko Oct 4 '18 at 20:04
  • I wouldn't use that password for the Master key in any environment with sensitive data. For a production environment you would avoid recreating the Master Key on code deployments. – David Browne - Microsoft Oct 4 '18 at 20:26
  • @DavidBrowne-Microsoft I agree. This is just a one time thing, but I also don't want to forget what the key was especially since it looks like a crazy strong password string. – dyslexicanaboko Oct 6 '18 at 15:56
2

You've got to put these things somewhere, where they are:

  • accessible only to the people who should have access to them;
  • protected from the people who shouldn't; and,
  • recoverable in the event of any disaster (be it people moving on or machines getting thrown in a tar pit).

But only you can know where that somewhere is in your environment. It could very well be source control (but probably not); Azure Key Vault is an option, depending on how cloud-averse your business is; some companies use a service like Enterprise LastPass or 1Password Business; or maybe a sticky note on Milton's red stapler is more your speed... as your peers and not business stakeholders, we just don't know.

  • Putting it in source control for the reasons you mentioned. Only developers have access and really without the database itself not much you can do anyhow and that's locked down. – dyslexicanaboko Oct 6 '18 at 15:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.