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.

Closegrained

What happens if you generate the script and run it? Same thing? Could you handle these types of permissions in a post-deploy script instead of through SSDT? – Polemics

Yes - but I suppose I could generate the script and modify it, but when I tried generating the script and running it directly it reported same errors. – Closegrained

Do you know the proper syntax to handle those for Amazon RDS? This might be an opportunity for a custom handler or perhaps a good time to use security through post-deploy scripts. – Polemics

I'm currently working with Amazon Tech Support. We've extracted the SQL script, taken out lots of security and therefore achieved the database changes.. now working on security. I dont think we've hit the specific problem above, but the first problem we are working on is create a new user on the database. We have created a new user under the instance (server level).. but we cannot create the user on the database with for the user login we created at the server level. – Closegrained

That's interesting. Can you create any users in the DB at all that way? I'd suggest a different way of creating the users, but if it's failing with a normal CREATE USER command my suggestion probably wouldn't work, though if you need to customize it, it probably could. I'm interested in hearing the solution for the Amazon side if/when you find it. Creating users/logins post-deploy could work once you know what TSQL works. – Polemics

I can create user at the server level, but not at the database level. My sql command for this in the database is Create user mynewuser for login mynewuser But it appears that the that is provided when we signed up for Amazon does not have db_owner role rights or equivalent on any of our databases. – Closegrained

Amazon determined that my sa account/user did not have the correct permissions. They have changed the permissions, and now I can run create user for login in the DB. – Closegrained

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.

Closegrained