Empty Column: machine

Showing posts with label machine. Show all posts

Monday, March 26, 2012

Encryption Key

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()
>>

Thursday, March 22, 2012

Encryption and recovery on standby machine

Hi there,

to follow on my question in the other forum, I have a database with triple des encrypted columns

Master key created, certificate and symmetric key using certificate. Values inserted into table (encrypted)

Can select etc, all works fine.

Back up and restore database on another server, decrypting the data results in NULLs being returned. Backed up and restore the master key, cerficate, etc but either it completes successfully or SQL states that the old and the new keys are the same and wont be changed. Master key opens (using password), cert opens (is viewable in sys.openkeys) but then closes automatically after trying to decrypt. Suggests there is something wrong with the certificate or the key, but I have no idea what it is.

Can someone please tell me exactly what is needed to view this on the 2ndary box? And what I might be missing?

Thanks

When you restore the database on the other server, you will need to add the service master key encryption to the database master key. You don't need to individually backup and restore anyhting else (don't worry about individually restoring the master key and the certificate). See this post for additional information on the master keys: http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx.

If you opened the master key using a password, things should also work with no change.

But the certificate cannot be "opened" and it never shows in sys.openkeys. What you need to open is the symmetric key and this should be done following the same steps that you did in the original database.

Please send me more details on the steps that you are following, and I'll be able to help you more specifically. Also, please copy-paste the text and number of any error that you encounter.

Thanks
Laurentiu

|||

Hi,

Thanks Laurentiu.

I added the SMK encrypted to the database masterkey, and I was able to succesfully view decrypted information on the secondary node.

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Tomorrow I will run through the procedure again using new databases, and post all the scripts I used for the encryption and restoration on the second server (it wont be pages full, merely one line per activity and someone else might get benefit from it as well)

|||

This ALTER MASTER KEY statement is the only thing you'll need to issue to complete the restoration of the database. Everything else should already be recovered with the database itself. Let us know if you hit any issues.

Thanks
Laurentiu

|||

Hi,

Ok source database -

create master key with password

create certificate

create symmetric key with encryption by certificate created above

open key

insert values into table (using encryptbykey)

create view that that uses decryptbykey, all works fine.

Backup database

Restore on 2nd machine

Open the master key

Alter master key add encryption by service master key

After this I open the symmetric key and try to decrypt but everything gets returned as NULLs.

Will run through this again and see if I missed anything.

|||

The steps you followed seem correct. Let us know if you've also been unable to decrypt the second time.

One simple suggestion: verify that the symmetric key is indeed opened (select * from sys.openkeys).

Thanks
Laurentiu

|||

Hi,

Tried it just now and the values decrypted fine. Havent touched the server since the post was made, and did not run any other scripts on it. Weird.,,

Repeated the restore again with another database, same steps, same result, NULLs when decrypting. The key is open and in sys.openkeys (cant open the key without opening the master key first), try to use the decryptbykey and the are no values returned (key remains open). Restarted SQL, key opens fine but still NULLs get returned but now the key gets closed automatically after the first decryptbykey attempt.

Will leave the server "as is" for now and check it again later to see if it's successfull then.

This made me think of some extra security questions, will post in a seperate thread :)

|||

You should be able to open the symmetric key without opening the master key if you added the service master key encryption to it.

The behavior that you describe is very strange indeed. Could you post the T-SQL that you used to open the key and to decrypt? I will try to repro this behavior, but because of holidays, I may not be able to do this very soon.

Thanks
Laurentiu

|||

This morning, check it out and the restored database is decrypting information... again no changes done, server just left overnight.

This is what I used to check the information/decrypt - you'll recognise it from your blog as I was using snippets of your code as part of this :)

If you need any other info, please let me know.

open symmetric key sk_player decryption by certificate cert_sk_admin;

select * from sys.openkeys

select id,name, CONVERT(varchar(10), decryptbykey(salary, 1, CONVERT(varchar(30), id))) AS salary from t_employees

select * from v_employees

select * from sys.openkeys

This was done before and only once, as it couldnt open the symmetric key without the master key being open first.

open master key decryption by password = 'xxxkickasspasswordxxxx';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||Both your steps and the SQL look right. Is this issue still occurring? What version are you using? (SELECT @.@.version).|||

Problem still occuring.

Version is (on the standby box) - SQL2005 Dev Edition on Windows Server 2000

On the main box it is SQL2005 Dev Edition on Windows 2003 Enterprise SP1

I got the Enterprise Editions of SQL2005 (which will go on the live systems) from MSDN and will try that as well, maybe it's something with the dev edition?

Thanks

|||

There shouldn't be any difference between Developer and Enterprise regarding encryption. Could you also specify how you created the certificate and the symmetric key?

Thanks
Laurentiu

|||

Hi there,

I create the cert and key using this (using Triple Des since the standby machine is Windows 2000 and doesnt do AES)

