CREATE CREDENTIAL (Transact-SQL)

適用於:SQL ServerAzure SQL 受控執行個體

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

注意

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

Transact-SQL 語法慣例

Syntax

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

注意

若要檢視 SQL Server 2014 (12.x) 和舊版的 Transact-SQL 語法,請參閱 舊版檔

引數

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) 時,IDENTITYSHARED 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 LOGINALTER 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 CREDENTIALIDENTITY 引數需要金鑰保存庫名稱。 CREATE CREDENTIALSECRET 引數要求 <Client ID> (不含連字號) 和 <Secret> 一併傳遞,而且兩者之間不含空格。

在下列範例中, 用戶端識別碼 (EF5C8E09-4D2A-4A76-9998-D93440D8115D) 已移除連字號並以字串 EF5C8E094D2A4A769998D93440D8115D 輸入,而 密碼 會以字串 SECRET_DBEngine來表示。

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

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

DECLARE @AuthClientId uniqueidentifier = 'EF5C8E09-4D2A-4A76-9998-D93440D8115D';
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 SIGNATURESECRET 引數需要共用存取簽章權杖。

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