Running sqlpackage.exe from AWS CodeDeploy throws an exception

Question

I'm attempting to run sqlpackage.exe from a script executed by AWS CodeDeploy.

The sqlpackage command runs fine from a local CMD prompt when logged in as the administrator but does not run when called as part of the CodeDeploy pipeline.

The following error occurs:

An unexpected failure occurred: DacInstance with the specified instance_id does not exist..

    Unhandled Exception: System.Data.SqlClient.SqlException: DacInstance with the specified instance_id does not exist.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

It would appear that a connection cannot be made to the database.

CodeDeploy runs as a windows service running under aLocal System account where as my command prompt where this works runs under the Administrator account. This is the only difference so I think this must be permissions issue.

It would appear that a dacpac needs to be installed by a user with sysadmin privileges. I attempted (as a test) to set the SQL Server user NT AUTHORITY\SYSTEM to have a role of DBCreator.

The deployment then failed with the following error.

The database settings cannot be modified. You must be a SysAdmin to apply these settings.
The database settings cannot be modified. You must be a SysAdmin to apply these settings.
An error occurred while the batch was being executed.
Updating database (Failed)

I am unsure how to proceed however. I'm guessing that making NT AUTHORITY\SYSTEM a SysAdmin is a bad idea!

Answer 1

CodeDeploy Host Agent Service runs as LocalSystem user, which should have NT AUTHORITY\SYSTEM and BUILTIN\Administrators privileges. This is how the CodeDeploy agent executes your script:

powershell.exe -ExecutionPolicy Bypass -File <absolute_path_to_your_script_here>

You can try putting the executable on the root along with the appspec.yml file if you are putting it in a folder within your deployment package. That being said, we have seem this issue with the older versions of host agent which should be resolved with the latest version released in March, 2017.