I just tested restoring an Azure SQL database. Only the contents of the tables without Row Level Security was restored. My security policy was restored. If I disable it and query the tables it is applied to I can see that the tables are empty.
This is the script we used to configure RLS:
--SELECT @@Version
CREATE SCHEMA Security
go
ALTER TABLE dbo.Workspaces ADD Tenant_Id UniqueIdentifier
DEFAULT CAST(SESSION_CONTEXT(N'Tenant_Id') AS UniqueIdentifier)
go
CREATE FUNCTION Security.tenantAccessPredicate(@UserId UniqueIdentifier)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS accessResult
WHERE @UserId = CAST(SESSION_CONTEXT(N'Tenant_Id') AS UniqueIdentifier)
go
CREATE SECURITY POLICY Security.tenantSecurityPolicy
ADD FILTER PREDICATE Security.tenantAccessPredicate(Tenant_Id) ON dbo.Workspaces,
ADD BLOCK PREDICATE Security.tenantAccessPredicate(Tenant_Id) ON dbo.Workspaces
go
We have checked the issue. SQLBackupAndFTP performs Azure backups via export in .bacpac file. It is not a low-level database’s backup, and it cannot get data that is not available for the user on whose behalf the backups are run.
We recommend you to create a separate user (for example backupAdmin) to perform backups, with providing all necessary rights, then update your predicate function and adding there condition “OR USER_NAME() = ‘backupAdmin’”
ALTER FUNCTION Security.tenantAccessPredicate(@UserId UniqueIdentifier)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS accessResult
WHERE @UserId = CAST(SESSION_CONTEXT(N'Tenant_Id') AS UniqueIdentifier) OR SYSTEM_USER = 'backupAdmin'
go
Now the backup includes all records. I can also read from the restored database using the backupAdmin account that has the db_owner role. I found that db_owner is needed for SQLBackupAndFTP to be able to create, backup and then drop a snapshot.
Other SQL accounts than backupAdmin does not seem to be restored since I cannot login with other users. Is this also a limitation of bacpac exports?
/* These users need to be dropped and recreated after restore */
CREATE USER read FROM LOGIN read;
EXEC sp_addrolemember 'db_datareader', 'read';
CREATE USER app FROM LOGIN app;
EXEC sp_addrolemember 'db_datareader', 'app';
EXEC sp_addrolemember 'db_datawriter', 'app';
CREATE USER migrator FROM LOGIN migrator;
EXEC sp_addrolemember 'db_datareader', 'migrator';
EXEC sp_addrolemember 'db_datawriter', 'migrator';
EXEC sp_addrolemember 'db_ddladmin', 'migrator';
CREATE ROLE migrator
GO
GRANT
SELECT,
INSERT,
UPDATE,
ALTER,
ALTER ANY SECURITY POLICY
TO migrator
GO
EXEC sp_addrolemember 'migrator', 'migrator'
GO
/* This user runs the backup and restore and seems to work after a restore */
CREATE USER backupAdmin FROM LOGIN backupAdmin;
EXEC sp_addrolemember 'db_owner', 'backupAdmin';
I would avoid using the server admin if possible. I tried adding the loginmanager server role to the backupAdmin account. Unfortunately restore fails. Looks like it is trying to create the user that is already running the restore.
Failed to restore "dm365-test-2019-07-07-1848" database on "Restoring" step. > Could not import package.
Error SQL72014: .Net SqlClient Data Provider: Msg 15063, Level 16, State 1, Line 1 The login already has an account under a different user name.
Error SQL72045: Script execution error. The executed script:
CREATE USER [backupAdmin] FOR LOGIN [backupAdmin];
Thank you for waiting. We have checked this issue and found out that it occurs because of database user [dbo] is mapping to login [backupadmin] (because of database recreation by backpadmin) and during the next attempt to mapp login [backupadmin] to database user [backupadmin] the issue occurs.
We recommend you to perform backups via backupAdmin login but restore them from a behalf login, who created a database, as a rule, it is a server admin login.