Self-Signed Certificate in Certificate Chain Error

There a few reasons you may be seeing this error, the two most common ones are:

  1. You are behind a “transparent proxy”, which means someone (such as your IT department) is intercepting HTTPS traffic, decrypting it, and then encrypting it using a self-signed certificate
  2. You are running software, such as anti-virus software, which is injecting a self-signed SSL certificates into the HTTPS messages you receive

When Storage Explorer encounters one of these “self-signed certificates”, it can no longer know if the HTTPS message it is receiving has been tampered with. If you have a copy of the self-signed certificate though, then you can tell Storage Explorer to trust it. If you are unsure of who is injecting the certificate, then you can try to find it yourself by doing the following:

  1. Install Open SSL
    • Windows (any of the light versions should suffice)
    • Mac and Linux: Should be included with your operating system
  2. Run Open SSL
    • Windows: Go to the install directory, then /bin/, then double click on openssl.exe
    • Mac and Linux: execute “openssl” from a terminal
  3. Execute s_client -showcerts -connect microsoft.com:443
  4. Look for self-signed certificates. If you’re unsure which are self-signed, then look for any where the subject (“s:”) and issuer (“i:”) are the same.
  5. Once you have found any self-signed certificates, then for each one, copy and paste everything from and including -----BEGIN CERTIFICATE----- to -----END CERTIFICATE----- to a new .cer file.

Open NotePad. Make sure, you are copying everything between —BEGIN CERTIFICATE— and —END CERTIFICATE— block. For example, if there are two certificates, then the file would be;

—BEGIN CERTIFICATE—

—END CERTIFICATE—

—BEGIN CERTIFICATE—

—END CERTIFICATE—

Save File as .cer on your computer.

  1. Open Storage Explorer and then go to Edit -> SSL Certificates -> Import Certificates. Using the file picker, find, select, and open the .cer files you created.

Resource;

https://github.com/CawaMS/StorageExplorerTroubleshootingGuide/blob/master/se-troubleshooting-guide.md#self-signed-certificate-in-certificate-chain

SSIS Project Deployment Model

This article talks about project deployment mode;

What is .ispac file ?

Project deployment file is a self-contained unit of deployment that includes the essential information about the package and parameters in the project.

How to create .ispac file?

The project deployment model is introduced in SQL Server 2012. This is something similar to create SSISDeploymentManifest in previous SQL Server SSIS versions. They were also called package deployment model.

When we build/run a package, Visual Studio will build .ispac file in bin\Development folder of the project. Using this file, we can deploy the project without Data Tool or Visual Studio.

How to deploy SSIS packages using .ispac?

Here are different ways to deploy .ispac file;

Using SSMS

We can use SSMS for this. Create a sub-folder under SSISDB database. Right-Click on the folder and select deploy project. Follow the wizard.

Using Visual Studio

We can import ispac file into visual studio

Open Visual Studio

File -> New -> Project (Business Intelligence -> Integrations Serices)

Select “Integration Services Import Project Wizard”

Using PowerShell

We can use Powershell to enumerate .ispac file. We would have more control using this option;

Open Powershell. We might have permission issues, so run this script first;

Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass 

Copy and save these two scripts on your drive. Run main script.

main.ps1

#################################################################################################
# Run following script if there is a execution policy error
#################################################################################################

#Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass

#PowerShell: main.ps1
#################################################################################################
# Change source, destination and environment properties
#################################################################################################
 
# Source
$IspacFilePath ="C:\Deploy\SSISProject.ispac"
 
# Destination
$SsisServer ="(local)"
$FolderName = "Demo"
$ProjectName = "SSISProject"
 
# Environment
$EnvironmentName = "STG"
$EnvironmentFolderName = ""
 
#################################################################################################
# Execute generic deployment script
. "$PSScriptRoot\support.ps1" $IspacFilePath $SsisServer $FolderName $ProjectName $EnvironmentName $EnvironmentFolderName

This is second script “support.ps1”.

################################
########## PARAMETERS ##########
################################ 
[CmdletBinding()]
Param(
    # IsPacFilePath is required
    [Parameter(Mandatory=$True,Position=1)]
    [string]$IspacFilePath,
     
    # SsisServer is required 
    [Parameter(Mandatory=$True,Position=2)]
    [string]$SsisServer,
     
    # FolderName is required
    [Parameter(Mandatory=$True,Position=3)]
    [string]$FolderName,
     
    # ProjectName is not required
    # If empty filename is used
    [Parameter(Mandatory=$False,Position=4)]
    [string]$ProjectName,
     
    # EnvironmentName is not required
    # If empty no environment is referenced
    [Parameter(Mandatory=$False,Position=5)]
    [string]$EnvironmentName,
     
    # EnvironmentFolderName is not required
    # If empty the FolderName param is used
    [Parameter(Mandatory=$False,Position=6)]
    [string]$EnvironmentFolderName
)
 
