Jak sprawdzić, czy kopia zapasowa serwera SQL jest szyfrowana za pomocą TDE bez przywracania kopii zapasowej

13

Czy istnieje sposób sprawdzenia z pliku kopii zapasowej programu SQL Server lub tabel MSDB, czy kopia zapasowa jest szyfrowana za pomocą TDE bez próby przywrócenia pliku kopii zapasowej?

Dzięki

sql-server

— yegnasew
źródło

10

Wyobraź sobie przez chwilę, że masz 1 terabajtową bazę danych. Tworzenie kopii zapasowej zajmuje trochę czasu, a szyfrowanie zajmuje trochę czasu. Wyobraź sobie, że:

9:00 - zaczniesz robić pełną kopię zapasową
9:01 - w innym oknie zaczynasz włączać TDE w bazie danych
9:05 - tworzenie kopii zapasowej jest zakończone
9:10 - TDE kończy się

Czego można się spodziewać po zwróceniu zapytania, biorąc pod uwagę, że jak tylko przywrócisz pełną kopię zapasową, nadal będzie stosować TDE, szyfrując resztę bazy danych?

I odwrotnie, wyobraź sobie, że zaczynasz od już zaszyfrowanej bazy danych i:

9:00 - usuwasz TDE (co zajmuje trochę czasu)
9:01 - zaczynasz pełną kopię zapasową
9:05 - strony danych nie są już szyfrowane
9:06 - pełna kopia zapasowa została ukończona

Czego można oczekiwać od zapytania? Oto przykładowe scenariusze, dlaczego szyfrowanie TDE nie jest jednym z pól zawartych w pliku msdb.dbo.backupset .

— Brent Ozar
źródło

Dziękuję wszystkim za szybką odpowiedź i @ScottHodgin tak. Chciałem wiedzieć, czy kopia zapasowa pochodzi z bazy danych TDE i czy odpowiedź Brenta była jasna.

— yegnasew

@Brent Ozar: W obu przypadkach chciałbym, aby zapytanie zwróciło „Częściowo zaszyfrowane”. Tak, oznacza to posiadanie właściwości 3-stanowej zamiast wartości logicznej. Oczywiście taka właściwość nie jest naprawdę wykonalna, chyba że Microsoft ją wdroży.

— Brian

@Brian Bingo. Nie jest to możliwe, biorąc pod uwagę obecny stan.

— Brent Ozar,

30

Głosowałem za odpowiedzią Brenta , ponieważ jego scenariusz zdecydowanie może zmącić wodę, czy kopia zapasowa zawierała dane TDE.

Jeśli jednak masz włączone TDE, wydaje się, że PRZYWRACANIE FILELISTONLY (Transact-SQL) może dostarczyć poszukiwanych informacji. W zestawie wyników znajduje się kolumna o nazwie TDEThumbprint„Pokazuje odcisk palca klucza szyfrowania bazy danych. Odcisk palca szyfratora to skrót SHA-1 certyfikatu, za pomocą którego klucz jest szyfrowany”.

Przejrzałem niektóre z moich kopii zapasowych, które były zarówno szyfrowane TDE, jak i nieszyfrowane TDE.

Kopie zapasowe moich baz danych TDE miały odcisk palca certyfikatu w tej kolumnie, a kopie zapasowe, które nie miały baz danych TDE, miały wartość zerową.

— Scott Hodgin
źródło

2

Rozszerzając odpowiedź Scotta, oto zapytanie SQL, które powie ci, czy kopia zapasowa jest zaszyfrowana, czy nie.

Declare @backupFile varchar(max) = 'J:\backups\psa20191029.bak'

 DECLARE @fileListTable TABLE (
    [LogicalName]           NVARCHAR(128),
    [PhysicalName]          NVARCHAR(260),
    [Type]                  CHAR(1),
    [FileGroupName]         NVARCHAR(128),
    [Size]                  NUMERIC(20,0),
    [MaxSize]               NUMERIC(20,0),
    [FileID]                BIGINT,
    [CreateLSN]             NUMERIC(25,0),
    [DropLSN]               NUMERIC(25,0),
    [UniqueID]              UNIQUEIDENTIFIER,
    [ReadOnlyLSN]           NUMERIC(25,0),
    [ReadWriteLSN]          NUMERIC(25,0),
    [BackupSizeInBytes]     BIGINT,
    [SourceBlockSize]       INT,
    [FileGroupID]           INT,
    [LogGroupGUID]          UNIQUEIDENTIFIER,
    [DifferentialBaseLSN]   NUMERIC(25,0),
    [DifferentialBaseGUID]  UNIQUEIDENTIFIER,
    [IsReadOnly]            BIT,
    [IsPresent]             BIT,
    [TDEThumbprint]         VARBINARY(32) -- remove this column if using SQL 2005
)
INSERT INTO @fileListTable EXEC('RESTORE FILELISTONLY FROM DISK = '''+@backupFile+'''')


select distinct LogicalName + case when TDEThumbprint is null then ' is not encrypted'
                                    else ' is encrypted'
                                    end as AmIEncrypted
from @fileListTable
where type='D'

— Anthony Genovese
źródło