Condividi tramite

Creare server collegati (motore di database di SQL Server)

  • Articolo

Si applica a: SQL Server e Istanza gestita di SQL di Azure

Questo articolo illustra come creare un server collegato e accedere ai dati da un'altra istanza di SQL Server, da Istanza gestita di SQL di Azure o da un'altra origine dati usando SQL Server Management Studio (SSMS) o Transact-SQL. I server collegati consentono al motore di database di SQL Server e a Istanza gestita di SQL di Azure di leggere i dati dalle origini dati remote e di eseguire comandi sui server di database remoti, ad esempio le origini dati OLE DB, al di fuori dell'istanza di SQL Server.

Background

In genere i server collegati sono configurati per consentire al motore di database di eseguire un'istruzione Transact-SQL che include tabelle in un'altra istanza di SQL Server o in un altro prodotto del database, ad esempio Oracle. È possibile configurare come server collegati molti tipi di origini dati, tra cui provider di database di terze parti e Azure Cosmos DB.

Dopo aver creato un server collegato, le query distribuite possono essere eseguite in questo server e consentono di creare un join di tabelle provenienti da più origini dati. Se il server collegato è definito come istanza di SQL Server o Istanza gestita di SQL di Azure, è possibile eseguire stored procedure remote.

Le funzionalità e gli argomenti obbligatori del server collegato possono variare in modo significativo. Tra gli esempi contenuti in questo articolo ne viene fornito uno tipico nel quale però non sono descritte tutte le opzioni. Per altre informazioni, vedere sp_addlinkedserver (Transact-SQL).

Autorizzazioni

Se si usano istruzioni Transact-SQL è richiesta l'autorizzazione ALTER ANY LINKED SERVER per il server o l'appartenenza nel ruolo predefinito del server setupadmin. Sei si usa Management Studio è richiesta l'autorizzazione CONTROL SERVER o l'appartenenza al ruolo predefinito del server sysadmin.

Creare un server collegato con SSMS

Creare un server collegato con SSMS usando la procedura seguente:

Aprire la finestra di dialogo Nuovo server collegato

In SQL Server Management Studio (SSMS):

  1. Apri Esplora oggetti.
  2. Espandere Oggetti server.
  3. Fare clic con il pulsante destro del mouse su Server collegati.
  4. Scegliere Nuovo server collegato.

Modificare la pagina Generale per le proprietà del server collegato

Nella casella Server collegato della pagina Generale digitare il nome dell'istanza di SQL Server a cui si sta eseguendo il collegamento.

Nota

Se l'istanza di SQL Server è quella predefinita, immettere il nome del computer che ospita l'istanza di SQL Server. Se l'istanza di SQL Server è un'istanza denominata, immettere il nome del computer e il nome dell'istanza, ad esempio Contabilità\SQLExpress.

Specificare il Tipo di server e le informazioni correlate se necessario:

  • SQL Server
    Identificare il server collegato come istanza di Microsoft SQL Server o Istanza gestita di SQL di Azure. Se per definire un server collegato si usa questo metodo, il nome specificato in Server collegato deve essere quello di rete del server. Inoltre, le eventuali tabelle recuperate dal server provengono dal database predefinito impostato per l'account di accesso del server collegato.

  • Altra origine dati
    Specificare un tipo di server OLE DB diverso da SQL Server. La selezione di questa opzione determina l'attivazione delle opzioni sottostanti.

    • Provider
      Consente di selezionare un'origine dati OLE DB nella casella di riepilogo. Il provider OLE DB viene registrato nel Registro di sistema con il valore PROGID specificato.

    • Nome prodotto
      Consente di specificare il nome del prodotto dell'origine dati OLE DB da aggiungere come server collegato.

    • Origine dati
      Consente di digitare il nome dell'origine dati in base alla modalità di interpretazione del provider OLE DB. Se ci si connette a un'istanza di SQL Server, specificare il nome dell'istanza.

    • Stringa provider
      Digitare il ProgID univoco del provider OLE DB che corrisponde all'origine dati. Per esempi di stringhe provider valide, vedere sp_addlinkedserver (Transact-SQL).

    • Location
      Digitare la posizione del database secondo la modalità di interpretazione del provider OLE DB.

    • Catalog
      Digitare il nome del catalogo da utilizzare durante la connessione al provider OLE DB.

Modificare la pagina Sicurezza per le proprietà del server collegato

Nella pagina Sicurezza specificare il contesto di protezione che verrà usato quando l'istanza originale si connette al server collegato. Esistono due strategie per configurare questa opzione, che è possibile usare singolarmente o in combinazione. La prima consiste nell'eseguire il mapping degli account di accesso dal server locale al server remoto e la seconda riguarda il modo in cui il server collegato deve considerare gli account di accesso non mappati.

