Sample Header Ad - 728x90

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