Connettersi a SQL Server se gli amministratori di sistema sono bloccati

Articolo
08/21/2023

Si applica a:SQL Server

Questo articolo descrive come è possibile riottenere l'accesso al motore di database di SQL Server come amministratore di sistema in caso di blocco. Un amministratore di sistema può perdere l'accesso a un'istanza di SQL Server per uno dei motivi seguenti:

Tutti gli account di accesso membri del ruolo predefinito del server sysadmin sono stati rimossi per errore.
Tutti i gruppi di Windows membri del ruolo predefinito del server sysadmin sono stati rimossi per errore.
Gli account di accesso membri del ruolo predefinito del server sysadmin sono assegnati a utenti che hanno lasciato la società o che non sono disponibili.
L'account sa è disabilitato o nessuno conosce la password.

Risoluzione

Per risolvere il problema di accesso, è consigliabile avviare l'istanza di SQL Server in modalità utente singolo. Questa modalità impedisce che si verifichino altre connessioni mentre si tenta di riottenere l'accesso. Da questa modalità è possibile connettersi all'istanza di SQL Server e aggiungere il proprio account di accesso al ruolo del server sysadmin. La procedura dettagliata per questa soluzione è disponibile nella sezione delle istruzioni dettagliate.

È possibile avviare un'istanza di SQL Server in modalità utente singolo con le opzioni -m o -f dalla riga di comando. Qualsiasi membro del gruppo Administrators locale del computer può quindi connettersi all'istanza di SQL Server come membro del ruolo predefinito del server sysadmin.

Quando si avvia l'istanza in modalità utente singolo, arrestare il servizio SQL Server Agent. In caso contrario, SQL Server Agent potrebbe connettersi per primo, acquisendo l'unica connessione disponibile al server e impedendo quindi ulteriori accessi.

È anche possibile che un'applicazione client sconosciuta usi l'unica connessione disponibile prima di poter eseguire l'accesso. Per evitare che ciò accada, è possibile usare l'opzione -m seguita da un nome di applicazione per limitare le connessioni a una singola connessione dall'applicazione specificata. Ad esempio, l'avvio di SQL Server con -mSQLCMD limita le connessioni a una singola connessione che viene identificata come programma client sqlcmd. Per connettersi tramite l'editor di query in Management Studio, usare -m"Microsoft SQL Server Management Studio - Query".

Importante

Non usare -m con un nome di applicazione come funzionalità di sicurezza. Le applicazioni client specificano il nome dell'applicazione tramite le impostazioni della stringa di connessione, quindi può essere facilmente sottoposto a spoofing con un nome falso.

La tabella seguente riepiloga i diversi modi per avviare l'istanza in modalità utente singolo dalla riga di comando.

Opzione	Descrizione	Quando utilizzarlo
`-m`	Limita le connessioni a una singola connessione	Quando non sono presenti altri utenti che provano a connettersi all'istanza oppure non si è certi del nome dell'applicazione usato per la connessione all'istanza.
`-mSQLCMD`	Limita le connessioni a una singola connessione, che deve identificarsi come programma client sqlcmd	Quando si prevede di connettersi all'istanza con sqlcmd e si vuole impedire ad altre applicazioni di ottenere l'unica connessione disponibile.
`-m"Microsoft SQL Server Management Studio - Query"`	Limita le connessioni a una singola connessione che deve identificarsi come applicazione Microsoft SQL Server Management Studio - Query.	Quando si prevede di connettersi all'istanza tramite l'editor di query di Management Studio e si vuole impedire ad altre applicazioni di ottenere l'unica connessione disponibile.
`-f`	Limita le connessioni a una singola connessione e avvia l'istanza con la configurazione minima	Quando un'altra configurazione impedisce l'avvio.

Istruzioni dettagliate

Per istruzioni dettagliate su come avviare SQL Server in modalità utente singolo, vedere Avviare SQL Server in modalità utente singolo.

Utilizzare PowerShell

Opzione 1: Eseguire i passaggi direttamente in un notebook eseguibile con Azure Data Studio

Nota

Prima di provare ad aprire questo notebook, controllare che Azure Data Studio sia installato nel computer locale. Per installare Azure Data Studio, vedere Informazioni su come installare Azure Data Studio.

Aprire il notebook in Azure Data Studio

Opzione 2: Eseguire il passaggio manualmente

Aprire un comando di Windows PowerShell - Eseguire come amministratore
Configurare il nome del servizio, l'istanza di SQL Server istanza e le variabili di accesso di Windows. Sostituire questi valori con altri corrispondenti all'ambiente

