CREATE CREDENTIAL (Transact-SQL)

  • Articolo

Si applica a:SQL Server Istanza gestita di SQL di Azure

Crea una credenziale a livello di server. Una credenziale è un record contenente le informazioni di autenticazione necessarie per connettersi a una risorsa all'esterno di SQL Server. La maggior parte delle credenziali include un utente e una password di Windows. Quando si salva il backup di un database in un determinato percorso, ad esempio, può essere necessario specificare credenziali speciali per accedere a tale percorso. Per altre informazioni, vedere Credenziali (motore di database).

Nota

Per creare credenziali a livello di database, usare CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL). Creare credenziali a livello di server con CREATE CREDENTIAL quando è necessario usare le stesse credenziali per più database nel server.

  • Creare credenziali con ambito database con CREATE DATABASE SCOPED CREDENTIAL per rendere il database più portabile. Quando un database viene spostato in un nuovo server, vengono spostate anche le credenziali con ambito database.
  • Usare le credenziali con ambito database nel database SQL.
  • Usare le credenziali con ambito database con PolyBase e Istanza gestita di SQL di Azure funzionalità di virtualizzazione dei dati.

Convenzioni di sintassi Transact-SQL

Sintassi

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

Nota

Per visualizzare la sintassi Transact-SQL per SQL Server 2014 (12.x) e versioni precedenti, vedere la documentazione delle versioni precedenti.

Argomenti

credential_name

