Back up your Keys !!

This morning I faced an interesting problem at a client. They had a whole bunch of data – driven subscriptions that had been running smoothly for months , but suddenly stopped working. The error message was "Logon Failed".

Well , I immediatly checked all usernames and passwords , those of the reporting services service, the jobs that were created and I even tried to reset the account used to connect to the database and the unattended execution account by using the RSConfig command. All to no avail. I decided that something must have become corrupt , so I decided to delete the encryption keys and start over. You can do this by using the RsKeyMgmt -d command.

You will however lose some of your stored settings , such as authentication information stored locally in reports for subscription purposes. Therefore , the golden rule is , back up your encryption keys regularly,so when tragedy strikes , you just delete the corrupt ones and restore the working backup. You can do this by using the following command :

RsKeyMgmt -e -f c:\key.snk -p<password>

These are some of the tags :

To restore a previously backed up key you go :

RsKeyMgmt -a -f c:\key.snk -p<password>

Might spare you a headache……

Update for SQL 2005 Service Pack 2

There’s been an update for SQL Service pack 2. If you’ve downloaded SP2 before the 5th March 2007 , you’ll need to apply the following update :

Knowledge base article

Don’t forget the OLE DB connection in Reporting Services 2005

So I hear everyone complaining about the poor support for Analysis Services in Reporting Services 2005.

 

What I’ve found is that for better performance you have to keep in mind the following :

 

1) The Analysis Services connection doesn’t generate the best MDX. It’s somewhat like the HTML that Word generates ( ok , maybe not that bad ).

2) In Reporting Services 2000 , we didn’t have it. So we created an OLE DB connection to Analysis Services, wrote our own MDX and the speed was quite good. Of course you still have the resultset "converted" into a SQL style resultset, you have to work around that.

 

Select an OLE DB connection , then click edit to get to the screen below.

Use the OLE DB provider for Analysis Services 9.0. You can then create a dataset the way you would normally create a SQL dataset , and then insert your MDX , as shown below.

You then get a normal reporting services dataset which you can use in a table or matrix. To use Paramaters , you need to create the MDX as a string , then spilt it where the parameter goes. see below :

This will customize the MDX depending on the selection the user has made. So you get the following result :

So if you want better performance connecting to AS from RS 2005 , don’t forget that this option is still available.It’s something to consider if you’re having performance issues using the standard connection