Storing sensitive information in a SQL Server project
2
votes
1
answer
634
views
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](https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-2017) .
USE AdventureWorks2012;
GO
-- 1. Should this be stored in my SQL Server Project? The password concerns me.
CREATE MASTER KEY ENCRYPTION BY
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;
Asked by dyslexicanaboko
(121 rep)
Oct 4, 2018, 06:58 PM
Last activity: Oct 4, 2018, 09:17 PM
Last activity: Oct 4, 2018, 09:17 PM