Sql-server – How to decrypt dtsx package

sql-server-2016ssis

I've got some SSIS packages stored as varbinary, and I can load them into a temp table like so:

    Use SSISDB

    if object_id('tempdb..#package') is not null begin drop table #package end

    create table #package ( package varbinary(max))

    insert into #package (package) EXEC SSISDB.catalog.get_project 'my_folder','my_project'

    select * from #package

this returns a very long varbinary string which starts with 0x

Problem: when I try and convert the varbinary to nvarchar, it is still the same 0x... varbinary. I think it is encrypted.

Question: How can I decrypt these varbinary strings and convert them to a searchable format?

I think I need a key, but I'm not sure where those are stored.

Best Answer

Update:

So the package is not encrypted. As Billinkc said, it is zipped as a .ispac file.

These .ispac files first have to be exported from the database, then extracted into their components, which can be one or more .dtsx packages.

Microsoft provide a powershell script that will export every .ispac from your database:

https://gallery.technet.microsoft.com/scriptcenter/ImportExport-projects-bca5f29f

<#  
.SYNOPSIS  
    Import folders and projects from local file system to SSIS catalog.  
.DESCRIPTION  
    Import SSIS projects from $ProjectFilePath. The folders under $ProjectFilePath 
    will be imported as folders in the SSIS catalog, with all ispac files under that 
    folder imported accordingly.  

    The script will connect to the local SQL Server instance. It will first 
    drop the SSISDB catalog if exists and create a new catalog with a fixed secret.  
.EXAMPLE  
    .\CatalogImport  
#>  

# Variables 
$ProjectFilePath = "C:\SSIS" 

# Load the IntegrationServices Assembly 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null; 

# Store the IntegrationServices Assembly namespace to avoid typing it every time 
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices" 

Write-Host "Connecting to server ..." 

# Create a connection to the server 
$sqlConnectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;" 
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString 

# Create the Integration Services object 
$integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection 

Write-Host "Removing previous catalog ..." 

# Drop the existing catalog if it exists 
if ($integrationServices.Catalogs.Count -gt 0) { $integrationServices.Catalogs["SSISDB"].Drop() } 

Write-Host "Creating new SSISDB Catalog ..." 

# Provision a new SSIS Catalog 
$catalog = New-Object $ISNamespace".Catalog" ($integrationServices, "SSISDB", "SUPER#secret1") 
$catalog.Create() 

write-host "Enumerating all folders..." 

$folders = ls -Path $ProjectFilePath -Directory 

if ($folders.Count -gt 0) 
{ 
    foreach ($filefolder in $folders) 
    { 
        Write-Host "Creating Folder " $filefolder.Name " ..." 

        # Create a new folder 
        $folder = New-Object $ISNamespace".CatalogFolder" ($catalog, $filefolder.Name, "Folder description") 
        $folder.Create() 

        $projects = ls -Path $filefolder.FullName -File -Filter *.ispac 
        if ($projects.Count -gt 0) 
        { 
            foreach($projectfile in $projects) 
            { 
                $projectfilename = $projectfile.Name.Replace(".ispac", "") 
                Write-Host "Deploying " $projectfilename " project ..." 

                # Read the project file, and deploy it to the folder 
                [byte[]] $projectFileContent = [System.IO.File]::ReadAllBytes($projectfile.FullName) 
                $folder.DeployProject($projectfilename, $projectFileContent) 
            } 
        } 
    } 
} 

Write-Host "All done."

Fill out the connection string, and all your packages will get saved to C:\SSIS

From here, the packages need to be extracted, which I did using python:

import os
import zipfile
import pandas as pd
from sqlalchemy import create_engine
import urllib


conn_str = (
r'Driver=ODBC Driver 13 for SQL Server;'
r'Server=NynyaBeeswax;'
r'Database=Really_Cool_DB;'
r'Trusted_Connection=Yes;')

quoted_conn_str = urllib.parse.quote_plus(conn_str)
engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted_conn_str))
cnxn  = engine = create_engine('mssql+pyodbc:///?`odbc_connect{}'.format(quoted_conn_str)).execution_options(autocommit=True)`

directory = 'C:\\SSIS\\'
directory_to_extract_to = 'D:\\Defeintely_not_local\\SSIS_Packages\\'

for subdir, dirs, files in os.walk(directory):
    for filename in files:
        if filename.endswith(".ispac"):
            #print(filename)
            #print (subdir)
            #print(dirs)
            folder = subdir.split('\\')[2]

            target_directory = directory_to_extract_to + folder 

+ '\\'

            with zipfile.ZipFile(subdir + '\\' + filename, 'r') 

as zip_ref:
                zip_ref.extractall(target_directory)
                #print(target_directory)
        else:
            continue

for subdir, dirs, files in os.walk(directory_to_extract_to):
    for file in files:

        if file.endswith('.dtsx'):

            folder = subdir.split('\\')[5]

            sql = """insert into pkg_xml_data (subdir, 

package_name, bulkcolumn, loaddate) 
            SELECT '{}','{}', CONVERT(XML, BulkColumn) AS 

BulkColumn, GETDATE() 
            FROM OPENROWSET(BULK '{}\\{}', SINGLE_BLOB) AS 

x;""".format(folder,file, subdir, file)

        #print(filename)
            print(sql)
            #engine.execute(sql)
Related Question