Rendere un database portabile usando database indipendenti

  • Articolo

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse Analytics

Usare gli utenti del database indipendente per autenticare le connessioni a SQL Server e al database SQL di Azure a livello di database. Un database indipendente è un database isolato dagli altri database e dall'istanza di SQL Server o del database SQL (e dal database master) che ospita il database.

SQL Server supporta gli utenti del database indipendente per l'autenticazione di Windows e SQL Server. Durante l'utilizzo del database SQL, combinare gli utenti del database indipendente con le regole del firewall a livello di database.

Questo articolo illustra i vantaggi correlati all'uso del modello di database indipendente rispetto al modello tradizionale basato su account di accesso/utente e alle regole del firewall a livello di server o Windows. L'uso del modello tradizionale basato su account di accesso/utente e delle regole del firewall a livello di server può essere ancora necessario in scenari specifici, per la gestibilità o per la logica di business dell'applicazione.

Modello tradizionale basato su account di accesso e utente

Nel modello di connessione tradizionale, gli utenti di Windows o i membri dei gruppi di Windows si connettono al motore di database fornendo le credenziali utente o di gruppo autenticate da Windows. In alternativa, gli utenti possono specificare un nome e una password e connettersi usando l'autenticazione di SQL Server. In entrambi i casi, il database master deve disporre di un account di accesso corrispondente alle credenziali di connessione.

Dopo che il motore di database ha verificato le credenziali di autenticazione di Windows o ha autenticato le credenziali di autenticazione di SQL Server, la connessione in genere tenta di connettersi a un database utente. Per connettersi a un database utente, l'account di accesso deve essere sottoposto a mapping (ovvero associato) a un utente del database nel database utente. La stringa di connessione può inoltre specificare la connessione a un database specifico. Questo è facoltativo in SQL Server, ma obbligatorio nel database SQL.

L'aspetto importante è che sia l'account di accesso (nel database master) che l'utente (nel database utente) devono esistere ed essere correlati tra loro. La connessione al database utente ha una dipendenza dall'account di accesso nel database master. Questa dipendenza limita la possibilità di spostare il database in un'istanza di SQL Server o in un server di database SQL di Azure host diverso.

Se non è disponibile una connessione al database master (ad esempio, perché è in corso un failover), la durata della connessione complessiva aumenterà o potrebbe verificarsi un timeout della connessione. Una connessione non disponibile potrebbe ridurre la scalabilità della connessione.

Modello di utente di database indipendente

Nel modello utente del database indipendente, l'account di accesso nel database master non è presente. Il processo di autenticazione viene invece eseguito nel database utente. L'utente del database nel database utente non dispone di un account di accesso associato nel database master.

Il modello utente del database indipendente supporta sia l'autenticazione di Windows che l'autenticazione di SQL Server. È possibile usarlo sia in SQL Server che in database SQL.

Per connettersi come utente di database indipendente, la stringa di connessione deve sempre contenere un parametro per il database utente. Il motore di database usa questo parametro per sapere quale database è responsabile della gestione del processo di autenticazione.

L'attività dell'utente del database indipendente è limitata al database di autenticazione. L'account utente del database deve essere creato in modo indipendente in ogni database necessario all'utente. Per modificare i database, gli utenti del database SQL devono creare una nuova connessione. Gli utenti del database indipendente in SQL Server possono modificare i database se è presente un utente identico in un altro database.

In Azure, Database SQL e Azure Synapse Analytics supportano le identità da Microsoft Entra ID (in precedenza Azure Active Directory) come utenti di database indipendenti. Database SQL supporta gli utenti di database indipendenti che usano l'autenticazione di SQL Server, ma Azure Synapse Analytics no. Per ulteriori informazioni, vedere Connessione al database SQL con l'autenticazione di Microsoft Entra.

Quando si usa l'autenticazione Microsoft Entra, gli utenti possono stabilire connessioni da SQL Server Management Studio usando l'autenticazione universale di Microsoft Entra. Gli amministratori possono configurare l'autenticazione universale per richiedere l'autenticazione a più fattori, che consente di verificare l'identità con una telefonata, un SMS, una scheda contestuale con PIN o una notifica dell'app per dispositivi mobili. Per ulteriori informazioni, vedere Utilizzare l'autenticazione a più fattori di Microsoft Entra.

Per Database SQL e Azure Synapse Analytics, il nome del database è sempre necessario nella stringa di connessione. Per questo, non sono necessarie modifiche della stringa di connessione quando si passa dal modello tradizionale al modello utente del database indipendente. Per le connessioni a SQL Server, il nome del database deve essere aggiunto alla stringa di connessione, se non è già presente.

Importante

Quando si usa il modello tradizionale, i ruoli e le autorizzazioni a livello di server possono limitare l'accesso a tutti i database. Quando si usa il modello di database indipendente, i proprietari e gli utenti del database con autorizzazione ALTER ANY USER possono concedere l'accesso al database. Questa autorizzazione riduce il controllo di accesso degli account di accesso al server con privilegi elevati ed espande il controllo di accesso per includere gli utenti di database con privilegi elevati.

