none
SQL Always On re-enter product key RRS feed

  • Question

  • Hello,
    After a failover of the SQL Always On, starting the Designer gives me a request to re-enter the productkey. When failover back the message is gone. Failing back, the productkey box appears again. Connected to the availability group name during setup (Orchestrator 2012 SP1).
    Any ideas?
    Peter
    • Edited by Peter Stap Friday, May 31, 2013 1:01 PM
    Friday, May 31, 2013 12:59 PM

Answers

  • Hello,

    first I would like to make sure that we are talking about a test enviroment with no other systems/databases involved, correct?


    I'm not sure but I think you might need to use the
    FORCE
    Option
    (RESTORE SERVICE MASTER KEY FROM FILE = 'path_to_file' DECRYPTION BY PASSWORD = 'password' FORCE)
    on the second node
    because the decryption of the Orchestrator database key with the current Service Master key of the second node is not possible.

    But as I said before: I'm not sure!

    • Marked as answer by Peter Stap Friday, May 31, 2013 2:48 PM
    Friday, May 31, 2013 2:38 PM

All replies

  • Hello,

    did you synchronize the SQL Master Keys?

    They need to be the same on both SQL-Nodes for the encryption/decryption to work correctly after failover.

    You can export it from the machine where you installed Orchestrator and import it on the second node:

    http://msdn.microsoft.com/en-us/library/ms190337.aspx

    http://msdn.microsoft.com/en-us/library/ms187972.aspx

    Be careful: If there are already other databases that make use of encryption esp. on the second node these might not work correctly afterwards...



    • Edited by PIfM Friday, May 31, 2013 1:32 PM
    Friday, May 31, 2013 1:16 PM
  • Hello PIfM,

    Getting this error while restoring

    Msg 15320, Level 16, State 12, Line 1

    An error occurred while decrypting master key 'Orchestrator' 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.


    Friday, May 31, 2013 1:29 PM
  • Make sure that you are working with the Service Master Key and not the Database Master Key.

    Vaughn

    Friday, May 31, 2013 2:12 PM
  • BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file'
        ENCRYPTION BY PASSWORD = 'password'

    RESTORE SERVICE MASTER KEY FROM FILE = 'path_to_file'
        DECRYPTION BY PASSWORD = 'password'

    Gives me the error above...what am I doing wrong here. 

    Friday, May 31, 2013 2:18 PM
  • As described in http://technet.microsoft.com/en-us/library/hh913929.aspx :

    Sqlcmd –Q”BACKUP SERVICE MASTER KEY TO FILE ='C:\BACKUP\MASTER_KEY.BAK' ENCRYPTION BY PASSWORD = 'password'”

    Sqlcmd –Q “RESTORE SERVICE MASTER KEY FROM FILE = 'c:\temp_backups\keys\service_master_key' DECRYPTION BY PASSWORD = 'password'”


    www.sc-orchestrator.eu , Blog sc-orchestrator.eu

    Friday, May 31, 2013 2:25 PM
    Answerer
  • Hello Stefan,

    Also tried that, same error

    Msg 15320, Level 16, State 10, Server CTLSQL01, Line 1
    An error occurred while decrypting master key 'Orchestrator' that was encrypted
    by the old master key. The FORCE option can be used to ignore this error and con
    tinue the operation, but data that cannot be decrypted by the old master key wil
    l become unavailable.

    Friday, May 31, 2013 2:32 PM
  • Hello,

    first I would like to make sure that we are talking about a test enviroment with no other systems/databases involved, correct?


    I'm not sure but I think you might need to use the
    FORCE
    Option
    (RESTORE SERVICE MASTER KEY FROM FILE = 'path_to_file' DECRYPTION BY PASSWORD = 'password' FORCE)
    on the second node
    because the decryption of the Orchestrator database key with the current Service Master key of the second node is not possible.

    But as I said before: I'm not sure!

    • Marked as answer by Peter Stap Friday, May 31, 2013 2:48 PM
    Friday, May 31, 2013 2:38 PM
  • Hello PlFM,

    No test environment but a new installation. There are a few other databases on it but there is no encryption on that used. Added the FORCE option and it seems to work now. All other applications are also still working. Failover several times and now the designer is working, no productkey dialog anymore. Maybe I read it wrong but I couldn't find this step in any documentation about installing SC ORCH2012 SP1 on a Always On 2012 cluster.

    Thankx,

    Friday, May 31, 2013 2:48 PM
  • Hello,

    this is what the SQL Always On documentation mentions.

    Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server) (http://msdn.microsoft.com/en-us/library/ff878487.aspx):
    "If you use transparent data encryption (TDE), the service master key for creating and decrypting other keys must be the same on every server instance that hosts an availability replica for the availability Group"

    I agree: A hint in the documentation of Orchestrator would be very helpful.

    Friday, May 31, 2013 3:06 PM
  • Here're the steps I took to deal with this situation

    Issue:

    An error occurred while decrypting master key 'Orchestrator' that was encrypted by the old master key. The error was ignored because the FORCE option was specified.

    1. Restore service master key with FORCE

    Tried connecting from Orchestrator console and received an error "Could not connect to the database. Please verify your database configuration setting, and that the SQL Server service is running".

    2. Remove the DB from the Availability group and add it back on all the replicas.

    3. Tried reconnecting the application connection and it worked.

    We flipped the AG on all the replicas and tested the application and it connected to the listener without issues.

    Lesson learned

    Service Master Key must be applied when initially setting up the instances for AlwaysOn. It may be a bit of a pain to synchronize them after the databases are created. If the DBs are small-midsize then it's not so bad but with VLDBs it will be a slight challenge to restore them across all the replicas and sync the SMK (service master key).

    Wednesday, February 10, 2016 8:38 PM