Autenticazione SQL in Azure Synapse Analytics

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 eseguire l'autenticazione a Synapse SQL, è possibile usare due opzioni:

• Autenticazione Microsoft Entra
• Autenticazione SQL

L'autenticazione SQL consente alle applicazioni legacy di connettersi ad Azure Synapse SQL in modo familiare, con un nome utente e una password. Tuttavia, l'autenticazione 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, vedere Disabilitazione dell'autenticazione locale in Azure Synapse Analytics.

Account amministrativi

Esistono due account amministrativi (nome utente amministratore SQL e amministratore di Microsoft Entra) 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.

• 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 database master. 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 Microsoft Entra

  È possibile configurare come amministratore anche un account singolo o di gruppo di sicurezza di Microsoft Entra. È facoltativo configurare un amministratore di Microsoft Entra, ma è necessario configurarne uno se si vogliono usare gli account Microsoft Entra per connettersi a Synapse SQL.

  • L'account amministratore Microsoft Entra controlla l'accesso ai pool SQL dedicati, mentre è possibile usare i ruoli Controllo degli accessi in base al ruolo di Synapse possono per controllare l'accesso ai pool serverless, ad esempio con il ruolo Amministratore Synapse e il ruolo Amministratore Synapse SQL.

Il nome utente amministratore SQL e gli account amministratore di Microsoft Entra 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 accedono al database master come utente dbo e hanno autorizzazioni limitate nel database master.
• Questi account non sono membri del ruolo predefinito del server sysadmin di SQL Server, che non è disponibile in Database SQL.
• Possono creare, modificare e rimuovere database, account di accesso, utenti nel database master 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 Microsoft Entra e amministratore di Synapse e quindi rimosso dal ruolo di amministratore di Microsoft Entra, l'utente perderà l'accesso ai pool SQL dedicati in Synapse. Dovrà essere rimosso e quindi aggiunto al ruolo Amministratore Synapse per ottenere nuovamente l'accesso ai pool SQL dedicati.

Pool SQL serverless

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;

Una volta creato l'account di accesso, è possibile creare utenti nei singoli database all'interno dell'endpoint del pool SQL serverless e concedere loro le autorizzazioni necessarie. 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.

Pool SQL dedicato

Percorso di accesso degli amministratori

Quando il firewall a livello di area di lavoro è configurato correttamente, il nome utente amministratore SQL e l'amministratore di SQL Microsoft Entra possono connettersi usando strumenti client come SQL Server Management Studio o SQL Server Data Tools. Solo gli strumenti più recenti offrono tutte le caratteristiche e le funzionalità.

Il diagramma seguente mostra una configurazione tipica per i due account amministratore:

Quando viene usata una porta aperta nel firewall a livello di server, gli amministratori possono connettersi a qualsiasi database SQL.

Creatori di database

Uno di questi ruoli amministrativi è il ruolo dbmanager. I membri di questo ruolo possono creare nuovi database. Per usare questo ruolo, creare un utente nel database master e quindi aggiungere l'utente al ruolo dbmanager del database.

Per creare un database, l'utente deve essere basato su un account di accesso di SQL Server nel database master o essere un utente di un database indipendente basato su un utente di Microsoft Entra.

1. Connettersi al database master usando un account amministratore.

2. creare un account di accesso con autenticazione di SQL Server con l'istruzione CREATE LOGIN. Istruzione di esempio:

   CREATE LOGIN Mary WITH PASSWORD = '<strong_password>';
   

   Nota

   Quando si crea un account di accesso o un utente di database indipendente, usare una password complessa. Per altre informazioni, vedere Strong Passwords.

   Per migliorare le prestazioni, gli account di accesso (entità a livello di server) vengono temporaneamente memorizzati nella cache a livello di database. Per aggiornare la cache di autenticazione, vedere DBCC FLUSHAUTHCACHE.

