VSTS & DLM Automation Releases

jespana

Hi all,

We are evaluating RedGate's Source Countrol and DLM Automation tools for a CI/CD proof of concept project. With that said, is it possible to release to a local database (meaning on-premise) from VSTS using DLM Automation? If so, what permissions need to be set to allow this to occur? And how does VSTS have to be setup to recognize an on-premise server?

Right now my release complains that the server can not be found.

2017-10-18T19:19:59.6237188Z ##[error]System.Management.Automation.ActionPreferenceStopException: The running command stopped because the preference variable "ErrorActionPreference" or common parameter is set to Stop: Couldn't connect to the database specified by the InputObject parameter: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Any feedback or guidance would be appreciated.

Thanks,
JEspana

way0utwest

First, this works and I demo this regularly. The key is your agent. How have you configured your process?

For me, I use SQL Auth, and I set a user name and password as variables for the environment. I use a secret for the password. Alternatively, you could run your agent as an account that has access, but I tend to find this less secure, unless I were to set a different pipeline for this release, using a specific agent account that I can limit to sysadmins accessing for release. I would be wary of the same pipeline/agent for build and release. Two separate functions.

Here's my production release definition:


For my production side, I have a variable here:


The VSTS agent is functioning exactly as if it were logged in as you. You need to set security context and networking to be sure the agent can connect to your instance. After that, it's running DDL/DML, just as any other login/user. The rights/permissions depend on what you allow to happen. If a db needs to be created, then you need those rights. If it's just CREATE/ALTER/DROP objects, those rights are needed.