Getting error in sql server 2022

• zacharyfull

  Valued Member

  Points: 62

  More actions

  March 6, 2025 at 9:23 am

  #4552418

  Hi folks

  We have migrated database from sql server 2014 std to sql server 2022 std. database get restored and old server is sql service is stopped.

  Now after few initial operation we are facing this error "Please create master key in the database or open master key in session"

  as checked on net restored database is encrypted by database master key but we donot know its password

  Any suggestion how to proceed ahead with any loss of data

  sprunki phase 4

• Mary_Jen

  Grasshopper

  Points: 15

  More actions

  March 6, 2025 at 10:21 am

  #4552451

  Your issue happens because the restored database has encrypted objects that need the master key. Since you don’t have the original password, try this:

  First, check if the master key exists with SELECT * FROM sys.symmetric_keys WHERE name = ##MS_DatabaseMasterKey##.

  If it does, try OPEN MASTER KEY DECRYPTION BY PASSWORD = your_password. If you don’t know it, you’ll need to restore it from a backup using RESTORE MASTER KEY FROM FILE = path_to_backup DECRYPTION BY PASSWORD = old_password ENCRYPTION BY PASSWORD = new_password. If no backup exists, create a new master key with CREATE MASTER KEY ENCRYPTION BY PASSWORD = new_secure_password, but be aware that you might need to re-encrypt dependent objects.

• Emperor100

  SSCrazy

  Points: 2212

  More actions

  March 6, 2025 at 8:58 pm

  #4552846

  @zacharyfull:

  Is it SSISDB by any chance?

  =======================================================================