I am getting the following error while configuring the reporting services on
my local machine. I cannot restore the key as I do not know the file or the
password.
Somebody please help!
ReportServicesConfigUI.WMIProvider.WMIProviderException: The encrypted value
for the "LogonCred" configuration setting cannot be decrypted.
(rsFailedToDecryptConfigInformation)
at
ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject mo)
at
ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.DeleteEncryptedInformation()Hello,
Are you installing a new version of Reporting Services (with clear
ReportServer database) or just reinstalling it with using previous version
of ReportServer database'
It looks like that you've reinstalled Reporting Services without recovering
previous encryption key.
If you don't have any copy of your previous encryption key, the only way to
make Reporting Services content available is to delete all unusable
encrypted data from ReportServer database.
Follow these steps to apply the encryption key to the report server
database:
1.. Run rskeymgmt.exe locally on the computer that hosts the report
server. You must use the -d apply argument. The following example
illustrates the argument you must specify:
rskeymgmt -d
2.. Restart Internet Information Service (IIS).
After the values are removed, you must re-specify the values as follows:
1.. Run rsconfig utility to specify a report server connection. This step
replaces the report server connection information. For more information, see
Configuring a Report Server Connection and rsconfig Utility.
2.. If you are supporting unattended report execution for reports that do
not use credentials, run rsconfig to specify the account used for this
purpose. For more information, see Configuring an Account for Unattended
Report Processing.
3.. For each report and shared data source that uses stored credentials,
you must retype the user name and password. For more information, see
Specifying Credential and Connection Information.
4.. Open and resave each subscription. Subscriptions retain residual
information about the encrypted credentials deleted during the rskeymgmt
delete operation. You can update the subscription by opening and saving it.
You do not need to modify or recreate it.
I hope this infomation will helpful.
Best Regards,
Radoslaw Lebkowski
U¿ytkownik "RouteC" <RouteC@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:D246447F-1622-4230-AC73-71D0F1073C11@.microsoft.com...
>I am getting the following error while configuring the reporting services
>on
> my local machine. I cannot restore the key as I do not know the file or
> the
> password.
> Somebody please help!
> ReportServicesConfigUI.WMIProvider.WMIProviderException: The encrypted
> value
> for the "LogonCred" configuration setting cannot be decrypted.
> (rsFailedToDecryptConfigInformation)
> at
> ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject
> mo)
> at
> ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.DeleteEncryptedInformation()|||Hi Rad,
The very first step in trying to delete the key is giving me the same
exception which is ReportServicesConfigUI.WMIProvider.WMIProviderException:
The encrypted value
for the "LogonCred" configuration setting cannot be decrypted.
rskeymgmt -d -i instance
This is completely new installation in 2steps.
1. Installed sql server 2005 with all other services except reporting
services as iis was not installed at that time.
2. Installed reporting services.
"Radoslaw Lebkowski" wrote:
> Hello,
> Are you installing a new version of Reporting Services (with clear
> ReportServer database) or just reinstalling it with using previous version
> of ReportServer database'
> It looks like that you've reinstalled Reporting Services without recovering
> previous encryption key.
> If you don't have any copy of your previous encryption key, the only way to
> make Reporting Services content available is to delete all unusable
> encrypted data from ReportServer database.
> Follow these steps to apply the encryption key to the report server
> database:
> 1.. Run rskeymgmt.exe locally on the computer that hosts the report
> server. You must use the -d apply argument. The following example
> illustrates the argument you must specify:
> rskeymgmt -d
> 2.. Restart Internet Information Service (IIS).
> After the values are removed, you must re-specify the values as follows:
> 1.. Run rsconfig utility to specify a report server connection. This step
> replaces the report server connection information. For more information, see
> Configuring a Report Server Connection and rsconfig Utility.
> 2.. If you are supporting unattended report execution for reports that do
> not use credentials, run rsconfig to specify the account used for this
> purpose. For more information, see Configuring an Account for Unattended
> Report Processing.
> 3.. For each report and shared data source that uses stored credentials,
> you must retype the user name and password. For more information, see
> Specifying Credential and Connection Information.
> 4.. Open and resave each subscription. Subscriptions retain residual
> information about the encrypted credentials deleted during the rskeymgmt
> delete operation. You can update the subscription by opening and saving it.
> You do not need to modify or recreate it.
> I hope this infomation will helpful.
> Best Regards,
> Radoslaw Lebkowski
>
> U¿ytkownik "RouteC" <RouteC@.discussions.microsoft.com> napisa³ w wiadomo¶ci
> news:D246447F-1622-4230-AC73-71D0F1073C11@.microsoft.com...
> >I am getting the following error while configuring the reporting services
> >on
> > my local machine. I cannot restore the key as I do not know the file or
> > the
> > password.
> > Somebody please help!
> >
> > ReportServicesConfigUI.WMIProvider.WMIProviderException: The encrypted
> > value
> > for the "LogonCred" configuration setting cannot be decrypted.
> > (rsFailedToDecryptConfigInformation)
> > at
> > ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject
> > mo)
> > at
> > ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.DeleteEncryptedInformation()
>
>|||Hmm, that's a little strange situation.
Under the following link there is a very similar problem with LogonCred
decryption problem.
http://groups.google.pl/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/ec7858be1ef25e24/9f72570a671b74e7?lnk=st&q=LogonCred+decrypted&rnum=1&hl=pl#9f72570a671b74e7
Maybe you have problem with authorization a connection to ReportServer
database (bad values contained in RSReportServer.config file used to connect
to the report server database)
Try to use rsconfig utility described below
http://msdn2.microsoft.com/en-us/library/aa179654(SQL.80).aspx
Regards
Radoslaw Lebkowski
U¿ytkownik "RouteC" <RouteC@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:7BC3B705-71DF-4B48-93FE-6959E94A51FF@.microsoft.com...
> Hi Rad,
> The very first step in trying to delete the key is giving me the same
> exception which is
> ReportServicesConfigUI.WMIProvider.WMIProviderException:
> The encrypted value
> for the "LogonCred" configuration setting cannot be decrypted.
> rskeymgmt -d -i instance
> This is completely new installation in 2steps.
> 1. Installed sql server 2005 with all other services except reporting
> services as iis was not installed at that time.
> 2. Installed reporting services.
> "Radoslaw Lebkowski" wrote:
>> Hello,
>> Are you installing a new version of Reporting Services (with clear
>> ReportServer database) or just reinstalling it with using previous
>> version
>> of ReportServer database'
>> It looks like that you've reinstalled Reporting Services without
>> recovering
>> previous encryption key.
>> If you don't have any copy of your previous encryption key, the only way
>> to
>> make Reporting Services content available is to delete all unusable
>> encrypted data from ReportServer database.
>> Follow these steps to apply the encryption key to the report server
>> database:
>> 1.. Run rskeymgmt.exe locally on the computer that hosts the report
>> server. You must use the -d apply argument. The following example
>> illustrates the argument you must specify:
>> rskeymgmt -d
>> 2.. Restart Internet Information Service (IIS).
>> After the values are removed, you must re-specify the values as follows:
>> 1.. Run rsconfig utility to specify a report server connection. This
>> step
>> replaces the report server connection information. For more information,
>> see
>> Configuring a Report Server Connection and rsconfig Utility.
>> 2.. If you are supporting unattended report execution for reports that
>> do
>> not use credentials, run rsconfig to specify the account used for this
>> purpose. For more information, see Configuring an Account for Unattended
>> Report Processing.
>> 3.. For each report and shared data source that uses stored
>> credentials,
>> you must retype the user name and password. For more information, see
>> Specifying Credential and Connection Information.
>> 4.. Open and resave each subscription. Subscriptions retain residual
>> information about the encrypted credentials deleted during the rskeymgmt
>> delete operation. You can update the subscription by opening and saving
>> it.
>> You do not need to modify or recreate it.
>> I hope this infomation will helpful.
>> Best Regards,
>> Radoslaw Lebkowski
>>
>> U?ytkownik "RouteC" <RouteC@.discussions.microsoft.com> napisa3 w
>> wiadomo?ci
>> news:D246447F-1622-4230-AC73-71D0F1073C11@.microsoft.com...
>> >I am getting the following error while configuring the reporting
>> >services
>> >on
>> > my local machine. I cannot restore the key as I do not know the file
>> > or
>> > the
>> > password.
>> > Somebody please help!
>> >
>> > ReportServicesConfigUI.WMIProvider.WMIProviderException: The encrypted
>> > value
>> > for the "LogonCred" configuration setting cannot be decrypted.
>> > (rsFailedToDecryptConfigInformation)
>> > at
>> > ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject
>> > mo)
>> > at
>> > ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.DeleteEncryptedInformation()
>>
Showing posts with label restore. Show all posts
Showing posts with label restore. Show all posts
Monday, March 26, 2012
Thursday, March 22, 2012
Encryption and database restore
Hi
Can anyone help?
I have a database using encryption with symmetric keys created using asymmetric keys.
I backed up this database and restored it on another machine along with the service master key.
I can read the encrypted data fine. But all the encrypted stored procs that are associated with the application that uses the data cannot read the data, they just return Nulls.
If I regenerate all the encrypted stored procs from script they work fine again.
Is there a way to restore the database without having to regenerate all the encrypted procs?
You are probably missing this sequence after you restore the database:
1) open master key using password
2) ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Your script probably creates the database master key too, so you end up recreating it with an encryption of the service master key, and everything works, but you only need the two commands above, to make your database work after restore.
See http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx for additional information on the master keys.
Thanks
Laurentiu
Hi
Well now I am really puzzled. I created the same database on 2 machines then restored both databases back on to a third machine. I can read the eccrypted data from one of the backups but not from the other. All 3 machines are running XP and SQL 2005
On the both databases with encrypted data I ran the following
OPEN MASTER KEY DECRYPTION BY PASSWORD = asecretpassword
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Then ran the script to read my encrypted data
Just done some more checking. If i take a database that I created on one machine and can read on another then restore it onto the 3rd then I cannot read it on the 3rd.
(This is also the machine I created the database on that I could not read on other machines)
When I try to open my symmetric key I get the following error
This is after opening and altering the master key as above
Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.
It seems that their is something incompatable on the third machine yet it is running the same version of OS and SQL as the other machines
|||
Are your machines in the same time-zone? Is the time properly synchronized between them? There is a known issue with restoring databases on a machine that has the time set "in the past". See page 2 of this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=177863&SiteID=1. But I'm not sure you're hitting this issue.
Can you post the statement that you execute before getting the error? Also, can you tell me some details about the key: what algorithm are you using and how did you create the key.
Just to make sure I understand the scenario: what you see is that whatever you encrypt on the third machine, you cannot decrypt on the other machines, and what you encrypt on the other machines, you cannot decrypt on this third machines. Otherwise, for the other machines, encryption and decryption works ok when restoring databases from one to another. Is this correct?
Thanks
Laurentiu
Also, can you execute winver on all three machines and post the version line including the build number information for all three of them? I just need the line under Microsoft Windows.
Thanks
Laurentiu
Hi Laurentiu
I have not tried writing encrypting any data on the databases I cannot read encrypted data from, I am more concerned with being able to read the data but I doubt that I would be able to write data as the errors i get indicate that the asymetric keys cannot be opened.
All machines are in the same time zone and domain except the 2003 server which is in Holland whilst the rest are in the UK and we are not using certificates. So I dont think it is a time zone thing
I have created another database build on a 4th machine running 2003 Server and copied a backup of that database onto my XP machines and I cannot read the encrypted data from the 2003 backup on any of the XP machines I have tried this on.
However if I restore the master key from the any machine where the database was created with the force option I can read the data.
The restore of the master key gives me a message saying that it could not open the asymmetric keys
I run the following bit of code to read the data
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Asecretpassword'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Go
This appears to run OK
Open Symmetric Key NoKeySymetricKey
Decryption by ASYMMETRIC KEY NoKey
SELECT top 10
CONVERT(VARCHAR(50),
DecryptbyKey(EncryptedNo)) AS ClearNumber
From MyTable
Close Symmetric Key NoKeySymetricKey
I get the following error
Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.
(10 row(s) affected)
Msg 15315, Level 16, State 1, Line 8
The key 'NoKeySymetricKey' is not open. Please open the key before using it.
Winver from the machines I have tried this on
(Mine)
Version 5.1 (Build 2600.xpsp_sp2_grd.050301-1519 : Service Pack 2)
(Andrews)
Version 5.1 (Build 2600.xpsp_sp2_grd.050301-1519 : Service Pack 2)
(Chris's)
Version 5.1 (Build 2600.xpsp_sp2_rtm.04083-2158 : Service Pack 2)
Can backup and restore between above machines OK but not restore from the 2 below
(Build Machine)
Version 5.1 (Build 2600.xpsp_sp2_rtm.04083-2158 : Service Pack 2)
The database built on this machine cannot be read on the first 2 of the 3 above machines;
have not tried it on the server below or on the machine runing Version 5.1 (Build 2600.xpsp_sp2_rtm.04083-2158 : Service Pack 2) (Chris's)
2003 Server
Version 5.2 (Build 3790.srv03_sp1_rtm.050324-1447 : Service Pack 1)
The database created on this machine can not be read on any of the other machines
I built the keys on all machines/databases with the same bit of code below
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'asecretpassword'
CREATE ASYMMETRIC KEY PassKey
WITH ALGORITHM = RSA_512
CREATE ASYMMETRIC KEY NoKey
WITH ALGORITHM = RSA_2048
CREATE SYMMETRIC KEY NoKeySymetricKey
WITH ALGORITHM = Triple_Des
ENCRYPTION BY ASYMMETRIC KEY NoKey
CREATE SYMMETRIC KEY PassSymetricKey
WITH ALGORITHM = Triple_Des
ENCRYPTION BY ASYMMETRIC KEY PassKey
Hi
Just tried the restore of database from another XP maxhine and it fails. If I force restore the master key it works OK. However if I try to restore without the force option I get the following message
Msg 15320, Level 16, State 2, Line 2
An error occurred while decrypting asymmetric key PassKey that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable.
|||
So it looks like the asymmetric keys cannot be decrypted unless you force restore the master key. It's strange that the master key appears to be valid, given that you receive no error when you try to open it.
Can you try encrypting something with the master key immediately after restore? Try creating another asymmetric key, then use that asymmetric key to encrypt something and see if you get any errors.
I'd also like to know if the same problem affects certificates.
Also, note that the timezone issue that I mentioned earlier affects symmetric keys, it's not certificate related. But now I'm pretty positive you're seeing a different issue.
Here's something else I'd like to ask: can you create a simple test database with a master key (password = BugCrypto90, let's say), an asymmetric key, and a symmetric key, on both your machine and the build machine. The databases should not contain any other data and you should see the same issue for them when trying to restore them on the other machine (i.e., opening the symmetric key should fail with a decryption error). Then submit a bug report at http://lab.msdn.microsoft.com/productfeedback/, and attach compressed backups of these two databases to the bug. Add a link to this thread in the description and ask for the bug to be assigned to me. If you use a different password for the keys, mention it in the description as well. This way, I can also try to reproduce this problem on our machines.
Thanks
Laurentiu
Hi Laurentiu
I will submit a bug report as asked.
However in the meantime I tried creating a new asymetric key on my restored database and encrypting some data. Everything went OK I was able to both write and read encrypted data created with my newly created asymetric key even though I couldnt read my old data.
Martin
|||Hi Again
I have logged the problem as a bug report but I dont know if you are going to be able to reproduce it.
I tried as you asked and created a test database on my machine and the build machine and created keys using the same method I used on my live database and I can backup the databases and restore them on different machines and open the symetric keys OK.
However I still have a problem with my database - will continue testing and let you know if I can re-produce it
Martin
|||Can you also look into whether the problem is specific to asymmetric keys or can be reproed with certificates as well?
Thanks for taking the time to file the report.
Laurentiu
|||I've received the bug report, but there are no attachments. Have you attached the test databases when you filed the report?
Thanks
Laurentiu
Hi
I have attached the databases again.
|||I do not see the attachments yet. Have you pressed the "Add Attachment" button to actually attach the files? You need to press that button after you browse-select the file.
Thanks
Laurentiu
Hi
will try again
It may be the security settings on our proxy server; they are pretty tight.
If it doesnt work let me know and I will do it from home
Martin
Encryption and database restore
Hi
Can anyone help?
I have a database using encryption with symmetric keys created using asymmetric keys.
I backed up this database and restored it on another machine along with the service master key.
I can read the encrypted data fine. But all the encrypted stored procs that are associated with the application that uses the data cannot read the data, they just return Nulls.
If I regenerate all the encrypted stored procs from script they work fine again.
Is there a way to restore the database without having to regenerate all the encrypted procs?
You are probably missing this sequence after you restore the database:
1) open master key using password
2) ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Your script probably creates the database master key too, so you end up recreating it with an encryption of the service master key, and everything works, but you only need the two commands above, to make your database work after restore.
See http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx for additional information on the master keys.
Thanks
Laurentiu
Hi
Well now I am really puzzled. I created the same database on 2 machines then restored both databases back on to a third machine. I can read the eccrypted data from one of the backups but not from the other. All 3 machines are running XP and SQL 2005
On the both databases with encrypted data I ran the following
OPEN MASTER KEY DECRYPTION BY PASSWORD = asecretpassword
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Then ran the script to read my encrypted data
Just done some more checking. If i take a database that I created on one machine and can read on another then restore it onto the 3rd then I cannot read it on the 3rd.
(This is also the machine I created the database on that I could not read on other machines)
When I try to open my symmetric key I get the following error
This is after opening and altering the master key as above
Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.
It seems that their is something incompatable on the third machine yet it is running the same version of OS and SQL as the other machines
|||
Are your machines in the same time-zone? Is the time properly synchronized between them? There is a known issue with restoring databases on a machine that has the time set "in the past". See page 2 of this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=177863&SiteID=1. But I'm not sure you're hitting this issue.
Can you post the statement that you execute before getting the error? Also, can you tell me some details about the key: what algorithm are you using and how did you create the key.
Just to make sure I understand the scenario: what you see is that whatever you encrypt on the third machine, you cannot decrypt on the other machines, and what you encrypt on the other machines, you cannot decrypt on this third machines. Otherwise, for the other machines, encryption and decryption works ok when restoring databases from one to another. Is this correct?
Thanks
Laurentiu
Also, can you execute winver on all three machines and post the version line including the build number information for all three of them? I just need the line under Microsoft Windows.
Thanks
Laurentiu
Hi Laurentiu
I have not tried writing encrypting any data on the databases I cannot read encrypted data from, I am more concerned with being able to read the data but I doubt that I would be able to write data as the errors i get indicate that the asymetric keys cannot be opened.
All machines are in the same time zone and domain except the 2003 server which is in Holland whilst the rest are in the UK and we are not using certificates. So I dont think it is a time zone thing
I have created another database build on a 4th machine running 2003 Server and copied a backup of that database onto my XP machines and I cannot read the encrypted data from the 2003 backup on any of the XP machines I have tried this on.
However if I restore the master key from the any machine where the database was created with the force option I can read the data.
The restore of the master key gives me a message saying that it could not open the asymmetric keys
I run the following bit of code to read the data
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Asecretpassword'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Go
This appears to run OK
Open Symmetric Key NoKeySymetricKey
Decryption by ASYMMETRIC KEY NoKey
SELECT top 10
CONVERT(VARCHAR(50),
DecryptbyKey(EncryptedNo)) AS ClearNumber
From MyTable
Close Symmetric Key NoKeySymetricKey
I get the following error
Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.
(10 row(s) affected)
Msg 15315, Level 16, State 1, Line 8
The key 'NoKeySymetricKey' is not open. Please open the key before using it.
Winver from the machines I have tried this on
(Mine)
Version 5.1 (Build 2600.xpsp_sp2_grd.050301-1519 : Service Pack 2)
(Andrews)
Version 5.1 (Build 2600.xpsp_sp2_grd.050301-1519 : Service Pack 2)
(Chris's)
Version 5.1 (Build 2600.xpsp_sp2_rtm.04083-2158 : Service Pack 2)
Can backup and restore between above machines OK but not restore from the 2 below
(Build Machine)
Version 5.1 (Build 2600.xpsp_sp2_rtm.04083-2158 : Service Pack 2)
The database built on this machine cannot be read on the first 2 of the 3 above machines;
have not tried it on the server below or on the machine runing Version 5.1 (Build 2600.xpsp_sp2_rtm.04083-2158 : Service Pack 2) (Chris's)
2003 Server
Version 5.2 (Build 3790.srv03_sp1_rtm.050324-1447 : Service Pack 1)
The database created on this machine can not be read on any of the other machines
I built the keys on all machines/databases with the same bit of code below
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'asecretpassword'
CREATE ASYMMETRIC KEY PassKey
WITH ALGORITHM = RSA_512
CREATE ASYMMETRIC KEY NoKey
WITH ALGORITHM = RSA_2048
CREATE SYMMETRIC KEY NoKeySymetricKey
WITH ALGORITHM = Triple_Des
ENCRYPTION BY ASYMMETRIC KEY NoKey
CREATE SYMMETRIC KEY PassSymetricKey
WITH ALGORITHM = Triple_Des
ENCRYPTION BY ASYMMETRIC KEY PassKey
Hi
Just tried the restore of database from another XP maxhine and it fails. If I force restore the master key it works OK. However if I try to restore without the force option I get the following message
Msg 15320, Level 16, State 2, Line 2
An error occurred while decrypting asymmetric key PassKey that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable.
|||
So it looks like the asymmetric keys cannot be decrypted unless you force restore the master key. It's strange that the master key appears to be valid, given that you receive no error when you try to open it.
Can you try encrypting something with the master key immediately after restore? Try creating another asymmetric key, then use that asymmetric key to encrypt something and see if you get any errors.
I'd also like to know if the same problem affects certificates.
Also, note that the timezone issue that I mentioned earlier affects symmetric keys, it's not certificate related. But now I'm pretty positive you're seeing a different issue.
Here's something else I'd like to ask: can you create a simple test database with a master key (password = BugCrypto90, let's say), an asymmetric key, and a symmetric key, on both your machine and the build machine. The databases should not contain any other data and you should see the same issue for them when trying to restore them on the other machine (i.e., opening the symmetric key should fail with a decryption error). Then submit a bug report at http://lab.msdn.microsoft.com/productfeedback/, and attach compressed backups of these two databases to the bug. Add a link to this thread in the description and ask for the bug to be assigned to me. If you use a different password for the keys, mention it in the description as well. This way, I can also try to reproduce this problem on our machines.
Thanks
Laurentiu
Hi Laurentiu
I will submit a bug report as asked.
However in the meantime I tried creating a new asymetric key on my restored database and encrypting some data. Everything went OK I was able to both write and read encrypted data created with my newly created asymetric key even though I couldnt read my old data.
Martin
|||Hi Again
I have logged the problem as a bug report but I dont know if you are going to be able to reproduce it.
I tried as you asked and created a test database on my machine and the build machine and created keys using the same method I used on my live database and I can backup the databases and restore them on different machines and open the symetric keys OK.
However I still have a problem with my database - will continue testing and let you know if I can re-produce it
Martin
|||Can you also look into whether the problem is specific to asymmetric keys or can be reproed with certificates as well?
Thanks for taking the time to file the report.
Laurentiu
|||I've received the bug report, but there are no attachments. Have you attached the test databases when you filed the report?
Thanks
Laurentiu
Hi
I have attached the databases again.
|||I do not see the attachments yet. Have you pressed the "Add Attachment" button to actually attach the files? You need to press that button after you browse-select the file.
Thanks
Laurentiu
Hi
will try again
It may be the security settings on our proxy server; they are pretty tight.
If it doesnt work let me know and I will do it from home
Martin
Monday, March 19, 2012
Encrypted stored proc
We have a third party app that had some bad code run against it this morning
.
We are trying to restore an SP from our backup but it is encrypted. Does
anyone know of a way to restore\de-encrypt an encrypted proc?
TIA, Jordanhttp://www.planetsourcecode.com/vb/...6J00S003GU.html
David Portas
SQL Server MVP
--|||There are some thirdparty stored procedures/ Application to do this.
You can search and find then in the net.
.
We are trying to restore an SP from our backup but it is encrypted. Does
anyone know of a way to restore\de-encrypt an encrypted proc?
TIA, Jordanhttp://www.planetsourcecode.com/vb/...6J00S003GU.html
David Portas
SQL Server MVP
--|||There are some thirdparty stored procedures/ Application to do this.
You can search and find then in the net.
Sunday, March 11, 2012
Encrypted DB -- Restore Question
Hi,
I have a DB in which I encrypt a few columns in a table. I am using a Symmetric key to encrypt and decrypt the data. When I take a back up of this DB and restore on another server ... my decryption doesn't work. I have dropped the master key and recreated it with same password and that didn't help either.
What are the rules to follow when we restore a db on a different server that has encrypted data ? Thanks.
You will need to open the master key in the new database once ,so that a copy of the same is saved in the master database. After that the decryption should work. Let me know if this helped?|||How do I open Master Key? Thanks!|||
Hi,
You shouldn't have to do anything special to restore a db with encrypted values. The one thing you may need to do is to alter the master key of the database and re-encrypt with the service master key. You can do this with:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'your_password_here';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY;
(for more info, take a look http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx).
EDIT: whoops, wrong link, sorry, this should now have the correct link.
|||I tried and that didn't help ...
I used OPEN MASTER KEY DECRYPTION BY PASSWORD = 'my pwd'
||| OPEN MASTER KEY DECRYPTION BY PASSWORD = ['password']
One other tying you can try, before you decrypt, run "SELECT * FROM sys.openkeys" to verify that the symmetric key is ready for decryption.
|||Are the operating system on both the machines the same, because certain encryption types like AES are supported on certain type of OS.|||I looked at the owner info again ... and the original DB was created by "sa" account itself ... where as the attached DB is owned by another user that is of SysAdmin group. Does that matter for decryption? I really appreciate your help!!|||The owner of the db shouldn't matter for decryption (especially since you confirmed you can select from the table without problems) as long as the user has not been denied view rights for the symmetric key. You also need rights on whatever is encrypting the symmetric key.
If the symmetric key shows up in sys.openkeys though, you should be fine as it means the key has been opened and ready for decryption.
Can you share the SQL you are using to decrypt the data?
|||Just a suggestion, try restore and backup instead of attachdb.Encrypted database deployment
I have an encrypeted database that I need to move from one development
environment to another. If I back it up and restore it to the new
environment, I get decryption errors, even though the Master Keys have been
created using the same password CREATE MASTER KEY ENCRYPTION BY PASSWORD = .
.
How can I move the database to the new environment?
Thanks.This issue is that the password is used to encrypt the database master key -
it isn't the database master key so you now have two different master keys
encrypted with the same password which doesn't help you a lot. If you
haven't already blown away the master key, you can re-encrypt it with the
new service master key like this:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Gerhard" <acsla@.community.nospam> wrote in message
news:0F2B0D28-2A97-4E60-B6B8-1F821177CA87@.microsoft.com...
>I have an encrypeted database that I need to move from one development
> environment to another. If I back it up and restore it to the new
> environment, I get decryption errors, even though the Master Keys have
> been
> created using the same password CREATE MASTER KEY ENCRYPTION BY PASSWORD =
> ...
> How can I move the database to the new environment?
> Thanks.|||Thanks. I did still have the password, so ran the below which ran without
error.
However, I still get:
Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.
when I try to open a symmetric key.
Can you help?
Thanks.
"Roger Wolter[MSFT]" wrote:
> This issue is that the password is used to encrypt the database master key
-
> it isn't the database master key so you now have two different master keys
> encrypted with the same password which doesn't help you a lot. If you
> haven't already blown away the master key, you can re-encrypt it with the
> new service master key like this:
> OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
> ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Gerhard" <acsla@.community.nospam> wrote in message
> news:0F2B0D28-2A97-4E60-B6B8-1F821177CA87@.microsoft.com...
>
>|||Did you run a CREATE MASTER KEY in this database before trying the OPEN
operation? If so, you just opened the new key you created - not the key the
symmetric keys were encrypted with. You may have to go back to your backup
that contains the original key.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Gerhard" <acsla@.community.nospam> wrote in message
news:54EF76EA-F4ED-44CD-BB7C-3EC017139AAD@.microsoft.com...[vbcol=seagreen]
> Thanks. I did still have the password, so ran the below which ran without
> error.
> However, I still get:
> Msg 15466, Level 16, State 1, Line 1
> An error occurred during decryption.
> when I try to open a symmetric key.
> Can you help?
> Thanks.
> "Roger Wolter[MSFT]" wrote:
>|||There's more information on OPEN here:
http://msdn2.microsoft.com/en-us/library/ms174433.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:evRJycVxGHA.3892@.TK2MSFTNGP03.phx.gbl...
> Did you run a CREATE MASTER KEY in this database before trying the OPEN
> operation? If so, you just opened the new key you created - not the key
> the symmetric keys were encrypted with. You may have to go back to your
> backup that contains the original key.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Gerhard" <acsla@.community.nospam> wrote in message
> news:54EF76EA-F4ED-44CD-BB7C-3EC017139AAD@.microsoft.com...
>|||I restored the database, ran the open and alter commands as below, but still
get the error:
Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.
Still looking for a way to get this successfully moved...
"Roger Wolter[MSFT]" wrote:
> There's more information on OPEN here:
> http://msdn2.microsoft.com/en-us/library/ms174433.aspx
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:evRJycVxGHA.3892@.TK2MSFTNGP03.phx.gbl...
>
>|||I'll bow out at this point. This should have worked if the problem was
indeed an issue with your mater key encryption so I assume something else is
going on. Maybe someone smarter than me can see what's happening.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Gerhard" <acsla@.community.nospam> wrote in message
news:F60CD778-4E38-4209-8E88-E960CB530579@.microsoft.com...[vbcol=seagreen]
>I restored the database, ran the open and alter commands as below, but
>still
> get the error:
> Msg 15466, Level 16, State 1, Line 1
> An error occurred during decryption.
> Still looking for a way to get this successfully moved...
>
> "Roger Wolter[MSFT]" wrote:
>|||I'm also getting the same error now on another database that was working in
this new environment, even when I restore a backup...
Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.
"Roger Wolter[MSFT]" wrote:
> There's more information on OPEN here:
> http://msdn2.microsoft.com/en-us/library/ms174433.aspx
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:evRJycVxGHA.3892@.TK2MSFTNGP03.phx.gbl...
>
>|||Did you back up all your Service Master Keys and Database Master Keys, like
BOL suggests? If so, you might see if you can successfully restore them
without the FORCE option.
"Gerhard" <acsla@.community.nospam> wrote in message
news:7D9F7228-93F7-48E9-B776-2E90EDB54C0F@.microsoft.com...[vbcol=seagreen]
> I'm also getting the same error now on another database that was working
> in
> this new environment, even when I restore a backup...
> Msg 15466, Level 16, State 1, Line 1
> An error occurred during decryption.
>
> "Roger Wolter[MSFT]" wrote:
>|||Hello,
The issue might be related to SQL service account. Is the service account
the same on both SQL development/test server instances? If not, you may try
to change the service account to the same domain user to test. The
following link is for your reference:
http://groups.google.com/group/micr...ls/browse_threa
d/thread/8596eefe3ed0edfc/a0979b976b3c7a57?lnk=st&q=Encrypted+database+peter
+yang&rnum=2&hl=en#a0979b976b3c7a57
If you have any update, please feel free to let's know. We look forward to
your reply.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
environment to another. If I back it up and restore it to the new
environment, I get decryption errors, even though the Master Keys have been
created using the same password CREATE MASTER KEY ENCRYPTION BY PASSWORD = .
.
How can I move the database to the new environment?
Thanks.This issue is that the password is used to encrypt the database master key -
it isn't the database master key so you now have two different master keys
encrypted with the same password which doesn't help you a lot. If you
haven't already blown away the master key, you can re-encrypt it with the
new service master key like this:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Gerhard" <acsla@.community.nospam> wrote in message
news:0F2B0D28-2A97-4E60-B6B8-1F821177CA87@.microsoft.com...
>I have an encrypeted database that I need to move from one development
> environment to another. If I back it up and restore it to the new
> environment, I get decryption errors, even though the Master Keys have
> been
> created using the same password CREATE MASTER KEY ENCRYPTION BY PASSWORD =
> ...
> How can I move the database to the new environment?
> Thanks.|||Thanks. I did still have the password, so ran the below which ran without
error.
However, I still get:
Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.
when I try to open a symmetric key.
Can you help?
Thanks.
"Roger Wolter[MSFT]" wrote:
> This issue is that the password is used to encrypt the database master key
-
> it isn't the database master key so you now have two different master keys
> encrypted with the same password which doesn't help you a lot. If you
> haven't already blown away the master key, you can re-encrypt it with the
> new service master key like this:
> OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
> ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Gerhard" <acsla@.community.nospam> wrote in message
> news:0F2B0D28-2A97-4E60-B6B8-1F821177CA87@.microsoft.com...
>
>|||Did you run a CREATE MASTER KEY in this database before trying the OPEN
operation? If so, you just opened the new key you created - not the key the
symmetric keys were encrypted with. You may have to go back to your backup
that contains the original key.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Gerhard" <acsla@.community.nospam> wrote in message
news:54EF76EA-F4ED-44CD-BB7C-3EC017139AAD@.microsoft.com...[vbcol=seagreen]
> Thanks. I did still have the password, so ran the below which ran without
> error.
> However, I still get:
> Msg 15466, Level 16, State 1, Line 1
> An error occurred during decryption.
> when I try to open a symmetric key.
> Can you help?
> Thanks.
> "Roger Wolter[MSFT]" wrote:
>|||There's more information on OPEN here:
http://msdn2.microsoft.com/en-us/library/ms174433.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:evRJycVxGHA.3892@.TK2MSFTNGP03.phx.gbl...
> Did you run a CREATE MASTER KEY in this database before trying the OPEN
> operation? If so, you just opened the new key you created - not the key
> the symmetric keys were encrypted with. You may have to go back to your
> backup that contains the original key.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Gerhard" <acsla@.community.nospam> wrote in message
> news:54EF76EA-F4ED-44CD-BB7C-3EC017139AAD@.microsoft.com...
>|||I restored the database, ran the open and alter commands as below, but still
get the error:
Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.
Still looking for a way to get this successfully moved...
"Roger Wolter[MSFT]" wrote:
> There's more information on OPEN here:
> http://msdn2.microsoft.com/en-us/library/ms174433.aspx
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:evRJycVxGHA.3892@.TK2MSFTNGP03.phx.gbl...
>
>|||I'll bow out at this point. This should have worked if the problem was
indeed an issue with your mater key encryption so I assume something else is
going on. Maybe someone smarter than me can see what's happening.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Gerhard" <acsla@.community.nospam> wrote in message
news:F60CD778-4E38-4209-8E88-E960CB530579@.microsoft.com...[vbcol=seagreen]
>I restored the database, ran the open and alter commands as below, but
>still
> get the error:
> Msg 15466, Level 16, State 1, Line 1
> An error occurred during decryption.
> Still looking for a way to get this successfully moved...
>
> "Roger Wolter[MSFT]" wrote:
>|||I'm also getting the same error now on another database that was working in
this new environment, even when I restore a backup...
Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.
"Roger Wolter[MSFT]" wrote:
> There's more information on OPEN here:
> http://msdn2.microsoft.com/en-us/library/ms174433.aspx
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:evRJycVxGHA.3892@.TK2MSFTNGP03.phx.gbl...
>
>|||Did you back up all your Service Master Keys and Database Master Keys, like
BOL suggests? If so, you might see if you can successfully restore them
without the FORCE option.
"Gerhard" <acsla@.community.nospam> wrote in message
news:7D9F7228-93F7-48E9-B776-2E90EDB54C0F@.microsoft.com...[vbcol=seagreen]
> I'm also getting the same error now on another database that was working
> in
> this new environment, even when I restore a backup...
> Msg 15466, Level 16, State 1, Line 1
> An error occurred during decryption.
>
> "Roger Wolter[MSFT]" wrote:
>|||Hello,
The issue might be related to SQL service account. Is the service account
the same on both SQL development/test server instances? If not, you may try
to change the service account to the same domain user to test. The
following link is for your reference:
http://groups.google.com/group/micr...ls/browse_threa
d/thread/8596eefe3ed0edfc/a0979b976b3c7a57?lnk=st&q=Encrypted+database+peter
+yang&rnum=2&hl=en#a0979b976b3c7a57
If you have any update, please feel free to let's know. We look forward to
your reply.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
Subscribe to:
Posts (Atom)