What are the challenges you face when working across database platforms? Take the survey
Options

Use Teamcity with Sql Change Automation Build for an AWS RDS instance with temporary database

Hi,
We are using Sql Change Automation in our Teamcity pipeline to deploy changes to our AWS RDS instance. Currently we have an on-prem virtual pc running Sql server to build the temporary database on. We want to get rid of this virtual pc as part of moving to AWS completely. I have been trying to use the RDS instance itself to build the scratch database on but so far without success.
Without specifying a temporary database name I get this error:

Dropping temporary database 'dlmautomation_d7708821-4c1d-4993-983e-e235bb7253f4' on 
[13:28:21][Step 1/2] 'our rds instance sql server'.
[13:28:22][Step 1/2] Invoke-DlmDatabaseSchemaValidation : SQL script failed to execute: User does not have permission to perform this action

the securable needed for this ALTER ANY DATABASE is not available on RDS

When put in a temporary database name I get this error:

[13:07:44][Step 1/2] VERBOSE: Dropping users
[13:07:44][Step 1/2] WARNING: The error 'The server principal "our_redgate_user" is not able to access the database "redgate_temp" under
[13:07:44][Step 1/2]  the current security context.' occurred when executing the following SQL:
@ERROR <> 0 SET NOEXEC ON

So it dropped itself and lost access to the temp database. On RDS the sysadmin role is not available

Is there anyone who got this scenario working?
Tagged:

Answers

  • Options
    I'm not overly familiar with RDS sadly but does it work if you precreate the temporary database in RDS?
    Have you visited our Help Centre?
  • Options
    FreekFreek Posts: 11 Bronze 1
    I did in the second scenario and I gave the redgate login db_owner rights. Unfortunately as you see in the log snippet it starts with dropping users first (including itself). After that it can't access the database anymore. In our on premises server the redgate user is sysadmin but that role is not available in RDS instances
  • Options
    Yeah ok that sounds about right - you'd need to have a pre-created database and specify it in the build parameters with -TemporaryDatabase so that it doesn't get dropped/recreated every time.
    Have you visited our Help Centre?
  • Options
    FreekFreek Posts: 11 Bronze 1
    So why is this happening when I do that:

    [13:07:44][Step 1/2] VERBOSE: Dropping users
    [13:07:44][Step 1/2] WARNING: The error 'The server principal "our_redgate_user" is not able to access the database "redgate_temp" under
    [13:07:44][Step 1/2]  the current security context.' occurred when executing the following SQL:
    @ERROR <> 0 SET NOEXEC ON

    ?
  • Options
    FreekFreek Posts: 11 Bronze 1
    I did in the second scenario and I gave the redgate login db_owner rights. Unfortunately as you see in the log snippet it starts with dropping users first (including itself). After that it can't access the database anymore. In our on premises server the redgate user is sysadmin but that role is not available in an RDS instance.
  • Options
    FreekFreek Posts: 11 Bronze 1
    I did in the second scenario and I gave the redgate login db_owner rights. Unfortunately as you see in the log snippet it starts with dropping users first (including itself). After that it can't access the database anymore. In our on premises server the redgate user is sysadmin but that role is not availabl
  • Options
    FreekFreek Posts: 11 Bronze 1
    I did in the second scenario and I gave the redgate login db_owner rights. Unfortunately as you see in the log snippet it starts with dropping users first (including itself). After that it can't access the database anymore. In our on premises server the redgate user is sysadmin but that role is not available in an RDS instance.
  • Options
    FreekFreek Posts: 11 Bronze 1
    I did in the second scenario and I gave the redgate login db_owner rights. Unfortunately as you see in the log snippet it starts with dropping users first (including itself). After that it can't access the database anymore. In our on premises server the redgate user is sysadmin but that role is not availabl
  • Options
    Russell DRussell D Posts: 1,324 Diamond 5
    edited August 8, 2019 1:48PM
    I wonder if it just doesn't work for the temporary database, and RDS is supported for deployment targets only - I'll try to find out.
    Have you visited our Help Centre?
  • Options
    FreekFreek Posts: 11 Bronze 1
    I've been working with the automation plugin for over two years now and with RDS instances little over a year since we migrated away from our own servers. So if there's anything I can do to assist, let me know. 
Sign In or Register to comment.