AWS re:Post을(를) 사용하면 다음에 동의하게 됩니다. AWS re:Post 이용 약관

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.

질문됨 9달 전455회 조회
2개 답변
1
수락된 답변

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

답변함 9달 전
profile picture
전문가
검토됨 9달 전
  • 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

답변함 9달 전
profile picture
전문가
검토됨 9달 전
  • 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...

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인

관련 콘텐츠