sp_addlinkedserver (T-SQL)

  • Artikel

Berlaku untuk:SQL ServerAzure SQL Managed Instance

Membuat server tertaut. Server tertaut menyediakan akses ke kueri heterogen terdistribusi dan heterogen terhadap sumber data OLE DB. Setelah server tertaut dibuat dengan menggunakan sp_addlinkedserver, kueri terdistribusi dapat dijalankan terhadap server ini. Jika server tertaut didefinisikan sebagai instans SQL Server, prosedur tersimpan jarak jauh dapat dijalankan.

Catatan

ID Microsoft Entra sebelumnya dikenal sebagai Azure Active Directory (Azure AD).

Konvensi sintaks transact-SQL

Sintaks

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 ]
[ ; ]

Argumen

[ @server = ] N'server'

Nama server tertaut yang akan dibuat. @server adalah sysname, tanpa default.

[ @srvproduct = ] N'srvproduct'

Nama produk sumber data OLE DB untuk ditambahkan sebagai server tertaut. @srvproduct adalah nvarchar(128), dengan default NULL. Jika nilainya adalah SQL Server, @provider, @datasrc, @location, @provstr, dan @catalog tidak perlu ditentukan.

[ @provider = ] N'provider'

Pengidentifikasi terprogram unik (PROGID) dari penyedia OLE DB yang sesuai dengan sumber data ini. @provider harus unik untuk penyedia OLE DB yang ditentukan yang diinstal pada komputer saat ini. @provider adalah nvarchar(128), dengan default NULL.

  • Di SQL Server 2019 (15.x) dan versi yang lebih lama, jika @provider dihilangkan, SQLNCLI digunakan. Menggunakan SQLNCLI akan mengalihkan SQL Server ke versi terbaru Penyedia OLE DB SQL Server Native Client. Penyedia OLE DB diharapkan terdaftar dengan PROGID yang ditentukan dalam registri. Alih-alih SQLNCLI, MSOLEDBSQL disarankan.

  • Dimulai dengan SQL Server 2022 (16.x), Anda harus menentukan nama penyedia. MSOLEDBSQL disarankan. Jika Anda menghilangkan @provider, Anda dapat mengalami perilaku tak terduga.

Penting

SQL Server Native Client (sering disingkat SNAC) telah dihapus dari SQL Server 2022 (16.x) dan SQL Server Management Studio 19 (SSMS). Penyedia SQL Server Native Client OLE DB (SQLNCLI atau SQLNCLI11) dan Penyedia Microsoft OLE DB warisan untuk SQL Server (SQLOLEDB) tidak direkomendasikan untuk pengembangan baru. Beralih ke Microsoft OLE DB Driver (MSOLEDBSQL) baru untuk SQL Server ke depannya.

[ @datasrc = ] N'datasrc'

Nama sumber data seperti yang ditafsirkan oleh penyedia OLE DB. @datasrc adalah nvarchar(4000), dengan default NULL. @datasrc diteruskan sebagai DBPROP_INIT_DATASOURCE properti untuk menginisialisasi penyedia OLE DB.

[ @location = ] N'location'

Lokasi database seperti yang ditafsirkan oleh penyedia OLE DB. @location adalah nvarchar(4000), dengan default NULL. @location diteruskan sebagai DBPROP_INIT_LOCATION properti untuk menginisialisasi penyedia OLE DB.

[ @provstr = ] N'provstr'

String koneksi khusus penyedia OLE DB yang mengidentifikasi sumber data unik. @provstr adalah nvarchar(4000), dengan default NULL. Argumen provstr diteruskan ke IDataInitialize atau diatur sebagai DBPROP_INIT_PROVIDERSTRING properti untuk menginisialisasi penyedia OLE DB.

Ketika server tertaut dibuat terhadap penyedia SQL Server Native Client OLE DB, instans dapat ditentukan dengan menggunakan SERVER kata kunci untuk SERVER=servername\instancename menentukan instans SQL Server tertentu. Nama server adalah nama komputer tempat SQL Server berjalan, dan nama instans adalah nama instans tertentu dari SQL Server tempat pengguna akan terhubung.

  • Untuk mengakses database cermin, string koneksi harus berisi nama database. Nama ini diperlukan untuk mengaktifkan upaya failover oleh penyedia akses data. Database dapat ditentukan dalam parameter @provstr atau @catalog . Secara opsional, string koneksi juga dapat menyediakan nama mitra failover.

  • Jika Anda menjalankan sp_addlinkedserver dari login lokal, atau login yang bukan bagian dari peran sysadmin , Anda mungkin menerima kesalahan berikut:

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

    Untuk mengatasi masalah ini, tambahkan parameter ke User ID string koneksi Anda. Dalam contoh berikut, myUser adalah ID Pengguna yang diteruskan ke string koneksi:

    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'*****'

    Untuk informasi selengkapnya, lihat Akses ke server jarak jauh ditolak karena tidak ada pemetaan masuk.

