Generate Encryption Key During Runtime

by

2014-3-24  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SQLAuthority' GO Certificates are used to safeguard encryption keys, which are used to encrypt data in the database. SQL Server 2005 has the capability to generate self-signed X.509 certificates. Prepare for Service Manager disaster recovery.; 7 minutes to read +3; In this article. This article describes the steps that you must take for Service Manager disaster recovery before problems occur. The steps that you take to recover from a disaster are based on completion of the steps that are outlined here.

-->

APPLIES TO: Azure Data Factory Azure Synapse Analytics (Preview)

This article describes basic security infrastructure that data movement services in Azure Data Factory use to help secure your data. Data Factory management resources are built on Azure security infrastructure and use all possible security measures offered by Azure.

In a Data Factory solution, you create one or more data pipelines. A pipeline is a logical grouping of activities that together perform a task. These pipelines reside in the region where the data factory was created.

Even though Data Factory is only available in few regions, the data movement service is available globally to ensure data compliance, efficiency, and reduced network egress costs.

Azure Data Factory does not store any data except for linked service credentials for cloud data stores, which are encrypted by using certificates. Bad piggies activation key generator. With Data Factory, you creates credential in Azure Key Vault. Data Factory retrieves the credential during the execution of an activity. For more information, see Store credential in Azure Key Vault.

Data encryption in transit

If the cloud data store supports HTTPS or TLS, all data transfers between data movement services in Data Factory and a cloud data store are via secure channel HTTPS or TLS .

Note

All connections to Azure SQL Database and Azure SQL Data Warehouse require encryption (SSL/TLS) while data is in transit to and from the database. When you're authoring a pipeline by using JSON, add the encryption property and set it to true in the connection string. For Azure Storage, you can use HTTPS in the connection string.

Note

To enable encryption in transit while moving data from Oracle follow one of the below options:

  1. In Oracle server, go to Oracle Advanced Security (OAS) and configure the encryption settings, which supports Triple-DES Encryption (3DES) and Advanced Encryption Standard (AES), refer here for details. ADF automatically negotiates the encryption method to use the one you configure in OAS when establishing connection to Oracle.
  2. In ADF, you can add EncryptionMethod=1 in the connection string (in the Linked Service). This will use SSL/TLS as the encryption method. To use this, you need to disable non-SSL encryption settings in OAS on the Oracle server side to avoid encryption conflict.

Data encryption at rest

Some data stores support encryption of data at rest. We recommend that you enable the data encryption mechanism for those data stores.

Azure SQL Data Warehouse

Transparent Data Encryption (TDE) in Azure SQL Data Warehouse helps protect against the threat of malicious activity by performing real-time encryption and decryption of your data at rest. This behavior is transparent to the client. For more information, see Secure a database in SQL Data Warehouse.

Azure SQL Database

Azure SQL Database also supports transparent data encryption (TDE), which helps protect against the threat of malicious activity by performing real-time encryption and decryption of the data, without requiring changes to the application. This behavior is transparent to the client. For more information, see Transparent data encryption for SQL Database and Data Warehouse.

Azure Data Lake Store

Azure Data Lake Store also provides encryption for data stored in the account. When enabled, Data Lake Store automatically encrypts data before persisting and decrypts before retrieval, making it transparent to the client that accesses the data. For more information, see Security in Azure Data Lake Store.

Azure Blob storage and Azure Table storage

Azure Blob storage and Azure Table storage support Storage Service Encryption (SSE), which automatically encrypts your data before persisting to storage and decrypts before retrieval. For more information, see Azure Storage Service Encryption for Data at Rest.

Amazon S3

Amazon S3 supports both client and server encryption of data at rest. For more information, see Protecting Data Using Encryption.

Amazon Redshift

Amazon Redshift supports cluster encryption for data at rest. For more information, see Amazon Redshift Database Encryption.

Salesforce

Salesforce supports Shield Platform Encryption that allows encryption of all files, attachments, and custom fields. For more information, see Understanding the Web Server OAuth Authentication Flow.

Hybrid scenarios

Hybrid scenarios require self-hosted integration runtime to be installed in an on-premises network, inside a virtual network (Azure), or inside a virtual private cloud (Amazon). The self-hosted integration runtime must be able to access the local data stores. For more information about self-hosted integration runtime, see How to create and configure self-hosted integration runtime.

The command channel allows communication between data movement services in Data Factory and self-hosted integration runtime. The communication contains information related to the activity. The data channel is used for transferring data between on-premises data stores and cloud data stores.

