Post reply

Symmetric Key issue with SSISDB

  • February 3, 2015 at 3:31 pm

    #303028

    I have an odd one. My team has recently been tasked with creating some new lower environment database servers for one of our DEV groups. The group in particular uses a lot of SSIS packages for most of their jobs. The SSISDB was brought over to the new designated VM (along with all other db's in that instance), but I have run into an odd issue with regard to the symmetric key encryption on the database (which I did not realize at the time was needing to be restored from a file in order to get it working again). The Symmetric keys are under the database as you would expect them to be, you can even see the keys when querying sys.symmetric_keys, but what is odd is when I try to back up the Master Key, it give me the following error:

    Msg 15581, Level 16, State 7, Line 1

    Please create a master key in the database or open the master key in the session before performing this operation.

    So I made sure I wasn't imagining things, and ran the query again on sys.symmetric_keys, and sure enough - I see my Master Key and other keys on the SSISDB.

    So I go over to the PROD server to see if I can just get the Master Key from it, but the keys are not there! And also - when looking at sys.symmetric_keys, there are no keys listed at all (not against SSISDB, master, MSDB, etc...).

    Everything works fine in PROD, but on this new system - none of the SQL jobs will run because they error out with:

    Environment reference Id: 3. Description: Please create a master key in the database or open the master key in the session before performing this operation. Source: .Net SqlClient Data Provider Started: 2:00:00 PM Finished: 2:00:01 PM Elapsed: 0.516 seconds. The package execution failed. The step failed.

    Anyone seen this before, or if you have any direction I can go in to resolve this (or additional information I may provide) it would be really appreciated.

  • February 3, 2015 at 4:52 pm

    #1775170

    Here is a bit more detail for better understanding. The snapshots below show you the QA and PROD systems that I am having the issue with, as well as the results and errors coming from each.

    QA SYSTEM

    sys.symmetric_keys

    You can plainly see in the snapshot above that the system has a Database Master Key for SSISDB. But then this is what happens when I attempt to backup the Master Key to a file:

    Master Key Backup

    Now - here is where it gets strange. I ran the same query for information on the symmetric keys out in PROD (thinking I could just back it up, and apply it to my new QA box, but look what happens):

    PROD SYSTEM

    sys.symmetric_keys

    Nothing there...and as such, this also turns out to be confirmed when attempting to backup what should be there by default (or do I have that wrong about SSISDB? Doesn't the system have symmetric key encryption applied by default, when it's created?):

    Master Key Backup

    Again, any and all help would be truly appreciated.

    Thanks!

  • February 3, 2015 at 5:56 pm

    #1775173

    After some light reading, I'd guess your issue is that the new machine has a different service master key, which won't work with your ssisdb master key.

    Assuming you know the ssisdb password, I am wondering if the solution from this

    alwayson article would do it for you.

    If you don't know the password, some details (and warnings) here[/url] on copying over the service master key

    As to why the query on prod would show no keys, what permissions did the login you used have on that database?

  • February 4, 2015 at 4:26 pm

    #1775511

    Hi Nevyn...

    The account in both database instances is a sysadmin equivalent account, using SQL authentication.

    The problem I am having now (after making a backup of the System Master Key from PROD and applying it to my new QA system) is everything from clr not being enabled to what looks like some sort of permissions issues on .NET assemblies.

    I am at the .NET assemblies issues at this point (having resolved the clr issue by simply enabling it in my instance). The errors point to things like database ownership needing to be changed to the 'sa' (which it already is), and setting TRUSTWORTHY ON for my database (which I am not very keen about), but in both efforts - my jobs are still failing.

    I have since put the TRUSTWORTHY setting back to OFF, and am discussing with various people on my end to see if the next steps are to simply backup and restore the database from PROD, and try the SMK again. I don't think that is going to fix my issue, but it is in discussion right now.

    We do not know what the Master Key password was set to for the SSISDB when it was initially set up (assuming one was applied at that time).

    Any other thoughts would be very welcome, and thank you again for everything thus far.

  • February 4, 2015 at 4:31 pm

    #1775512

    P.S. All errors are in the SQL Agent job histories to the jobs I am trying to get to work.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply