0

I have to implement a solution where I have to deploy a SSIS project (xy.ispac) from one machine to another. So far I've managed to copy-cut-paste the following stuff from all around the internet:

# Variables
$ServerName = "target"
$SSISCatalog = "SSISDB" # sort of constant
$CatalogPwd = "catalog_password"

$ProjectFilePath = "D:\Projects_to_depoly\Project_1.ispac"

$ProjectName = "Project_name"
$FolderName = "Data_collector"

# Load the IntegrationServices Assembly
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices")

# 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=$ServerName;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

$integrationServices = New-Object "$ISNamespace.IntegrationServices" $sqlConnection

$catalog = $integrationServices.Catalogs[$SSISCatalog]

# Create the Integration Services object if it does not exist
if (!$catalog) {
    # Provision a new SSIS Catalog
    Write-Host "Creating SSIS Catalog ..."
    $catalog = New-Object "$ISNamespace.Catalog" ($integrationServices, $SSISCatalog, $CatalogPwd)
    $catalog.Create()
}

$folder = $catalog.Folders[$FolderName]

if (!$folder)
{
    #Create a folder in SSISDB
    Write-Host "Creating Folder ..."
    $folder = New-Object "$ISNamespace.CatalogFolder" ($catalog, $FolderName, $FolderName)            
    $folder.Create()  
}

# Read the project file, and deploy it to the folder
Write-Host "Deploying Project ..."
[byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
$folder.DeployProject($ProjectName, $projectFile)

This seemed to be working surprisingly well on the development machine - test server pair. However, the live environment will be a bit different, the machine doing the deployment job (deployment server, or DS from now on) and the SQL Server (DB for short) the project is to be deployed are in different domains and since SSIS requires windows authentication, I'm going to need to run the above code locally on DS but using credentials of a user on the DB.

And that's the point where I fail. The only thing that worked is to start the Powershell command line interface using runas /netonly /user:thatdomain\anuserthere powershell, enter the password, and paste the script unaltered into it. Alas, this is not an option, since there's no way to pass the password to runas (at least once with /savecred) and user interactivity is not possible anyway (the whole thing has to be automated).

I've tried the following:

  • Simply unning the script on DS, the line $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString would use the credentials from DS which is not recognized by DB, and New-Object does not have a -Credential arg that I could pass to
  • Putting everything into an Invoke-Command with -Credential requires using -Computername as well. I guess it would be possible to use the local as 'remote' (using . as Computername) but it still complains about access being denied. I'm scanning through about_Remote_Troubleshooting, so far without any success.

Any hints on how to overcome this issue?

gemisigo
  • 205
  • 4
  • 12
  • Which step it is failing? Is it failing while connecting remote target machine ISCatalog or it is failing while Deploy project? – Kannan Kandasamy Sep 26 '16 at 15:13
  • It fails at creating the integration services object from the sql connection: `$integrationServices = New-Object "$ISNamespace.IntegrationServices" $sqlConnection`. – gemisigo Sep 26 '16 at 19:35
  • From this server can you try executing to different instance(Server) of SSIS Catalog? Looks like some access issue... – Kannan Kandasamy Sep 26 '16 at 19:37
  • I cannot, there're no other instances available. But I guess, I'm slowly making progress. While it's the mentioned line that throws error message, the reason for that is actually the previous line where the sql connection is made from the connection string. Now, if I could convince it somehow to use the other domain\user credentials for creating that connection, I would be back on track. I guess. – gemisigo Sep 27 '16 at 09:20
  • I am guessing that the code is correct, but some access issue on the server, that is what i am trying to troubleshoot.. – Kannan Kandasamy Sep 27 '16 at 09:26
  • The code is correct, no doubt (check the part about using runas to start the powershell cli in the question). I'm pretty sure the culprit is the connection established with the wrong credentials. After that everything based on it is doomed to fail. I just can't figure out how to make the right connection. – gemisigo Sep 28 '16 at 16:44

1 Answers1

0

A solution might be to use a sql user (with the right access rights) instead of an AD used. Something like this should work. (Check also the answer to correct the connection string)

Community
  • 1
  • 1
ldobre
  • 361
  • 2
  • 6
  • Not really. Deploying SSIS packages requires windows authentication. Using SQL user (login) is not possible here. – gemisigo Sep 28 '16 at 16:40