Aggiungere i mapping degli account di accesso

Facoltativamente, è possibile specificare il modo in cui gli account di accesso del server locale verranno autenticati usando il server collegato.

In Mapping account di accesso al server locale/account di accesso al server remoto ripetere il processo seguente per ogni account di accesso di cui eseguire il mapping:

  1. Selezionare Aggiungi.

  2. Specificare un valore per Account di accesso locale.

    Consente di specificare l'ID di accesso locale con cui è possibile connettersi al server collegato. L'account di accesso locale può essere basato sull'autenticazione di SQL Server o sull'autenticazione di Windows. L'uso di un gruppo di Windows o di un utente di database indipendente non è supportato. Utilizzare questo elenco per limitare la connessione a determinati account di accesso o per consentire ad alcuni account di connettersi con un account di accesso diverso.

    Nota

    I problemi comuni relativi ai server collegati che usano l'autenticazione di Windows in un'istanza remota di SQL Server derivano da problemi con i nomi delle entità servizio (SPN). Per altre informazioni, vedere Supporto del nome dell'entità servizio (SPN) nelle connessioni client. Microsoft Kerberos Configuration Manager for SQL Server è uno strumento di diagnostica che semplifica la risoluzione dei problemi di connettività correlati a Kerberos con SQL Server. Per altre informazioni, vedere Microsoft Kerberos Configuration Manager per SQL Server.

  3. Selezionare Rappresentazione (facoltativo).

    Consente di passare il nome utente e la password dall'account di accesso locale al server collegato. Nel caso dell'autenticazione di SQL Server, è necessario che nel server remoto esista un account di accesso con un nome e una password identici. Nel caso degli account di accesso Windows, è necessario che l'account di accesso sia valido nel server collegato.

    Per utilizzare la funzionalità di rappresentazione, è indispensabile che la configurazione risponda ai requisiti per la delega.

  4. Specificare un valore per Utente remoto se non si usa la rappresentazione.

    Usare l'utente remoto per eseguire il mapping dell'utente definito in Account di accesso locale. L'utente remoto deve essere un account di accesso basato sull'autenticazione di SQL Server nel server remoto.

  5. Specificare un valore per Password remota se non si usa la rappresentazione.

    • Consente di specificare la password dell'Utente remoto.

  6. Selezionare Rimuovi per rimuovere un account di accesso locale esistente, se necessario.

Specificare il contesto di sicurezza predefinito per gli account di accesso non presenti nell'elenco di mapping

In un ambiente di dominio in cui gli utenti eseguono la connessione tramite account di dominio personalizzati, la selezione di Verranno effettuate con il contesto di sicurezza corrente dell'account di accesso è spesso la soluzione migliore. Quando gli utenti eseguono la connessione all'istanza originale di SQL Server utilizzando un account di accesso di SQL Server , la scelta ottimale spesso consiste nel selezionare Verranno effettuate con il contesto di sicurezza seguente, quindi fornendo le credenziali necessarie per l'autenticazione nel server collegato.

Selezionare una delle seguenti opzioni:

  • Non verranno effettuate
    Per gli account di accesso non definiti nell'elenco non verrà effettuata una connessione.

  • Verranno effettuate senza un contesto di sicurezza
    Per gli account di accesso non definiti nell'elenco verrà effettuata una connessione senza un contesto di sicurezza.

  • Verranno effettuate con il contesto di sicurezza corrente dell'account di accesso
    Per gli account di accesso non definiti nell'elenco verrà effettuata una connessione con il contesto di sicurezza corrente. Se la connessione al server locale è stata effettuata usando l'autenticazione di Windows, per la connessione al server remoto verranno utilizzate le credenziali di Windows. Se la connessione al server locale è stata effettuata usando l'autenticazione di SQL Server, per la connessione al server remoto verranno usati il nome e la password dell'account di accesso. In tal caso, è necessario che nel server remoto esista un account di accesso con un nome e una password identici.

  • Verranno effettuate con il contesto di sicurezza seguente
    Per gli account di accesso non definiti nell'elenco verrà effettuata una connessione usando l'account di accesso e la password specificati nelle caselle in Account di accesso remoto e Password. L'account di accesso remoto deve essere basato sull'autenticazione di SQL Server nel server remoto.

    Attenzione

    Se un server collegato è configurato con l'opzione Realizzato utilizzando questo contesto di protezione, qualsiasi utente nell'istanza può accedere al server collegato remoto usando questo contesto. Ciò può comportare il potenziale imprevisto di abuso o accesso interno dannoso. L’accesso remoto autenticato SQL fornito al server collegato devono essere concesse le autorizzazioni minime necessarie sul server remoto, per garantire il principio del minimo privilegio e ridurre la superficie di attacco.

Modificare la pagina Opzioni server nelle proprietà del server collegato (facoltativo)