Se si ha un'istanza predefinita, usare MSSQLSERVER senza un nome di istanza.
```
$service_name = "MSSQL`$instancename"
$sql_server_instance = "machine_name\instance"
$login_to_be_granted_access = "[CONTOSO\PatK]"
```
Arrestare il servizio SQL Server in modo che possa essere riavviato con la modalità utente singolo, usando il comando seguente:

Se si ha un'istanza predefinita, usare MSSQLSERVER senza un nome di istanza.
```
net stop $service_name
```
Avviare ora l'istanza di SQL Server in modalità utente singolo e consentire solo a SQLCMD.exe di connettersi (/mSQLCMD)

Nota

Assicurarsi di usare le lettere maiuscole per SQLCMD

Se si ha un'istanza predefinita, usare MSSQLSERVER senza un nome di istanza.
```
net start $service_name /f /mSQLCMD
```
Usando sqlcmd, eseguire un comando CREATE LOGIN seguito dal comando ALTER SERVER ROLE. Questo passaggio presuppone che sia stato eseguito l'accesso a Windows con un account membro del gruppo Administrators locale. Si presume che siano stati sostituiti i nomi di dominio e di accesso con le credenziali che si vogliono concedere all'appartenenza a sysadmin.

Se si ha un'istanza predefinita, usare il nome del server.
```
sqlcmd.exe -E -S $sql_server_instance -Q "CREATE LOGIN $login_to_be_granted_access FROM WINDOWS; ALTER SERVER ROLE sysadmin ADD MEMBER $login_to_be_granted_access; "
```
Nota

Se viene visualizzato l'errore seguente, è necessario assicurarsi che nessun altro SQLCMD sia connesso a SQL Server:
Sqlcmd: Error: Microsoft ODBC Driver X for SQL Server : Login failed for user 'CONTOSO\BobD'. Reason: Server is in single user mode. Only one administrator can connect at this time..
Modalità mista (facoltativo): se SQL Server è in esecuzione in modalità di autenticazione mista, è anche possibile:
1. Concedere a un account di accesso SQL l'appartenenza al ruolo sysadmin. Eseguire codice come il seguente per creare un nuovo account di accesso con autenticazione di SQL Server che fa parte del ruolo predefinito del server sysadmin. Sostituire ?j8:z$G=JE9 con una password efficace a propria scelta.
  
  Se si ha un'istanza predefinita, usare il nome del server.
```
$strong_password = "j8:zG=J?E9"
sqlcmd.exe -E -S $sql_server_instance -Q "CREATE LOGIN TempLogin WITH PASSWORD = '$strong_password'; ALTER SERVER ROLE sysadmin ADD MEMBER TempLogin; "
```
2. È inoltre possibile che SQL Server sia in esecuzione in modalità di autenticazione mista e che si voglia reimpostare la password di un account sa abilitato. Modificare la password dell'account sa con la sintassi seguente. Assicurarsi di sostituire j8:zG=J?E9 con una password efficace a propria scelta:
  
  Se si ha un'istanza predefinita, usare il nome del server.
```
$strong_password = "j8:zG=J?E9"
sqlcmd.exe -E -S $sql_server_instance -Q "ALTER LOGIN sa WITH PASSWORD = $strong_password; "
```
Arrestare e riavviare l'istanza di SQL Server in modalità multiutente

Se si ha un'istanza predefinita, usare MSSQLSERVER senza un nome di istanza.
```
net stop $service_name
net start $service_name
```

Usare Gestione configurazione SQL Server e SQL Server Management Studio (SSMS)

Presupposti per le istruzioni:

SQL Server è in esecuzione in Windows 8 o versione successiva. Quando applicabile vengono indicate lievi modifiche per le versioni precedenti di SQL Server o Windows.
SQL Server Management Studio è installato nel computer.

Eseguire queste istruzioni mentre si è connessi a Windows come membro del gruppo Administrators locale.

Dal menu Start di Windows fare clic con il pulsante destro del mouse sull'icona per Gestione configurazione SQL Server e scegliere Esegui come amministratore per passare le credenziali di amministratore a Gestione configurazione.
Nel riquadro sinistro di Gestione configurazione SQL Server selezionare Servizi di SQL Server. Nel riquadro destro trovare l'istanza di SQL Server. Nell'istanza predefinita di SQL Server è incluso (MSSQLSERVER) dopo il nome del computer. Le istanze denominate vengono visualizzate in maiuscolo con lo stesso nome presente in Server registrati. Fare clic con il pulsante destro del mouse sull'istanza di SQL Server e quindi scegliere Proprietà.
Nella casella Specificare un parametro di avvio della scheda Parametri di avvio digitare -m (ovvero un trattino seguito dalla lettera m minuscola) e selezionare Aggiungi. .

In alcune versioni precedenti di SQL Server non è presente alcuna scheda Parametri di avvio. In questo caso, nella scheda Avanzate fare doppio clic su Parametri di avvio. I parametri vengono visualizzati in una finestra piccola. Fare attenzione a non modificare nessuno dei parametri esistenti. Al termine, aggiungere un nuovo parametro ;-m (ovvero un punto e virgola seguito da un trattino e una lettera m minuscola) e quindi selezionare OK. .
Selezionare OKe, dopo il messaggio di riavvio, fare clic con il pulsante destro del mouse sul nome del server e quindi scegliere Riavvia.
Dopo il riavvio di SQL Server, il server sarà in modalità utente singolo. Verificare che SQL Server Agent non sia in esecuzione. altrimenti l'unica connessione presente non sarà più disponibile a causa del relativo utilizzo da parte di questo servizio.
Dal menu Start di Windows fare clic con il pulsante destro del mouse sull'icona per Management Studio e scegliere Esegui come amministratore. In questo modo le credenziali di amministratore verranno passate a SSMS.

Per le versioni precedenti di Windows, l'opzione Esegui come amministratore viene visualizzata come sottomenu.

In alcune configurazioni, tramite SSMS si tenta di stabilire diverse connessioni. Non sarà possibile stabilire più connessioni perché SQL Server è in modalità utente singolo. In base allo specifico scenario, eseguire una delle azioni seguenti.
1. Effettuare la connessione con Esplora oggetti mediante l'autenticazione di Windows, che include le credenziali di amministratore. Espandere Sicurezza, Account di accessoe fare doppio clic sul proprio account di accesso. Nella pagina Ruoli server selezionare sysadmin e quindi selezionare OK.
2. Anziché effettuare la connessione con Esplora oggetti, utilizzare una finestra Query tramite l'autenticazione di Windows (in cui sono incluse le credenziali di amministratore) Si tratta dell'unica modalità di connessione possibile se non è stato usato Esplora oggetti. Eseguire codice come il seguente per aggiungere un nuovo account di accesso con autenticazione di Windows che fa parte del ruolo predefinito del server sysadmin. Nell'esempio seguente viene aggiunto un utente di dominio denominato CONTOSO\PatK.
```
CREATE LOGIN [CONTOSO\PatK] FROM WINDOWS;
ALTER SERVER ROLE sysadmin ADD MEMBER [CONTOSO\PatK];
```
3. Se SQL Server è in esecuzione nella modalità di autenticazione mista, effettuare la connessione con una finestra Query usando l'autenticazione di Windows (in cui sono incluse le credenziali di amministratore). Eseguire codice come il seguente per creare un nuovo account di accesso con autenticazione di SQL Server che fa parte del ruolo predefinito del server sysadmin.
```
CREATE LOGIN TempLogin WITH PASSWORD = '************';
ALTER SERVER ROLE sysadmin ADD MEMBER TempLogin;
```
  Avviso
  
  Sostituire ************ con una password complessa.
4. Se SQL Server è in esecuzione nella modalità di autenticazione mista e si vuole reimpostare la password dell'account sa, eseguire la connessione con una finestra Query usando l'autenticazione di Windows, che include le credenziali di amministratore. Modificare la password dell'account sa con la sintassi seguente.
```
ALTER LOGIN sa WITH PASSWORD = '************';
```
  Avviso
  
  Sostituire ************ con una password complessa.
Chiudere Management Studio.
Questi passaggi successivi ripristinano la modalità multiutente per SQL Server. Nel riquadro sinistro di Gestione configurazione SQL Server selezionare Servizi di SQL Server.
Nel riquadro destro fare clic con il pulsante destro del mouse sull'istanza di SQL Server e quindi scegliere Proprietà.
Nella casella Parametri esistenti della scheda Parametri di avvio selezionare -m e quindi selezionare Rimuovi.

In alcune versioni precedenti di SQL Server non è presente alcuna scheda Parametri di avvio. In questo caso, nella scheda Avanzate fare doppio clic su Parametri di avvio. I parametri vengono visualizzati in una finestra piccola. Rimuovere i caratteri ;-m aggiunti in precedenza e selezionare OK.
Fare clic con il pulsante destro del mouse sul nome del server e quindi scegliere Riavvia. Assicurarsi di avviare di nuovo SQL Server Agent se è stato arrestato prima di avviare SQL Server in modalità utente singolo.

A questo punto è possibile connettersi normalmente con uno degli account che fa parte del ruolo predefinito del server sysadmin.