# Replace empty projectname with filename
if (-not $ProjectName)
{
  $ProjectName = [system.io.path]::GetFileNameWithoutExtension($IspacFilePath)
}
# Replace empty Environment folder with project folder
if (-not $EnvironmentFolderName)
{
  $EnvironmentFolderName = $FolderName
}
 
clear
Write-Host "========================================================================================================================================================"
Write-Host "==                                                         Used parameters                                                                            =="
Write-Host "========================================================================================================================================================"
Write-Host "Ispac File Path        : " $IspacFilePath
Write-Host "SSIS Server            : " $SsisServer
Write-Host "Project Folder Path    : " $FolderName
Write-Host "Project Name           : " $ProjectName
Write-Host "Environment Name       : " $EnvironmentName
Write-Host "Environment Folder Path: " $EnvironmentFolderName
Write-Host "========================================================================================================================================================"
Write-Host ""
 
###########################
########## ISPAC ##########
###########################
# Check if ispac file exists
if (-Not (Test-Path $IspacFilePath))
{
    Throw  [System.IO.FileNotFoundException] "Ispac file $IspacFilePath doesn't exists!"
}
else
{
    $IspacFileName = split-path $IspacFilePath -leaf
    Write-Host "Ispac file" $IspacFileName "found"
}
 
 
############################
########## SERVER ##########
############################
# Load the Integration Services Assembly
Write-Host "Connecting to server $SsisServer "
$SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
[System.Reflection.Assembly]::LoadWithPartialName($SsisNamespace) | Out-Null;
 
# Create a connection to the server
$SqlConnectionstring = "Data Source=" + $SsisServer + ";Initial Catalog=master;Integrated Security=SSPI;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring
 
# Create the Integration Services object
$IntegrationServices = New-Object $SsisNamespace".IntegrationServices" $SqlConnection
 
# Check if connection succeeded
if (-not $IntegrationServices)
{
  Throw  [System.Exception] "Failed to connect to server $SsisServer "
}
else
{
   Write-Host "Connected to server" $SsisServer
}
 
 
#############################
########## CATALOG ##########
#############################
# Create object for SSISDB Catalog
$Catalog = $IntegrationServices.Catalogs["SSISDB"]
 
# Check if the SSISDB Catalog exists
if (-not $Catalog)
{
    # Catalog doesn't exists. The user should create it manually.
    # It is possible to create it, but that shouldn't be part of
    # deployment of packages.
    Throw  [System.Exception] "SSISDB catalog doesn't exist. Create it manually!"
}
else
{
    Write-Host "Catalog SSISDB found"
}
 
 
############################
########## FOLDER ##########
############################
# Create object to the (new) folder
$Folder = $Catalog.Folders[$FolderName]
 
# Check if folder already exists
if (-not $Folder)
{
    # Folder doesn't exists, so create the new folder.
    Write-Host "Creating new folder" $FolderName
    $Folder = New-Object $SsisNamespace".CatalogFolder" ($Catalog, $FolderName, $FolderName)
    $Folder.Create()
}
else
{
    Write-Host "Folder" $FolderName "found"
}
 
 
#############################
########## PROJECT ##########
#############################
# Deploying project to folder
if($Folder.Projects.Contains($ProjectName)) {
    Write-Host "Deploying" $ProjectName "to" $FolderName "(REPLACE)"
}
else
{
    Write-Host "Deploying" $ProjectName "to" $FolderName "(NEW)"
}
# Reading ispac file as binary
[byte[]] $IspacFile = [System.IO.File]::ReadAllBytes($IspacFilePath)
$Folder.DeployProject($ProjectName, $IspacFile)
$Project = $Folder.Projects[$ProjectName]
if (-not $Project)
{
    # Something went wrong with the deployment
    # Don't continue with the rest of the script
    return ""
}
 
 
#################################
########## ENVIRONMENT ##########
#################################
# Check if environment name is filled
if (-not $EnvironmentName)
{
    # Kill connection to SSIS
    $IntegrationServices = $null
 
    # Stop the deployment script
    Return "Ready deploying $IspacFileName without adding environment references"
}
 
# Create object to the (new) folder
$EnvironmentFolder = $Catalog.Folders[$EnvironmentFolderName]
 
# Check if environment folder exists
if (-not $EnvironmentFolder)
{
  Throw  [System.Exception] "Environment folder $EnvironmentFolderName doesn't exist"
}
 
