sp_addlinkedserver (Transact-SQL)

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

Crea un server collegato, Un server collegato consente l'accesso a query eterogenee distribuite su origini dati OLE DB. Dopo la creazione di un server collegato tramite sp_addlinkedserver, è possibile eseguire query distribuite su questo server. Se il server collegato è definito come istanza di SQL Server, è possibile eseguire stored procedure remote.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

sp_addlinkedserver
    [ @server = ] N'server'
    [ , [ @srvproduct = ] N'srvproduct' ]
    [ , [ @provider = ] N'provider' ]
    [ , [ @datasrc = ] N'datasrc' ]
    [ , [ @location = ] N'location' ]
    [ , [ @provstr = ] N'provstr' ]
    [ , [ @catalog = ] N'catalog' ]
    [ , [ @linkedstyle = ] linkedstyle ]
[ ; ]

Argomenti

[ @server = ] N'server'

Nome del server collegato da creare. @server è sysname, senza impostazione predefinita.

[ @srvproduct = ] N'srvproduct'

Nome del prodotto dell'origine dati OLE DB da aggiungere come server collegato. @srvproduct è nvarchar(128), con il valore predefinito NULL. Se il valore è SQL Server, non è necessario specificare @provider, @datasrc, @location, @provstr e @catalog .

[ @provider = ] N'provider'

Identificatore programmatico univoco (PROGID) del provider OLE DB che corrisponde a questa origine dati. Il @provider deve essere univoco per il provider OLE DB specificato installato nel computer corrente. @provider è nvarchar(128), con il valore predefinito NULL.

• In SQL Server 2019 (15.x) e versioni precedenti, se @provider viene omesso, SQLNCLI viene usato . L'uso SQLNCLI reindirizzerà SQL Server alla versione più recente del provider OLE DB di SQL Server Native Client. Il provider OLE DB deve essere registrato nel Registro di sistema con il valore PROGID specificato. Invece di SQLNCLI, MSOLEDBSQL è consigliabile.

• A partire da SQL Server 2022 (16.x), è necessario specificare un nome del provider. È consigliatoMSOLEDBSQL . Se si omette @provider, è possibile riscontrare un comportamento imprevisto.

[ @datasrc = ] N'datasrc'

Nome dell'origine dati interpretato dal provider OLE DB. @datasrc è nvarchar(4000), con il valore predefinito NULL. @datasrc viene passato come DBPROP_INIT_DATASOURCE proprietà per inizializzare il provider OLE DB.

[ @location = ] N'location'

Percorso del database interpretato dal provider OLE DB. @location è nvarchar(4000), con il valore predefinito NULL. @location viene passato come DBPROP_INIT_LOCATION proprietà per inizializzare il provider OLE DB.

[ @provstr = ] N'provstr'

Stringa di connessione specifica del provider OLE DB che identifica un'origine dati univoca. @provstr è nvarchar(4000), con un valore predefinito .NULL L'argomento provstr viene passato a IDataInitialize o impostato come DBPROP_INIT_PROVIDERSTRING proprietà per inizializzare il provider OLE DB.

Quando il server collegato viene creato nel provider OLE DB di SQL Server Native Client, è possibile specificare l'istanza usando la SERVER parola chiave per SERVER=servername\instancename specificare un'istanza specifica di SQL Server. Il nome server è il nome del computer in cui è in esecuzione SQL Server e nomeistanza è il nome dell'istanza specifica di SQL Server a cui verrà connesso l'utente.

• Per accedere a un database con mirroring, è necessario che la stringa di connessione contenga il nome del database, al fine di consentire i tentativi di failover da parte del provider di accesso ai dati. Il database può essere specificato nel parametro @provstr o @catalog . Facoltativamente, la stringa di connessione può specificare anche il nome di un partner di failover.

• Se si esegue sp_addlinkedserver da un account di accesso locale o da un account di accesso che non fa parte del ruolo sysadmin , è possibile che venga visualizzato l'errore seguente:

  Access to the remote server is denied because no login-mapping exists.
  

  Per risolvere questo problema, aggiungere il User ID parametro al stringa di connessione. Nell'esempio seguente è myUser l'ID utente passato al stringa di connessione:

  EXEC master.dbo.sp_addlinkedserver @server = N'LinkServerName',
      @provider = N'SQLNCLI',
      @srvproduct = 'MS SQL Server',
      @provstr = N'SERVER=serverName\InstanceName;User ID=myUser'
  
  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LinkServerName',
      @locallogin = NULL,
      @useself = N'False',
      @rmtuser = N'myUser',
      @rmtpassword = N'*****'
  

  Per altre informazioni, vedere Accesso al server remoto negato perché non esiste alcun mapping di accesso.