On-premises data store credentials

The credentials can be stored within data factory or be referenced by data factory during the runtime from Azure Key Vault. If storing credentials within data factory, it is always stored encrypted on the self-hosted integration runtime.

  • Store credentials locally. If you directly use the Set-AzDataFactoryV2LinkedService cmdlet with the connection strings and credentials inline in the JSON, the linked service is encrypted and stored on self-hosted integration runtime. In this case the credentials flow through azure backend service, which is extremely secure, to the self-hosted integration machine where it is finally encrypted and stored. The self-hosted integration runtime uses Windows DPAPI to encrypt the sensitive data and credential information.

  • Store credentials in Azure Key Vault. You can also store the data store's credential in Azure Key Vault. Data Factory retrieves the credential during the execution of an activity. For more information, see Store credential in Azure Key Vault.

  • Store credentials locally without flowing the credentials through Azure backend to the self-hosted integration runtime. If you want to encrypt and store credentials locally on the self-hosted integration runtime without having to flow the credentials through data factory backend, follow the steps in Encrypt credentials for on-premises data stores in Azure Data Factory. All connectors support this option. The self-hosted integration runtime uses Windows DPAPI to encrypt the sensitive data and credential information.

    Use the New-AzDataFactoryV2LinkedServiceEncryptedCredential cmdlet to encrypt linked service credentials and sensitive details in the linked service. You can then use the JSON returned (with the EncryptedCredential element in the connection string) to create a linked service by using the Set-AzDataFactoryV2LinkedService cmdlet.

Ports used when encrypting linked service on self-hosted integration runtime

By default, PowerShell uses port 8060 on the machine with self-hosted integration runtime for secure communication. If necessary, this port can be changed.

Encryption in transit

All data transfers are via secure channel HTTPS and TLS over TCP to prevent man-in-the-middle attacks during communication with Azure services.

Encryption Software

You can also use IPSec VPN or Azure ExpressRoute to further secure the communication channel between your on-premises network and Azure.

Azure Virtual Network is a logical representation of your network in the cloud. You can connect an on-premises network to your virtual network by setting up IPSec VPN (site-to-site) or ExpressRoute (private peering).

Rsa Public Key Encryption

The following table summarizes the network and self-hosted integration runtime configuration recommendations based on different combinations of source and destination locations for hybrid data movement.

SourceDestinationNetwork configurationIntegration runtime setup
On-premisesVirtual machines and cloud services deployed in virtual networksIPSec VPN (point-to-site or site-to-site)The self-hosted integration runtime should be installed on an Azure virtual machine in the virtual network.
On-premisesVirtual machines and cloud services deployed in virtual networksExpressRoute (private peering)The self-hosted integration runtime should be installed on an Azure virtual machine in the virtual network.
On-premisesAzure-based services that have a public endpointExpressRoute (Microsoft peering)The self-hosted integration runtime can be installed on-premises or on an Azure virtual machine.

The following images show the use of self-hosted integration runtime for moving data between an on-premises database and Azure services by using ExpressRoute and IPSec VPN (with Azure Virtual Network):

ExpressRoute

IPSec VPN

Firewall configurations and allow list setting up for IP addresses

Firewall requirements for on-premises/private network

In an enterprise, a corporate firewall runs on the central router of the organization. Windows Firewall runs as a daemon on the local machine in which the self-hosted integration runtime is installed.

The following table provides outbound port and domain requirements for corporate firewalls:

Domain namesOutbound portsDescription
*.servicebus.windows.net443Required by the self-hosted integration runtime to connect to data movement services in Azure Data Factory.
*.frontend.clouddatahub.net443Required by the self-hosted integration runtime to connect to the Data Factory service.
download.microsoft.com443Required by the self-hosted integration runtime for downloading the updates. If you have disabled auto-update, you can skip configuring this domain.
*.core.windows.net443Used by the self-hosted integration runtime to connect to the Azure storage account when you use the staged copy feature.
*.database.windows.net1433Required only when you copy from or to Azure SQL Database or Azure SQL Data Warehouse and optional otherwise. Use the staged-copy feature to copy data to SQL Database or SQL Data Warehouse without opening port 1433.
*.azuredatalakestore.net
login.microsoftonline.com/<tenant>/oauth2/token		443Required only when you copy from or to Azure Data Lake Store and optional otherwise.

Note

You might have to manage ports or set up allow list for domains at the corporate firewall level as required by the respective data sources. This table only uses Azure SQL Database, Azure SQL Data Warehouse, and Azure Data Lake Store as examples.