[ @catalog = ] N'catalog'

Katalog yang akan digunakan ketika koneksi dibuat ke penyedia OLE DB. @catalog adalah sysname, dengan default NULL. @catalog diteruskan sebagai DBPROP_INIT_CATALOG properti untuk menginisialisasi penyedia OLE DB. Ketika server tertaut didefinisikan terhadap instans SQL Server, katalog mengacu pada database default tempat server tertaut dipetakan.

[ @linkedstyle = ] gaya tertaut

Diidentifikasi hanya untuk tujuan informasi. Tidak didukung. Kompatibilitas di masa mendatang tidak dijamin.

Mengembalikan nilai kode

0 (berhasil) atau 1 (kegagalan).

Tataan hasil

Tidak ada.

Keterangan

Tabel berikut ini memperlihatkan cara agar server tertaut dapat disiapkan untuk sumber data yang dapat diakses melalui OLE DB. Server tertaut dapat disiapkan lebih dari satu cara untuk sumber data tertentu; mungkin ada lebih dari satu baris untuk jenis sumber data. Tabel ini juga memperlihatkan nilai parameter yang sp_addlinkedserver akan digunakan untuk menyiapkan server tertaut.

Sumber data OLE DB jarak jauh Penyedia OLE DB @srvproduct @provider @datasrc @location @provstr @catalog
SQL Server Penyedia OLE DB Klien Asli SQL Server SQL Server 1 (default)
SQL Server Penyedia OLE DB Klien Asli SQL Server SQLNCLI Nama jaringan SQL Server (untuk instans default) Nama database (opsional)
SQL Server Penyedia OLE DB Klien Asli SQL Server SQLNCLI nama instans nama\server (untuk instans tertentu) Nama database (opsional)
Oracle, versi 8 dan yang lebih baru Penyedia Oracle untuk OLE DB Mana pun OraOLEDB.Oracle Alias untuk database Oracle
Akses/Jet Penyedia Microsoft OLE DB untuk Jet Mana pun Microsoft.Jet.OLEDB.4.0 Jalur lengkap file database Jet
Sumber data ODBC Penyedia Microsoft OLE DB untuk ODBC Mana pun MSDASQL DSN sistem sumber data ODBC
Sumber data ODBC Penyedia Microsoft OLE DB untuk ODBC Mana pun MSDASQL string koneksi ODBC
Sistem file Penyedia Microsoft OLE DB untuk Layanan Pengindeksan Mana pun MSIDXS Nama katalog Layanan Pengindeksan
Lembar Bentang Microsoft Excel Penyedia Microsoft OLE DB untuk Jet Mana pun Microsoft.Jet.OLEDB.4.0 Jalur lengkap file Excel Excel 5.0
Database Db2 IBM Microsoft OLE DB Provider untuk DB2 Mana pun DB2OLEDB Lihat dokumentasi Microsoft OLE DB Provider untuk DB2. Nama katalog database DB2

1 Cara menyiapkan server tertaut ini memaksa nama server yang ditautkan agar sama dengan nama jaringan instans jarak jauh SQL Server. Gunakan @datasrc untuk menentukan server.

2 "Apa pun" menunjukkan bahwa nama produk bisa apa saja.

Penyedia SQL Server Native Client OLE DB adalah penyedia yang digunakan dengan SQL Server jika tidak ada nama penyedia yang ditentukan atau jika SQL Server ditentukan sebagai nama produk. Bahkan jika Anda menentukan nama penyedia yang lebih lama, SQLOLEDB, itu berubah menjadi SQLNCLI ketika bertahan pada katalog.

Parameter @datasrc, @location, @provstr, dan @catalog mengidentifikasi database atau database tempat server yang ditautkan. Jika salah satu parameter ini adalah NULL, properti inisialisasi OLE DB yang sesuai tidak diatur.

Di lingkungan berkluster, saat Anda menentukan nama file untuk menunjuk ke sumber data OLE DB, gunakan nama konvensi penamaan universal (UNC) atau drive bersama untuk menentukan lokasi.

Prosedur sp_addlinkedserver tersimpan tidak dapat dijalankan dalam transaksi yang ditentukan pengguna.

Penting

Azure SQL Managed Instance saat ini hanya mendukung SQL Server, SQL Database, dan instans terkelola SQL lainnya sebagai sumber data jarak jauh.

Penting

