Showing posts with label configuration. Show all posts
Showing posts with label configuration. Show all posts

Monday, March 26, 2012

Encryption Keys (Reporting Services Configuration)

Ok I am playing around with SQL Server 2005 got it all setup. Then I decided to change the admin password and run dcpromo. Now I got all the issues resolved except this one. It had a key from what the server use to be called. I deleted it to make a new key for the state of the server now. Well big mistake I am finding out. Below are the only real directions I have found to recreate the key and it is not a lot of help. Does anyone know how to do this? Not like SQL Server 2000 to many choices and I got click happy on over load. Thanks in advance...

SQL Server Setup Help

Encryption Keys (Reporting Services Configuration)
Delete
Deletes the symmetric key and all encrypted content, including connection strings and stored credentials. You should only delete the symmetric key if you cannot restore it.

Once you delete the symmetric key, you must re-enter the missing connection strings and stored credentials in the reports and shared data sources that no longer have these values. You must also update all subscriptions that use delivery extensions that store encrypted data. This includes the file share delivery extension and any third-party delivery extension that use encrypted value.

There is no automated way to update this information. Each report, subscription, and shared data source that uses stored credentials and connection strings must be updated one at a time.

Initialization (Reporting Services Configuration)
Remove
Click Remove to remove the encryption keys of the selected report server instance from the report server database. You can remove keys to remove a report server from a scale-out deployment. With this option, only the encryption keys for the specified report server instance are removed. Encrypted data in the report server database is not affected.

As a precaution, be sure to create a backup copy of the symmetric key before you remove it. Once you remove the encryption keys of the last report server in the list, you introduce new requirements for any subsequent report server initialization for that database. The new requirement is that after you initialize a report server you must restore a backup copy of the symmetric key. Restoring the symmetric key is necessary if you want to access the encrypted data that is currently in the report server database.

If you no longer need the encrypted data or if you do not have a backup copy of the key, you must delete the encrypted data. For more information, see Encryption Keys (Reporting Services Configuration).

Event Viewer Log

Event Type: Error
Event Source: Report Server Windows Service (MSSQLSERVER)
Event Category: Management
Event ID: 107
Date: 2/9/2006
Time: 11:13:32 PM
User: N/A
Computer: VM-IPDG3
Description:
Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database.


If you don't have a backup of your key and you've deleted it, then the only thing you could do is to create a new key. From the "Encryption keys" panel in the config tool, click "change". By now you've lost all secure information, so you should reenter them. You need to redo the "Database setup" panel, and the credentials for data sources.|||

James Wu

Thanks for the reply I will try this when I get home. On the "Encyption Keys" panel in the configure reporting sections if memory serves me. The Change was disabled and the only two buttons out of the four I could click were Restore and Delete. The Change and Backup buttons were disabled. Can you supply a step by step to get where you are talking about.

I know if you go into

Microsoft SQL Server 2005|||

Yes your are right. "Change" and "Backup" will be disabled if your report server is not initialized. Here is a more detailed article on how to delete and recreate encryption keys :) http://msdn2.microsoft.com/en-us/library/ms156010.aspx

|||

James Wu

Thanks for the link I will research it now. I am using vmware so I reverted back a snapshot and started over. Made a few dumb errors that I should have been smarter not to do. Thanks Again

Encryption Key Errors

I got error as below:

Failure sending mail: The report server has encountered a configuration error. See the report server log files for more information.

Then i found error in log file as below:

ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information., AuthzInitializeContextFromSid: Win32 error: 5; possible reason - service account doesn't have rights to check domain user SIDs.;
Info:

Does that mean sth to do with SQL agent running account or SQl Server Report Service running account ?

Thanks

Nick

http://support.microsoft.com/?kbid=842423|||

Thanks for you information. Teo

when i change the Local account for running SQL Server Report Services to dominon account, then i got http://servername/reports to view report, then i got error below:

An error has occurred during report processing. (rsProcessingAborted)
The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) (rsRPCError)
Bad Data. (Exception from HRESULT: 0x80090005)

What i am understand is that when i create this report it's under local system account , so this report doesn't work when i change the user account. is that right? and how can i handle it ?

Cheers

Nick

|||You will need to re-initialize the server. Assuming RS 2005, use the Reporting Services Configuration (found under the Configuration Tools program group in Microsoft SQL Server 2005 group) to do so. Or, you can drop the decrypted content by running rskeymgmt -d (you will need to reset your data source connection strings after this).|||