create certificate cert_sk_td3 with subject = 'Certificate'

create symmetric key sk_td3 with algorithm = triple_des encryption by certificate cert_sk_td3;

Let me know if you need any more info

|||

We have tried the scenario that you describe, moving a database between a Windows 2000 and a Windows 2003 machine, and we were successful in decrypting data after restores. We were not able to reproduce the problem that you described.

You mentioned that data becomes available after a while. How much time did it took before you could decrypt? Could you post the entire script that you are executing for encrypting the data and then decrypting it after restoring the database?

I'd also suggest to try to encrypt and decrypt some value after the restore to verify if the behavior that you are noticing is also happening for data encrypted after the restore. If the encryption would fail as well, then that would indicate an issue with the key.

In your scenario, which is the machine on which the decryption fails after restore? Is it the 2000 machine or the 2003 one? (I'm not sure what you mean by the standby machine)

Thanks
Laurentiu

|||

Hi Laurentiu,

In the interim I have upgradede both SQL2005s from Dev Edition to Enterprise Edition (from the MSDN Subscription downloads)

What usually happens with the data is that is becomes available the next morning when I am back at the office, after playing with it in the afternoon and not getting any results. I have tried things like restart SQL and reboot the server to see if that would speed up the recovery, but it didnt help anything. Nothing happens in the server during that time, I just get in, open the symm key and it decrypts.

I am going from a Server2003 (SP1 and hotfixes) to a Server2000(SP4 and all hotfixes). It is on the 2000 Server that I cant decrypt (I am using TripleDES to test since AES isnt supported on 2000).

I retried this after the upgrade from Dev to Enterprise, but I still had the problem occuring. I am able to open the key (appears in sys.openkeys) but inserting any values into the 2000 database just insert NULLs into the encrypted column. So the key doesnt seem happy to do encryption or decryption.

I then backed up the database on the 2000 server, and restored it again on the 2003 server (where it came from originally) under a new name. Again I opened the master key and did the service master key change, opened the symmetric key and was able to view the information. So yeah, it seems everything is fine on the database side, just something on the 2000 set up is causing some issues.

This is a bit of a mystery, I have left the machines and will check it tomorrow morning to see if the 2000 machine magically starts to decrypt again. There arent any jobs or policies on them and they are purely standalone.

Is there any trace or debugging that I can enable that might give some more info?

Encryption and recovery on standby machine

Hi there,

to follow on my question in the other forum, I have a database with triple des encrypted columns

Master key created, certificate and symmetric key using certificate. Values inserted into table (encrypted)

Can select etc, all works fine.

Can someone please tell me exactly what is needed to view this on the 2ndary box? And what I might be missing?

Thanks

If you opened the master key using a password, things should also work with no change.

Please send me more details on the steps that you are following, and I'll be able to help you more specifically. Also, please copy-paste the text and number of any error that you encounter.

Thanks
Laurentiu

|||

Hi,

Thanks Laurentiu.

I added the SMK encrypted to the database masterkey, and I was able to succesfully view decrypted information on the secondary node.

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||

Thanks
Laurentiu

|||

Hi,

Ok source database -

create master key with password

create certificate

create symmetric key with encryption by certificate created above

open key

insert values into table (using encryptbykey)

create view that that uses decryptbykey, all works fine.

Backup database

Restore on 2nd machine

Open the master key

Alter master key add encryption by service master key

After this I open the symmetric key and try to decrypt but everything gets returned as NULLs.

Will run through this again and see if I missed anything.

|||

The steps you followed seem correct. Let us know if you've also been unable to decrypt the second time.

One simple suggestion: verify that the symmetric key is indeed opened (select * from sys.openkeys).

Thanks
Laurentiu

|||

Hi,

Tried it just now and the values decrypted fine. Havent touched the server since the post was made, and did not run any other scripts on it. Weird.,,

Will leave the server "as is" for now and check it again later to see if it's successfull then.

This made me think of some extra security questions, will post in a seperate thread :)

|||

You should be able to open the symmetric key without opening the master key if you added the service master key encryption to it.

Thanks
Laurentiu

|||

This morning, check it out and the restored database is decrypting information... again no changes done, server just left overnight.

This is what I used to check the information/decrypt - you'll recognise it from your blog as I was using snippets of your code as part of this :)

If you need any other info, please let me know.

open symmetric key sk_player decryption by certificate cert_sk_admin;

select * from sys.openkeys

select id,name, CONVERT(varchar(10), decryptbykey(salary, 1, CONVERT(varchar(30), id))) AS salary from t_employees

select * from v_employees

select * from sys.openkeys

This was done before and only once, as it couldnt open the symmetric key without the master key being open first.

open master key decryption by password = 'xxxkickasspasswordxxxx';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||Both your steps and the SQL look right. Is this issue still occurring? What version are you using? (SELECT @.@.version).|||

Problem still occuring.

