Database Mail or SSIS Packages Failing Due to Master Key, Object Not Set, or .NET Framework Error Messages
Quite often, as a Production DBA, you come across issues that are not so apparent within error messages. For example, a few days after migrating a PROD server from SQL Server 2012 Standard Edition to SQL Server 2016 Standard Edition, I noticed reports were no longer being delivered via database mail. When migrating from SQL Server 2014 Standard to SQL Server 2016 in our QA environment, this same issue occurred. Within the database mail logs, I noticed many errors stating “object reference not set to an instance of an object.”
After some solid Googling, the search results were suggesting a permission/role issue, .NET Framework 64-bit, or patch level issue. However, the machine in question had all of the required .NET versions installed, most recent updates, and I was attempting all actions with sysadmin. While troubleshooting the database mail, we also noticed SSIS packages were not running and new deployments failed with the error “please create a master key in the database or open the master key in the session before performing the operation.” Now we are all hands on deck, and must get production running.
Examples of Errors:
After continued digging into this situation, we discovered a known issue where SSIS packages start failing after migrating SSIDB from 2012-2014. However, when we experienced this issue the second time around, we were migrating SSIDB from SQL Server 2014 to 2016. It looks like this issue wasn’t fixed!! This can be verified when opening the Properties of the Integration Services Catalog SSISDB fails due to the invalid object name “SSISDB.catalog.customized_logging_levels.”
Since you can’t open the properties to verify the Catalog Schema, query:
[SSISDB].[internal].[catalog_properties]
SELECT * FROM [SSISDB].[internal].[catalog_properties]
There we go, the SCHEMA_BUILD is 12.0.5589.7 but we are using SQL Server 2016 (13.0.4474.0). We want our SSISDB to have a 13.% SCHEMA_BUILD.
You know you followed best practices when migrating SSISDB, so this confirms your issue. The SCHEMA_BUILD in the Catalog was not updated and now you have a mismatch in the schema, much to your chagrin.
The Fix
Hopefully, you still have access to the previous SSISDB on the instance you migrated from or a copy of the backup so that you can restore to an instance with the same version of SQL Server as your source instance. If not, you will need to locate the original SSIS package files and import the packages into Visual Studio to re-deploy to your new instance after we fix this issue. While exporting packages from the old instance, make sure to make note of any folders when you expand SSISDB within Integration Services Catalog because you will need to recreate them also.
The first part of this fix is pretty quick:
- drop SSISDB on the new 2016 server,
- create a new catalog,
- create the new folders.
The more packages you have to redeploy, the longer this will take. Should there be upwards of 100 + packages, it may be worth the time to invest in automating this process, pinging one of your Junior DBAs to assist, or calling up RDX.
On your new instance, drop the SSISDB:
USE [master]
GO
ALTER DATABASE [SSISDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [SSISDB]
GO
Create a new Integration Services Catalog, re-create all of the folders, and re-deploy your packages
Right-click Integration Services Catalog, Create Catalog…
When the GUI loads, create a strong password and save in a safe location, click OK
Verify you can now view the Properties of SSISDB in Integration Services Catalog & your Schema Build is now 13.% (12.% if from 2012 to 2014)
Start re-creating the folders in the Integration Services Catalog SSISDB ***You must use Windows Authentication to create Catalog Folders***
Redeploy packages & you are back in business
You will know with the successful package deployment, and/or validate database mail is functioning by sending a test mail and querying msdb.dbo.sysmail_allitems or msdb.dbo.sysmail_event_log.
Change time or mailitem_id for better filtering:
SELECT top 10 mailitem_id, subject, sent_status, sent_date, body
FROM msdb.dbo.sysmail_allitems
— WHERE mailitem_id =
order by sent_date desc
SELECT top 10 *
FROM msdb.dbo.sysmail_event_log
WHERE event_type <> ‘information’
— mailitem_id =
order by log_date desc
In conclusion, this is just one example of the unique situations Production DBAs come across when supporting a plethora of environments. Sometimes, following best practices still have unforeseen obstacles that we need to work around, even if there isn’t clear-cut documentation when searching a specific error. Always make sure you are verifying your sources, and don’t start making changes without properly researching the root cause. Hopefully, this will help save some headache and relieve tension in a potentially high stress situation.
Thanks for reading!
References: