CREATE CREDENTIAL (Transact-SQL)

文章
07/26/2024

建立伺服器層級認證。認證是包含驗證資訊的記錄，而該資訊是連線到 SQL Server 外部資源時的必要資訊。大部分認證都包含 Windows 使用者和密碼。例如，將資料庫備份儲存至某位置，可能需要 SQL Server 提供特殊的認證才能存取該位置。如需詳細資訊，請參閱認證 (資料引擎)。

注意

若要在資料庫層級建立認證，請使用 CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)。當您需要針對伺服器上的多個資料庫使用相同的認證時，請使用建立伺服器層級認證 CREATE CREDENTIAL 。

使用建立資料庫範圍認證 CREATE DATABASE SCOPED CREDENTIAL ，讓資料庫更具可攜性。當資料庫移動至新的伺服器時，資料庫範圍認證會隨著一起移動。
在 SQL Database 上使用資料庫範圍認證。
搭配 PolyBase 和 Azure SQL 受控執行個體數據虛擬化功能使用資料庫範圍認證。

Transact-SQL 語法慣例

語法

CREATE CREDENTIAL credential_name
WITH IDENTITY = 'identity_name'
    [ , SECRET = 'secret' ]
        [ FOR CRYPTOGRAPHIC PROVIDER cryptographic_provider_name ]

引數

credential_name