Version is (on the standby box) - SQL2005 Dev Edition on Windows Server 2000

On the main box it is SQL2005 Dev Edition on Windows 2003 Enterprise SP1

I got the Enterprise Editions of SQL2005 (which will go on the live systems) from MSDN and will try that as well, maybe it's something with the dev edition?

Thanks

|||

There shouldn't be any difference between Developer and Enterprise regarding encryption. Could you also specify how you created the certificate and the symmetric key?

Thanks
Laurentiu

|||

Hi there,

I create the cert and key using this (using Triple Des since the standby machine is Windows 2000 and doesnt do AES)

create certificate cert_sk_td3 with subject = 'Certificate'

create symmetric key sk_td3 with algorithm = triple_des encryption by certificate cert_sk_td3;

Let me know if you need any more info

|||

In your scenario, which is the machine on which the decryption fails after restore? Is it the 2000 machine or the 2003 one? (I'm not sure what you mean by the standby machine)

Thanks
Laurentiu

|||

Hi Laurentiu,

In the interim I have upgradede both SQL2005s from Dev Edition to Enterprise Edition (from the MSDN Subscription downloads)

I am going from a Server2003 (SP1 and hotfixes) to a Server2000(SP4 and all hotfixes). It is on the 2000 Server that I cant decrypt (I am using TripleDES to test since AES isnt supported on 2000).

Is there any trace or debugging that I can enable that might give some more info?

sql

Encryption and recovery on standby machine

Hi there,

to follow on my question in the other forum, I have a database with triple des encrypted columns

Master key created, certificate and symmetric key using certificate. Values inserted into table (encrypted)

Can select etc, all works fine.

Can someone please tell me exactly what is needed to view this on the 2ndary box? And what I might be missing?

Thanks

If you opened the master key using a password, things should also work with no change.

Please send me more details on the steps that you are following, and I'll be able to help you more specifically. Also, please copy-paste the text and number of any error that you encounter.

Thanks
Laurentiu

|||

Hi,

Thanks Laurentiu.

I added the SMK encrypted to the database masterkey, and I was able to succesfully view decrypted information on the secondary node.

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||

Thanks
Laurentiu

|||

Hi,

Ok source database -

create master key with password

create certificate

create symmetric key with encryption by certificate created above

open key

insert values into table (using encryptbykey)

create view that that uses decryptbykey, all works fine.

Backup database

Restore on 2nd machine

Open the master key

Alter master key add encryption by service master key

After this I open the symmetric key and try to decrypt but everything gets returned as NULLs.

Will run through this again and see if I missed anything.

|||

The steps you followed seem correct. Let us know if you've also been unable to decrypt the second time.

One simple suggestion: verify that the symmetric key is indeed opened (select * from sys.openkeys).

Thanks
Laurentiu

|||

Hi,

Tried it just now and the values decrypted fine. Havent touched the server since the post was made, and did not run any other scripts on it. Weird.,,

Will leave the server "as is" for now and check it again later to see if it's successfull then.

This made me think of some extra security questions, will post in a seperate thread :)

|||

You should be able to open the symmetric key without opening the master key if you added the service master key encryption to it.

Thanks
Laurentiu

|||

This morning, check it out and the restored database is decrypting information... again no changes done, server just left overnight.

This is what I used to check the information/decrypt - you'll recognise it from your blog as I was using snippets of your code as part of this :)

If you need any other info, please let me know.

open symmetric key sk_player decryption by certificate cert_sk_admin;

select * from sys.openkeys

select id,name, CONVERT(varchar(10), decryptbykey(salary, 1, CONVERT(varchar(30), id))) AS salary from t_employees

select * from v_employees

select * from sys.openkeys

This was done before and only once, as it couldnt open the symmetric key without the master key being open first.

open master key decryption by password = 'xxxkickasspasswordxxxx';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||Both your steps and the SQL look right. Is this issue still occurring? What version are you using? (SELECT @.@.version).|||

Problem still occuring.

Version is (on the standby box) - SQL2005 Dev Edition on Windows Server 2000

On the main box it is SQL2005 Dev Edition on Windows 2003 Enterprise SP1

I got the Enterprise Editions of SQL2005 (which will go on the live systems) from MSDN and will try that as well, maybe it's something with the dev edition?

Thanks

|||

There shouldn't be any difference between Developer and Enterprise regarding encryption. Could you also specify how you created the certificate and the symmetric key?

Thanks
Laurentiu

|||

Hi there,

I create the cert and key using this (using Triple Des since the standby machine is Windows 2000 and doesnt do AES)

create certificate cert_sk_td3 with subject = 'Certificate'

create symmetric key sk_td3 with algorithm = triple_des encryption by certificate cert_sk_td3;

Let me know if you need any more info

|||

