BACKUP DATABASE permission denied when using AWS RDS



  • Our team is migrating to the cloud and we have chosen RDS For MSSQL to host our current db server. As part of that, we have a lot of stored procedures that need to be modified. Some of those are backupping certain databases on disk. As this is RDS and we do not have access to the server directly, I have modified a backup proc to point to D:\S3\ path as this (according to the https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/User.SQLServer.Options.S3-integration.html#Appendix.SQLServer.Options.S3-integration.enabling ) location should be accessible.

    That said, when I run the below stored procedure I get an error:

    CREATE PROCEDURE TestStoredProcGeorgi
    AS
    BEGIN
      BACKUP DATABASE [Status] TO DISK = 'D:\S3\status.bak'
        WITH RETAINDAYS = 0,
            INIT;
    END
    GO
    

    execution: exec TestStoredProcGeorgi this is the error I get:

    Msg 262, Level 14, State 1, Procedure TestStoredProcGeorgi, Line 4 [Batch Start Line 9]
    BACKUP DATABASE permission denied in database 'Status'.
    Msg 3013, Level 16, State 1, Procedure TestStoredProcGeorgi, Line 4 [Batch Start Line 9]
    BACKUP DATABASE is terminating abnormally.
    

    Note: I have enabled S3_Integration on the RDS Instance

    What I am missing here?



  • you should follow this passages:

    First, if your databases don’t already have an option group, you’ll want to add one. Here’s the CLI I used in PowerShell to add one to my database:

    aws rds create-option-group `
        --option-group-name hsroptiongroup `
        --engine-name sqlserver-ex `
        --major-engine-version "14.00" `
        --option-group-description "Option Group to customize SQL Server hsr"
    

    From there, you need to add an option to the option group. The command is pretty explicit:

    aws rds add-option-to-option-group `
        --apply-immediately `
        --option-group-name hsroptiongroup `
        --options "OptionName=SQLSERVER_BACKUP_RESTORE, OptionSettings=[{Name=IAM_ROLE_ARN,Value=arn:aws:iam::xxxxxxx:role/sqlbackup}]"
    

    Attach the option group to the instance next:

    aws rds modify-db-instance `
        --db-instance-identifier hsr `
        --option-group-name hsroptiongroup `
        --apply-immediately
    

    So, done, right? I can now run BACKUP DATABASE commands.

    Well…. No.

    The trick is, you’ve made it possible to get a SQL Server native backup. You didn’t make it possible to run the BACKUP command. Instead, you have to do something that looks like this:

    EXEC msdb.dbo.rds_backup_database 
        @source_db_name = 'mydb',
        @s3_arn_to_backup_to = 'arn:aws:s3:::mybackup/mydb.bak',
        --[@kms_master_key_arn='arn:aws:kms:region:account-id:key/key-id'], 
        @overwrite_s3_backup_file = 1,
        @type = 'FULL'; --,
    --[@number_of_files=n];
    

    The deal is, you can get a native backup. That means, you can copy a file, in my case there, mydb.bak, and run a restore locally, or on another RDS instance. You can only take FULL or DIFFERENTIAL. You’ll note the parameter @kms_master_key_arn. That’s so you can encrypt the backup (a very good idea). I skipped setting up a key for this demo.

    There’s also a command for restoring, msdb.dbo.rds_restore_database.

    Source: https://www.scarydba.com/2020/06/29/sql-server-backups-on-aws-rds/




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2