Firewall

SQL Server

Per SQL Server, le regole di Windows Firewall si applicano a tutte le connessioni e producono gli stessi effetti sugli account di accesso (connessioni con modello tradizionale) e sugli utenti di database indipendente. Per ulteriori informazioni su Windows Firewall, vedere Configurazione di Windows Firewall per l'accesso al motore di database.

Firewall del database SQL

Database SQL consente regole del firewall separate per connessioni a livello di server (account di accesso) e per connessioni a livello di database (utenti di database indipendente). Quando si connette a un database utente, il database SQL verifica per prima cosa le regole del firewall a livello di database. Se non esistono regole che consentono l'accesso al database, Database SQL verifica le regole del firewall a livello di server. La verifica delle regole del firewall a livello di server richiede l'accesso al database master del server di database SQL.

L'uso combinato di regole del firewall a livello di database e utenti di database indipendenti consente di non dover accedere al database master del server durante la connessione. Il risultato è una migliore scalabilità delle connessioni.

Per ulteriori informazioni sulle regole del firewall di database SQL, vedere gli argomenti seguenti:

Differenze di sintassi

Modello tradizionale Modello di utente di database indipendente
Quando connesso al database master:

CREATE LOGIN login_name WITH PASSWORD = 'strong_password';

Quindi, quando connesso a un database utente:

CREATE USER 'user_name' FOR LOGIN 'login_name';		 Quando connesso a un database utente:

CREATE USER user_name WITH PASSWORD = 'strong_password';
Modello tradizionale Modello di utente di database indipendente
Per modificare una password nel contesto del database master:

ALTER LOGIN login_name WITH PASSWORD = 'strong_password';		 Per modificare una password nel contesto del database utente:

ALTER USER user_name WITH PASSWORD = 'strong_password';

Istanza gestita di SQL

Istanza gestita di SQL di Azure si comporta come SQL Server in locale nel contesto dei database indipendenti. Assicurarsi di modificare il contesto del database da database master a database utente durante la creazione dell'utente indipendente. Quando si imposta l'opzione di indipendenza, inoltre, non devono essere presenti connessioni attive al database utente. Utilizzare il codice seguente come guida.

Avviso

Lo script di esempio seguente usa un'istruzione kill per chiudere tutti i processi utente nel database. Assicurarsi di comprendere le conseguenze di questo script e di adattarlo all'azienda prima di eseguirlo. Assicurarsi inoltre che nessun'altra connessione sia attiva nel database di Istanza gestita di SQL, perché lo script interromperà altri processi in esecuzione nel database.

USE master;

SELECT * FROM sys.dm_exec_sessions
WHERE database_id  = db_id('Test')

DECLARE @kill_string varchar(8000) = '';
SELECT @kill_string = @kill_string + 'KILL ' + str(session_id) + '; '  
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('Test') and is_user_process = 1;

EXEC(@kill_string);
GO

sp_configure 'contained database authentication', 1;  
GO
 
RECONFIGURE;  
GO 

SELECT * FROM sys.dm_exec_sessions
WHERE database_id  = db_id('Test')

ALTER DATABASE Test
SET containment=partial

USE Test;  
GO 

CREATE USER Carlo  
WITH PASSWORD='Enterpwdhere*'  

SELECT containment_desc FROM sys.databases
WHERE name='Test'

Osservazioni:

  • Gli utenti del database indipendente devono essere abilitati per ogni istanza di SQL Server. Per ulteriori informazioni, vedere Autenticazione del database indipendente (opzione di configurazione del server).
  • Gli account di accesso e gli utenti del database indipendente con nomi diversi possono coesistere nelle applicazioni.
  • Supponiamo che un account di accesso nel database master abbia il nome name1. Se si crea un utente del database indipendente denominato name1, quando viene specificato un nome di database nella stringa di connessione, il contesto dell'utente del database verrà preferito a quello dell'account di accesso durante la connessione al database. Quindi, gli utenti del database indipendente hanno la precedenza rispetto agli account di accesso con lo stesso nome.
  • In Database SQL, il nome dell'utente del database indipendente non può essere uguale a quello dell'account amministratore del server.
  • L'account amministratore del server di database SQL non può mai essere un utente del database indipendente. L'amministratore del server dispone di autorizzazioni sufficienti per creare e gestire utenti del database indipendente. L'amministratore del server può concedere a utenti di database indipendente autorizzazioni per i database utente.
  • Dal momento che gli utenti del database indipendente sono entità di sicurezza a livello di database, è necessario creare utenti del database indipendente in ogni database in cui verranno usati. L'identità è limitata al database. L'identità è indipendente (da ogni punto di vista) rispetto a un utente con lo stesso nome e la stessa password in un altro database nello stesso server.
  • Usare le stesse password complesse usate in genere per gli account di accesso.

Contenuto correlato