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.
Nota
Microsoft Entra ID era precedentemente conosciuto come Azure Active Directory (Azure AD).
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'usoSQLNCLI
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 diSQLNCLI
,MSOLEDBSQL
è consigliabile.A partire da SQL Server 2022 (16.x), è necessario specificare un nome del provider. È consigliato
MSOLEDBSQL
. Se si omette @provider, è possibile riscontrare un comportamento imprevisto.
Importante
SQL Server Native Client (spesso abbreviato SNAC) è stato rimosso da SQL Server 2022 (16.x) e da SQL Server Management Studio 19 (SSMS). Il provider OLE DB di SQL Server Native Client (SQLNCLI o SQLNCLI11) e il provider OLE DB legacy Microsoft per SQL Server (SQLOLEDB) non sono consigliati per lo sviluppo di nuove applicazioni. In futuro, passare al nuovo driver Microsoft OLE DB (MSOLEDBSQL) per SQL Server.
[ @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.
Origine dati OLE DB remota | Provider OLE DB | @srvproduct | @provider | @datasrc | @location | @provstr | @catalog |
---|---|---|---|---|---|---|---|
SQL Server | Provider OLE DB di SQL Server Native Client | SQL Server 1 (impostazione predefinita) | |||||
SQL Server | Provider OLE DB di SQL Server Native Client | SQLNCLI |
Nome di rete di SQL Server (per l'istanza predefinita) | Nome di database (facoltativo) | |||
SQL Server | Provider OLE DB di SQL Server Native Client | SQLNCLI |
nomeistanza servername\ (per un'istanza specifica) | Nome di database (facoltativo) | |||
Oracle, versione 8 e successive | Provider Oracle per OLE DB | Any | OraOLEDB.Oracle |
Alias per il database Oracle | |||
Access/Jet | Provider Microsoft OLE DB per Jet | Any | Microsoft.Jet.OLEDB.4.0 |
Percorso completo del file di database Jet | |||
Origine dati ODBC | Provider Microsoft OLE DB per ODBC | Any | MSDASQL |
DSN di sistema dell'origine dati ODBC | |||
Origine dati ODBC | Provider Microsoft OLE DB per ODBC | Any | MSDASQL |
Stringa di connessione ODBC | |||
File system | Provider Microsoft OLE DB per il servizio di indicizzazione | Any | MSIDXS |
Nome del catalogo del Servizio di indicizzazione | |||
Foglio di calcolo di Microsoft Excel | Provider Microsoft OLE DB per Jet | Any | Microsoft.Jet.OLEDB.4.0 |
Percorso completo del file di Excel | Excel 5.0 | ||
IBM Db2 Database | Provider Microsoft OLE DB per DB2 | Any | DB2OLEDB |
Vedere provider Microsoft OLE DB per DB2 documentazione. | Nome del catalogo del database DB2 |
1 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.
2 "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.
Importante
Istanza gestita di SQL di Azure supporta attualmente solo SQL Server, database SQL e altre istanze gestite di SQL come origini dati remote.
Importante
Quando viene creato un server collegato tramite sp_addlinkedserver
, viene aggiunto un mapping automatico predefinito per tutti gli account di accesso locali. Per i provider non SQL Server, gli account di accesso autenticati di SQL Server potrebbero essere in grado di accedere al provider con l'account del servizio SQL Server. Si consiglia agli amministratori di considerare l'utilizzo di sp_droplinkedsrvlogin <linkedserver_name>, NULL
per rimuovere il mapping globale.
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.
Importante
Il provider OLE DB di SQL Server Native Client (SQLNCLI) rimane deprecato e non è consigliabile usarlo per il nuovo lavoro di sviluppo. Usare invece il nuovo Microsoft OLE DB Driver per SQL Server (MSOLEDBSQL) che verrà aggiornato con le funzionalità server più recenti.
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
.
Nota
In questo esempio si presuppone che sia Microsoft Access che il Northwind
database di esempio siano installati e che il Northwind
database risieda in C:\Msoffice\Access\Samples nello stesso server dell'istanza di SQL Server.
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 .
Nota
Il nome dell'origine dati ODBC specificato deve essere definito come System DSN nel server prima di utilizzare il server collegato.
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
Nota
Microsoft Entra ID era precedentemente conosciuto come Azure Active Directory (Azure AD).
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.