when you say re-initialize the server , does that mean i need to delete the existing isntance from reporting service configruation , and create a new one. not just click the initialize button .

is that right understanding?

Cheers

Nick

|||By just clicking on the Initialization button in the Reporting Services Configuration tool.|||

i got erroer when i click initialize button in Report service configuration tool,

"Joining report server ac553f4....... to the web farm of the local instance" The task failed.

Exception Details"

ReportServicesConfigUI.WMIProvider.WMIProviderException: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject mo)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.InitializeReportServer(String installationId)

Thanks

Nick

|||it looks like the only option is to drop the encryption content using rskeymgmt -d. You will need to reset your data source credentials after this.|||

Hi all, I have seen that I have a similar problem with Encryption Key working with reports. My problem is that I′m working with VSt2003 and I have some reports made, but yesterday I was just testing some ASP.NET examples with VS2005. This morning when I continued working with reports in VS2003, I recieved this error:

The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled)

And it wasn′t possible to see the reports.

Do you now the way to fix this problem?

Thanks in Advance

|||I don't know what happened yesterday but somehow you managed to deactivate the server. Re-initializing ASP.NET (aspnet_regiis) will do the trick. Anyway, if you have backed up the encryption key (you did, didn't you?), now is the perfect time to use the Reporting Services Configuration utility and restore it. If you haven't, do rskeymgmt -d.sql

Wednesday, March 21, 2012

Encrypting the Configuration Settings in SSIS package

Hello Everybody,

I have developed a SSIS package to pull data from a remote SQL Server.

I have specified Database related settings in a dtsconfig file.

I was just wondering if we have any way of encrypting the config file so that only my package can read information out of it.

Or is there any other better way where i can store my database configuration (uid, pwd) so that it is not viewed by anyone.

please help me with this issude, thanks in advance...

Regards,

Sudhir Kesharwani

SSIS will not store passwords in configurations files.

Look up the ProtectionLevel property. There are a few settings there that would interest you.

sql

Encrypting the configuration file values stored in SQL server

Hi All,

I have the following requirement. I need to store the password for the connection manager in the configuration file. The sink for the configuration file is SQL Server. Though the password field appears as "******" the actual value is being taken as ""******" itself. If i update the SQL server table with the correct value, then the package starts working. But, the password is shown as clear text.

If i write logic to encrypt the password column in the configuration table, is there a way to tell the SSIS execute engine to decrypt the password before using the same for making the connection.

Is there a place holder, where i can write the decrypt code so that the decrypted password can be sent to the execution engine?

Thanks In Advance,

Madhu

I think the short answer to this is no, and no code hooks either.

I think though that there is also an argument, that says it would not be more secure than what you have now. If you encrypt the data, you need to then secure the key. So what will you do to secure the key? Why not use strong security to secure the password data instead of worrying about how to secure the key? I accept that the encryption adds an extra step, but I'm not convinced it will actually be any safer.

|||I'm not sure if it's a good idea, but couldn't he create a script task to decrypt the password and reset the connection manager's connectionstring property before the connection manager is used in the package?|||

Yes and no. Some connections are used before your script task could run, such as connections used for logging.

How would you secure the key used to decrypt the password? You need to secure the encryption/decryption key, so why not just secure the password to start with?

|||DarrenSQLIS is right the recommended way to do this is to store the password in the connection. SSIS will automatically encrypt these so that they are not stored in cleartext.|||Thanks for the thoughts Darren. As suggested by you, way to go is to store the password in SQL server and make sure that the access to the configuration table is only for administrators.|||

Denise, I think you are talking about the package level encryption, protection levels and such like. Nice though it is, it is not very useful, as I think you should "externalise" any kind of security information.

Using package encryption becomes unfeasible when you have to migrate packages between environments. Configurations solve that migration issue, but don't give you the encryption that is often seen as a requirement for some organisations. I'd argue that is should not be a big deal, secure the password so you don't have to worry about the key, but often it is an internal "standard" that must be complied with.

Still we have the choice of package encryption, which is better than not!

encrypting data of config table

hi. i need some help please. i had a problem with using configuration files in april ctp so i moved to sql server configuration which i found as a better solution for storing connection info since it can encrypt the password. this as far as i know as i'm moving from beta 1 version.

when creating the database at the client's server, the configuration table needs to be created as well and the data manually inserted using insert statements.