Saat server tertaut dibuat dengan menggunakan sp_addlinkedserver, pemetaan mandiri default ditambahkan untuk semua login lokal. Untuk penyedia non-SQL Server, login Terautentikasi SQL Server mungkin dapat memperoleh akses ke penyedia di bawah akun layanan SQL Server. Administrator harus mempertimbangkan untuk menggunakan sp_droplinkedsrvlogin <linkedserver_name>, NULL untuk menghapus pemetaan global.

Izin

Pernyataan tersebut sp_addlinkedserver memerlukan ALTER ANY LINKED SERVER izin. (SQL Server Management Studio Kotak dialog Server Tertaut Baru diimplementasikan dengan cara yang memerlukan keanggotaan dalam peran server tetap sysadmin .)

Contoh

J. Menggunakan Penyedia Microsoft SQL Server OLE DB

Contoh berikut membuat server tertaut bernama SEATTLESales. Nama produk adalah SQL Server, dan tidak ada nama penyedia yang digunakan.

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

Contoh berikut membuat server S1_instance1 tertaut pada instans SQL Server dengan menggunakan driver SQL Server OLE DB.

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

Contoh berikut membuat server S1_instance1 tertaut pada instans SQL Server dengan menggunakan penyedia SQL Server Native Client OLE DB.

Penting

Penyedia SQL Server Native Client OLE DB (SQLNCLI) tetap tidak digunakan lagi dan tidak disarankan untuk menggunakannya untuk pekerjaan pengembangan baru. Sebagai gantinya, gunakan Driver Microsoft OLE DB baru untuk SQL Server (MSOLEDBSQL) yang akan diperbarui dengan fitur server terbaru.

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

B. Menggunakan Penyedia Microsoft OLE DB untuk Microsoft Access

Penyedia Microsoft.Jet.OLEDB.4.0 tersambung ke database Microsoft Access yang menggunakan format 2002-2003. Contoh berikut membuat server tertaut bernama SEATTLE Mktg.

Catatan

Contoh ini mengasumsikan bahwa Microsoft Access dan database sampel Northwind diinstal dan database Northwind berada di C:\Msoffice\Access\Samples di server yang sama dengan instans 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. Gunakan Penyedia Microsoft OLE DB untuk ODBC dengan datasrc parameter

Contoh berikut membuat server tertaut bernama SEATTLE Payroll yang menggunakan Penyedia Microsoft OLE DB untuk ODBC (MSDASQL) dan parameter @datasrc .

Catatan

Nama sumber data ODBC yang ditentukan harus didefinisikan sebagai DSN Sistem di server sebelum Anda menggunakan server tertaut.

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

D. Menggunakan Penyedia Microsoft OLE DB untuk lembar bentang Excel

Untuk membuat definisi server tertaut menggunakan Penyedia Microsoft OLE DB untuk Jet untuk mengakses lembar bentang Excel dalam format 1997 - 2003, pertama-tama buat rentang bernama di Excel dengan menentukan kolom dan baris lembar kerja Excel untuk dipilih. Nama rentang kemudian dapat dirujuk sebagai nama tabel dalam kueri terdistribusi.

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

Untuk mengakses data dari lembar bentang Excel, kaitkan rentang sel dengan nama. Kueri berikut ini dapat digunakan untuk mengakses rentang SalesData bernama yang ditentukan sebagai tabel dengan menggunakan server tertaut yang disiapkan sebelumnya.

SELECT *
   FROM ExcelSource...SalesData;
GO

Jika SQL Server berjalan di bawah akun domain yang mengakses berbagi jarak jauh, jalur UNC dapat digunakan alih-alih drive yang dipetakan.

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

E. Menggunakan Penyedia Microsoft OLE DB untuk Jet untuk mengakses file teks

Contoh berikut membuat server tertaut untuk mengakses file teks secara langsung, tanpa menautkan file sebagai tabel dalam file Access .mdb. Penyedia adalah Microsoft.Jet.OLEDB.4.0 dan string penyedia adalah Text.

Sumber data adalah jalur lengkap direktori yang berisi file teks. File schema.ini, yang menjelaskan struktur file teks, harus ada di direktori yang sama dengan file teks. Untuk informasi selengkapnya tentang cara membuat file schema.ini, lihat dokumentasi Mesin Database Jet.

Pertama, buat server tertaut.

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

Siapkan pemetaan login.

EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL;

Cantumkan tabel di server tertaut.

EXEC sp_tables_ex txtsrv;

Kueri salah satu tabel, dalam hal file1#txtini , menggunakan nama empat bagian.

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

F. Menggunakan Microsoft OLE DB Provider untuk DB2