[ @catalog = ] N'catalog'

Catalogo da utilizzare quando viene stabilita una connessione al provider OLE DB. @catalog è sysname, con un valore predefinito .NULL @catalog viene passato come DBPROP_INIT_CATALOG proprietà per inizializzare il provider OLE DB. Quando il server collegato viene definito in base a un'istanza di SQL Server, il catalogo fa riferimento al database predefinito a cui è mappato il server collegato.

[ @linkedstyle = ] linkedstyle

Identificato solo a scopo informativo. Non supportato. Non è garantita la compatibilità con le versioni future.

Valori del codice restituito

0 (esito positivo) o 1 (errore).

Set di risultati

Nessuno.

Osservazioni:

Nella tabella seguente vengono descritte le possibili configurazioni di un server collegato per origini dati accessibili tramite OLE DB. Un server collegato può essere configurato in modi diversi per un'origine dati specifica. Per un tipo di origine dati possono essere disponibili più righe. Questa tabella mostra anche i valori dei sp_addlinkedserver parametri da usare per la configurazione del server collegato.

¹ Questo modo di configurare un server collegato forza il nome del server collegato allo stesso nome di rete dell'istanza remota di SQL Server. Usare @datasrc per specificare il server.

² "Any" indica che il nome del prodotto può essere qualsiasi cosa.

Il provider OLE DB di SQL Server Native Client è il provider usato con SQL Server se non viene specificato alcun nome del provider o se SQL Server viene specificato come nome del prodotto. Anche se si specifica il nome del provider precedente, SQLOLEDB, viene modificato in SQLNCLI quando viene salvato in modo permanente nel catalogo.

I parametri @datasrc, @location, @provstr e @catalog identificano il database o i database a cui punta il server collegato. Se uno di questi parametri è NULL, la proprietà di inizializzazione OLE DB corrispondente non è impostata.

In un ambiente cluster, quando si specificano nomi di file che puntano a origini dati OLE DB, la posizione deve essere specificata nel formato UNC oppure deve corrispondere a un'unità condivisa.

La stored procedure sp_addlinkedserver non può essere eseguita all'interno di una transazione definita dall'utente.

Autorizzazioni