The following table provides inbound port requirements for Windows Firewall:

Inbound portsDescription
8060 (TCP)Required by the PowerShell encryption cmdlet as described in Encrypt credentials for on-premises data stores in Azure Data Factory, and by the credential manager application to securely set credentials for on-premises data stores on the self-hosted integration runtime.

IP configurations and allow list setting up in data stores

Some data stores in the cloud also require that you allow the IP address of the machine accessing the store. Ensure that the IP address of the self-hosted integration runtime machine is allowed or configured in the firewall appropriately.

The following cloud data stores require that you allow the IP address of the self-hosted integration runtime machine. Some of these data stores, by default, might not require allow list.

Frequently asked questions

Can the self-hosted integration runtime be shared across different data factories?

Yes. More details here.

What are the port requirements for the self-hosted integration runtime to work?

The self-hosted integration runtime makes HTTP-based connections to access the internet. The outbound ports 443 must be opened for the self-hosted integration runtime to make this connection. Open inbound port 8060 only at the machine level (not the corporate firewall level) for credential manager application. If Azure SQL Database or Azure SQL Data Warehouse is used as the source or the destination, you need to open port 1433 as well. For more information, see the Firewall configurations and allow list setting up for IP addresses section.

Next steps

For information about Azure Data Factory Copy Activity performance, see Copy Activity performance and tuning guide.

-->

SQL Server 2014 Integration Services (SSIS)SQL Server 2014 Integration Services (SSIS)包括 SSISDB 数据库。includes the SSISDB database.查询 SSISDB 数据库中的视图可以检查 SSISDB 目录中存储的对象、设置和操作数据。You query views in the SSISDB database to inspect objects, settings, and operational data that are stored in the SSISDB catalog.本主题说明如何备份和还原该数据库。This topic provides instructions for backing up and restoring the database.

SSISDB 目录存储部署到 ** 服务器的包**Integration ServicesIntegration Services。The SSISDB catalog stores the packages that you've deployed to the Integration ServicesIntegration Services server.有关该目录的详细信息，请参阅 SSIS 目录。For more information about the catalog, see SSIS Catalog.

