Configurazione lato server per la connessione a SQL Server con autenticazione SQL Server

Data di creazione della pagina : domenica 29 maggio 2022

ambiente

SQL Server

SQL Server 2019

Windows (utilizzato come server)

Windows 10 Pro 20H2

Windows Server (utilizzato come server)

Windows Server 2019

precondizione

Ho provato diverse versioni del sistema operativo e SQL Server da installare, ma in ogni caso, le impostazioni al momento dell'installazione e dopo l'installazione di SQL Server sono le seguenti.

SQL Server viene installato con privilegi di amministratore.
Il percorso di installazione di SQL Server rimane il valore predefinito.
Il sistema operativo viene installato quasi immediatamente.
Si supponga che SQL Server Management Studio è installato sul lato server.
Tutto in questi suggerimenti è lato server ad eccezione del controllo della connessione client.

La funzionalità SQL Server seleziona solo il servizio Motore di database.

Verificare la presenza di istanze sia in Istanza predefinita che in Istanza denominata.

Lasciare l'account del servizio al valore predefinito.

La modalità di autenticazione è solo l'autenticazione di Windows. Abilitare l'autenticazione di SQL Server in una configurazione successiva come passaggio di configurazione.

Creare un database denominato "TestDatabase". Ne ho creato solo uno nuovo per la conferma e non ho creato nulla all'interno.

Si supponga di creare un account denominato "TestUser" ed eseguire l'autenticazione di SQL Server con questo account. Hai accesso a "TestDatabase".

Abilitare l'autenticazione di SQL Server

Se l'autenticazione di SQL Server non è stata abilitata durante l'installazione di SQL Server, attenersi alla seguente procedura:

Avviare Microsoft SQL Server Management Studio sul lato server.

Accedi al tuo server con l'autenticazione di Windows. Se è presente un nome di istanza, immetterlo nel formato "nome server < >\nome istanza <>".

In Esplora oggetti fare clic con il pulsante destro del mouse sul server e scegliere Proprietà.

Seleziona "Sicurezza" dal menu a sinistra e seleziona "Modalità di autenticazione di SQL Server e modalità di autenticazione di Windows" da "Autenticazione server".

Una volta confermato, verrà richiesto di riavviare SQL Server.

Fare clic con il pulsante destro del mouse sul server da Esplora oggetti e selezionare Riavvia. Il riavvio richiede i privilegi di amministratore.

Selezionare Sì per riavviare SQL Server.

Consenti connessioni remote

Nel caso in cui pensi che sia abilitato per impostazione predefinita.

In SQL Server Management Studio fare clic con il pulsante destro del mouse sul server da Esplora oggetti e scegliere Proprietà.

Seleziona "Connetti" dal menu a sinistra e assicurati che "Consenti connessioni remote a questo server" sia selezionato in "Connessione server remoto".

Autorizzazione di accesso tramite account sa (solo se necessario)

Se desideri connetterti in remoto con il tuo account sa, configuralo. SA è il più alto livello di privilegio, quindi è una buona idea disabilitarlo se non ne hai bisogno. A proposito, se è stata abilitata l'autenticazione di SQL Server durante l'installazione di SQL Server, le connessioni remote sono già abilitate per l'account sa.

Aprire SQL Server Management Studio, espandere Server, Sicurezza e Accesso da Esplora oggetti, fare clic con il pulsante destro del mouse su sa e scegliere Proprietà.

Seleziona "Generale" dal menu a sinistra e inserisci la tua password.

Seleziona "Stato" dal menu a sinistra e imposta "Accedi" su "Abilita".

TCP/IP

Abilitazione di TCP/IP

Aprire la cartella Microsoft SQL Server 2019 dal menu Start e selezionare Gestione configurazione SQL Server 2019.

* Il nome cambia a seconda della versione di SQL Server.

Dalla struttura ad albero a sinistra espandere Gestione configurazione SQL Server (locale), Configurazione di rete SQL Server e selezionare Protocollo MSSQLSERVER. Fare clic con il pulsante destro del mouse su TCP/IP dall'elenco a destra e selezionare Abilita.

* Il nome di "MSSQLSERVER" cambierà nel nome se il nome dell'istanza è specificato al momento dell'installazione.

Fare clic su OK.

Selezionare SERVIZI SQL Server dalla struttura a sinistra, fare clic con il pulsante destro del mouse su SQL Server (MSSQLSERVER) dall'elenco a destra e scegliere Riavvia.

Controllo o specifica della porta TCP (se si specifica un nome di istanza)

Se non si specifica un nome di istanza, viene specificata la porta 1433 TCP predefinita, ma se si specifica un nome di istanza, è necessario utilizzare altre porte.

Dalla struttura ad albero a sinistra espandere Gestione configurazione SQL Server (locale), Configurazione di rete SQL Server e selezionare Protocollo MSSQLSERVER. Fare doppio clic su TCP/IP dall'elenco a destra per aprire le proprietà.

