Condividi tramite

Connettersi a SQL Server se gli amministratori di sistema sono bloccati

  • Articolo

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 accessi di membri del ruolo predefinito del server di amministratore di sistema sono stati rimossi per errore.

  • Tutti i gruppi Windows membri del ruolo predefinito del server di amministratore di sistema sono stati rimossi per errore.

  • Gli account di accesso membri del ruolo predefinito del server di amministratore di sistema sono assegnati a individui 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 dell'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 utilizzare
-m Limita le connessioni a una singola connessione 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 Si prevede di effettuare la connessione 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. Si prevede di effettuare la connessione 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 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

  1. Aprire un prompt di Windows PowerShell con privilegi elevati.

  2. Configurare il nome del servizio, l'istanza di SQL Server istanza e le variabili di accesso di Windows. Sostituire questi valori con altri che si collegano 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]"

  3. Arrestare il servizio SQL Server in modo che possa essere riavviato in modalità utente singolo, usando il comando seguente:

    Se si ha un'istanza predefinita, usare MSSQLSERVER senza un nome di istanza.

    net stop $service_name

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

  5. 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. In questo passaggio si presume che siano stati sostituiti i nomi di dominio e di accesso con le credenziali che si vogliono concedere all'adesione ad amministratore di sistema.

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

    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.

  6. Modalità mista (facoltativo): se SQL Server è in esecuzione in modalità di autenticazione mista, è anche possibile:

    1. Concedere l'adesione al ruolo di amministratore di sistema a un accesso di SQL server. 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 <strong_password> con una password efficace a propria scelta.

      Se si ha un'istanza predefinita, usare il nome del server.

      $strong_password = "<strong_password>"
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 un'istanza di SQL Server sia in esecuzione in modalità di autenticazione mista e che si voglia ripristinare la password di un account sa abilitato. Modificare la password dell'account sa con la sintassi seguente. Assicurarsi di sostituire <strong_password> con una password efficace a propria scelta:

      Se si ha un'istanza predefinita, usare il nome del server.

      $strong_password = "<strong_password>"
sqlcmd.exe -E -S $sql_server_instance -Q "ALTER LOGIN sa WITH PASSWORD = $strong_password; "

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

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

  2. 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à.

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

  4. Selezionare OKe, dopo il messaggio di riavvio, fare clic con il pulsante destro del mouse sul nome del server e quindi scegliere Riavvia.

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

  6. 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 = '<strong_password>';
ALTER SERVER ROLE sysadmin ADD MEMBER TempLogin;

      Avviso

      Sostituire <password_complessa> con una password complessa.

    4. Se SQL Server è in esecuzione in modalità di autenticazione mista e si vuole ripristinare la password dell'account sa, eseguire la connessione a un intervallo di query usando l'autenticazione di Windows, che include le credenziali amministrative. Modificare la password dell'account sa con la sintassi seguente.

      ALTER LOGIN sa WITH PASSWORD = '<strong_password>';

      Avviso

      Sostituire <password_complessa> con una password complessa.

  7. Chiudere Management Studio.

  8. Questi passaggi successivi ripristinano la modalità multiutente per SQL Server. Nel riquadro sinistro di Gestione configurazione SQL Server selezionare Servizi di SQL Server.

  9. Nel riquadro destro fare clic con il pulsante destro del mouse sull'istanza di SQL Server e quindi scegliere Proprietà.

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

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

Contenuto correlato