备份 SSIS 数据库To Back up the SSIS Database

  1. 打开 SQL Server Management StudioSQL Server Management Studio 并连接到 SQL ServerSQL Server实例。Open SQL Server Management StudioSQL Server Management Studio and connect to an instance of SQL ServerSQL Server.

  2. 使用 BACKUP MASTER KEY Transact-SQL 语句备份 SSISDB 数据库的主密钥。Back up the master key for the SSISDB database, by using the BACKUP MASTER KEY Transact-SQL statement.该密钥存储在您指定的文件中。The key is stored in a file that you specify.使用密码加密该文件中的主密钥。Use a password to encrypt the master key in the file.

    有关语句的详细信息，请参阅 BACKUP MASTER KEY (Transact-SQL)。For more information about the statement, see BACKUP MASTER KEY (Transact-SQL).

    在下面的示例中，将主密钥导出到 c:temp directoryRCTestInstKey 文件。In the following example, the master key is exported to the c:temp directoryRCTestInstKey file.使用 LS2Setup! 密码加密主密钥。The LS2Setup! password is used to encrypt the master key.

  3. 在 中使用 “备份数据库” SQL Server Management StudioSQL Server Management Studio对话框备份 SSISDB 数据库。Back up the SSISDB database by using the Backup Database dialog box in SQL Server Management StudioSQL Server Management Studio.有关详细信息，请参阅 如何备份数据库 (SQL Server Management Studio)。For more information, see How to: Back Up a Database (SQL Server Management Studio).

  4. 通过执行以下操作，生成 ##MS_SSISServerCleanupJobLogin## 的 CREATE LOGIN 脚本。Generate the CREATE LOGIN script for ##MS_SSISServerCleanupJobLogin##, by doing the following.有关详细信息，请参阅 CREATE LOGIN (Transact-SQL)。For more information, see CREATE LOGIN (Transact-SQL).

    1. 在 SQL Server Management StudioSQL Server Management Studio的对象资源管理器中，展开 “安全性” 节点，然后展开 “登录名” 节点。In Object Explorer in SQL Server Management StudioSQL Server Management Studio, expand the Security node and then expand the Logins node.

    2. 右键单击 ##MS_SSISServerCleanupJobLogin##，然后依次单击“编写登录脚本为”“CREATE 到”“新查询编辑器窗口”。 > **** > ****Right-click ##MS_SSISServerCleanupJobLogin##, and then click Script Login as > CREATE To > New Query Editor Window.

  5. 如果要将 SSISDB 数据库还原到从未创建 SSISDB 目录的 SQL ServerSQL Server 实例，请执行以下操作生成 sp_ssis_startup 的 CREATE PROCEDURE 脚本。If you will be restoring the SSISDB database to an SQL ServerSQL Server instance where the SSISDB catalog was never created, generate the CREATE PROCEDURE script for sp_ssis_startup, by doing the following.有关详细信息，请参阅 CREATE PROCEDURE (Transact-SQL)。For more information, see CREATE PROCEDURE (Transact-SQL).

    1. 在对象资源管理器中，展开 '数据库' 节点，然后展开 '系统数据库 > ' '主 > 可编程性 > ' '存储过程' 节点。In Object Explorer, expand the Databases node and then expand the System Databases > master > Programmability > Stored Procedures node.

    2. 右键单击 dbo.sp_ssis_startup，然后依次单击“编写存储过程脚本为”“CREATE 到”“新查询编辑器窗口”。 > **** > ****Right click dbo.sp_ssis_startup, and then click Script Stored Procedure as > CREATE To > New Query Editor Window.

  6. 确认 SQL Server 代理已启动Confirm that SQL Server Agent has been started

  7. 如果要将 SSISDB 数据库还原到从不创建 SSISDB 目录的 SQL ServerSQL Server 实例，请执行以下操作生成 SSIS 服务器维护作业的脚本。If you will be restoring the SSISDB database to an SQL ServerSQL Server instance where the SSISDB catalog was never created, generate a script for the SSIS Server Maintenance Job by doing the following.创建 SSISDB 目录时自动在 SQL ServerSQL Server 代理中创建该脚本。The script is created in SQL ServerSQL Server Agent automatically when the SSISDB catalog is created.该作业帮助清除保留期窗口之外的操作日志并删除较旧版本的项目。The job helps clean up cleanup operation logs outside the retention window and remove older versions of projects.

    1. 在对象资源管理器中，展开 “SQL Server 代理” 节点，然后展开 “作业” 节点。In Object Explorer, expand the SQL Server Agent node and then expand the Jobs node.

    2. 右键单击 'SSIS 服务器维护作业'，然后单击 '创建到 > 新查询编辑器窗口的脚本作业 > '。Right click SSIS Server Maintenance Job, and then click Script Job as > CREATE To > New Query Editor Window.

还原 SSIS 数据库To Restore the SSIS Database