指定要建立的認證名稱。 credential_name 的開頭不可以是編號 (#) 符號。系統認證必須以 ## 為開頭。

重要

當使用共用存取簽章 (SAS) 時，此名稱必須符合容器路徑、開頭為 https，而且不能包含正斜線。請參閱範例 D。

當用於使用外部數據平台進行備份/還原時，例如 Azure Blob 儲存體或 S3 相容平臺，下表提供常見的路徑：

外部資料來源	位置路徑	範例
Azure Blob 儲存體（V2）	`https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>`	範例 D.
與 S3 相容的物件儲存體	- S3 相容的記憶體： `s3://<server_name>:<port>/` - AWS S3： `s3://<bucket_name>.S3.<region>.amazonaws.com[:port]/<folder>` 或 `s3://s3.<region>.amazonaws.com[:port]/<bucket_name>/<folder>`	範例 F.

IDENTITY ='identity_name'

指定連接到伺服器外部時所要使用的帳戶名稱。認證用來存取 Azure Key Vault 時，IDENTITY 是金鑰保存庫的名稱。請參閱以下的範例 C。認證使用共用存取簽章 (SAS) 時，IDENTITY 是 SHARED ACCESS SIGNATURE。請參閱下方範例 D。

重要

Azure SQL Database 只支援 Azure Key Vault 與共用存取簽章身分識別。不支援 Windows 使用者身分識別。

SECRET ='secret'

指定外寄驗證所需的秘密。

當認證用來存取 Azure 金鑰保存庫時，SECRET 自變數必須格式化為服務主體的<用戶端標識元>（不含連字元）和<秘密>，一起傳遞，且兩者之間沒有空格。 請參閱以下的範例 C。當認證使用共用存取簽章時，SECRET 是共用存取簽章權杖。請參閱下方範例 D。如需有關在 Azure 容器上建立預存存取原則與共用存取簽章的詳細資訊，請參閱第 1 課：在 Azure 容器上建立預存存取原則和共用存取簽章。

針對密碼編譯提供者 cryptographic_provider_name

指定企業金鑰管理提供者的名稱（EKM）。 如需金鑰管理的詳細資訊，請參閱可延伸金鑰管理 (EKM)。

備註

當 IDENTITY 是 Windows 使用者時，秘密可以是密碼。秘密是利用服務主要金鑰來加密的。如果重新產生服務主要金鑰，便會利用新的服務主要金鑰來重新加密秘密。

建立認證之後，您可以利用 CREATE LOGIN 或 ALTER LOGIN，將其對應至 SQL Server 登入。 SQL Server 登入只能對應至一個認證，但單一認證則可對應至多個 SQL Server 登入。如需詳細資訊，請參閱認證 (資料引擎)。伺服器層級認證只能對應至登入，不能對應至資料庫使用者。

您可以在 sys.credentials 目錄檢視中，看到認證的相關資訊。

如果提供者沒有任何登入對應認證，系統就會使用對應至 SQL Server 服務帳戶的認證。

一個登入可以具有多個對應認證，只要這些認證用於不同的提供者即可。但是，每個登入的每個提供者必須只有一個對應認證。相同的認證可對應至其他登入。

權限

需要 ALTER ANY CREDENTIAL 權限。

範例

A. 建立 Windows 身分識別的認證

下列範例會建立一個稱為 AlterEgo 的認證。這個認證包含 Windows 使用者 Mary5 和密碼。

CREATE CREDENTIAL AlterEgo WITH IDENTITY = 'Mary5',
    SECRET = '<EnterStrongPasswordHere>';
GO

B. 建立 EKM 的認證

下列範例會透過 EKM 的管理工具 (包含基本帳戶類型和密碼)，在 EKM 模組上使用之前建立的帳戶 User1OnEKM。伺服器上系統管理員帳戶會建立用來連線到 EKM 帳戶的認證，並將其指派給 User1 SQL Server 帳戶：

CREATE CREDENTIAL CredentialForEKM
    WITH IDENTITY='User1OnEKM', SECRET='<EnterStrongPasswordHere>'
    FOR CRYPTOGRAPHIC PROVIDER MyEKMProvider;
GO

/* Modify the login to assign the cryptographic provider credential */
ALTER LOGIN User1
ADD CREDENTIAL CredentialForEKM;

C. 使用 Azure 金鑰保存庫建立 EKM 的認證

下列範例會建立 SQL Server 認證，供資料庫引擎在使用適用於 Microsoft Azure Key Vault 的 SQL Server 連接器時用於存取 Azure Key Vault。如需使用 SQL Server 連接器的完整範例，請參閱使用 Azure Key Vault 進行可延伸金鑰管理 (SQL Server)。

重要

CREATE CREDENTIAL 的 IDENTITY 引數需要金鑰保存庫名稱。 CREATE CREDENTIAL 的 SECRET 引數要求 <Client ID> (不含連字號) 和 <Secret> 一併傳遞，而且兩者之間不含空格。

在下列範例中， 用戶端標識符 （11111111-2222-3333-4444-555555555555）會移除連字元，並輸入為字串 11111111222233334444555555555555 ，而 Secret 則以字串 SECRET_DBEngine表示。

USE master;
CREATE CREDENTIAL Azure_EKM_TDE_cred
    WITH IDENTITY = 'ContosoKeyVault',
    SECRET = '11111111222233334444555555555555SECRET_DBEngine'
    FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov ;

下列範例會使用 用戶端識別碼和祕密字串的變數建立相同的認證，然後串連在一起以形成 SECRET 引數。 REPLACE 函數可用來從用戶端識別碼中移除連字號。

DECLARE @AuthClientId uniqueidentifier = '11111111-AAAA-BBBB-2222-CCCCCCCCCCCC';
DECLARE @AuthClientSecret varchar(200) = 'SECRET_DBEngine';
DECLARE @pwd varchar(max) = REPLACE(CONVERT(varchar(36), @AuthClientId) , '-', '') + @AuthClientSecret;

EXEC ('CREATE CREDENTIAL Azure_EKM_TDE_cred
    WITH IDENTITY = ''ContosoKeyVault'', SECRET = ''' + @PWD + '''
    FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov ;');

D. 使用SAS令牌建立認證

適用於：SQL Server 2014 (12.x) 到目前版本與 Azure SQL 受控執行個體。

下列範例會使用 SAS 權杖來建立共用存取簽章憑證。如需在 Azure 容器上建立預存存取原則和共用存取簽章，再使用共用存取簽章來建立認證的教學課程，請參閱教學課程：搭配使用 Azure Blob 儲存體和 SQL Server 資料庫。

重要

CREDENTIAL NAME 引數要求名稱與容器路徑相符，開頭為 https，而且不包含尾端斜線。 IDENTITY 引數需要名稱 SHARED ACCESS SIGNATURE。 SECRET 引數需要共用存取簽章權杖。

SHARED ACCESS SIGNATURE 祕密開頭不應該有 ? 。

USE master
CREATE CREDENTIAL [https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>] -- this name must match the container path, start with https and must not contain a trailing forward slash.
    WITH IDENTITY='SHARED ACCESS SIGNATURE' -- this is a mandatory string and do not change it.
    , SECRET = 'sharedaccesssignature' -- this is the shared access signature token
GO

E. 建立受控識別的認證

下列範例會建立代表 Azure SQL 受控識別或 Azure Synapse 服務的認證。在此情況下，密碼與祕密並不適用。

CREATE CREDENTIAL ServiceIdentity WITH IDENTITY = 'Managed Identity';
GO

F. 建立備份/還原至 S3 兼容記憶體的認證

適用於：SQL Server 2022 (16.x) 和更新版本

開放式 S3 相容標準提供記憶體路徑和詳細數據，這些路徑和詳細數據可能會根據記憶體平臺而有所不同。如需詳細資訊，請參閱 SQL Server S3 相容物件儲存體的 URL 備份。

針對大部分的 S3 相容記憶體，此範例會建立伺服器層級認證並執行 BACKUP TO URL。

USE [master];
CREATE CREDENTIAL [s3://<endpoint>:<port>/<bucket>]
WITH
        IDENTITY    = 'S3 Access Key',
        SECRET      = '<AccessKeyID>:<SecretKeyID>';
GO

BACKUP DATABASE [SQLTestDB]
TO      URL = 's3://<endpoint>:<port>/<bucket>/SQLTestDB.bak'
WITH    FORMAT /* overwrite any existing backup sets */
,       STATS = 10
,       COMPRESSION;

不過，AWS S3 支援兩種不同的 URL 標準。

S3://<BUCKET_NAME>.S3.<REGION>.AMAZONAWS.COM/<FOLDER> (預設值)
S3://S3.<REGION>.AMAZONAWS.COM/<BUCKET_NAME>/<FOLDER>

有多種方式可成功建立 AWS S3 的認證：

在認證名稱中提供貯體名稱和路徑和區域。

-- S3 bucket name: datavirtualizationsample
-- S3 bucket region: us-west-2
-- S3 bucket folder: backup

CREATE CREDENTIAL [s3://datavirtualizationsample.s3.us-west-2.amazonaws.com/backup]
WITH    
        IDENTITY    = 'S3 Access Key'
,       SECRET      = 'accesskey:secretkey';
GO

BACKUP DATABASE [AdventureWorks2022]
TO URL  = 's3://datavirtualizationsample.s3.us-west-2.amazonaws.com/backup/AdventureWorks2022.bak'
WITH COMPRESSION, FORMAT, MAXTRANSFERSIZE = 20971520;
GO

或者，

CREATE CREDENTIAL [s3://s3.us-west-2.amazonaws.com/datavirtualizationsample/backup]
WITH    
        IDENTITY    = 'S3 Access Key'
,       SECRET      = 'accesskey:secretkey';
GO

BACKUP DATABASE [AdventureWorks2022]
TO URL  = 's3://s3.us-west-2.amazonaws.com/datavirtualizationsample/backup/AdventureWorks2022.bak'
WITH COMPRESSION, FORMAT, MAXTRANSFERSIZE = 20971520;
GO

或者，請在認證名稱中提供貯體名稱和路徑，但將每個 BACKUP/RESTORE 命令中的區域參數化。使用與RESTORE_OPTIONS中的 BACKUP_OPTIONS S3 特定區域字串，'{"s3": {"region":"us-west-2"}}'例如。

-- S3 bucket name: datavirtualizationsample
-- S3 bucket region: us-west-2
-- S3 bucket folder: backup

CREATE CREDENTIAL   [s3://datavirtualizationsample.s3.amazonaws.com/backup]
WITH    
        IDENTITY    = 'S3 Access Key'
,       SECRET      = 'accesskey:secretkey';
GO

BACKUP DATABASE [AdventureWorks2022]
TO URL  = 's3://datavirtualizationsample.s3.amazonaws.com/backup/AdventureWorks2022.bak'
WITH
  BACKUP_OPTIONS = '{"s3": {"region":"us-west-2"}}' -- REGION AS PARAMETER)
, COMPRESSION, FORMAT, MAXTRANSFERSIZE = 20971520;
GO

RESTORE DATABASE AdventureWorks2022_1 
FROM URL = 's3://datavirtualizationsample.s3.amazonaws.com/backup/AdventureWorks2022.bak'
WITH 
  MOVE 'AdventureWorks2022' 
  TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\AdventureWorks2022_1.mdf'
, MOVE 'AdventureWorks2022_log' 
  TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\AdventureWorks2022_1.ldf'
, STATS = 10, RECOVERY
, REPLACE, RESTORE_OPTIONS = '{"s3": {"region":"us-west-2"}}'; -- REGION AS PARAMETER)
GO

分享方式：

CREATE CREDENTIAL (Transact-SQL)

語法

引數

credential_name

IDENTITY ='identity_name'

SECRET ='secret'

針對密碼編譯提供者 cryptographic_provider_name

備註

權限

範例

A. 建立 Windows 身分識別的認證

B. 建立 EKM 的認證

C. 使用 Azure 金鑰保存庫建立 EKM 的認證

D. 使用SAS令牌建立認證

E. 建立受控識別的認證

F. 建立備份/還原至 S3 兼容記憶體的認證

意見反映

更多資源

分享方式：

CREATE CREDENTIAL (Transact-SQL)

語法

引數

credential_name

IDENTITY ='identity_name'

SECRET ='secret'

針對密碼編譯提供者 cryptographic_provider_name

備註

權限

範例

A. 建立 Windows 身分識別的認證

B. 建立 EKM 的認證

C. 使用 Azure 金鑰保存庫建立 EKM 的認證

D. 使用SAS令牌建立認證

E. 建立受控識別的認證

F. 建立備份/還原至 S3 兼容記憶體的認證

相關內容

意見反映

更多資源