主页 / user-11639

Nic's questions

Martin Hope
Nic
Asked: 2018-07-11 08:08:35 +0800 CST
  • 3

我正在尝试在 SQL 2017 中配置备份加密，但遇到了一些问题。

我在一台机器上创建主密钥和证书没有问题。将证书安装在另一台机器上并读取备份也没有问题，但是，如果另一台机器运行的服务帐户与创建证书的服务帐户不同，我有一个问题。

以下是我正在采取的步骤（我也尝试恢复主密钥，但这也会引发错误）：

/* 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.

我查看的参考资料试图弄清楚这里发生了什么：

我还确保所有机器之间的 SMK 都是相同的，但在恢复证书方面仍然没有运气。

我确信我在这里做错了什么，但我无法弄清楚是什么。

任何想法，将不胜感激。谢谢。

sql-server backup
Martin Hope
Nic
Asked: 2016-08-31 15:07:17 +0800 CST
  • 7

SQL Server 2016，我正在尝试使用一些常规数据并返回一个 JSON 对象以供另一个系统处理。另一个系统无法识别数组包装器，因此我尝试使用 WITHOUT_ARRAY_WRAPPER 来摆脱它。在子查询中使用时，会返回奇怪的结果...

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')

这会产生预期的数据，使用数组包装器......

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

但是， WITHOUT_ARRAY_WRAPPER 会产生...

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\"}"}] }

而我希望它会产生

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

错误或预期结果？

编辑：调整预期结果

sql-server subquery