October 7, 2020 at 5:46 pm
Hi all,
I now have an instance of SQL Server 2019 where the SSISDB database that holds the SSIS Catalog, does not have the master key open, and we don't have the password. There's no way to get the password, as the only personnel that might have known it have departed. Thus while I can create a new folder in the SSIS catalog, creating a new "environment" isn't possible right now. What I'd like to have is a query that can identify all the data that has actually been encrypted, so that I can at least script out all the packages and other items and be able to put it all back after I do the ALTER MASTER KEY FORCE REGENERATE. As this instance is a DEV environment as opposed to production, I'm not concerned with the loss of the encrypted data as much as I am with knowing exactly what will get lost when I do this, so that I can proactively identify what will need to be re-created. Does anyone have a query for this, or perhaps supply a link? My online searches haven't gotten very far.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 7, 2020 at 5:53 pm
Out of interest, what do you mean by 'does not have the master key open'? I've never had to deal with anything like that.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 7, 2020 at 6:56 pm
I get the following error message when trying to create an "environment" within a folder in the SSIS Catalog:
Msg 15581, Level 16, State 7, Line 17
Please create a master key in the database or open the master key in the session before performing this operation.
When I try to OPEN MASTER KEY DECRYPTION BY PASSWORD = N'passwordIdonthavegoeshere', I get the error that tells me that the key is not encrypted by that decryptor. So that's what I'm talking about. There are existing environments on this server, and I've created a new folder that I want to have as the one standard folder for all packages to "reference". Ideally, a query that can at least identify what will be lost when I try ALTER MASTER KEY FORCE REGENERATE ... would tell me which info will be lost so I can proactively get that info ahead of time and be able to put it all back after the forced regeneration.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 7, 2020 at 8:40 pm
I had same issue
Use Ssisdb
Go
Open master key decryption by password 'xxx'
Alter master key add encryption by service master key
Let me know if you still have an issue
October 7, 2020 at 8:50 pm
I had same issue
Use Ssisdb
Go
Open master key decryption by password 'xxx'
Alter master key add encryption by service master key
Let me know if you still have an issue
Your link gets a page not found error generated by Microsoft...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 7, 2020 at 9:00 pm
If you've got SQL Search at the ready, try searching SSISDB procs for the text 'EncryptByKey'. I think that will indicate most, if not all, of the encrypted columns.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 7, 2020 at 9:47 pm
Ummm... that's not quite what I'm trying to accomplish. While that will reveal the tables, I need to know how to join them together and such... However, I will can't try that out as I do don't have SQL Search.
EDIT: Thought I had it as I do have SQL Toolbelt. Either that or it's there under another cover somehow...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 7, 2020 at 10:45 pm
My apologies
Below is the link that fixed the issue for me
October 7, 2020 at 11:14 pm
this is very common when SSISDB was restored from another server. Please follow this article:
https://blog.pythian.com/how-to-restore-ssisdb-to-another-server-and-migrate-the-ssis-catalog/
October 8, 2020 at 1:06 pm
My apologies
Below is the link that fixed the issue for me
Again, this is not the nature of the problem. I will be unable to use the password that was initially used to encrypt the master key, as that's been permanently lost, and I don't even know whether or not the problem was caused by having SSISDB restored from a different server. None of that info is available and isn't ever going to be. Also understand that if I muck things up, I'm likely to be unable to fix the problem, so I want to see a solution that sufficiently resembles my situation before I even think about being comfortable with it, or alternately, it's coming from someone whose knowledge is well-known to me.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 8, 2020 at 1:11 pm
this is very common when SSISDB was restored from another server. Please follow this article:
https://blog.pythian.com/how-to-restore-ssisdb-to-another-server-and-migrate-the-ssis-catalog/
Well ... at least this is close. If I had more experience as a DBA, I'd be inclined to give things a shot, but as I don't have that experience, I'm not real comfortable. If I could know in advance exactly which parameters of which packages would get lost, I could at least identify that data and then use ALTER MASTER KEY FORCE REGENERATE, and not worry quite so much. No one has yet supplied a query I could use to at least identify which pieces of data would get lost if I do that, and my searches online have been in vain. Maybe someone else can search better simply because they're not me, and they're thought process will be different, and they'll use different search terms, and happen across what I need, or they'll have the requisite experience and just know what to do...
EDIT: Just an fyi ... I don't know if SSISDB was restored from another server or not, and the people that might know are gone. Can I run a query that would tell me if that database was restored from elsewhere?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 8, 2020 at 1:34 pm
I will be unable to use the password that was initially used to encrypt the master key, as that's been permanently lost, and I don't even know whether or not the problem was caused by having SSISDB restored from a different server.
At least learn from this mistake the hard way, and when you create the new key, you put the password somewhere secure. SSISDB very much warns/informs you that you must ensure you keep that password safe.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 8, 2020 at 1:57 pm
sgmunson wrote:I will be unable to use the password that was initially used to encrypt the master key, as that's been permanently lost, and I don't even know whether or not the problem was caused by having SSISDB restored from a different server.
At least learn from this mistake the hard way, and when you create the new key, you put the password somewhere secure. SSISDB very much warns/informs you that you must ensure you keep that password safe.
It was lost by personnel prior to me. I only discovered the problem existed AFTER the two parties who had any shot at knowing what had taken place, had left the company. I'm already preparing a standards document to hold details on using SSIS environments and will be providing the new password used to the IT director who can be a caretaker for such things.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 19, 2022 at 1:09 pm
sgmunson, i am very curious to hear the outcome of Your issue! - did You find a solution? - i am in the same kind of pinch!.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply