Autenticazione SQL in Azure Synapse Analytics

  • Articolo

Azure Synapse Analytics include due fattori di forma SQL che consentono di controllare il consumo di risorse. Questo articolo illustra come i due fattori di forma controllano l'autenticazione utente.

Per Synapse SQL, è possibile usare due tipi di autorizzazione:

  • Autorizzazione di Microsoft Entra
  • Autorizzazione SQL

L'autorizzazione SQL consente alle applicazioni legacy di connettersi ad Azure Synapse SQL in modo familiare. Tuttavia, l'autenticazione di Microsoft Entra consente di gestire centralmente l'accesso alle risorse di Azure Synapse, ad esempio i pool SQL. Azure Synapse Analytics supporta la disabilitazione dell'autenticazione locale, ad esempio l'autenticazione SQL, sia durante che dopo la creazione dell'area di lavoro. Una volta disabilitata, l'autenticazione locale può essere abilitata in qualsiasi momento dagli utenti autorizzati. Per altre informazioni sull'autenticazione solo Microsoft Entra-only, vedere Disabilitazione dell'autenticazione locale in Azure Synapse Analytics.

Account amministrativi

Esistono due account amministrativi (nome utente amministratore SQL e amministratore di SQL Active Directory) che fungono da amministratori. Per identificare questi account amministratore per i pool SQL, aprire il portale di Azure e passare alla scheda Proprietà dell'area di lavoro di Synapse.

SQL Server Admins

  • Nome utente amministratore SQL

    Quando si crea un'istanza di Azure Synapse Analytics, è necessario designare un account di accesso di amministratore del server. SQL Server crea tale account come account di accesso nel master database. Tale account, che effettua la connessione con l'autenticazione di SQL Server (nome utente e password), Può esistere un solo account di questo tipo.

  • Amministratore di SQL Active Directory

    Un account Microsoft Entra, un singolo o un account del gruppo di sicurezza, può anche essere configurato come amministratore. È facoltativo configurare un amministratore di Microsoft Entra, ma è necessario configurare un amministratore di Microsoft Entra se si vogliono usare gli account Microsoft Entra per connettersi a Synapse SQL.

    • L'account amministratore di Microsoft Entra controlla l'accesso ai pool SQL dedicati, mentre i ruoli controllo degli accessi in base al ruolo di Synapse possono essere usati per controllare l'accesso ai pool serverless, ad esempio con il ruolo Synapse Amministrazione istrator e Synapse SQL Amministrazione istrator.

Il nome utente amministratore SQL e gli account amministratore di SQL Active Directory hanno le caratteristiche seguenti:

  • Questi sono gli unici account che possono connettersi automaticamente a qualsiasi database SQL nel server. Per connettersi a un database utente, gli altri account devono essere il proprietario del database o avere un account utente nel database utente.
  • Questi account accedono ai database utente come utente dbo e hanno a disposizione tutte le autorizzazioni nei database utente. Anche il proprietario di un database utente accede al database come utente dbo.
  • Non immettere il master database come dbo utente e disporre di autorizzazioni limitate nel master database.
  • Questi account non sono membri del ruolo predefinito del server sysadmin di SQL Server, che non è disponibile in Database SQL.
  • Può creare, modificare ed eliminare database, account di accesso, utenti nel master database e regole del firewall IP a livello di server.
  • Questi account possono aggiungere e rimuovere membri per i ruoli dbmanager e loginmanager.
  • Essi possono visualizzare la tabella di sistema sys.sql_logins.

Nota

Se un utente è configurato come amministratore di Active Directory e Synapse Amministrazione istrator e quindi rimosso dal ruolo di amministratore di Active Directory, l'utente perderà l'accesso ai pool SQL dedicati in Synapse. Devono essere rimossi e quindi aggiunti al ruolo synapse Amministrazione istrator per ottenere nuovamente l'accesso ai pool SQL dedicati.

Per gestire gli utenti che hanno accesso al pool SQL serverless, è possibile seguire queste istruzioni.