in this case, how do i enter the data in a way that is encrypted?

that's my question
thanks
I'm not sure I understand your question, but I think the answer is you can't do this. We don't support encrypting SQL Server configurations.|||I believe the question is just about writing data to an encrypted column. In that case, you should be able to just write values as you were writing them before, SQL Server would take care of encrypting the contents.

Sunday, March 11, 2012

Encrypted .dtsconfig files

Hi all,
Is there any plans in the pipeline to provide us a way of encrypting our configuration files.

I ask because I use configuration files everywhere in order to make my packages location independant but then you have to lock down access to those configuration files because they may have passwords in them.

I'd rather they were encrypted.

Thoughts?

-JamieJamie,
Have you thought about securing the config files by setting up NTFS permissions?
As a former Network Administrator, I found that it is much easier to manage resource access if it is centralized in one location. Personally, I would setup a group in Active Directory for users that need access to the config files and grant only that group to have read/write access to the folders containing your config files. (System Administrators still have full control and Backup Operators would have read access)
I would consider Encryption if the files are being sent over the wire and you want to make sure the file contents are not captured via a sniffer. It would be relatively easy to create a utitlity to encrypt and decrypt the files using the System.Security.Cryptography class.
Larry|||Hi Larry,
Yeah, that's what we're doing. I think the encryption feature would be nice as well tho.

-Jamie|||We considered an encryption feature, but didn't have time. It's a nice feature that we didn't get to.
K|||Right. Well you know what that means :)
http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=aebad475-0aa6-4fce-ba06-3ef42dd7856d|||

Jamie Thomson wrote:

Hi all,
Is there any plans in the pipeline to provide us a way of encrypting our configuration files.

I ask because I use configuration files everywhere in order to make my packages location independant but then you have to lock down access to those configuration files because they may have passwords in them.

I'd rather they were encrypted.

Thoughts?

-Jamie

You can store your configuration files on SQL Server; just as an alternative.

Wednesday, March 7, 2012

Enabling remote errors

Hi. I am trying to get remote errors to display in Report Manager. I've set the "EnableRemoteErrors" configuration value to True in the ReportServer.ConfigurationInfo table. Then I restarted both the Reporting Services Service and IIS. However, I am still seeing the following message when running a report:

  • An error has occurred during report processing.

    What am I missing?

    Thanks

    Has anyone had a chance to look into this? Please let me know if you need more information. Thanks.

    - Mark

    |||

    Check the reportserver logfiles (not the service logfiles!). They may contain more details about the error.

    The logfiles are typically located in: \Program Files\Microsoft SQL Server\MSSQL.x\Reporting Services\Logfiles

    -- Robert

    |||

    The problem has fixed itself. I am not sure how, but it did. I haven't made any changes. The only thing I did was reload my data processing extension. I doubt that's what fixed the problem because afterwards I was able to change the "EnableRemoteErrors" value back and forth and see the effect immediately, without restarting or reloading anything.

    Strange...

    |||

    Hi,

    I am pretty new to reporting services, can you please help me on how to set the 'EnableRemoteErrors' ?

    Thanks in advance.

    |||

    Please read this related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230593&SiteID=1

    -- Robert

  • Enabling remote errors

    Hi. I am trying to get remote errors to display in Report Manager. I've set the "EnableRemoteErrors" configuration value to True in the ReportServer.ConfigurationInfo table. Then I restarted both the Reporting Services Service and IIS. However, I am still seeing the following message when running a report:

  • An error has occurred during report processing.

    What am I missing?

    Thanks

    Has anyone had a chance to look into this? Please let me know if you need more information. Thanks.

    - Mark

    |||

    Check the reportserver logfiles (not the service logfiles!). They may contain more details about the error.

    The logfiles are typically located in: \Program Files\Microsoft SQL Server\MSSQL.x\Reporting Services\Logfiles

    -- Robert

    |||

    The problem has fixed itself. I am not sure how, but it did. I haven't made any changes. The only thing I did was reload my data processing extension. I doubt that's what fixed the problem because afterwards I was able to change the "EnableRemoteErrors" value back and forth and see the effect immediately, without restarting or reloading anything.

    Strange...

    |||

    Hi,

    I am pretty new to reporting services, can you please help me on how to set the 'EnableRemoteErrors' ?

    Thanks in advance.

    |||

    Please read this related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230593&SiteID=1

    -- Robert

  •