• Rod at work (4/4/2012)


    I think what happened was the former DBA, when he set up our current server for SQL Server, he just copied all of the report files from the old server, and plopped them into the folder directory for the new server (years ago). And I'm guessing that's why they've never worked on the current, production server.

    How do I fix it, given that scenario?

    If that is how he did it, and he did not do anything with the encryption key, it's not supposed to work. Is the old server still available and operational? Perhaps you can "backup" the encryption key there, and "restore" it to this server.

    Of course, before you go and change the key on the new server, make a backup copy of that, just in case things get worse.

    There are several reasons why this won't work. The encryption key must be backed up when anything dealing with credentials changes... like the service accounts used. The algorithm used to create the symmetric key, also uses the private and public keys on the server (those of the user performing the work). It then uses the password provided to encrypt the file where you backup the key.

    I am intimately involved in a similar situation this week. Perhaps we can help each other here.

    Thank-you,
    David Russell
    Any Cloud, Any Database, Oracle since 1982