# Check if environment exists
if(-not $EnvironmentFolder.Environments.Contains($EnvironmentName))
{
  Throw  [System.Exception] "Environment $EnvironmentName doesn't exist in $EnvironmentFolderName "
}
else
{
    # Create object for the environment
    $Environment = $Catalog.Folders[$EnvironmentFolderName].Environments[$EnvironmentName]
 
    if ($Project.References.Contains($EnvironmentName, $EnvironmentFolderName))
    {
        Write-Host "Reference to" $EnvironmentName "found"
    }
    else
    {
        Write-Host "Adding reference to" $EnvironmentName
        $Project.References.Add($EnvironmentName, $EnvironmentFolderName)
        $Project.Alter() 
    }
}
 
 
########################################
########## PROJECT PARAMETERS ##########
########################################
$ParameterCount = 0
# Loop through all project parameters
foreach ($Parameter in $Project.Parameters)
{
    # Get parameter name and check if it exists in the environment
    $ParameterName = $Parameter.Name
    if ($ParameterName.StartsWith("CM.","CurrentCultureIgnoreCase"))
    {
        # Ignoring connection managers
    }
    elseif ($ParameterName.StartsWith("INTERN_","CurrentCultureIgnoreCase"))
    {
        # Internal parameters are ignored (where name starts with INTERN_)
        Write-Host "Ignoring Project parameter" $ParameterName " (internal use only)"
    }
    elseif ($Environment.Variables.Contains($Parameter.Name))
    {
        $ParameterCount = $ParameterCount + 1
        Write-Host "Project parameter" $ParameterName "connected to environment"
        $Project.Parameters[$Parameter.Name].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, $Parameter.Name)
        $Project.Alter()
    }
    else
    {
        # Variable with the name of the project parameter is not found in the environment
        # Throw an exeception or remove next line to ignore parameter
        Throw  [System.Exception]  "Project parameter $ParameterName doesn't exist in environment"
    }
}
Write-Host "Number of project parameters mapped:" $ParameterCount
 
 
########################################
########## PACKAGE PARAMETERS ##########
########################################
$ParameterCount = 0
# Loop through all packages
foreach ($Package in $Project.Packages)
{
    # Loop through all package parameters
    foreach ($Parameter in $Package.Parameters)
    {
        # Get parameter name and check if it exists in the environment
        $PackageName = $Package.Name
        $ParameterName = $Parameter.Name
        if ($ParameterName.StartsWith("CM.","CurrentCultureIgnoreCase"))
        {
            # Ignoring connection managers
        }
        elseif ($ParameterName.StartsWith("INTERN_","CurrentCultureIgnoreCase"))
        {
            # Internal parameters are ignored (where name starts with INTERN_)
            Write-Host "Ignoring Package parameter" $ParameterName " (internal use only)"
        }
        elseif ($Environment.Variables.Contains($Parameter.Name))
        {
            $ParameterCount = $ParameterCount + 1
            Write-Host "Package parameter" $ParameterName "from package" $PackageName "connected to environment"
            $Package.Parameters[$Parameter.Name].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, $Parameter.Name)
            $Package.Alter()
        }
        else
        {
            # Variable with the name of the package parameter is not found in the environment
            # Throw an exeception or remove next line to ignore parameter
            Throw  [System.Exception]  "Package parameter $ParameterName from package $PackageName doesn't exist in environment"
        }
    }
}
Write-Host "Number of package parameters mapped:" $ParameterCount
 
 
###########################
########## READY ########## 

We can run these scripts from PowerShell UI. Navigate to your folder and run these from command line like this;

.\main.ps1

Hope this will help.

Resources;

https://docs.microsoft.com/en-us/sql/integration-services/packages/deploy-integration-services-ssis-projects-and-packages?view=sql-server-ver15

Creating Repo and adding project using UI

Create a repo using Azure DevOPS UI;

Project Settings -> Repositories -> Create

Name Repository “Demo”.

Open command prompt.

git clone https://myOrg@dev.azure.com/[orgName]/[projectName]/_git/Demo

Create a project for example Demo in a separate folder;

Copy project file from separate folder to clone folder;

Open solution in clone folder. Commit changes and you are good to go.

Related links

Send Email after Release Deployment in Azure DevOps

Azure DevOPS sends an email notification to team member after the compilation succeeds or fails. This is default behavior. We are more concerned when the deployment is complete so that manual or automated testing can be started.

Click on Project settings –> Notifications –> New Subscriptions –> Release –> A deployment is completed

The default settings are to send mail to all members of the current project when the deployment is complete. Specific settings can be changed according to project needs.

Now, trigger a deployment. If all goes well, you should be able to receive a similar email notification. I currently have two environments, and according to the configuration just now, any deployment on both environment will be notified.

SQL Server integration services environment setup

These are required steps;

  1. SQL Server Integration Services

Install Integration Services

  1. Create SSIS DB Catalog using SQL Server Management Studio

Create and Configure SSIS Catalog

  1. Microsoft Access Database Engine 2016 Redistributable (ACE driver for Excel Files)

Install Microsoft Access Database Engine 2016 Redistributable

  1. A domain account that has read/write/delete permission to file system.

This will be used as a proxy account. This is required because SSIS Packages will run from SQL Server Agent and the job will fail because of security context.