Per visualizzare o specificare le opzioni del server, selezionare la pagina Opzioni server. È possibile modificare qualsiasi opzione seguente:

  • Regole di confronto compatibili
    Influisce sull'esecuzione delle query distribuite in server collegati. Se questa opzione è impostata su true, SQL Server considera tutti i caratteri del server collegato compatibili con il server locale, relativamente al set di caratteri e alla sequenza delle regole di confronto oppure al tipo di ordinamento. Ciò consente a SQL Server di inviare al provider i confronti per colonne di tipo carattere. Se questa opzione non è impostata, SQL Server valuta sempre i confronti per le colonne di tipo carattere localmente.

    Impostare questa opzione solo se nell'origine dati corrispondente al server collegato il set di caratteri e il tipo di ordinamento corrispondono a quelli del server locale.

  • Accesso ai dati
    Consente di attivare e disabilitare un server collegato per l'accesso a query distribuite.

  • RPC
    Abilita le chiamate RPC (Remote Procedure Call) dal server specificato.

  • Chiamate RPC in uscita
    Viene abilitata l'esecuzione di chiamate RPC al server specificato.

  • Usa regole di confronto remote
    Determina se vengono utilizzate le regole di confronto di una colonna remota o di un server locale.

    Se è true, per le origini dati di SQL Server vengono usate le regole di confronto delle colonne remote, mentre per le origini dati non di SQL Server vengono usate le regole di confronto specificate nel relativo nome.

    Se è false, per le query distribuite vengono sempre utilizzate le regole di confronto predefinite del server locale, mentre il nome delle regole di confronto e le regole di confronto delle colonne remote vengono ignorati. L'impostazione predefinita è false.

  • Nome regole di confronto
    Consente di specificare il nome della regola di confronto usata dall'origine dati remota quando l'opzione Usa regole di confronto remote è true e l'origine dati non è di SQL Server. È necessario specificare il nome di uno dei set di regole di confronto supportate da SQL Server.

    Usare questa opzione per accedere a un'origine dei dati OLE DB diversa da SQL Server ma le cui regole di confronto corrispondono a una delle regole di confronto di SQL Server.

    Il server collegato deve supportare regole di confronto singole da utilizzare per tutte le colonne del server. Non impostare questa opzione se il server collegato supporta più regole di confronto nella stessa origine dati oppure se non è possibile stabilire se le regole di confronto del server collegato corrispondono a una delle regole di confronto di SQL Server.

  • Connection Timeout
    Valore di timeout espresso in secondi per la connessione al server collegato.

    Se il valore è 0, usare il valore predefinito sp_configure dell'opzione remote login timeout.

  • Timeout query
    Valore di timeout espresso in secondi per le query eseguite nel server collegato.

    Se il valore è 0, usare il valore predefinito sp_configure dell'opzione remote query timeout.

  • Abilita promozione delle transazioni distribuite
    Usare questa opzione per proteggere le azioni di una procedura da server a server tramite una transazione MS DTC (Microsoft Distributed Transaction Coordinator). Quando questa opzione è impostata su TRUE, la chiamata di una stored procedure remota comporta l'avvio di una transazione distribuita e l'integrazione della transazione in MS DTC. Per altre informazioni, vedere sp_serveroption (Transact-SQL).

Salvare il server collegato

Seleziona OK.

Visualizzare o modificare le opzioni del provider di server collegati in SSMS

Non tutti i provider dispongono delle stesse opzioni. Ad esempio, alcuni tipi di dati, a differenza di altri, dispongono di indici. Usare questa finestra di dialogo per consentire a SQL Server di riconoscere le funzionalità del provider. SQL Server installa alcuni provider di dati comuni; tuttavia, quando il prodotto che fornisce i dati cambia, il provider installato da SQL Server potrebbe non supportare tutte le funzionalità più recenti. La migliore fonte di informazioni sulle funzionalità del prodotto che fornisce i dati è la documentazione di quel prodotto.
Per aprire la pagina Opzioni provider del server collegato in SSMS:

  1. Apri Esplora oggetti.
  2. Espandere Oggetti server.
  3. Espandere Server collegati.
  4. Espandere Provider.
  5. Fare clic con il pulsante destro del mouse su un provider e scegliere Proprietà.