In your scenario, which is the machine on which the decryption fails after restore? Is it the 2000 machine or the 2003 one? (I'm not sure what you mean by the standby machine)

Thanks
Laurentiu

|||

Hi Laurentiu,

In the interim I have upgradede both SQL2005s from Dev Edition to Enterprise Edition (from the MSDN Subscription downloads)

I am going from a Server2003 (SP1 and hotfixes) to a Server2000(SP4 and all hotfixes). It is on the 2000 Server that I cant decrypt (I am using TripleDES to test since AES isnt supported on 2000).

Is there any trace or debugging that I can enable that might give some more info?

Encryption and recovery on standby machine

Hi there,

to follow on my question in the other forum, I have a database with triple des encrypted columns

Master key created, certificate and symmetric key using certificate. Values inserted into table (encrypted)

Can select etc, all works fine.

Can someone please tell me exactly what is needed to view this on the 2ndary box? And what I might be missing?

Thanks

If you opened the master key using a password, things should also work with no change.

Please send me more details on the steps that you are following, and I'll be able to help you more specifically. Also, please copy-paste the text and number of any error that you encounter.

Thanks
Laurentiu

|||

Hi,

Thanks Laurentiu.

I added the SMK encrypted to the database masterkey, and I was able to succesfully view decrypted information on the secondary node.

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||

Thanks
Laurentiu

|||

Hi,

Ok source database -

create master key with password

create certificate

create symmetric key with encryption by certificate created above

open key

insert values into table (using encryptbykey)

create view that that uses decryptbykey, all works fine.

Backup database

Restore on 2nd machine

Open the master key

Alter master key add encryption by service master key

After this I open the symmetric key and try to decrypt but everything gets returned as NULLs.

Will run through this again and see if I missed anything.

|||

The steps you followed seem correct. Let us know if you've also been unable to decrypt the second time.

One simple suggestion: verify that the symmetric key is indeed opened (select * from sys.openkeys).

Thanks
Laurentiu

|||

Hi,

Tried it just now and the values decrypted fine. Havent touched the server since the post was made, and did not run any other scripts on it. Weird.,,

Will leave the server "as is" for now and check it again later to see if it's successfull then.

This made me think of some extra security questions, will post in a seperate thread :)

|||

You should be able to open the symmetric key without opening the master key if you added the service master key encryption to it.

Thanks
Laurentiu

|||

This morning, check it out and the restored database is decrypting information... again no changes done, server just left overnight.

This is what I used to check the information/decrypt - you'll recognise it from your blog as I was using snippets of your code as part of this :)

If you need any other info, please let me know.

open symmetric key sk_player decryption by certificate cert_sk_admin;

select * from sys.openkeys

select id,name, CONVERT(varchar(10), decryptbykey(salary, 1, CONVERT(varchar(30), id))) AS salary from t_employees

select * from v_employees

select * from sys.openkeys

This was done before and only once, as it couldnt open the symmetric key without the master key being open first.

open master key decryption by password = 'xxxkickasspasswordxxxx';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||Both your steps and the SQL look right. Is this issue still occurring? What version are you using? (SELECT @.@.version).|||

Problem still occuring.

Version is (on the standby box) - SQL2005 Dev Edition on Windows Server 2000

On the main box it is SQL2005 Dev Edition on Windows 2003 Enterprise SP1

I got the Enterprise Editions of SQL2005 (which will go on the live systems) from MSDN and will try that as well, maybe it's something with the dev edition?

Thanks

|||

There shouldn't be any difference between Developer and Enterprise regarding encryption. Could you also specify how you created the certificate and the symmetric key?

Thanks
Laurentiu

|||

Hi there,

I create the cert and key using this (using Triple Des since the standby machine is Windows 2000 and doesnt do AES)

create certificate cert_sk_td3 with subject = 'Certificate'

create symmetric key sk_td3 with algorithm = triple_des encryption by certificate cert_sk_td3;

Let me know if you need any more info

|||

