Sql-server – Can’t deploy SSIS package – The certificate chain was issued by an authority that is not trusted

Tom Li- MSFT described a solution to what sounds like your problem, but his solution was for SQL Server 2008. (But just how much has the installer been improved since then?)

https://social.msdn.microsoft.com/forums/sqlserver/en-US/f009e660-503a-4569-a92d-9cc2ef790ca3/install-failure-certificate-chain-trust

Key points from his post include:

1. This certificate is used as a mechanism to protect SQL Server's login information(including password) when using SQL authentication.

   By default this self-signed certificate should only affect the login information and only when using SQL authentication.

   Edit: My certificate name usually matches the SQL Server's login account.

2. Export the certificate and delete it by using "certmgr.msc". Then run "setup.exe" to install SQL Server 2008 again.

   You also can add this certificate to the Trusted Root Certification Authorities store to solve this issue. In order to do this, please execute:

   Certutil -Addstore -f Root <File.Cer>

3. Run "cliconfg.exe" from a Windows run dialog to determine that you did not check "Force protocol encryption".

You have a lot going on here.

Package vs Project deployment model

Starting with the 2012 release, SSIS projects have the choice between a Package Deployment Model and a Project Deployment Model. Project allows you to create project level artifacts - shared connection managers, project (and package) parameters. The default deployment model for new projects is Project Deployment Model.

The deployable unit of code from a project deployment model is the .ispac file. Your SSIS packages + project parameters + shared connection managers + manifest file get zipped up into a file named MyProject.ispac. The ispac file is deployed to a SQL Server catalog called SSISDB.

The deployable unit of code from package deployment model is the SSIS package itself. You can deploy the .dtsx file to a File System, the SSIS Package Store (also just the file system) or a SQL Server instance. However, the SSIS package will be stored in the MSDB (sysdtspackages90/sysssispackages)

The 2016 release complicates the above matrix in that we can now deploy .dtsx package(s) into the SSISDB. This is to address the incremental deployment story. Behind the scenes, it will generate an ispac and that's what's deployed. But that's only for a 2016 server which won't help your 2012 story.

SSIS Versions

SSIS packages have versions that correspond to the version of SQL Server and tooling you're working with (2005/2008/2008 R2/2012/2014/2016/v.Next). Packages are only* forward compatible so if you make the mistake of opening a 2005 package with the 2014 tooling (or deploy with it) to a 2005 instance guess what, you've upgraded to the current version and deployed to a location that doesn't speak that dialect.

2016 confuses this issue because the tooling now supports targeting (Project -> properties, Configuration Properties -> General: TargetServerVersion)

Deployment

Ispacs are deployed via isdeploymentwizard.exe or you can do it in TSQL.

-- You must be in SQLCMD mode
-- Otherwise, modify the value of $(isPacPath) down below
:setvar isPacPath "C:\sandbox\SSDTDeploy\TSQLDeploy\bin\Development\TSQLDeploy.ispac"

DECLARE
    @folder_name nvarchar(128) = 'TSQLDeploy'
,   @folder_id bigint = NULL
,   @project_name nvarchar(128) = 'TSQLDeploy'
,   @project_stream varbinary(max)
,   @operation_id bigint = NULL;

-- Read the zip (ispac) data in from the source file
SELECT
    @project_stream = T.stream
FROM
(
    SELECT 
        *
    FROM 
        OPENROWSET(BULK N'$(isPacPath)', SINGLE_BLOB ) AS B
) AS T (stream);

-- Test for catalog existences
IF NOT EXISTS
(
    SELECT
        CF.name
    FROM
        catalog.folders AS CF
    WHERE
        CF.name = @folder_name
)
BEGIN
    -- Create the folder for our project
    EXECUTE [catalog].[create_folder] 
        @folder_name
    ,   @folder_id OUTPUT;
END

-- Actually deploy the project
EXECUTE [catalog].[deploy_project] 
    @folder_name
,   @project_name
,   @project_stream
,   @operation_id OUTPUT;

That sounds like what you want because you can be a local SQL User but if memory serves, the above functions do this impersonation jiggery that goes awry if you're not a Windows user.

This application requires one of the components..

With 2005/2008/2008 R2 you could only get SSDT, nee BIDS, with the installation media. This meant you needed Developer Edition at a minimum to build out your SSIS Packages. Starting with 2012, you could download the SSDT tooling directly from MS without having a license for SQL Server. However, you are only licensed for development purposes. The command line tools are not classified as development tooling so dtutil or dtexec will both fail a licensing check and report the above (or a similar error message).

The resolution, as you point out, is to install the Integration Services Service on the machine that requires "more" than just Integration Services development. Be aware that this does count as an installation of SQL Server from a licensing perspective so be sure the person in your organization that handles licensing is aware of machines where you have done this so you don't have an unpleasant surprise come audit time.