Le opzioni del provider sono definite come segue:

  • Parametro dinamico
    Indica che il provider consente l'utilizzo della sintassi con il marcatore di parametro "?" nel caso di query con parametri. Impostare questa opzione solo se il provider supporta l'interfaccia ICommandWithParameters e "?" come marcatore di parametro. L'impostazione di questa opzione consente a SQL Server di eseguire query con parametri sul provider. In determinati casi, la possibilità di eseguire query con parametri sul provider può determinare un miglioramento delle prestazioni.

  • Query nidificate
    Indica che il provider supporta le istruzioni SELECT nidificate nella clausola FROM. L'impostazione di questa opzione consente a SQL Server di delegare al provider specifiche query che richiedono l'annidamento di istruzioni SELECT nella clausola FROM.

  • Solo livello zero
    Indica che sul provider vengono richiamate solo le interfacce OLE DB di livello 0.

  • Consenti in-process

    SQL Server consente la creazione di un'istanza del provider come server In-Process. Se questa opzione non viene impostata, per impostazione predefinita viene creata un'istanza del provider al di fuori del processo di SQL Server. La creazione di un'istanza del provider all'esterno del processo di SQL Server protegge il processo stesso dagli errori del provider. Quando l'istanza del provider viene creata al di fuori del processo di SQL Server, gli aggiornamenti o gli inserimenti che fanno riferimento a colonne lunghe (text, ntext o image) non sono consentiti.

  • Aggiornamenti non in transazioni
    SQL Server consente gli aggiornamenti anche se ITransactionLocal non è disponibile. Se questa opzione è abilitata, gli aggiornamenti sul provider non sono recuperabili poiché il provider non supporta le transazioni.

  • Indici come percorso di accesso
    SQL Server tenta di utilizzare gli indici del provider per recuperare i dati. Per impostazione predefinita, gli indici vengono utilizzati solo per i metadati e non vengono mai aperti

  • Accesso ad hoc non consentito
    SQL Server non consente l'accesso ad hoc tramite le funzioni OPENROWSET e OPENDATASOURCE sul provider OLE DB. Quando questa opzione non è impostata, in SQL Server non è possibile l'accesso ad hoc.

  • Supporta l'operatore 'Like'
    Indica che il provider supporta le query che utilizzano la parola chiave LIKE.

Creare un server collegato con Transact-SQL

Per creare un server collegato con Transact-SQL, usare le istruzioni sp_addlinkedserver (Transact-SQL), CREATE LOGIN (Transact-SQL) e sp_addlinkedsrvlogin (Transact-SQL).

In questo esempio viene creato un server collegato a un'altra istanza di SQL Server tramite Transact-SQL:

  1. Nell'editor query immettere il comando Transact-SQL seguente per il collegamento a un'istanza di SQL Server denominata SRVR002\ACCTG:

    USE [master]  
GO  
EXEC master.dbo.sp_addlinkedserver   
    @server = N'SRVR002\ACCTG',   
    @srvproduct=N'SQL Server';  
GO

  2. Eseguire il codice riportato di seguito per configurare il server collegato in modo da utilizzare le credenziali di dominio dell'account di accesso utilizzate dal server collegato.

    EXEC master.dbo.sp_addlinkedsrvlogin   
    @rmtsrvname = N'SRVR002\ACCTG',   
    @locallogin = NULL ,   
    @useself = N'True';  
GO

Completamento: passaggi da effettuare dopo aver creato un server collegato

La procedura seguente consente di convalidare un server collegato.

Testare il server collegato

Considerare uno dei due approcci seguenti per testare l'autenticazione di un server collegato nel contesto di sicurezza corrente.

  • Per verificare la possibilità di connettersi a un server collegato in SSMS, passare al server collegato in Esplora oggetti, fare clic con il pulsante destro del mouse sul server collegato, quindi scegliere Connessione di test.

  • Per verificare la possibilità di connettersi a un server collegato in T-SQL, eseguire una semplice istruzione SELECT, ad esempio, per recuperare informazioni di base sul catalogo del database. In questo esempio vengono restituiti i nomi dei database nel server collegato.

    SELECT name FROM [SRVR002\ACCTG].master.sys.databases;  
GO

Creare un join tra tabelle da un server collegato

Utilizzare nomi in quattro parti per fare riferimento a un oggetto in un server collegato. Eseguire il codice riportato di seguito per restituire un elenco di tutti gli account di accesso nel server locale e i relativi account di accesso corrispondenti nel server collegato.

SELECT local.name AS LocalLogins, linked.name AS LinkedLogins  
FROM master.sys.server_principals AS local  
LEFT JOIN [SRVR002\ACCTG].master.sys.server_principals AS linked  
     ON local.name = linked.name;  
GO

Se viene restituito NULL per l'account di accesso del server collegato, significa che tale account di accesso non è presente nel server collegato. Questi account di accesso non potranno essere utilizzati dal server collegato a meno che tale server non sia configurato per passare un contesto di sicurezza differente oppure se il server collegato accetta connessioni anonime.

Server collegati con Istanza gestita di SQL di Azure

Se si usa Istanza gestita di SQL di Azure, vedere gli esempi seguenti di sp_addlinkedserver (Transact-SQL):

Passaggi successivi

Per altre informazioni sulla gestione dei server collegati, vedere gli articoli seguenti: