Things I learned in AWS - This week : RDS MSSQL Master DB access
The RDS master account does not have the same privileges as the SQL Server SA account - Why???!!
This week I discovered that the RDS master user account doesn't have sa privileges. For those who are new to RDS, the master user account is the account created during the initial RDS instance creation, from there you typically use this account to create the actual databases within RDS.
Then the best practice is to create additional accounts that have their specific privileges used to interact with each database. This follows the "least privilege model" - giving the users just enough access to do their work and nothing more.
In MS SQL Server, there are several internal databases such as master, model, msdb, and tempdb that are used for a variety of purposes. We had a requirement to create a global stored procedure that could be used by all the other databases. To achieve this, you simply create a stored procedure in the master db, but this requires higher privileges, which are typically available either with the "sa" account or with an account created by the "sa" and given the correct permissions.
Unfortunately, when attempting to create a stored procedure in the master database, we would receive the following error:
Msg 262, Level 14, State 18, Procedure sp_some_procedure_name, Line 1 [Batch Start Line 7] CREATE PROCEDURE permission denied in database 'master'.
Attempting to create users with access to the master db proved just as unsuccessful.
On to the why:
Since RDS is a managed service, there are certain things that aren't available to you, and master db privileges are one of them. I opened a support ticket to see if anything could be done about it and here's the response I received, which was directly from the AWS premium support.
Dear Customer,
Thank you for contacting AWS Premium Support. I am <name-removed> from the RDS team and I will be assisting you today.
From the case correspondence, I understand that you are trying to create a new system stored procedure on RDS MSSQL and it throws error “CREATE PROCEDURE permission denied in database 'master'”. Also, you like to know is there any way to create account with elevated privilege such as “SA”
As per my analysis, you are trying to use the elevated permissions which are not allowed in RDS. AS RDS is managed service, certain features are restricted [1]. RDS master user is the user with higher privilege available but user does not have access to master db. RDS as well sysadmin privilege.
I would like to sincerely apologize for the inconvenient this limitation may have caused and, thank you for the understanding that there is bit of limitations with managed service like RDS where some permissions restrictions are in place to maintain integrity and security. [2]
I have attached the link for the best practices and common troubleshooting tips.
[1] Reference: Master User Account Privileges
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.MasterAccounts.htmlI
hope the above information is helpful and provides clarity in this regard.
[2] Reference: Microsoft SQL Server security
Please feel free to reach out to me should you have any further concerns on this matter, I am happy to help.
I wish you a wonderful day further.
Workaround:
While AWS doesn't want you to have access to the master db, that hasn't stopped people from trying (and succeeding). Here's an interesting way to get around this. Full warning, AWS may find a way to stop you in the future which could cause some breaking changes on your side.
Totally unrelated tips for MS SQL.
Here's a quick and easy SQL statement to get the current size of each individual database for MS SQL Server (in RDS or not).
select name, size,
size * 8/1024 as MB,
size * 8/1024/1024 as GB,
max_size
from sys.master_files
order by size desc;
Backup a specific database to s3
Backup and restore specific databases to S3. In order to complete the steps below, you will need to apply the correct DB Options set which allows backup/restore, and a policy that allows you to write to s3.
exec msdb.dbo.rds_backup_database @source_db_name='<db_name>', @s3_arn_to_backup_to='arn:aws:s3:::<bucket_name>.bak';
Check the status
If the process fails be sure to view the task_info in the output. In order to see it all, it's easier to copy and paste it into the query window or text editor so you can read it a little easier.
exec msdb.dbo.rds_task_status;
Restore a database
exec msdb.dbo.rds_restore_database @restore_db_name='<db_name>',
@s3_arn_to_backup_to='arn:aws:s3:::<bucket_name>.bak';
Check the status
Same as the above: check the status to see how it's coming along and if it fails use the task_info column to determine why
exec msdb.dbo.rds_task_status;
Photo Credit: Photo by Stephen Pedersen on Unsplash