put a pw for a DB

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)
LVL 5
25112Asked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
>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
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
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
 
Peter HutchisonConnect With a Mentor Senior Network Systems SpecialistCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
25112Author Commented:
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
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
>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
 
25112Author Commented:
>>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
 
25112Author Commented:
that's a good solution.. thanks sir.
0
All Courses

From novice to tech pro — start learning today.