Contoh berikut membuat server tertaut bernama DB2 yang menggunakan Microsoft OLE DB Provider untuk 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. Menambahkan database Azure SQL sebagai server tertaut untuk digunakan dengan kueri terdistribusi di cloud dan database lokal

Anda dapat menambahkan database Azure SQL sebagai server tertaut lalu menggunakannya dengan kueri terdistribusi yang mencakup database lokal dan cloud. Ini adalah komponen untuk solusi hibrid database yang mencakup jaringan perusahaan lokal dan cloud Azure.

Produk kotak SQL Server berisi fitur kueri terdistribusi, yang memungkinkan Anda menulis kueri untuk menggabungkan data dari sumber data lokal dan data dari sumber jarak jauh (termasuk data dari sumber data non-SQL Server) yang didefinisikan sebagai server tertaut. Setiap database Azure SQL (kecuali database server master logis) dapat ditambahkan sebagai server tertaut individual dan kemudian digunakan langsung di aplikasi database Anda sebagai database lainnya.

Manfaat menggunakan Azure SQL Database termasuk pengelolaan, ketersediaan tinggi, skalabilitas, bekerja dengan model pengembangan yang familier, dan model data relasional. Persyaratan aplikasi database Anda menentukan bagaimana aplikasi tersebut akan menggunakan Azure SQL Database di cloud. Anda dapat memindahkan semua data sekaligus ke Azure SQL Database, atau secara progresif memindahkan beberapa data Anda sambil menyimpan data yang tersisa di tempat. Untuk aplikasi database hibrid seperti itu, Azure SQL Database sekarang dapat ditambahkan sebagai server tertaut dan aplikasi database dapat mengeluarkan kueri terdistribusi untuk menggabungkan data dari Azure SQL Database dan sumber data lokal.

Berikut adalah contoh yang menjelaskan cara menyambungkan ke database Azure SQL menggunakan kueri terdistribusi.

Pertama, tambahkan satu database Azure SQL sebagai server tertaut, menggunakan menggunakan SQL Server Native Client.

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

Tambahkan kredensial dan opsi ke server tertaut ini.

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

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

Sekarang, gunakan server tertaut untuk menjalankan kueri menggunakan nama empat bagian, bahkan untuk membuat tabel baru dan menyisipkan data.

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;

Kueri data menggunakan nama empat bagian:

SELECT * FROM LinkedServerName.DatabaseName.SchemaName.TableName;

H. Membuat server tertaut Azure SQL Managed Instance dengan autentikasi identitas terkelola

Catatan

ID Microsoft Entra sebelumnya dikenal sebagai Azure Active Directory (Azure AD).

Untuk membuat server tertaut dengan autentikasi identitas terkelola, jalankan T-SQL berikut, ganti <managed_instance> dengan instans terkelola SQL Anda sendiri. Metode autentikasi menggunakan ActiveDirectoryMSI dalam parameter @provstr . Pertimbangkan untuk menggunakan @locallogin = NULL secara opsional untuk mengizinkan semua login lokal.

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

Untuk mengaktifkan autentikasi dengan identitas terkelola, identitas terkelola yang ditetapkan ke Azure SQL Managed Instance perlu ditambahkan sebagai login ke instans terkelola jarak jauh. Baik identitas terkelola yang ditetapkan sistem dan yang ditetapkan pengguna didukung.

Jika identitas utama diatur, identitas tersebut digunakan, jika tidak, identitas terkelola yang ditetapkan sistem akan digunakan. Jika identitas terkelola dibuat ulang dengan nama yang sama, login pada instans jarak jauh juga perlu dibuat ulang, karena ID Aplikasi identitas terkelola baru dan SQL Managed Instance perwakilan layanan SID tidak lagi cocok. Untuk memverifikasi kecocokan kedua nilai ini, konversikan SID ke ID aplikasi dengan kueri berikut.

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

I. Membuat server tertaut SQL Managed Instance dengan autentikasi Microsoft Entra pass-through

Untuk membuat server tertaut dengan autentikasi pass-through, jalankan T-SQL berikut, ganti <managed_instance> dengan server instans terkelola SQL Anda sendiri:

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

Dengan autentikasi pass-through, konteks keamanan login lokal dibawa ke instans jarak jauh. Autentikasi pass-through mengharuskan perwakilan Microsoft Entra ditambahkan sebagai login di Azure SQL Managed Instance lokal dan jarak jauh. Kedua instans terkelola harus berada dalam Grup Kepercayaan Server. Saat persyaratan terpenuhi, pengguna dapat masuk ke instans lokal dan mengkueri instans jarak jauh melalui objek server yang ditautkan.

Konten terkait