Per creare un account di accesso al pool SQL serverless, usare la sintassi seguente:

CREATE LOGIN Mary WITH PASSWORD = '<strong_password>';
-- or
CREATE LOGIN [Mary@domainname.net] FROM EXTERNAL PROVIDER;

Quando l'account di accesso esiste, è possibile creare utenti nei singoli database all'interno dell'endpoint del pool SQL serverless e concedere le autorizzazioni necessarie a questi utenti. Per creare un utente, è possibile usare la sintassi seguente:

CREATE USER Mary FROM LOGIN Mary;
-- or
CREATE USER Mary FROM LOGIN Mary@domainname.net;
-- or
CREATE USER [mike@contoso.com] FROM EXTERNAL PROVIDER;

Una volta creati l'account di accesso e l'utente, è possibile usare la normale sintassi di SQL Server per concedere diritti.

Utenti non amministratori

In genere, gli account non amministratori non devono accedere al master database. Creare utenti di database indipendente a livello di database con l'istruzione CREATE USER (Transact-SQL) .

L'utente può essere un utente del database indipendente di Microsoft Entra (se è stato configurato l'ambiente per l'autenticazione di Microsoft Entra) o un utente del database indipendente per l'autenticazione di SQL Server o un utente di autenticazione di SQL Server basato su un account di accesso di autenticazione di SQL Server (creato nel passaggio precedente).

Per creare utenti, connettersi al database ed eseguire istruzioni simili ai seguenti esempi:

CREATE USER Mary FROM LOGIN Mary;
CREATE USER [mike@contoso.com] FROM EXTERNAL PROVIDER;

Inizialmente, solo gli amministratori o il proprietario del database possono creare utenti. Per autorizzare utenti aggiuntivi a creare nuovi utenti, concedere all'utente selezionato l'autorizzazione ALTER ANY USER con un'istruzione come la seguente:

GRANT ALTER ANY USER TO Mary;

Per concedere a utenti aggiuntivi il controllo completo del database, rendere tali utenti membri del ruolo predefinito del database db_owner.

Nel database SQL di Azure o in Synapse serverless usare l'istruzione ALTER ROLE.

ALTER ROLE db_owner ADD MEMBER Mary;

Nel pool SQL dedicato usare EXEC sp_addrolemember.

EXEC sp_addrolemember 'db_owner', 'Mary';

Nota

Uno dei motivi comuni per creare un utente di database in base a un account di accesso server è l'esigenza degli utenti di accedere a più database. Dato che gli utenti di database indipendenti sono singole entità, ogni database gestisce utente e password propri. Ciò può causare complicazioni quando l'utente deve ricordare le password per tutti i database e può diventare insostenibile quando occorre modificare più password per molti database.

Gruppi e ruoli

In una gestione efficiente degli accessi vengono usate autorizzazioni assegnate a gruppi e ruoli anziché singoli utenti.

  • Quando si usa l'autenticazione Microsoft Entra, inserire gli utenti di Microsoft Entra in un gruppo Microsoft Entra. Creare un utente di database indipendente per il gruppo. Inserire uno o più utenti di database in un ruolo del database e quindi assegnare autorizzazioni al ruolo del database.

  • Quando si usa l'autenticazione di SQL Server, creare utenti di database indipendenti nel database. Inserire uno o più utenti di database in un ruolo del database e quindi assegnare autorizzazioni al ruolo del database.

I ruoli del database possono essere ruoli predefiniti come db_owner, db_ddladmin, db_datawriter, db_datareader, db_denydatawriter e db_denydatareader. Per concedere autorizzazioni complete a un numero limitato di utenti viene usato comunemente db_owner. Gli altri ruoli predefiniti del database sono utili per ottenere rapidamente un database semplice nello sviluppo, ma non sono consigliabili per la maggior parte dei database di produzione.

Il ruolo predefinito del database db_datareader, ad esempio, concede l'accesso in lettura a tutte le tabelle del database, che in genere è più di quanto strettamente necessario.