Generate Encryption Key During Runtime Download

  1. 如果要将 SSISDB 数据库还原到从不创建 SSISDB 目录的 SQL ServerSQL Server 实例，请通过运行 sp_configure 存储过程来启用公共语言运行时 (clr)。If you are restoring the SSISDB database to an SQL ServerSQL Server instance where the SSISDB catalog was never created, enable common language runtime (clr) by running the sp_configure stored procedure.有关详细信息，请参阅 sp_configure (Transact-SQL) 和 clr enabled 选项。For more information, see sp_configure (Transact-SQL) and clr enabled Option.

  2. 如果要将 SSISDB 数据库还原到从不创建 SSISDB 目录的 SQL ServerSQL Server 实例，请创建非对称密钥和对应非对称密钥的登录名并将 UNSAFE 权限授予该登录名。If you are restoring the SSISDB database to an SQL ServerSQL Server instance where the SSISDB catalog was never created, create the asymmetric key and the login from the asymmetric key, and grant UNSAFE permission to the login.

    Integration ServicesIntegration ServicesCLR 存储过程要求将 UNSAFE 权限授予该登录名，因为该登录名需要对受限制资源（如 Microsoft Win32 API）的其他访问权限。CLR stored procedures require UNSAFE permissions to be granted to the login because the login requires additional access to restricted resources, such as the Microsoft Win32 API.有关 UNSAFE 代码权限的详细信息，请参阅 Creating an Assembly。For more information about the UNSAFE code permission, see Creating an Assembly.

  3. 在 中使用 “还原数据库” SQL Server Management StudioSQL Server Management Studio对话框从备份中还原 SSISDB 数据库。Restore the SSISDB database from the backup by using the Restore Database dialog box in SQL Server Management StudioSQL Server Management Studio.有关详细信息，请参阅以下主题。For more information, see the following topics.

  4. 执行你在备份 SSIS 数据库中为 ##MS_SSISServerCleanupJobLogin##、sp_ssis_startup 和 SSIS 服务器维护作业创建的脚本。Execute the scripts that you created in the To Back up the SSIS Database for ##MS_SSISServerCleanupJobLogin##, sp_ssis_startup, and SSIS Server Maintenance Job.确认 SQL Server 代理已启动。Confirm that SQL Server Agent has been started.

  5. 运行以下语句以将 sp_ssis_startup 过程设置为自动执行。Run the following statement to set the sp_ssis_startup prodecure for autoexecution.有关详细信息，请参阅 sp_procoption (Transact-SQL)。For more information, see sp_procoption (Transact-SQL).

  6. 通过在 ** 中使用“登录属性”对话框，将 SSISDB 用户 ##MS_SSISServerCleanupJobUser##（SSISDB 数据库）映射到 ##MS_SSISServerCleanupJobLogin##。**SQL Server Management StudioSQL Server Management StudioMap the SSISDB user ##MS_SSISServerCleanupJobUser## (SSISDB database) to ##MS_SSISServerCleanupJobLogin##, by using the Login Properties dialog box in SQL Server Management StudioSQL Server Management Studio.

  7. 使用下列方法之一还原主密钥。Restore the master key by using one of the following methods.有关加密的详细信息，请参阅 Encryption Hierarchy。For more information about encryption, see Encryption Hierarchy.

    • 方法1Method 1

      如果已备份数据库主密钥且具有用于加密主密钥的密码，则使用此方法。Use this method if you've already performed a backup of the database master key, and you have the password used to encrypt the master key.

      备注

      确认 SQL ServerSQL Server 服务帐户有权读取备份密钥文件。Confirm that the SQL ServerSQL Server service account has permissions to read the backup key file.

      备注

      如果服务主密钥尚未加密数据库主密钥，将看到 SQL Server Management StudioSQL Server Management Studio 中显示的以下警告消息。You will see the following warning message displayed in SQL Server Management StudioSQL Server Management Studio if the database master key has not yet been encrypted by the service master key.忽略警告消息。Ignore the warning message.

      当前主密钥无法解密。已忽略此错误，因为指定了 FORCE 选项。The current master key cannot be decrypted. The error was ignored because the FORCE option was specified.

      FORCE 参数指定即使当前数据库主密钥未打开，也应继续执行还原过程。The FORCE argument specifies that the restore process should continue even if the current database master key is not open.对于 SSISDB 目录，由于在您正在其中还原数据库的实例上未打开数据库主密钥，您将看到此消息。For the SSISDB catalog, because the database master key has not been opened on the instance where you are restoring the database, you will see this message.

    • 方法2Method 2

      如果您具有用于创建 SSISDB 的原始密码，则使用此方法。Use this method if you have the original password that was used to create SSISDB.

  8. 通过运行 Integration ServicesIntegration Services catalog.check_schema_version ，确定 SSISDB 目录架构与二进制文件（ISServerExec 和 SQLCLR 程序集）是否兼容。Determine whether the SSISDB catalog schema and the Integration ServicesIntegration Services binaries (ISServerExec and SQLCLR assembly) are compatible, by running catalog.check_schema_version.

  9. 若要确认 SSISDB 数据库已成功还原，请针对 SSISDB 目录执行操作，如运行部署到 Integration ServicesIntegration Services 服务器的包。To confirm that the SSISDB database has been restored successfully, perform operations against the SSISDB catalog such as running packages that have been deployed to the Integration ServicesIntegration Services server.有关详细信息，请参阅 使用 SQL Server Management Studio 在 SSIS 服务器上运行包。For more information, see Run a Package on the SSIS Server Using SQL Server Management Studio.

Encryption Key Example

移动 SSIS 数据库To Move the SSIS Database

Generate Encryption Key During Runtime Error

  • 按移动用户数据库的说明操作。Follow the instructions for moving user databases.有关详细信息，请参阅 Move User Databases。For more information, see Move User Databases.

    确保您备份 SSISDB 数据库的主密钥并保护备份文件。Ensure that you back up the master key for the SSISDB database and protect the backup file.有关详细信息，请参阅 备份 SSIS 数据库。For more information, see To Back up the SSIS Database.

    确保在尚未创建 SSISDB 目录的新 SQL ServerSQL Server 实例中创建 Integration Services (SSIS) 相关对象。Ensure that the Integration Services (SSIS) relevant objects are created in the new SQL ServerSQL Server instance where the SSISDB catalog has not yet been created.