Solved

put a pw for a DB

Posted on 2013-06-26
7
168 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 20

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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

635 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