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

Freek

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?

Russell D

I'm not overly familiar with RDS sadly but does it work if you precreate the temporary database in RDS?

Freek

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

Russell D

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.

Freek

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

?

Freek

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.

Freek

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

Freek

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.

Freek

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

Russell D

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.

Freek

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.