SQL Server RDS - Restore Native On Premise Backup - Error "because the principal "dbo" does not exist"

Question

I am restoring a native on premise backup from a client into our AWS RDS SQL Server instance.  In order to verify the TDE certificate was installed correctly, I attempted the restore without the certificate and got the message "Cannot find server certificate with thumbprint".  I installed the TDE certificate and that message goes away.

When I attempt the restore the backup: It loads successfully from S3, there is a long pause, then the message "Aborted the task because of a task failure or a concurrent RESTORE_DB request" with the last message being "Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission."  (Full message below)

In searching for a resolution, I do not find  the combination or errors.  I found references that it could be that the on premise is owned by a user not available in RDS -- the client changed the owner to sa and re-exported and I received the same error.

Full log:
```
[2024-03-16 22:35:23.027] Task execution has started.
[2024-03-16 22:35:23.213] clientPRD.BAK: Completed processing 100% of S3 chunks.
[2024-03-16 22:35:23.243] 83 percent processed.
[2024-03-16 22:35:23.257] 99 percent processed.
[2024-03-16 22:35:23.267] 100 percent processed.
[2024-03-16 22:35:23.360] Processed 624 pages for database 'kpmg_encrypted', file 'client_PROD' on file 1.
[2024-03-16 22:35:23.367] Processed 2 pages for database 'kpmg_encrypted', file 'client_PROD_log' on file 1.
[2024-03-16 22:35:23.677] RESTORE DATABASE successfully processed 626 pages in 0.152 seconds (32.149 MB/sec).
[2024-03-16 22:36:22.033] clientPRD.BAK: S3 processing completed successfully
[2024-03-16 22:36:22.077] Aborted the task because of a task failure or a concurrent RESTORE_DB request.
[2024-03-16 22:36:22.250] clientPRD.BAK: S3 processing has been aborted
[2024-03-16 22:36:22.250] Task has been aborted
[2024-03-16 22:36:22.257] Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

```

Accepted Answer

In case, you can't change the owner, then you need to create an intermediate or temporary EC2 SQL Server restore the database there and edit the owner and then take backup again for the restore to RDS SQLServer

Answer

The error suggests that principal dbo (database owner) doesn't exist
```
Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.
```
Check the Database owner of the DB on on-premises using sp_helpdb

if it is blank, then add an existing user which is Sysadmin like 'sa' to be the owner of the database by modifying it in the DB properties

if it not blank, You can create the same login in the RDS SQLServer with same permissions as master user [1]
After performing this, take a backup and restore with TDE restore process.

Reach out to support if this doesn't help.

[1] https://repost.aws/knowledge-center/rds-sql-server-clone-login-permissions

SQL Server RDS - Restore Native On Premise Backup - Error "because the principal "dbo" does not exist"

관련 콘텐츠