Viene specificato il nome delle credenziali create. credential_name non può iniziare con il simbolo del cancelletto (#). perché tale simbolo viene utilizzato per le credenziali di sistema.

Importante

Quando si usa una firma di accesso condiviso (SAS), questo nome deve corrispondere al percorso del contenitore, iniziare con https e non deve contenere una barra. Vedere l'esempio D.

Se usato per il backup/ripristino tramite a piattaforme dati esterne, ad esempio Archiviazione BLOB di Azure o piattaforme compatibili con S3, la tabella seguente fornisce percorsi comuni:

Origine dati esterna Percorso Esempio
Archiviazione BLOB di Azure (V2) https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername> Esempio D.
Risorsa di archiviazione di oggetti compatibile con S3 - Archiviazione compatibile con S3: s3://<server_name>:<port>/
- AWS S3: s3://<bucket_name>.S3.<region>.amazonaws.com[:port]/<folder>
o s3://s3.<region>.amazonaws.com[:port]/<bucket_name>/<folder>		 Esempio F.

IDENTITY ='identity_name'

Specifica il nome dell'account da utilizzare per la connessione all'esterno del server. Quando la credenziale viene usata per accedere a Azure Key Vault, IDENTITY è il nome dell'insieme di credenziali delle chiavi. Vedere l'esempio C riportato di seguito. Quando le credenziali usano una firma di accesso condiviso (SAS), il valore di IDENTITY è SHARED ACCESS SIGNATURE. Vedere l'esempio D riportato di seguito.

Importante

Database SQL di Azure supporta solo le identità Azure Key Vault e di firma di accesso condiviso. Le identità utente di Windows non sono supportate.

SECRET ='secret'

Specifica il segreto richiesto per l'autenticazione in uscita.

Quando le credenziali vengono usate per accedere ad Azure Key Vault, l'argomento edizione Standard CRET deve essere formattato come ID> client dell'entità <servizio (senza trattini) e< segreto>, passato insieme senza uno spazio tra di essi. Vedere l'esempio C riportato di seguito. Quando le credenziali usano una firma di accesso condiviso, il valore di SECRET è il token della firma di accesso condiviso. Vedere l'esempio D riportato di seguito. Per informazioni sulla creazione di criteri di accesso archiviati e una firma di accesso condiviso in un contenitore di Azure, vedere Lezione 1: Creare criteri di accesso archiviati e una firma di accesso condiviso.

FOR CRYPTOGRAPHIC PROVIDER cryptographic_provider_name

Specifica il nome di un provider EKM (Extensible Key Management). Per altre informazioni sulla gestione delle chiavi, vedere Extensible Key Management (EKM).

Osservazioni:

Se IDENTITY è un utente di Windows, il segreto può essere la password. Il segreto viene crittografato con la chiave master del servizio. Se la chiave master del servizio viene rigenerata, il segreto viene ricrittografato con la nuova chiave master del servizio.

Dopo aver creato una credenziale, è possibile eseguirne il mapping a un account di accesso di SQL Server usando CREATE LOGIN o ALTER LOGIN. È possibile eseguire il mapping di un account di accesso di SQL Server a una sola credenziale, mentre è possibile eseguire il mapping di una credenziale a più account di accesso di SQL Server. Per altre informazioni, vedere Credenziali (motore di database). È possibile eseguire il mapping di una credenziale solo a livello di server, non a un utente del database.

Le informazioni sulle credenziali sono visibili nella vista del catalogo sys.credentials.

Se non sono presenti credenziali di cui viene eseguito il mapping a un account accesso per il provider, vengono usate le credenziali di cui viene eseguito il mapping all'account del servizio SQL Server.

A un account di accesso è possibile eseguire il mapping di più credenziali, a condizione che vengano utilizzate con provider distinti. È possibile eseguire il mapping di una sola credenziale per provider per ogni account di accesso. Sulla stessa credenziale è possibile eseguire il mapping ad altri account di accesso.

Autorizzazioni

È richiesta l'autorizzazione ALTER ANY CREDENTIAL.

Esempi

R. Creazione di una credenziale per l'identità Windows

Nell'esempio seguente viene creata la credenziale denominata AlterEgo. Tale credenziale contiene l'utente di Windows Mary5 e una password.

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

B. Creazione di una credenziale per EKM

Nell'esempio seguente viene usato un account creato in precedenza e denominato User1OnEKM in un modulo EKM tramite gli strumenti di gestione di EKM, con un tipo di account di base e una password. L'account sysadmin nel server crea una credenziale che viene usata per connettersi all'account EKM e la assegna all'account User1 di 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. Creazione di una credenziale per EKM con l'insieme di credenziali chiave di Azure

Nell'esempio seguente viene creata una credenziale di SQL Server utilizzabile dal motore di database per l'accesso ad Azure Key Vault tramite il Connettore SQL Server per Microsoft Azure Key Vault. Per un esempio completo dell'uso del Connettore SQL Server, vedere Extensible Key Management con Azure Key Vault (SQL Server).

Importante

L'argomento IDENTITY di CREATE CREDENTIAL richiede il nome dell'insieme di credenziali delle chiavi. L'argomento SECRET di CREATE CREDENTIAL richiede che i valori per <ID client> (senza trattini) e <Secret> vengano passati insieme senza essere separati da spazi.

Nell'esempio seguente l' ID client (EF5C8E09-4D2A-4A76-9998-D93440D8115D) viene immesso con tutti i trattini rimossi come stringa EF5C8E094D2A4A769998D93440D8115D e il Segreto è rappresentato dalla stringa SECRET_DBEngine.

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

Nell'esempio seguente viene creata la stessa credenziale usando le variabili per le stringhe ID client e Secret, che vengono quindi concatenate insieme per formare l'argomento SECRET. La funzione REPLACE viene usata per rimuovere i trattini dall'ID client.

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. Creazione di credenziali con un token di firma di accesso condiviso

Si applica a: SQL Server, a partire da SQL Server 2014 (12.x) fino alla versione corrente, e Istanza gestita di SQL di Azure.

Nell'esempio seguente viene creata una credenziale di firma di accesso condiviso usando un token di firma di accesso condiviso. Per un'esercitazione sulla creazione di criteri di accesso archiviati e una firma di accesso condiviso in un contenitore di Azure e sulla creazione di credenziali tramite la firma di accesso condiviso, vedere Esercitazione: Usare Microsoft Archiviazione BLOB di Azure con i database di SQL Server.

Importante

L'argomento CREDENTIAL NAME richiede che il nome corrisponda al percorso del contenitore, inizi con https e non contenga una barra finale. L'argomento IDENTITY richiede il nome, SHARED ACCESS SIGNATURE. L'argomento SECRET richiede il token della firma di accesso condiviso.

L'argomento SECRET di SHARED ACCESS SIGNATURE non deve essere preceduto da ?.

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. Creazione di credenziali per l'identità gestita

Nell'esempio seguente vengono create le credenziali che rappresentano l'identità gestita del servizio SQL di Azure o Azure Synapse. La password e il segreto non sono applicabili in questo caso.

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

F. Creare credenziali per il backup/ripristino nell'archiviazione compatibile con S3

Si applica a: SQL Server 2022 (16.x) e versioni successive

Lo standard compatibile con S3 aperto offre percorsi di archiviazione e dettagli che possono differire in base alla piattaforma di archiviazione. Per altre informazioni, vedere Backup di SQL Server nell'URL per l'archiviazione degli oggetti compatibile con S3.

Per la maggior parte dell'archiviazione compatibile con S3, questo esempio crea credenziali a livello di server ed esegue un oggetto 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 supporta tuttavia due diversi standard di URL.

  • S3://<BUCKET_NAME>.S3.<REGION>.AMAZONAWS.COM/<FOLDER> (predefinito)
  • S3://S3.<REGION>.AMAZONAWS.COM/<BUCKET_NAME>/<FOLDER>

Esistono più approcci per creare correttamente una credenziale per AWS S3:

  • Specificare il nome del bucket e il percorso e l'area nel nome delle credenziali.

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

    Oppure

    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

  • In alternativa, specificare il nome e il percorso del bucket nel nome delle credenziali, ma parametrizzare l'area all'interno di ogni BACKUP/RESTORE comando. Usare la stringa di area specifica di S3 in BACKUP_OPTIONS e RESTORE_OPTIONS, ad esempio . '{"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

Contenuto correlato