In your scenario, which is the machine on which the decryption fails after restore? Is it the 2000 machine or the 2003 one? (I'm not sure what you mean by the standby machine)

Thanks
Laurentiu

|||

Hi Laurentiu,

In the interim I have upgradede both SQL2005s from Dev Edition to Enterprise Edition (from the MSDN Subscription downloads)

I am going from a Server2003 (SP1 and hotfixes) to a Server2000(SP4 and all hotfixes). It is on the 2000 Server that I cant decrypt (I am using TripleDES to test since AES isnt supported on 2000).

Is there any trace or debugging that I can enable that might give some more info?

Wednesday, March 21, 2012

Encrypting SPs and maybe triggers, how?

Don't want my sa to be able to tinker with Stored procedures or triggers on
the production machine. I saw some software that had them encrypted. Could
not go in and see their text. How can this be done?
Thanks for any help.
BobUse the WITH ENCRYPTION clause. However, I believe there are tools
available on the Internet to unencrypt the text. Also, note that once
encrypted the object cannot be scripted out for other purposes. Be sure to
save the original DDL in a secure location if needed in the future.
HTH
Jerry
"RDufour" <rdufour@.sgiims.com> wrote in message
news:O9FtvxuuFHA.4040@.TK2MSFTNGP10.phx.gbl...
> Don't want my sa to be able to tinker with Stored procedures or triggers
> on
> the production machine. I saw some software that had them encrypted. Could
> not go in and see their text. How can this be done?
> Thanks for any help.
> Bob
>

Encrypting SPs and maybe triggers, how?

Don't want my sa to be able to tinker with Stored procedures or triggers on
the production machine. I saw some software that had them encrypted. Could
not go in and see their text. How can this be done?
Thanks for any help.
Bob
Use the WITH ENCRYPTION clause. However, I believe there are tools
available on the Internet to unencrypt the text. Also, note that once
encrypted the object cannot be scripted out for other purposes. Be sure to
save the original DDL in a secure location if needed in the future.
HTH
Jerry
"RDufour" <rdufour@.sgiims.com> wrote in message
news:O9FtvxuuFHA.4040@.TK2MSFTNGP10.phx.gbl...
> Don't want my sa to be able to tinker with Stored procedures or triggers
> on
> the production machine. I saw some software that had them encrypted. Could
> not go in and see their text. How can this be done?
> Thanks for any help.
> Bob
>

Wednesday, March 7, 2012

Enabling Network Protocols in MSDE 2000

Hello,
I installed MSDE 2000 it's not accepting connections from
any machine i think the network protocols got disabled is
there a way to enable them rather than installing again
MSDE installs a tool called srvnetcn.exe that can be used to configure the
network protocols of an MSDE instance. It is located in
Program Files\Microsoft SQL Server\80\Tools\Binn
Jim
"sch" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4e501c41d83$c6d49b80$a101280a@.phx.gbl...
> Hello,
> I installed MSDE 2000 it's not accepting connections from
> any machine i think the network protocols got disabled is
> there a way to enable them rather than installing again
|||Hi Jim,
Actually svrnetcn.exe.
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Jim Young" <thorium48@.hotmail.com> wrote in message
news:OYHIoBaHEHA.2300@.tk2msftngp13.phx.gbl...
> MSDE installs a tool called srvnetcn.exe that can be used to configure the
> network protocols of an MSDE instance. It is located in
> Program Files\Microsoft SQL Server\80\Tools\Binn
> Jim
>
> "sch" <anonymous@.discussions.microsoft.com> wrote in message
> news:1a4e501c41d83$c6d49b80$a101280a@.phx.gbl...
>
|||Thanks Very Much Guys!
|||
>--Original Message--
>MSDE installs a tool called srvnetcn.exe that can be
used to configure the
>network protocols of an MSDE instance. It is located in
>Program Files\Microsoft SQL Server\80\Tools\Binn
>Jim
>
>"sch" <anonymous@.discussions.microsoft.com> wrote in
message[color=darkblue]
>news:1a4e501c41d83$c6d49b80$a101280a@.phx.gbl...
from[color=darkblue]
is
>
>.
>works great, thanks Jim

Friday, February 24, 2012

Enable SQL Server 2005 Remote Connections in Windows 2000

Hi,
I setup SQL Server 2005 on a Windows 2000 Professional machine. The
MSSQLSERVER service seems to be running fine in the beginning but does
not restart if I enable connections via TCP/IP.
I have this problem only on a Windows 2000 machine and it works fine if
I setup SQL Server 2005 on a Windows XP machine.
- ramadu
:
> Thanks for your response Sri,
> Hope that will help you resolve the problem. If you meet any further
> problem, please feel free to post here.
> Regards,
> Steven Cheng
> Microsoft MSDN Online Support Lead
>
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>Hi Ramadu,
Thank you for your post.
Since Steven is Out of Office, I will take response of this issue.
Would you please provide some detailed information for me to troubleshoting?
1. Please let me know if there is any error in Event log when you start SQL
Server.
2. Please let me know if there is any error message in the SQL error log.
By default, the error log is located at Program Files\Microsoft SQL
Server\MSSQL.n\MSSQL\LOG\ERRORLOG
Please let me know the result and so that I can provide further assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.

enable remote errors option?

Hello,

Reporting Services 2005 error messagess always state that for details the report should be run on the local machine or that remote errors should be enabled.

It might be a silly question, but how does one enable the remote errors?
I searched the BOL and the net but found no clues, just many error samples Big Smile

We are facing situations where we can not log onto the server and rune the report from localhost/Reports....

ThanksNot sure but sounds like a .net error - it may be in the .config files?
CustomErrors="RemoteOnly" should be CustomErrors="Off" or something to that effect.|||

You could set the "EnableRemoteErrors" configuration value to True in the ReportServer.ConfigurationInfo database table.

Alternatively, you can use the SetSystemProperties SOAP call:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_63le.asp

-- Robert

|||Hi Robert,

Thanks. Thats what I was looking for. How about adding that to the Site settings form on the report manager?

P.S. I had to restart iis (5.0 ...) to make it take effect.

Thanks again for the quick reply

Sunday, February 19, 2012

enable advanced performance - no UPS

Hi!
I just enabled disk cache and advanced performance on our SQL-server
machine's disks. The computer doesn't have any UPS, so I guess I could be in
trouble if there is a power outage. But how serious is this? The machine is
a dedicated sql server which mainly serves as a development machine. We do
however store some license information in the sql-server, that database is
however backed up on a regular basis.
Which are the worst case scenario?
- Re-install Win. Serv. 2003?
- Re-install SQL-server?
- Some corrupted/lost datarows in the database if someone worked on it when
the outage happened?
- Complete loss of all data?
- Unusable database files?
Regards,
Peterhi Peter,
I consider that "complete loss of all data" is the worst situation for
anyone, but i was thinking about UPS...Listen to me, even in Spain, lots of
organizations own an UPS, I can't believe it!! It's cheaper, isn't?
--
current location: alicante (es)
"Peter HartlÃ©n" wrote:
> Hi!
> I just enabled disk cache and advanced performance on our SQL-server
> machine's disks. The computer doesn't have any UPS, so I guess I could be in
> trouble if there is a power outage. But how serious is this? The machine is
> a dedicated sql server which mainly serves as a development machine. We do
> however store some license information in the sql-server, that database is
> however backed up on a regular basis.
> Which are the worst case scenario?
> - Re-install Win. Serv. 2003?
> - Re-install SQL-server?
> - Some corrupted/lost datarows in the database if someone worked on it when
> the outage happened?
> - Complete loss of all data?
> - Unusable database files?
> Regards,
> Peter
>
>|||"Peter Hartlén" <peter@.data.se> wrote in message
news:OE9flvDSGHA.4384@.tk2msftngp13.phx.gbl...
> Hi!
> I just enabled disk cache and advanced performance on our SQL-server
> machine's disks. The computer doesn't have any UPS, so I guess I could be
> in trouble if there is a power outage. But how serious is this? The
> machine is a dedicated sql server which mainly serves as a development
> machine. We do however store some license information in the sql-server,
> that database is however backed up on a regular basis.
> Which are the worst case scenario?
> - Re-install Win. Serv. 2003?
> - Re-install SQL-server?
> - Some corrupted/lost datarows in the database if someone worked on it
> when the outage happened?
> - Complete loss of all data?
> - Unusable database files?
>
Realistically, the worst case is that you will need to recover your
databsaes from backup.
However, you should NEVER do this either in production or development. The
reasons for doing it in production are obvious.
In development turning on write cashing on disks badly distorts the
performance characteristics of the databsae by eliminating the cost of log
flushing. This can leave perforance problems to arise in production when
you wonder why trying to do 2000 single-row inserts without a transaction is
slow.
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> skrev i
meddelandet news:Oj8LdKGSGHA.4920@.tk2msftngp13.phx.gbl...
> Realistically, the worst case is that you will need to recover your
> databsaes from backup.
> However, you should NEVER do this either in production or development.
> The reasons for doing it in production are obvious.
> In development turning on write cashing on disks badly distorts the
> performance characteristics of the databsae by eliminating the cost of log
> flushing. This can leave perforance problems to arise in production when
> you wonder why trying to do 2000 single-row inserts without a transaction
> is slow.
> David
I'm not sure I follow, you say "caching distorts the performance
characteristics of the database", but a 1.5min compared to 12min import
(almost 10 times slower) quite clearly indicates that there is a huge
performance benefit using caching.
Are you saying that after a while, the performance will decrease when using
caching because it messes up the log flushing? Are you even saying caching
invalidates the transaction log characteristics of a database?
Best regards,
Peter|||"Peter Hartlén" <peter@.data.se> wrote in message
news:uqUda1ZSGHA.1688@.TK2MSFTNGP11.phx.gbl...
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> skrev i
> meddelandet news:Oj8LdKGSGHA.4920@.tk2msftngp13.phx.gbl...
>> Realistically, the worst case is that you will need to recover your
>> databsaes from backup.
>> However, you should NEVER do this either in production or development.
>> The reasons for doing it in production are obvious.
>> In development turning on write cashing on disks badly distorts the
>> performance characteristics of the databsae by eliminating the cost of
>> log flushing. This can leave perforance problems to arise in production
>> when you wonder why trying to do 2000 single-row inserts without a
>> transaction is slow.
>> David
> I'm not sure I follow, you say "caching distorts the performance
> characteristics of the database", but a 1.5min compared to 12min import
> (almost 10 times slower) quite clearly indicates that there is a huge
> performance benefit using caching.
No that's a design flaw in your application. See below.
> Are you saying that after a while, the performance will decrease when
> using caching because it messes up the log flushing?
No.
> Are you even saying caching invalidates the transaction log
> characteristics of a database?
Yes. And you generally will not be able to use such caching in production.
So you shouldn't in development.
If you see a 10x difference with write caching, you probably have an
application problem. The most common such problem is flushing the
transaction log too often. You can only flush the log so many times a
second. If you insist on flushing the log after each row of the import
(which is what happens when you don't use a transaction), then you will
severly limit the throughput of your application.
You might well miss this design flaw on a development system which has write
caching enabled.
David|||Hi David, thanks for your reply!
> If you see a 10x difference with write caching, you probably have an
> application problem. The most common such problem is flushing the
> transaction log too often. You can only flush the log so many times a
> second. If you insist on flushing the log after each row of the import
> (which is what happens when you don't use a transaction), then you will
> severly limit the throughput of your application.
> You might well miss this design flaw on a development system which has
> write caching enabled.
>
I am not an expert when it comes to writing transactional database code, but
I think I have a fairly good grip on the basic functionality.
I know my development machine (using MSDE) has write cache, but our
testserver didn't. My code, starts a transaction at the beginning of the
import and commits it at the end of the transaction (not using any nested
transactions, should I?), unless something went wrong.
I am not sure what you mean by flushing the log, I only start a transaction
and commit it.
The test was performed on the testserver, without, and later on with, write
cache enabled. The code never changed, nor did the hardware itself, only the
write cache, and I got a 10x improvment when enabling write cache.
Perhaps my "single transaction of the entire import" is bad practice, should
I use one large and many smaller transactions during the import?
Are you saying write cache shouldn't impose a 10x performance benefit on a
SQL-server?
Thanks,
Peter|||"Peter Hartlén" <peter@.data.se> wrote in message
news:eOqfoXZTGHA.5500@.TK2MSFTNGP12.phx.gbl...
> Hi David, thanks for your reply!
>> If you see a 10x difference with write caching, you probably have an
>> application problem. The most common such problem is flushing the
>> transaction log too often. You can only flush the log so many times a
>> second. If you insist on flushing the log after each row of the import
>> (which is what happens when you don't use a transaction), then you will
>> severly limit the throughput of your application.
>> You might well miss this design flaw on a development system which has
>> write caching enabled.
> I am not an expert when it comes to writing transactional database code,
> but I think I have a fairly good grip on the basic functionality.
> I know my development machine (using MSDE) has write cache, but our
> testserver didn't. My code, starts a transaction at the beginning of the
> import and commits it at the end of the transaction (not using any nested
> transactions, should I?), unless something went wrong.
> I am not sure what you mean by flushing the log, I only start a
> transaction and commit it.
> The test was performed on the testserver, without, and later on with,
> write cache enabled. The code never changed, nor did the hardware itself,
> only the write cache, and I got a 10x improvment when enabling write
> cache.
> Perhaps my "single transaction of the entire import" is bad practice,
> should I use one large and many smaller transactions during the import?
No. One single transaction is just right. The mistake most people make is
omiting the transaction, and letting each statement commit by itself.
> Are you saying write cache shouldn't impose a 10x performance benefit on a
> SQL-server?
Yes. The only time write caching should makes a huge difference is when the
application is flushing the log (commiting transactions) too often.
What sort of performance numbers are you seeing? How are you doing the
import.
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23EZWYAdTGHA.736@.TK2MSFTNGP12.phx.gbl...
> "Peter Hartlén" <peter@.data.se> wrote in message
> news:eOqfoXZTGHA.5500@.TK2MSFTNGP12.phx.gbl...
>> Hi David, thanks for your reply!
>> If you see a 10x difference with write caching, you probably have an
>> application problem. The most common such problem is flushing the
>> transaction log too often. You can only flush the log so many times a
>> second. If you insist on flushing the log after each row of the import
>> (which is what happens when you don't use a transaction), then you will
>> severly limit the throughput of your application.
>> You might well miss this design flaw on a development system which has
>> write caching enabled.
>>
>> I am not an expert when it comes to writing transactional database code,
>> but I think I have a fairly good grip on the basic functionality.
>> I know my development machine (using MSDE) has write cache, but our
>> testserver didn't. My code, starts a transaction at the beginning of the
>> import and commits it at the end of the transaction (not using any nested
>> transactions, should I?), unless something went wrong.
>> I am not sure what you mean by flushing the log, I only start a
>> transaction and commit it.
>> The test was performed on the testserver, without, and later on with,
>> write cache enabled. The code never changed, nor did the hardware itself,
>> only the write cache, and I got a 10x improvment when enabling write
>> cache.
>> Perhaps my "single transaction of the entire import" is bad practice,
>> should I use one large and many smaller transactions during the import?
> No. One single transaction is just right. The mistake most people make
> is omiting the transaction, and letting each statement commit by itself.
>> Are you saying write cache shouldn't impose a 10x performance benefit on
>> a SQL-server?
> Yes. The only time write caching should makes a huge difference is when
> the application is flushing the log (commiting transactions) too often.
> What sort of performance numbers are you seeing? How are you doing the
> import.
>
Let me explain a bit more.
When when a user needs to make a large number of changes to a database, as
in an import, there are four different places the changes have to be made:
The database pages in memory, the database files, the log records in memory
and the log file. The changes are made immediately to the database pages in
memory and the log records in memory. Background processes will then write
(or "flush") the changes to the files on disk. When you commit a
transaction, you must wait for any changes made to the log records in memory
to be flushed to disk.
If you are commiting after every statement, which is what happens if you
don't use an explicit transaction, then you must wait for the log file to be
written after each statement. This is the typical case where using write
caching on the disk will make a big difference.
If you do use a transaction, then the changes to the database and log in
memory are written out by background processes. These background processes
don't really benefit from write caching on the disks since they are writing
out large amounts of data. So if you have good transaction scoping, I am
surprised that you see a big performance difference with write caching
enabled.
David|||Do I fill stupid now or what, I was so sure I hade the file read operation
as a transaction, but it was the second operation, when updating the main
tables with the data in the temporary tables.
Adding transaction to the file read operation improved the import time to
near 2min compared to 12min.
Thanks a lot for you patience and explanations David!
Regards,
Peter
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> skrev i
meddelandet news:e5NXJMdTGHA.5884@.TK2MSFTNGP14.phx.gbl...
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23EZWYAdTGHA.736@.TK2MSFTNGP12.phx.gbl...
>> "Peter Hartlén" <peter@.data.se> wrote in message
>> news:eOqfoXZTGHA.5500@.TK2MSFTNGP12.phx.gbl...
>> Hi David, thanks for your reply!
>> If you see a 10x difference with write caching, you probably have an
>> application problem. The most common such problem is flushing the
>> transaction log too often. You can only flush the log so many times a
>> second. If you insist on flushing the log after each row of the import
>> (which is what happens when you don't use a transaction), then you will
>> severly limit the throughput of your application.
>> You might well miss this design flaw on a development system which has
>> write caching enabled.
>>
>> I am not an expert when it comes to writing transactional database code,
>> but I think I have a fairly good grip on the basic functionality.
>> I know my development machine (using MSDE) has write cache, but our
>> testserver didn't. My code, starts a transaction at the beginning of the
>> import and commits it at the end of the transaction (not using any
>> nested transactions, should I?), unless something went wrong.
>> I am not sure what you mean by flushing the log, I only start a
>> transaction and commit it.
>> The test was performed on the testserver, without, and later on with,
>> write cache enabled. The code never changed, nor did the hardware
>> itself, only the write cache, and I got a 10x improvment when enabling
>> write cache.
>> Perhaps my "single transaction of the entire import" is bad practice,
>> should I use one large and many smaller transactions during the import?
>> No. One single transaction is just right. The mistake most people make
>> is omiting the transaction, and letting each statement commit by itself.
>>
>> Are you saying write cache shouldn't impose a 10x performance benefit on
>> a SQL-server?
>> Yes. The only time write caching should makes a huge difference is when
>> the application is flushing the log (commiting transactions) too often.
>> What sort of performance numbers are you seeing? How are you doing the
>> import.
>
> Let me explain a bit more.
> When when a user needs to make a large number of changes to a database, as
> in an import, there are four different places the changes have to be made:
> The database pages in memory, the database files, the log records in
> memory and the log file. The changes are made immediately to the database
> pages in memory and the log records in memory. Background processes will
> then write (or "flush") the changes to the files on disk. When you commit
> a transaction, you must wait for any changes made to the log records in
> memory to be flushed to disk.
> If you are commiting after every statement, which is what happens if you
> don't use an explicit transaction, then you must wait for the log file to
> be written after each statement. This is the typical case where using
> write caching on the disk will make a big difference.
> If you do use a transaction, then the changes to the database and log in
> memory are written out by background processes. These background
> processes don't really benefit from write caching on the disks since they
> are writing out large amounts of data. So if you have good transaction
> scoping, I am surprised that you see a big performance difference with
> write caching enabled.
> David
>

Monday, March 26, 2012

Encryption Key

Thursday, March 22, 2012

Encryption and recovery on standby machine

Encryption and recovery on standby machine

Encryption and recovery on standby machine

Encryption and recovery on standby machine

Wednesday, March 21, 2012

Encrypting SPs and maybe triggers, how?

Encrypting SPs and maybe triggers, how?

Wednesday, March 7, 2012

Enabling Network Protocols in MSDE 2000

Friday, February 24, 2012

Enable SQL Server 2005 Remote Connections in Windows 2000

enable remote errors option?

Sunday, February 19, 2012

enable advanced performance - no UPS

Empty Column

Blog Archive

About Me