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

0

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.

asked 8 months ago419 views
2 Answers
1
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

answered 8 months ago
profile picture
EXPERT
reviewed 8 months ago
  • I was able to spin up a Windows instance with SQL Server. It took a little time to resolve the import on that instance. However, even exporting from there with owner set, I still got the original error on restore to RDS. For my purposes, I will just use my windows instance since this is a short term project.

    For those who come across this in the future, I was not able to resolve the original error.

1

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 <database name>

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

answered 8 months ago
profile picture
EXPERT
reviewed 8 months ago
  • The first part of the suggested solution is a common suggested solution I saw elsewhere. However, it cannot be applied. There is no database to change the owner on. (It temporarily exists during the restore operation. Trying to change the owner while the restore is in progress is prohibited. When the stored procedure fails, the database is removed). Also, the msdb.dbo.rds_restore_database does not work on existing databases so it is not possible to create an empty database and set the owner.

    Thanks you for the provided link. I will ask my client to run and provide me with the resulting script and see if that helps...

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions