Nic's questions

Estou tentando configurar a criptografia de backup no SQL 2017 e encontrei alguns problemas.

Não tenho problemas em criar a chave mestra e o certificado em uma máquina. Também não tenho problemas para instalar o certificado em outra máquina e ler o backup, no entanto, tenho um problema em que isso não funciona se a outra máquina estiver executando uma conta de serviço diferente daquela em que o certificado é criado.

Aqui estão as etapas que estou tomando (também tentei restaurar a chave mestra, mas isso também gera um erro):

/* Server 1 */
/* Create the master key */
USE master;  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomeRandomSecureString';  
GO 


/* Create the certificate to be used for backups */
CREATE CERTIFICATE BackupCert  
   WITH SUBJECT = 'Backup Encryption Certificate';  
GO

/* Backup the master key */
BACKUP MASTER KEY TO FILE = '\\FileShare\DatabaseMasterKey_Master.key'   
    ENCRYPTION BY PASSWORD = 'SomeRandomPwd';

BACKUP CERTIFICATE BackupCert TO FILE = '\\FileShare\BackupCert.cer'
  WITH PRIVATE KEY (FILE = '\\FileShareBackupCert.pvk',   
     ENCRYPTION BY PASSWORD = 'RandomEncryptionPwd');   

GO 

/* Server 2 */
/* Create master key */
USE master;  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomeRandomSecureString';  
GO 

/* Restore the cert */
CREATE CERTIFICATE BackupCert FROM FILE = '\\FileShare\BackupCert.cer'
  WITH PRIVATE KEY (FILE = '\\FileShare\BackupCert.pvk',   
     DECRYPTION BY PASSWORD = 'RandomEncryptionPwd');

--Msg 15208, Level 16, State 6, Line 32
--The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

/* Try restoring the master key instead */
DROP MASTER KEY;

RESTORE MASTER KEY FROM FILE = '\\FileShare\DatabaseMasterKey_Master.key'   
    DECRYPTION BY PASSWORD = 'RandomEncryptionPwd'
    ENCRYPTION BY PASSWORD = 'RandomEncryptionPwd';

--Msg 15317, Level 16, State 2, Line 39
--The master key file does not exist or has invalid format.

Referências que eu olhei para tentar descobrir o que está acontecendo aqui:

• https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-encryption?view=sql-server-2017
• https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/create-a-database-master-key?view=sql-server-2017
• https://learn.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql?view=sql-server-2017
• https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/create-an-encrypted-backup?view=sql-server-2017

Também garanti que o SMK seja o mesmo entre todas as máquinas, mas ainda não tive sorte em restaurar o certificado.

Tenho certeza de que estou fazendo algo errado aqui, mas não consegui descobrir o quê.

Todas as idéias seriam apreciadas. Obrigado.

SQL Server 2016, estou tentando trabalhar com alguns dados regulares e retornar um objeto JSON para processamento por outro sistema. O outro sistema não reconhece o wrapper de matriz e, portanto, estou tentando usar WITHOUT_ARRAY_WRAPPER para me livrar disso. Quando usado em uma subconsulta, resultados ímpares são retornados...

SELECT @@SERVERNAME AS [Servername],
( SELECT [Name], [Recovery_Model_Desc]  
    FROM sys.databases
    WHERE name in ('master', 'model', 'msdb')
    FOR JSON PATH
    ) AS d
FOR JSON PATH, ROOT('ServerInformation')

Isso produz os dados esperados, com o wrapper do array...

{"ServerInformation":[{"Servername":"MyServer","d":[{"Name":"master","Recovery_Model_Desc":"SIMPLE"},{"Name":"model","Recovery_Model_Desc" :"COMPLETO"},{"Nome":"msdb","Recovery_Model_Desc":"SIMPLE"}]}]}

No entanto, WITHOUT_ARRAY_WRAPPER produz...

SELECT @@SERVERNAME AS [Servername],
( SELECT [Name], [Recovery_Model_Desc]  
    FROM sys.databases
    WHERE name in ('master', 'model', 'msdb')
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) AS d
FOR JSON PATH, ROOT('ServerInformation')

{"ServerInformation":[{"Servername":"MyServer","d":"{\"Name\":\"master\",\"Recovery_Model_Desc\":\"SIMPLE\"},{\"Name \":\"model\",\"Recovery_Model_Desc\":\"FULL\"},{\"Name\":\"msdb\",\"Recovery_Model_Desc\":\"SIMPLE\"}"}] }

considerando que eu esperaria que produzisse

{"ServerInformation":{"Servername":"MyServer","d":{"Name":"master","Recovery_Model_Desc":"SIMPLE"},{"Name":"model","Recovery_Model_Desc":" COMPLETO"},{"Nome":"msdb","Recovery_Model_Desc":"SIMPLE"}}}

Bug ou resultados esperados?

Editar: ajuste os resultados esperados