* Il nome di "MSSQLSERVER" cambierà nel nome se il nome dell'istanza è specificato al momento dell'installazione.

Fare clic sulla scheda Indirizzo IP.

Controllare le porte elencate in Porte dinamiche TCP nel gruppo IPAll. A proposito, questo valore può essere modificato in qualsiasi valore. È possibile specificare questo valore più avanti nelle impostazioni del firewall.

firewall

Firewall (TCP)

Dal menu Start, apri la cartella Strumenti di amministrazione di Windows e seleziona Windows Defender Firewall con sicurezza avanzata.

Fare clic con il pulsante destro del mouse su Regole in ingresso nell'albero a sinistra e selezionare Nuova regola.

Seleziona Porte.

Selezionare TCP.

Se non è stato specificato un nome di istanza per SQL Server, immettere 1433 per Porta locale specifica.

Se è stato specificato un nome di istanza per SQL Server, verificare in Gestione configurazione SQL Server la presenza di "Porta locale specifica" oppure specificare la porta dinamica TCP immessa.

Assicurati che "Consenti connessione" sia selezionato.

Si prega di controllare solo "Dominio" e "Privato". Non è destinato ad essere pubblicato su Internet.

Inserisci un nome che sarà facile da capire quando lo vedrai in seguito. Qui, è "[Aggiungi] SQL Server TCP 1433".

Stato aggiunto.

Se non si specifica un nome di istanza, la configurazione viene completata.

Firewall (UDP) (se si specifica un nome di istanza)

Apri Windows Defender Firewall con sicurezza avanzata, fai clic con il pulsante destro del mouse su Regole in ingresso nell'albero a sinistra e seleziona Nuova regola.

Seleziona Porte.

Selezionare UDP e immettere 1434 per Porta locale specifica.

Assicurati che "Consenti connessione" sia selezionato.

Si prega di controllare solo "Dominio" e "Privato". Non è destinato ad essere pubblicato su Internet.

Inserisci un nome che sarà facile da capire quando lo vedrai in seguito. Qui, è "[Aggiunto] SQL Server UDP 1434".

Stato aggiunto.

Avviare SQL Server Browser (se si specifica un nome di istanza)

Aprire la cartella Microsoft SQL Server 2019 dal menu Start e selezionare Gestione configurazione SQL Server 2019.

* Il nome cambia a seconda della versione di SQL Server.

Dalla struttura ad albero a sinistra espandere Gestione configurazione SQL Server (locale) e selezionare Servizi SQL Server. Fare clic con il pulsante destro del mouse su SQL Server Rowzer dall'elenco a destra e scegliere Proprietà.

Seleziona la scheda "Servizi" e imposta "Modalità di avvio" nel gruppo "Generale" su "Automatico". Una volta selezionato, fare clic sul pulsante OK.

Quando si torna alla schermata precedente, fare nuovamente clic con il pulsante destro del mouse su SQL Server Rowzer e scegliere Avvia.

Questo completa la configurazione.

Quando non riesci a connetterti con tutti i mezzi

Se l'autenticazione di SQL Server non viene eseguita anche dopo aver effettuato le impostazioni finora, è possibile connettersi effettuando le impostazioni seguenti. Tuttavia, tieni presente che potrebbe essere più debole rispetto all'impostazione precedente in termini di sicurezza.

Provare innanzitutto a connettersi in SQL Server Management Studio

Quando si controlla la connessione con un programma o simili, sono presenti molti elementi di controllo, ad esempio l'utente della connessione che non disponeva dell'autorità di accesso al database, pertanto verificare innanzitutto se l'autenticazione di SQL Server può essere eseguita da SQL Server Management Studio.

Il profilo di rete è "privato"?

A volte, lo si imposta accidentalmente su "pubblico" subito dopo l'installazione di Windows o la connessione a una nuova rete. In questo stato, non sarà possibile connettersi in rete in molti altri modi oltre a SQL Server. Se ci si trova in una rete sicura che non è un numero imprecisato di reti condivise, impostarla su "private".

Consenti sqlservr.exe sul firewall

Si noti che questa impostazione equivale a specificare tutte le porte di SQL Server.

Apri Windows Defender Firewall con sicurezza avanzata, fai clic con il pulsante destro del mouse su Regole in ingresso nell'albero a sinistra e seleziona Nuova regola.

Seleziona Programmi.

In Questo percorso di programma specificare il percorso del seguente file EXE: MSSQL15.SQLEXPRESS dipende dalla versione di SQL Server.

%ProgramFiles%\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Binn\sqlservr.exe

Assicurati che "Consenti connessione" sia selezionato.

Si prega di controllare solo "Dominio" e "Privato". Non è destinato ad essere pubblicato su Internet.

Inserisci un nome che sarà facile da capire quando lo vedrai in seguito. Qui, è "[Add] SQL Server EXE".

Stato aggiunto.

Passaggi per eseguire la migrazione di tutti i dati tra server in SQL Server