How to deploy SQLPROJ DACPAC to Amazon RDS

Question

We have always done our database design and development with VS2012 SSDT using the .sqlproj extension, and deployed to SQL Server using SQLPackage with a DACPAC... or alternatively setup a publish rule from Visual Studio.

We have migrated our database to Amazon RDS SQL Server.

We've recently made some changes to the database design and I've tried to publish the changes, and I get this error.

Error SQL72014: .Net SqlClient Data Provider: Msg 15151, Level 16, State 1, Line 1 Cannot find the user 'dbo', because it does not exist or you do not have permission.
Error SQL72045: Script execution error.  The executed script:
REVOKE INSERT
    ON OBJECT::[dbo].[table_name] TO [database_role] CASCADE
    AS [dbo];

I've searched everywhere for how to publish this type of database project to Amazon, and other than resorting to maintaining a local SQL Server database for the schema and buying Red-Gate SQL Compare to migrate the changes.. I'm stumped..

Any suggestions would be much appreciated.

Answer 1

The simple answer is that we can use the sqlpackage command line, or the sqlproj Publish function to update the amazon rds sql database in the same way it can be used on any other server.

The problems I encountered appear to be caused by not declaring and configuring the instance sa user (our main sa account for the Amazon RDS SQL Instance that you setup when you first create the SQL Instance on Amazon).

Because I had not declared the user and its role membership in the database, its membership of db_owner role was dropped from the database, and it appeared impossible to recreate after that.

So - if you make a mistake in doing the deployment and break your sql dbo permissions on your target database - the solution is to go into the Amazon RDS console, find the SQL instance, modify the instance, and change the set a new master password (even if it is the same as the existing one), and then tick the box at the bottom to apply instantly. (This is the instructions provided by Amazon support team - and has worked this morning a number of times in my research trial and error process..).

The key part to deploying the upgrade without breaking the security is as follows.

1. I defined a second database project called Master which is used to contain server level configuration. Into this master project, I created 2 users using this syntax

   CREATE LOGIN [myusername] with password = 'mypassword';

   • myinstancesa - this is the exact same name as the sa account you created when you defined the instance.
   • myappuser - this is the user that I will use in my application connection string so that my application does not run as the sa account and I can implement security at the database level to enforce or limit the ability of the application to accidentally delete or update certain tables..
2. In my main database project - I create a database reference to the Master, and the tick the box suppress errors in referenced projects to unresolved references.

3. In my main database project - I define the application role that I want to grant all my application connection rights to - for example -myapp_role using this syntax.

   CREATE ROLE [myapp_role] AUTHORIZATION [dbo]; and then grant membership to my app user to this role EXECUTE sp_addrolemember @rolename = N'myapp_role', @membername = N'myappuser';

4. Wherever I create objects that need permissions, I grant the permission to the role, not the user, this might not be necessary but in non Amazon life, it has made restoring and reconfiguring security much easier as the role is transported with the database backup/restore between servers. eg GRANT INSERT ON OBJECT::[mytable] TO [myapp_role]

5. In the main database project, create the user to represent the instance sa user CREATE USER [myinstancesa] FOR LOGIN [myinstancesa] WITH DEFAULT_SCHEMA = dbo

6. Grant membership of the db_owner role to the instance sa user - this is the security your user already has that you need to mirror to prevent it from being removed. EXECUTE sp_addrolemember @rolename = N'db_owner', @membername = N'myinstancesa';

7. Now you can either publish this to the target server, or you can make a dacpac and deploy it with sqlpackage. Below is the command line I used. If you use sqlpackage, take a snapshot of the main database and also the master referenced database.

   sqlpackage.exe /a:Publish /sf:mysnapshot.dacpac /tsn:long.instance.id.and.name.amazon.com /tu:myinstancesa /tp:"password in quotes" /tdn:myTargetDatabaseName /p:DropPermissionsNotInSource=True /p:DropRoleMembersNotInSource=false /p:BlockOnPossibleDataLoss=false /p:DropConstraintsNotInSource=true /p:DropExtendedPropertiesNotInSource=true /p:DropIndexesNotInSource=true /p:DropObjectsNotInSource=true /p:GenerateSmartDefaults=true /p:IgnoreIdentitySeed=true /p:IgnoreIncrement=true /p:IgnoreLoginSids=true /p:IgnoreWithNocheckOnForeignKeys=true /p:VerifyDeployment=true /v:Master=master.dacpac

The mysnapshot.dacpac must be the full path or relative path to the snapshot, and you must also make a snapshot of the master project and include the full or relative path as the last variable (does not always need to be the last variable). The tsn is the endpoint named in the Amazon console the tdn is the target database name, and the password I put in quotes in case you have special characters or puncutation characters.