3. Creare un utente di database usando l'istruzione CREATE USER. L'utente può essere un utente di database indipendente con autenticazione Microsoft Entra (se l'ambiente è stato configurato per l'autenticazione Microsoft Entra), un utente di database indipendente con autenticazione SQL Server oppure un utente con autenticazione SQL Server basato su un account di accesso con autenticazione SQL Server (creato nel passaggio precedente).

   Istruzioni di esempio:

   Creare un utente con Microsoft Entra ID:

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

   Creare un utente del database indipendente del database SQL:

   CREATE USER Ann WITH PASSWORD = '<strong_password>';
   

   Creare un utente di SQL Server basato su un account di accesso per l'autenticazione di SQL Server:

   CREATE USER Mary FROM LOGIN Mary;
   

4. Aggiungere il nuovo utente al ruolo del database dbmanager in master usando la routine sp_addrolemember (si noti che l'istruzione ALTER ROLE non è supportata in SQL con provisioning). Istruzioni di esempio:

   EXEC sp_addrolemember 'dbmanager', 'Mary'; 
   EXEC sp_addrolemember 'dbmanager', 'mike@contoso.com]'; 
   

   Nota

   Poiché dbmanager è un ruolo del database nel database master, è possibile aggiungere solo un utente di database al ruolo dbmanager. Non si può aggiungere un account di accesso a livello di server a un ruolo a livello di database.

5. Se necessario, configurare una regola firewall per consentire la connessione del nuovo utente. Il nuovo utente può essere gestito da una regola firewall esistente.

L'utente potrà così connettersi al database master e creare nuovi database. L'account che crea il database ne diventa il proprietario.

Gestione degli account di accesso

L'altro ruolo amministrativo è il ruolo di gestione degli account di accesso. I membri di questo ruolo possono creare nuovi account di accesso nel database master. Se si vuole, è possibile completare la stessa procedura (ovvero creare un account di accesso e aggiungere un utente al ruolo loginmanager) per consentire a un utente di creare nuovi account di accesso nel database master. Gli account di accesso non sono in genere necessari perché è consigliabile usare utenti di database indipendente che eseguono l'autenticazione a livello di database anziché utenti basati su account di accesso. Per altre informazioni, vedere Utenti di database indipendente: rendere portabile un database.

Utenti non amministratori

Gli account non amministratore non hanno in genere bisogno dell'accesso al database master. Creare utenti di database indipendente a livello di database con l'istruzione CREATE USER (Transact-SQL) .

L'utente può essere un utente di database indipendente con autenticazione Microsoft Entra (se l'ambiente è stato configurato per l'autenticazione Microsoft Entra), un utente di database indipendente con autenticazione SQL Server oppure un utente con autenticazione SQL Server basato su un account di accesso con autenticazione 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 di 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.

Per iniziare, vedere l'elenco di autorizzazioni in Autorizzazioni (Motore di database) e il poster relativo alle 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 database master quando si eseguono le istruzioni CREATE/ALTER/DROP DATABASE.
• L'utente di database corrispondente all'account di accesso Amministratore del server non può essere modificato o eliminato.
• L'account Amministratore del server verrà disabilitato se è abilitata l'autenticazione solo Microsoft Entra.
• L'inglese americano è la lingua predefinita dell'account di accesso Amministratore del server.
• Soltanto gli amministratori (account di accesso Amministratore del server o Amministratore Microsoft Entra) e i membri del ruolo di database dbmanager nel database master sono autorizzati a eseguire le istruzioni CREATE DATABASE e DROP DATABASE.
• È necessario essere connessi al database master quando si eseguono le istruzioni CREATE/ALTER/DROP LOGIN. È tuttavia sconsigliato l'uso di account di accesso. 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.
• Soltanto gli utenti con account di accesso dell'entità di livello server e i membri del ruolo del database loginmanager nel database master sono autorizzati a eseguire le istruzioni CREATE LOGIN, ALTER LOGIN e 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.
• Le istruzioni CREATE/ALTER/DROP LOGIN e CREATE/ALTER/DROP USER non sono supportate quando per l'area di lavoro di Azure Synapse è abilitata l'autenticazione solo Microsoft Entra.
• 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 in o da tale ruolo del database, potrebbe verificarsi il seguente errore: 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.

Contenuto correlato

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