SSIS Project Deployment to Remote Server in SQL 2016

0
votes

We have a new SSIS project, our first in SQL 2016 (previous version was 2008). We decided that the environment parameters and the Integration Server will serve our purposes best so we're using Project Deployment Model.

All of our development is done locally on our laptops but we all connect to the same development database (hosted on an Azure Virtual machine). We have no trouble connecting to the database using SSMS or VS2k15 Server Explorer on our machines. The packages are retrieving data form there, everything is working great.

However, now we would like to deploy these up to our development server, test them, then deploy them to our staging server, test them, and finally deploy to production.

When I try to use the deployment wizard we get to the 'Select Destination' step. It does not seem to allow us to choose a server that isn't in our local network. There is no option to connect to a remote server and when I enter my remote server's url I get the following error...

Failed to connect to server [my server address]. Login failed. The Login is form an untrusted domain and cannot be used with Windows authentication (Error 18452).

There is no option given for SQL Authentication (which is what we use to connect everywhere else, including WITHIN the project I'm currently working on).

Is there really no way to deploy this remotely? Looking for a solution.

1
visual-studio-2015ssissql-server-2016
Is your database in same box where ssis resides? Are you providing the same name (format) that you are providing in SSMS? - p2k
I've created the SSIS project in Visual Studio locally (my laptop). We're deploying to Dev (and eventually Proud) servers on Azure Virtual Machines. So no, not on same box. I AM using exactly the same name as I'm using in SSMS to connect to the remote server. - Larry Grady
Can you connect to Azure Virtual machine remotely (rdp)? Are you able to access the SSISDB from your ssms login? - p2k
Yes, I can RDP into the Azure VM. Yes, I can access the SSISDB from my SSMS login. The issue seems to be that there is no choice for SQL credentials in the deployment wizard. - Larry Grady

1 Answers

0
votes

So there doesn't seem to be a way to do this with the Wizard. I was able to copy the .ispac file up to the virtual server. Then I created a catalog and a folder under the Integration Services.

I found an example that allowed me to use OPENROWSET to set a binary variable from the ispac file. Then the catalog.deploy_project stored procedure to deploy that project to SQL's Integration Catalog. This did work and I'm able to run my packages on the server now. However when I first tried to do this I got an error

The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.

So it would not allow me to run the stored procedure until I logged in with Windows Authentication. That must be a limitation, or I suppose a security feature.

Is this really the best way to do this? Am I going to have to copy a physical file up to my server and run this stored procedure when I want to deploy from our development laptop to the dev server and then to the staging and live servers. These servers are on different boxes so we're going to have to copy the file over from server to server.

So I figured out how to get done what I need to get done, but is there a better way to do this? Some best practice I'm missing?