Solved

put a pw for a DB

Posted on 2013-06-26
7
158 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dimension table indexes 8 29
Replace Dates in query 14 42
SSRS  - Dropdown with Null 3 25
SQL Server - Getting the most recent engagement for each contact 9 27
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

825 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