L'istruzione sp_addlinkedserver richiede l'autorizzazione ALTER ANY LINKED SERVER . (SQL Server Management Studio La finestra di dialogo Nuovo server collegato viene implementata in modo da richiedere l'appartenenza al ruolo predefinito del server sysadmin .

Esempi

R. Usare il provider OLE DB di Microsoft SQL Server

Nell'esempio seguente viene creato un server collegato denominato SEATTLESales. Il nome del prodotto è SQL Server e non vengono utilizzati nomi di provider.

USE master;
GO
EXEC sp_addlinkedserver
   N'SEATTLESales',
   N'SQL Server';
GO

Nell'esempio seguente viene creato un server S1_instance1 collegato in un'istanza di SQL Server usando il driver OLE DB di SQL Server.

EXEC sp_addlinkedserver
   @server=N'S1_instance1',
   @srvproduct=N'',
   @provider=N'MSOLEDBSQL',
   @datasrc=N'S1\instance1';

Nell'esempio seguente viene creato un server S1_instance1 collegato in un'istanza di SQL Server usando il provider OLE DB di SQL Server Native Client.

EXEC sp_addlinkedserver
   @server=N'S1_instance1',
   @srvproduct=N'',
   @provider=N'SQLNCLI',
   @datasrc=N'S1\instance1';

B. Usare il provider Microsoft OLE DB per Microsoft Access

Il provider Microsoft.Jet.OLEDB.4.0 si connette al database di Microsoft Access che utilizza il formato 2002-2003. Nell'esempio seguente viene creato un server collegato denominato SEATTLE Mktg.

EXEC sp_addlinkedserver
   @server = N'SEATTLE Mktg',
   @provider = N'Microsoft.Jet.OLEDB.4.0',
   @srvproduct = N'OLE DB Provider for Jet',
   @datasrc = N'C:\MSOffice\Access\Samples\Northwind.mdb';
GO

C. Usare il provider Microsoft OLE DB per ODBC con il datasrc parametro

Nell'esempio seguente viene creato un server collegato denominato SEATTLE Payroll che usa il provider Microsoft OLE DB per ODBC (MSDASQL) e il parametro @datasrc .

EXEC sp_addlinkedserver
   @server = N'SEATTLE Payroll',
   @srvproduct = N'',
   @provider = N'MSDASQL',
   @datasrc = N'LocalServer';
GO

D. Usare il foglio di calcolo Microsoft OLE DB Provider for Excel

Per creare una definizione del server collegato utilizzando il provider Microsoft OLE DB per Jet per accedere a un foglio di calcolo di Excel nel formato 1997 - 2003, creare prima un intervallo denominato in Excel specificando le colonne e le righe del foglio di lavoro di Excel da selezionare. È quindi possibile fare riferimento al nome dell'intervallo come a un nome di tabella in una query distribuita.

EXEC sp_addlinkedserver 'ExcelSource',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:\MyData\DistExcl.xls',
   NULL,
   'Excel 5.0';
GO

Per accedere ai dati di un foglio di calcolo di Microsoft Excel, assegnare un nome a un intervallo di celle. Per accedere a un intervallo denominato SalesData come tabella tramite il server collegato impostato nell'esempio precedente è possibile utilizzare la query seguente.

SELECT *
   FROM ExcelSource...SalesData;
GO

Se SQL Server è in esecuzione con un account di dominio che accede a una condivisione remota, è possibile usare un percorso UNC anziché un'unità mappata.

EXEC sp_addlinkedserver 'ExcelShare',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   '\\MyServer\MyShare\Spreadsheets\DistExcl.xls',
   NULL,
   'Excel 5.0';

E. Usare il provider Microsoft OLE DB per Jet per accedere a un file di testo

Nell'esempio seguente viene creato un server collegato per accedere direttamente a file di testo senza dover collegare i file come tabelle in un file di Access con estensione mdb. Il provider è Microsoft.Jet.OLEDB.4.0 e la stringa corrispondente è Text.

L'origine dati corrisponde al percorso completo della directory che include i file di testo. In questa directory è necessario creare un file schema.ini che descriva la struttura dei file di testo. Per altre informazioni su come creare un file di schema.ini, vedere la documentazione di Jet motore di database.

Creare prima di tutto un server collegato.

EXEC sp_addlinkedserver txtsrv, N'Jet 4.0',
   N'Microsoft.Jet.OLEDB.4.0',
   N'c:\data\distqry',
   NULL,
   N'Text';

Configurare i mapping di accesso.

EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL;

Elencare le tabelle nel server collegato.

EXEC sp_tables_ex txtsrv;

Eseguire una query su una delle tabelle, in questo caso file1#txt, usando un nome in quattro parti.

SELECT * FROM txtsrv...[file1#txt];

F. Usare il provider Microsoft OLE DB per DB2

Nell'esempio seguente viene creato un server collegato denominato DB2 che usa il provider Microsoft OLE DB per DB2.

EXEC sp_addlinkedserver @server = N'DB2',
    @srvproduct = N'Microsoft OLE DB Provider for DB2',
    @catalog = N'DB2',
    @provider = N'DB2OLEDB',
    @provstr = N'Initial Catalog=pubs;
       Data Source=DB2;
       HostCCSID=1252;
       Network Address=XYZ;
       Network Port=50000;
       Package Collection=admin;
       Default Schema=admin;';

G. Aggiungere un database SQL di Azure come server collegato da usare con query distribuite nei database cloud e locali

È possibile aggiungere un database SQL di Azure come server collegato e usarlo con query distribuite che si estendono sui database locali e cloud. Si tratta di un componente per le soluzioni ibride di database che si estendono su reti aziendali locali e sul cloud di Azure.

Il prodotto sql Server contiene la funzionalità di query distribuita, che consente di scrivere query per combinare dati da origini dati locali e dati da origini remote (inclusi i dati provenienti da origini dati non SQL Server) definite come server collegati. Ogni database SQL di Azure (ad eccezione del database del master server logico) può essere aggiunto come singolo server collegato e quindi usato direttamente nelle applicazioni di database come qualsiasi altro database.

I vantaggi dell'uso di database SQL di Azure includono gestibilità, disponibilità elevata, scalabilità, uso di un modello di sviluppo familiare e un modello di dati relazionale. I requisiti dell'applicazione di database determinano come userebbe database SQL di Azure nel cloud. È possibile spostare tutti i dati contemporaneamente in database SQL di Azure o spostare progressivamente alcuni dati mantenendo i dati rimanenti in locale. Per un'applicazione di database ibrido di questo tipo, è ora possibile aggiungere database SQL di Azure come server collegati e l'applicazione di database può eseguire query distribuite per combinare dati da origini dati database SQL di Azure e locali.

Ecco un esempio che illustra come connettersi a un database SQL di Azure usando query distribuite.

Aggiungere prima di tutto un database SQL di Azure come server collegato usando SQL Server Native Client.

EXEC sp_addlinkedserver
    @server = 'LinkedServerName',
    @srvproduct = '',
    @provider = 'sqlncli',
    @datasrc = 'ServerName.database.windows.net',
    @location = '',
    @provstr = '',
    @catalog = 'DatabaseName';

Aggiungere credenziali e opzioni a questo server collegato.

EXEC sp_addlinkedsrvlogin
  @rmtsrvname = 'LinkedServerName',
  @useself = 'false',
  @rmtuser = 'LoginName',
  @rmtpassword = 'myPassword';

EXEC sp_serveroption 'LinkedServerName', 'rpc out', true;

Usare ora il server collegato per eseguire query usando nomi in quattro parti, anche per creare una nuova tabella e inserire dati.

EXEC ('CREATE TABLE SchemaName.TableName(col1 int not null CONSTRAINT PK_col1 PRIMARY KEY CLUSTERED (col1) )') at LinkedServerName;
EXEC ('INSERT INTO SchemaName.TableName VALUES(1),(2),(3)') at LinkedServerName;

Eseguire query sui dati usando nomi in quattro parti:

SELECT * FROM LinkedServerName.DatabaseName.SchemaName.TableName;

H. Creare Istanza gestita di SQL di Azure server collegato con l'autenticazione dell'identità gestita

Per creare un server collegato con l'autenticazione dell'identità gestita, eseguire il T-SQL seguente, sostituendo <managed_instance> con la propria istanza gestita di SQL. Il metodo di autenticazione usa ActiveDirectoryMSI nel parametro @provstr . È consigliabile usare @locallogin = NULL facoltativamente per consentire tutti gli account di accesso locali.

EXEC master.dbo.sp_addlinkedserver
    @server = N'MyLinkedServer',
    @srvproduct = N'',
    @provider = N'MSOLEDBSQL',
    @provstr = N'Server=<mi_name>.<dns_zone>.database.windows.net,1433;Authentication=ActiveDirectoryMSI;';

EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname = N'MyLinkedServer',
    @useself = N'False',
    @locallogin = N'user1@contoso.com';

Per abilitare l'autenticazione con identità gestite, è necessario aggiungere un'identità gestita assegnata al Istanza gestita di SQL di Azure come account di accesso all'istanza gestita remota. Sono supportate sia le identità gestite assegnate dal sistema che le identità gestite assegnate dall'utente.

Se è impostata un'identità primaria, viene usata, in caso contrario viene usata l'identità gestita assegnata dal sistema. Se l'identità gestita viene ricreata con lo stesso nome, è necessario ricreare anche l'account di accesso nell'istanza remota, perché il nuovo ID applicazione di identità gestita e Istanza gestita di SQL SID dell'entità servizio non corrispondono più. Per verificare la corrispondenza di questi due valori, convertire SID in ID applicazione con la query seguente.

SELECT convert(uniqueidentifier, sid) as MSEntraApplicationID
FROM sys.server_principals
WHERE name = '<managed_instance_name>';

I. Creare un server collegato di Istanza gestita di SQL di Azure con l'autenticazione pass-through di Microsoft Entra

Per creare un server collegato con autenticazione pass-through, eseguire il codice T-SQL seguente, sostituendo <managed_instance> con il proprio server di istanza gestita di SQL:

EXEC master.dbo.sp_addlinkedserver
    @server = N'MyLinkedServer',
    @srvproduct = N'',
    @provider = N'MSOLEDBSQL',
    @datasrc = N'<mi_name>.<dns_zone>.database.windows.net,1433';

Con l'autenticazione pass-through, il contesto di sicurezza dell'account di accesso locale viene trasportato all'istanza remota. L'autenticazione pass-through richiede l'aggiunta dell'entità Microsoft Entra come account di accesso sia nel Istanza gestita di SQL di Azure locale che in remoto. Entrambe le istanze gestite devono trovarsi in un gruppo di attendibilità del server. Quando vengono soddisfatti i requisiti, l'utente può accedere a un'istanza locale ed eseguire query sull'istanza remota tramite l'oggetto server collegato.

Contenuto correlato

• Stored procedure di query distribuite (Transact-SQL)
• sp_addlinkedsrvlogin (Transact-SQL)
• sp_addserver (Transact-SQL)
• sp_dropserver (Transact-SQL)
• sp_serveroption (Transact-SQL)
• sp_setnetname (Transact-SQL)
• Stored procedure di sistema (Transact-SQL)
• Tabelle di sistema (Transact-SQL)