È preferibile usare l'istruzione CREATE ROLE per creare ruoli del database definiti dall'utente e concedere con attenzione a ogni ruolo le autorizzazioni minime necessarie per le esigenze aziendali. Quando un utente è membro di più ruoli, vengono aggregate le autorizzazioni di tutti.

Autorizzazioni

Nel database SQL possono essere concesse o negate singolarmente oltre 100 autorizzazioni. Molte di queste autorizzazioni sono annidate. L'autorizzazione UPDATE per uno schema, ad esempio, include l'autorizzazione UPDATE per ogni tabella all'interno di tale schema. Come nella maggior parte dei sistemi di autorizzazioni, la negazione di un'autorizzazione determina l'override di una concessione.

A causa dell'annidamento e del numero delle autorizzazioni, progettare un sistema di autorizzazioni appropriato per proteggere correttamente il database può richiedere un attento studio.

Iniziare con l'elenco delle autorizzazioni in Autorizzazioni (motore di database) ed esaminare l'immagine delle dimensioni del poster delle autorizzazioni del motore di database.

Considerazioni e restrizioni

Quando si gestiscono gli account di accesso e gli utenti nel database SQL, tenere presente quanto segue:

  • È necessario essere connessi al master database durante l'esecuzione delle CREATE/ALTER/DROP DATABASE istruzioni.
  • L'utente di database corrispondente all'account di accesso Amministratore del server non può essere modificato o eliminato.
  • L'amministratore del server verrà disabilitato se è abilitata l'autenticazione solo Entra-only.
  • L'inglese americano è la lingua predefinita dell'account di accesso Amministratore del server.
  • Solo gli amministratori (account di accesso amministratore del server o amministratore di Microsoft Entra) e i membri del ruolo del database dbmanager nel master database dispongono dell'autorizzazione per eseguire le CREATE DATABASE istruzioni e DROP DATABASE .
  • È necessario essere connessi al master database durante l'esecuzione delle CREATE/ALTER/DROP LOGIN istruzioni. Tuttavia, l'uso degli account di accesso è sconsigliato. Usare invece gli utenti del database indipendente. Per altre informazioni, vedere Utenti di database indipendente: rendere portabile un database.
  • Per connettersi a un database utente è necessario specificare il nome del database nella stringa di connessione.
  • Solo l'account di accesso dell'entità a livello di server e i membri del ruolo del database loginmanager nel master database hanno l'autorizzazione per eseguire le CREATE LOGINistruzioni , ALTER LOGINe DROP LOGIN .
  • Quando si eseguono le istruzioni CREATE/ALTER/DROP LOGIN e CREATE/ALTER/DROP DATABASE in un'applicazione ADO.NET, non è consentito usare i comandi con parametri. Per ulteriori informazioni, vedere Comandi e parametri.
  • Quando si esegue l’istruzione CREATE USER con l’opzione FOR/FROM LOGIN, l’istruzione deve essere l'unica in un batch Transact-SQL.
  • Quando si esegue l’istruzione ALTER USER con l’opzione WITH LOGIN, l’istruzione deve essere l'unica in un batch Transact-SQL.
  • CREATE/ALTER/DROP LOGIN Le istruzioni e CREATE/ALTER/DROP USER non sono supportate quando l'autenticazione solo Entra di Microsoft è abilitata per l'area di lavoro di Azure Synapse.
  • Per CREATE/ALTER/DROP un utente richiede l’autorizzazione ALTER ANY USER per il database.
  • Quando il proprietario di un ruolo del database tenta di aggiungere o rimuovere un altro utente del database da o verso tale ruolo del database, potrebbe verificarsi l'errore seguente: l'utente o il ruolo 'Name' non esiste nel database. Questo errore si verifica perché l'utente non è visibile al proprietario. Per risolvere questo problema, concedere al proprietario del ruolo l’autorizzazione VIEW DEFINITION per l'utente.

Passaggi successivi

Per altre informazioni, vedere Utenti di database indipendente: rendere portabile un database.