Solved

put a pw for a DB

Posted on 2013-06-26
7
164 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

751 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