I am no SSIS expert and am struggling here I am hoping some of you can assist.
I migrated an envornment from SQL Server 2019 --> 2022. There was a SSISDB database that restored to the new environment. i can see the package in there.
There is a job that i wanted to modify that has a reference to the old server in it and i wanted to change it to point to the new. however when i try to edi tthe step i get the following error.
A connection was successfully established with the server, but then an error occured during the login process. (providoer: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
When i select ok i can get past this. The job is of type SQL Server Integration Services Package, the server i have changed to the new server \ instance. I selected the ... to see if i could view the package but when i select it I get the same error.
I am at a loss any help woudl be apprectiated. I have read article about assigning a certificate however this was not undertaken on the 2019 server an di di dnot have any issues.
Cheers
April 23, 2024 at 2:40 pm
Sounds to me like the certificate chain to identify the server is not valid. This sounds like an SSL issue. I'd reach out to the server team and make sure that the cert assigned to the server is valid.
Alternately, if you have set up SQL to use a cert, you would need to ensure that cert is valid. I believe there is an option to turn off the cert requirement or to always trust the cert. I imagine someone turned that on on the 2019 box but not on the 2022 box.
I do strongly encourage you to set up the cert though - it allows for data encryption for data in motion which is ALWAYS nice. Otherwise MITM attacks are possible and easy on your server(s) for someone on your network.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 23, 2024 at 3:04 pm
when you migrated the ssisdb did you follow the procedure to obtain the DMK, there are various guides on how to do this and the other steps involved
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 23, 2024 at 3:09 pm
I maybe wrong however I dont believe certs were configured on the last deployment unless 2022 requires them. i will speak to the infra team and see what they say.
April 23, 2024 at 3:13 pm
I can 100% say i did not follow the procedure that said i did review the procedure after, I am in the proces sof seeing if the team has the details of the SSISDB password that was set, that said I did blow the SSISDB away as a test as i thought lets see what happens when i am not usng a migrated SSISDB database.
Funnily enough I created a new SSISDB setup the password etc as you are asked to do. Then i tried to create a job to call a SSIS package and the same happened again.
Very strange.
April 23, 2024 at 3:13 pm
confirm you backed up and restored the DMK?
What other steps did you take to migrate the ssisdb?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 23, 2024 at 3:24 pm
Correct me if I am wrong, but isn't the error a login related issue, not a database related issue?
To me it sounds like the server cert is not recognized or trusted. I suspect you get a similar error if you connect from SSMS to the SQL instance UNLESS you click to ignore cert errors.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 23, 2024 at 3:28 pm
I did not, All i did was to lift and shift the Database and restore it to the new instance. That said i am not 100 with SSIS and as such i did not backup and restore the key, I did read a guide after so i know how to do this however i need to opbtain the password used initially whcih i am trying to find out then will attempt to export it again.
I am thinking this would most likely be the issue.
I have check the old server in relation to the SSL certs and i cant see that any are in place. not sure if that is a red herring.
cheers
April 23, 2024 at 3:36 pm
the DMK is part of the encryption hierarchy used to encrypt the sensitive data withn the database, it must be backed up and restored.
There are various logins provisioned into the ssidb during the migration
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 23, 2024 at 4:56 pm
I will see where i get on and feedback to let you know how i got on.
April 23, 2024 at 4:59 pm
So we have no issue logging into the the instance at all, the setup is pretty much as it was before i migrated. The issue only arises when I try to modify a Job that calls a SSIS package.
April 23, 2024 at 5:03 pm
So we have no issue logging into the the instance at all, the setup is pretty much as it was before i migrated. The issue only arises when I try to modify a Job that calls a SSIS package.
yes it will likely try and open sensitive authentication details and without the DMK they cannot be decrypted
I’m assuming your package has connections to data sources, etc
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 23, 2024 at 5:07 pm
Possibly a silly question but did you set up the SPN's when you installed the new instance?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 23, 2024 at 8:03 pm
I will see where i get on and feedback to let you know how i got on.
important to understand the encryption hierarchy, each time a project\package is created and has sensitive credentials a cert is created, this is protected by the DMK, this in turn would be encrypted by the SMK providing seamless access. Without being able to open the DMK you have no access to the certs.
query the sys.Certificates catalog in the ssisdb you will see a number of rows
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 24, 2024 at 8:00 am
Yes the SPN were set.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply