Solved

put a pw for a DB

Posted on 2013-06-26
7
161 Views
Last Modified: 2013-06-26
i am able to put a password for a backup file.

BACKUP DATABASE DBNAME TO DISK='SomeLocation' WITH MEDIAPASSWORD='password'

this assumes that a password is kept for each backup, for security reasons.. (as the backup file can get emailed etc).. what if the person forgot the WITH MEDIAPASSWORD? then it becomes vulnerable?

is there an option to put a pw on the db level, overall...

Can we create a password for the database itself, instead of just with the backup command? (so that if someone broke into the server, they will need to know the right password to backup or need a password to restore later on, if they got hold of a backup file that did not have a password)
0
Comment
Question by:25112
  • 3
  • 3
7 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 375 total points
ID: 39279115
if you are just concern about securing the physical db files , go for TDE, it is pretty simple and effective, all you have to take care is  the certs and the master key

http://sql-articles.com/blogs/implementing-configuring-transparent-data-encryption-tde/
0
 
LVL 19

Assisted Solution

by:Peter Hutchison
Peter Hutchison earned 125 total points
ID: 39279125
No, SQL uses sql logins or domain accounts to restrict access to databases, you cannot put just a password on it - maybe with simple databases like Access.

The password on the backup file is a security feature to prevent unauthorized access, if you do not know the password, you will not be able to access or restore the backup file. It is therefore secure. Having no password on the backup will make it vunerable.
0
 
LVL 5

Author Comment

by:25112
ID: 39279145
thanks-

with TDE, we still have a concern..

we have a stored proc that has the key to decrypt values for a encrypted column.. but the concern is if the backup file is hacked somehow, the
person who gets it can get into this proc to get the password.. and then they can decrypt and see all the sensitive info..

so with TDE, the problem happens because we store the password (key) inside the database and that seems not right and makes TDE less effective? (when using the key within the app so that the sensitive info is displayed only in the app for the user)

any ideas to do this differently to avoid this concern?

CREATE PROCEDURE [schema].[procname]  
WITH EXECUTE AS 'userLogin'  
AS  
BEGIN  
 OPEN MASTER KEY DECRYPTION BY PASSWORD = 'longone'  
 OPEN SYMMETRIC KEY Keyname  
 DECRYPTION BY CERTIFICATE CertName
END
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 375 total points
ID: 39279209
>but the concern is if the backup file is hacked somehow, the  person who gets it can get into this proc to get the password

If you have TDE enabled, the only way to restore the database from the backup file is to have the cert and the master key, as long as you store them at a separate secure location this wont rise.

>so with TDE, the problem happens because we store the password (key) inside the database and that seems not right and makes TDE less effective?
Thats not a good option, you shouldnt store the password there; assume the hacker has enough permissions to backup the certs and copy it to his location then they can grab all the sensitive info from the backup file.
0
 
LVL 5

Author Comment

by:25112
ID: 39279348
>>If you have TDE enabled, the only way to restore the database from the backup file is to have the cert and the master key,

Oh, you mean make the whole DB TDE, right? but we need to focus only on one sensitive column. If we do TDE for the whole DB, it can be lot of overhead, right?

>>Thats not a good option, you shouldnt store the password there;
that was the only way we knew for the app to decrypt the sensitive info and display on the screen.. you cant decrypt without the password.. so it has to be in the code, right? how else can the app display the sensitive info on the screen? if we don't call it from a stored proc, how else could we do it?
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 375 total points
ID: 39279398
>but we need to focus only on one sensitive column. If we do TDE for the whole DB, it can be lot of overhead, right?
there will some overhead with CPU .

You can encrypt the stored procedure
CREATE PROCEDURE [schema].[procname]  
WITH ENCRYPTION, EXECUTE AS 'userLogin'  
AS  
BEGIN  
 OPEN MASTER KEY DECRYPTION BY PASSWORD = 'longone'  
 OPEN SYMMETRIC KEY Keyname  
 DECRYPTION BY CERTIFICATE CertName
END
0
 
LVL 5

Author Comment

by:25112
ID: 39280088
that's a good solution.. thanks sir.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question