June 19, 2013 at 10:07 am
Hi friends,
I used the following query to restore the encrypted database.
USE master;
GO
CREATE DATABASE Encry2
ON
( NAME = Encry2_dat,
FILENAME = 'D:\SQL2008R2DB\MSSQL10_50.SQL2008R2\MSSQL\Backup\TDEPost.mdf',
SIZE = 3,
MAXSIZE = 5,
FILEGROWTH = 1 )
LOG ON
( NAME = Encry2_log,
FILENAME = 'D:\SQL2008R2DB\MSSQL10_50.SQL2008R2\MSSQL\Backup\TDEPost2Log.ldf',
SIZE = 1MB,
MAXSIZE = 5MB,
FILEGROWTH = 1MB ) ;
GO
open MASTER KEY DECRYPTION BY PASSWORD = 'password';
RESTORE DATABASE Encry2
FROM DISK = N'D:\SQL2008R2DB\MSSQL10_50.SQL2008R2\MSSQL\Backup\TDEPost.bak'
WITH FILE = 1,
MOVE N'mydb' TO N'D:\SQL2008R2DB\MSSQL10_50.SQL2008R2\MSSQL\Backup\TDEPost.mdf',
MOVE N'mydb_log' TO N'D:\SQL2008R2DB\MSSQL10_50.SQL2008R2\MSSQL\Backup\TDEPost.ldf',
NOUNLOAD, REPLACE, STATS = 10
CLOSE MASTER KEY
It gives me following error message
Msg 3234, Level 16, State 2, Line 2
Logical file 'mydb' is not part of database 'Encry2'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
My concern is I want to show that , I encrypted the database.
I already show that both the backup files , one gereted before the backup and one after are totally different in terms of size and also the content.
Now, I need to show what kind of problem one can get if he/she restore the backup file which I have created after encryption.!!
do they need password of master key.?
how one can restore the encrypted database backup file other than me ?
please help.
thanks.
June 19, 2013 at 11:26 am
Learner44 (6/19/2013)
Msg 3234, Level 16, State 2, Line 2Logical file 'mydb' is not part of database 'Encry2'. Use RESTORE FILELISTONLY to list the logical file names.
And when you did what the error message said, what was the output?
p.s. Why are you creating a database then restoring over that newly created database?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2013 at 12:21 pm
I am not sure..I found this step similar and my goal oriented..so I followed them.
My objective is to show, that I have already encrypted database.
Now my senior needs to check, wether database is encrypted or not.
I need to know what are the step that he needs to follow inorder to restore the encrypted database.
I encypted one database called 'TDE'
First I have created backup called, TDEPRE.bak
then I created Master key and certificate.
Followed by this , I created Database master key , AES-128 and encrypted it with certificate.
Then i run the backup query in order to achive the TDEPOST.bak.
Now I checked the size of the two backup files are different and also their content too.
Now I want to show that no other user can restore the database without having proper key.
I need to know the steps for that..!!!
please help.
thanks.
June 19, 2013 at 12:32 pm
If you're not going to answer my questions, I'm not going to help you.
As for the steps to restore a TDE-encrypted database, they're in Books Online.
To prove it won't restore without the certificate, try to restore the database on a server that doesn't have the encryption certificate. The restore will fail.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2013 at 1:42 pm
I am sorry if I didn't understand your quetion and not answered..
actually I was running the query to restore the database from TDEPost.bak file via sql sommand which I just metioned above..
and I got that message.
I have got accesses to server..
I saved my prebackup and postbackup files there.
Now I was informing my senior that if someone try to restore the database..without having proper key It want allow..
and believe me ..when he right click and choose the option restore the database and went to file of postback..he successfully restored it in..SQLSERVER.
although it was under my login..
But Now I am confused..how I can show him, that database is encrypted and if your restore it won't without proper key.
I also used following query!!
SELECT
db.name,
db.is_encrypted,
dm.encryption_state,
dm.percent_complete,
dm.key_algorithm,
dm.key_length
FROM
sys.databases db
LEFT OUTER JOIN sys.dm_database_encryption_keys dm
ON db.database_id = dm.database_id;
GO
it shows..
nameis_encryptedencryption_statepercent_completekey_algorithmkey_length
TDE1 3 0 AES 128
Now what are the step that I should follow and under shose login..so that I can get some negative result like for an example I won't be able to restore the database under that login and inform them..this is due to without key/certificate..
I am sorry if I still didn't give you the answer above of your quetions..
thanks.
June 19, 2013 at 1:44 pm
Learner44 (6/19/2013)
But Now I am confused..how I can show him, that database is encrypted and if your restore it won't without proper key.
To prove it won't restore without the certificate, try to restore the database on a server that doesn't have the encryption certificate. The restore will fail.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2013 at 1:51 pm
GilaMonster (6/19/2013)
Learner44 (6/19/2013)
But Now I am confused..how I can show him, that database is encrypted and if your restore it won't without proper key.To prove it won't restore without the certificate, try to restore the database on a server that doesn't have the encryption certificate. The restore will fail.
I didn't get this point..Can you please elobrate..
My situation is
I have SQLSERVER002\sql2008R2 server name
In that..I created one database called -TDE
In that..I created one table called - dbo.address
which carries 2368 records.
I created backup which is stored on server in backup folder.
server drive I have mapped into my pc as s:/ drive.
So my path for accessing that backup files are like s:\MSSQLSQL2008R2\MSSQL\Backup.
now How can I start following your step!!!
thanks in advance.
June 19, 2013 at 1:59 pm
Really, you're working with encryption and you either don't know what a certificate is or don't know how to restore a backup on another server?
Leave off the encryption and spend a couple weeks reading up on the basics of databases and backups.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2013 at 2:02 pm
I already metioned I am new to this encryption field..
I am following steps from the msdn articles..doing this and understaing them with all of your help.
June 19, 2013 at 2:05 pm
Learner44 (6/19/2013)
I already metioned I am new to this encryption field..
The steps I gave have little to do with encryption, more to do with basic database operations like restoring a backup. If you don't know how to restore a backup on a different server, I suggest you spend some time on basic backup/restore operations before you look into encryption.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2013 at 2:08 pm
OK I have started looking on how to restore the database on different server..
if been through any article for this please let me know..
thanks.
June 19, 2013 at 2:11 pm
Books Online. The RESTORE DATABASE page. I suggest you spend some time reading Books Online on a variety of topics. Maybe also buy a copy of one of the SQL 2008 admin exam training guides, not for the exam, for the fairly decent coverage of a lot of topics
p.s. There's no special command for restoring a backup to 'another server'. There's a command for restoring a backup, you choose where you're restoring it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 20, 2013 at 4:09 am
Learner44 (6/19/2013)
open MASTER KEY DECRYPTION BY PASSWORD = 'password';RESTORE DATABASE Encry2
FROM DISK = N'D:\SQL2008R2DB\MSSQL10_50.SQL2008R2\MSSQL\Backup\TDEPost.bak'
WITH FILE = 1,
MOVE N'mydb' TO N'D:\SQL2008R2DB\MSSQL10_50.SQL2008R2\MSSQL\Backup\TDEPost.mdf',
MOVE N'mydb_log' TO N'D:\SQL2008R2DB\MSSQL10_50.SQL2008R2\MSSQL\Backup\TDEPost.ldf',
NOUNLOAD, REPLACE, STATS = 10
CLOSE MASTER KEY
It gives me following error message
Msg 3234, Level 16, State 2, Line 2
Logical file 'mydb' is not part of database 'Encry2'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
Why are you opening the master key manually? The default is to create a master key that is protected by the service master key and you have to actually alter the DMK and drop encryption by server. Did you do this?
Learner44 (6/19/2013)
do they need password of master key.?
No, they shouldn't need it!
Learner44 (6/19/2013)
Followed by this , I created Database master key , AES-128 and encrypted it with certificate.
No, you create the master key which is then used to encrypt the private key of the certificate you want to use for TDE!
As Gail has pointed out you should gain more experience with TDE and understand how it works before attempting to configure it.
Having said that, as a guide the following are relative to encrypting a database using TDE
selectDB_NAME(database_id) AS [DBName]
, case encryption_state
WHEN 0 THEN 'No encryption'
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encryption in progress'
WHEN 3 THEN 'Encrypted'
WHEN 4 THEN 'Key change in progress'
WHEN 5 THEN 'Decryption in progress'
WHEN 6 THEN 'Protection change in progress'
END AS [EncryptionState]
, percent_complete
from sys.dm_database_encryption_keys
To restore the database on the same server, you merely need to restore the database backup. As the certificate already exists the restore will be successful.
If you want to restore the backup to another server you need to
Recreate the original certificate in the master database on the target server from the source servers certificate backup file (you'll need the password used to encrypt the backup originally).
Restore the backup of the database from the source server to the target server. As the certificate exists the restore will be successful.
Bear in mind that the user performing this work needs permission to create databases, certificates, master keys, etc. Usually a system administrator.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply