migav
asked on
SSIS 2014: Can’t deploy new projects.
SQL SERVER : Microsoft SQL Server 2014 (SP2-CU3) (KB3204388) - 12.0.5538.0 (X64)
OS : Windows Server 2008 R2 Datacenter SP1 (X64)
Hi!
We have in acceptance a SSIS catalog where we found recently that we can’t deploy new projects, last successfully deployed project was 12-Sep-2017.
When we deploy a new project to the server only one symmetric key and one certificate is created for the project and when executing any package from this project we instantly get a result of ‘Unexpected Termination’. We don’t get any error message on the execution report from the catalog. The package is never executed.
If we deploy the same project on top of a previous existing project that had 2 symmetric keys and 2 certificates, the packages run successfully.
We detached the ssisdb and created a brand new catalog from scratch and we re-deployed all our projects (17 total) and only 2 of them created 2 keys and 2 certificates, the other deployments only created 1 object of each and gave the same ‘Unexpected Termination ‘ error.
Finally we undo the brand new catalog by re-attaching the previous catalog.
The operating system error is :
OS : Windows Server 2008 R2 Datacenter SP1 (X64)
Hi!
We have in acceptance a SSIS catalog where we found recently that we can’t deploy new projects, last successfully deployed project was 12-Sep-2017.
When we deploy a new project to the server only one symmetric key and one certificate is created for the project and when executing any package from this project we instantly get a result of ‘Unexpected Termination’. We don’t get any error message on the execution report from the catalog. The package is never executed.
If we deploy the same project on top of a previous existing project that had 2 symmetric keys and 2 certificates, the packages run successfully.
We detached the ssisdb and created a brand new catalog from scratch and we re-deployed all our projects (17 total) and only 2 of them created 2 keys and 2 certificates, the other deployments only created 1 object of each and gave the same ‘Unexpected Termination ‘ error.
Finally we undo the brand new catalog by re-attaching the previous catalog.
The operating system error is :
The SSIS Execution Process could not write to the IS catalog:
<server name>:SSISDB Error details: Cannot find the symmetric key 'MS_Enckey_Proj_Param_10', because it does not exist or you do not have permission.;
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at Microsoft.SqlServer.IntegrationServices.Server.Shared.ExecutionParameterLoader.GetExecutionParameter()
at Microsoft.SqlServer.IntegrationServices.Server.ISServerExec.ProjectOperator.StartPackage()
at Microsoft.SqlServer.IntegrationServices.Server.ISServerExec.ProjectOperator.PerformOperation()
SSISDB Error details: Cannot find the symmetric key 'MS_Enckey_Proj_Param_10', because it does not exist
it looks like your upgrade did not go well
the above post steps may help
more see this link
SSIS Catalog deployment not creating certificates
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/16eaab25-bb2e-4210-a9ad-8fb78eb7c120/ssis-catalog-deployment-not-creating-certificates?forum=sqlintegrationservices
https://social.msdn.microsoft.com/Forums/en-US/85e225fb-6324-4a01-a8f5-24ae9d5447c5/ssisdb-backup-restore-why-use-master-key?forum=sqlintegrationservices
--
more
Integration Services catalog (SSISDB) uses the encryption mechanism available in SQL Server to protect its sensitive data. So backup and restore of SSIS catalog across machines need some extra steps in addition to the usual backup and restore steps. This post describes all the steps involved in backup and restore of SSIS catalog.
http://amortizes.rssing.com/chan-2090545/all_p6.html
it looks like your upgrade did not go well
the above post steps may help
more see this link
SSIS Catalog deployment not creating certificates
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/16eaab25-bb2e-4210-a9ad-8fb78eb7c120/ssis-catalog-deployment-not-creating-certificates?forum=sqlintegrationservices
https://social.msdn.microsoft.com/Forums/en-US/85e225fb-6324-4a01-a8f5-24ae9d5447c5/ssisdb-backup-restore-why-use-master-key?forum=sqlintegrationservices
--
more
Integration Services catalog (SSISDB) uses the encryption mechanism available in SQL Server to protect its sensitive data. So backup and restore of SSIS catalog across machines need some extra steps in addition to the usual backup and restore steps. This post describes all the steps involved in backup and restore of SSIS catalog.
http://amortizes.rssing.com/chan-2090545/all_p6.html
ASKER
Hi!
No upgrade was done.
The changes are :
-Dec 2016 the cu3 was installed
-Sep 2017 the sp catalog.create_execution was changed to fix timouts.
-Sep 2017 last sucessfull deploy.
-Oct 2017 when the issue was detected.
Best regards,
No upgrade was done.
The changes are :
-Dec 2016 the cu3 was installed
-Sep 2017 the sp catalog.create_execution was changed to fix timouts.
-Sep 2017 last sucessfull deploy.
-Oct 2017 when the issue was detected.
Best regards,
So you are saying all these SSIS packages were designed for your SQL 2014 server? If yes, double check that the target SQL version in these packages say 2014. It should be creating one key and not two.
ASKER
Hi! Kevin,
We are using Visual Studio 2013 - SSIS project - data tools, how to change the target server ?
Best regards
We are using Visual Studio 2013 - SSIS project - data tools, how to change the target server ?
Best regards
I am using VS 2015 on this machine but the path should be similar for you.
Right-click on the project, select Properties, click on Configuration Properties, verify or select TargetServerVersion under Deployment Target Version. For general database projects, it is under Properties | Project Settings | Target Platform.
Right-click on the project, select Properties, click on Configuration Properties, verify or select TargetServerVersion under Deployment Target Version. For general database projects, it is under Properties | Project Settings | Target Platform.
ASKER
Hi! Kevin,
It's odd .. now it's working again! the "cure" was a reboot. Note that target version was not verified/changed.
Next time I'll start with a reboot and target version!
Best regards,
It's odd .. now it's working again! the "cure" was a reboot. Note that target version was not verified/changed.
Next time I'll start with a reboot and target version!
Best regards,
Glad you figured it out.
ASKER
Boomerang ! the problem returned.
The facts are:
Was installed the CU7 on 12/Oct/2017 the version now is Microsoft SQL Server 2014 (SP2-CU7) (KB4032541) - 12.0.5556.0 (X64)
On 24/Oct was published a brand new project with same problem.
I reboot with no luck.
I detached and attached ssisdb and reboot with no luck.
I did a profiler, republished and captured the line :
"Cannot find the symmetric key 'MS_Enckey_Proj_Param_1005 1', because it does not exist or you do not have permission."
Tomorrow I'm going to create the symmetric key 'MS_Enckey_Proj_Param_1005 1' and test if it works.
"USE SSISDB
CREATE CERTIFICATE MS_Cert_Proj_Param_10051 WITH SUBJECT = 'ISServerCertificate'
CREATE SYMMETRIC KEY MS_Enckey_Proj_Param_10051 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE MS_Cert_Proj_Param_10051
OPEN SYMMETRIC KEY MS_Enckey_Proj_Param_10051 DECRYPTION BY CERTIFICATE MS_Cert_Proj_Param_10051
CLOSE SYMMETRIC KEY MS_Enckey_Proj_Param_10051 "
Regards,
The facts are:
Was installed the CU7 on 12/Oct/2017 the version now is Microsoft SQL Server 2014 (SP2-CU7) (KB4032541) - 12.0.5556.0 (X64)
On 24/Oct was published a brand new project with same problem.
I reboot with no luck.
I detached and attached ssisdb and reboot with no luck.
I did a profiler, republished and captured the line :
"Cannot find the symmetric key 'MS_Enckey_Proj_Param_1005
Tomorrow I'm going to create the symmetric key 'MS_Enckey_Proj_Param_1005
"USE SSISDB
CREATE CERTIFICATE MS_Cert_Proj_Param_10051 WITH SUBJECT = 'ISServerCertificate'
CREATE SYMMETRIC KEY MS_Enckey_Proj_Param_10051
OPEN SYMMETRIC KEY MS_Enckey_Proj_Param_10051
CLOSE SYMMETRIC KEY MS_Enckey_Proj_Param_10051
Regards,
ASKER
Hi! All
Was tested without luck :
VS 2017 published target version 2014 - sysadmin
VS 2017 published target version 2012 - sysadmin
VS 2017 published target version 2012 - sysadmin - RunMode 32bit
VS 2013 published sysadmin
Regards,
Was tested without luck :
VS 2017 published target version 2014 - sysadmin
VS 2017 published target version 2012 - sysadmin
VS 2017 published target version 2012 - sysadmin - RunMode 32bit
VS 2013 published sysadmin
Regards,
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Did you recently upgrade your SQL Server? If so, it is possible the SSIS catalog needs updating.
Try the following:
- Detach the SSISDB
- Create a new SSIS catalog
- Redeploy projects
This should force SSIS to use the newest service versus being caught in the middle as I believe SQL 2014 only uses one key now and older version used two.