sp_addlinkedserver (Transact-SQL)

適用対象: はいSQL Server (サポートされているすべてのバージョン) はいAzure SQL Managed Instance

リンク サーバーを作成します。 リンク サーバーを使用すると、OLE DB データ ソースに対する異種の分散クエリの利用が可能になります。 を使用してリンク サーバーを作成 sp_addlinkedserver した後、このサーバーに対して分散クエリを実行できます。 リンク サーバーを SQL Serverインスタンスとして定義した場合は、リモート ストアド プロシージャを実行できます。

トピック リンク アイコン Transact-SQL 構文表記規則

構文

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]   
     [ , [ @provider= ] 'provider_name' ]  
     [ , [ @datasrc= ] 'data_source' ]   
     [ , [ @location= ] 'location' ]   
     [ , [ @provstr= ] 'provider_string' ]   
     [ , [ @catalog= ] 'catalog' ]   

引数

[ @server = ]' サーバー '

作成するリンク サーバーの名前を指定します。 引数サーバー sysname で、 既定値はありません。

[ @srvproduct = ] ' product_name '

リンク サーバーとして追加するOLE DBソースの製品名を指定します。 この値 product_name nvarchar(128) で、既定値は NULL です。 値がの場合 SQL Server、provider_name、data_source、provider_string、 および カタログ を指定する必要があります。

[ @provider = ] ' provider_name '

このデータ ソースに対応する OLE DB プロバイダーの一意なプログラム識別子 (PROGID) を指定します。 指定 provider_name、 現在のコンピューターにインストールされている指定OLE DBプロバイダーに対して一意である必要があります。 この値 provider_name nvarchar(128) で、既定値は NULL です。ただし、この provider_name 場合は、SQLNCLI が使用されます。

注意

SQLNCLI を使用すると、最新バージョンの SQL Server SQL Server Native Client OLE DBされます。 OLE DB プロバイダーは、指定の PROGID を使用してレジストリに登録されることが想定されています。

重要

以前の Microsoft OLE DB Provider for SQL Server (SQLOLEDB) と SQL Server Native Client OLE DB プロバイダー (SQLNCLI) は非推奨のままであり、新しい開発作業にはどちらの使用もお勧めできません。 代わりに、新しい Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) を使用します。これは、最新のサーバー機能で更新されます。

[ @datasrc = ] ' data_source '

データ プロバイダーによって解釈されるデータ ソースのOLE DBします。 この値 data_source nvarchar( 4000 ) ですdata_source プロバイダーを初期化するために、DBPROP_INIT_DATASOURCE プロパティとしてOLE DBされます。

[ @location = ]' 場所 '

OLE DB プロバイダーで認識されるデータベースの場所を指定します。 値の 場所は nvarchar( 4000 ) で、既定値は NULL です。 引数の 場所は 、プロバイダーを初期化DBPROP_INIT_LOCATIONプロパティとしてOLE DBされます。

[ @provstr = ] ' provider_string '

一意なデータ ソースを識別する、OLE DB プロバイダー固有の接続文字列を指定します。 この値 provider_string nvarchar( 4000 ) で、既定値は NULL です。 引数 provstr は 、IDataInitialize に渡されるか、DBPROP_INIT_PROVIDERSTRING プロパティとして設定して、OLE DBされます。

Native Client OLE DB プロバイダーに対してリンク サーバーを作成する場合は、 として SERVER キーワードを使用して インスタンスを指定し、 の特定のインスタンス SQL Server SERVER=servername\\instancename を指定できます SQL Server 。 servername 実行中のコンピューターの名前、instancename はユーザーが接続される の特定のインスタンスの SQL Server SQL Server 名前です。

注意

ミラー化されたデータベースにアクセスするには、接続文字列にデータベース名が含まれている必要があります。 この名前は、データ アクセス プロバイダーがフェールオーバーを試行できるようにするために必要です。 データベースは @ 、provstr または catalog パラメーターで @ 指定 できます。 必要に応じて、接続文字列でフェールオーバー パートナー名を指定できます。

[ @catalog = ]' カタログ '

プロバイダーへの接続が確立された場合に使用するカタログOLE DBします。 値カタログ sysname で、 既定値は NULL です。 引数カタログ 、 プロパティとして渡DBPROP_INIT_CATALOGプロバイダーを初期化OLE DBされます。 のインスタンスに対してリンク サーバーが定義されている場合、カタログは、リンク サーバーがマップされる既定 SQL Server のデータベースを参照します。

リターン コードの値

0 (成功) または 1 (失敗)

結果セット

[なし] :

解説

次の表は、リンク サーバーを使用してアクセスできるデータ ソースに対してリンク サーバーを設定する方法をOLE DB。 リンク サーバーは、特定のデータ ソースに対して複数の方法で設定できます。データ ソースの種類に対して複数の行を指定できます。 この表には、リンク sp_addlinkedserver サーバーの設定に使用するパラメーター値も示します。

リモート OLE DB データ ソース OLE DB プロバイダー product_name provider_name data_source location provider_string catalog
SQL Server Microsoft SQL Server Native Client OLE DB プロバイダー SQL Server1 (既定値)
SQL Server Microsoft SQL Server Native Client OLE DB プロバイダー SQLNCLI のネットワーク名 SQL Server (既定のインスタンスの場合) データベース名 (省略可能)
SQL Server Microsoft SQL Server Native Client OLE DB プロバイダー SQLNCLI Servername \instancename (特定のインスタンスの場合) データベース名 (省略可能)
Oracle、バージョン 8 以降 Oracle Provider for OLE DB Any OraOLEDB.Oracle Oracle データベースに対する別名
Access/Jet Microsoft OLE DB Provider for Jet Any Microsoft.Jet.OLEDB.4.0 Jet データベース ファイルの完全パス
ODBC データ ソース (ODBC data source) Microsoft OLE DB Provider for ODBC Any MSDASQL ODBC データ ソースのシステム DSN
ODBC データ ソース (ODBC data source) Microsoft OLE DB Provider for ODBC Any MSDASQL ODBC 接続文字列
ファイル システム Microsoft OLE DB Provider for Indexing Service Any MSIDXS インデックス作成サービス カタログ名
Microsoft Excel ワークシート Microsoft OLE DB Provider for Jet Any Microsoft.Jet.OLEDB.4.0 Excel ファイルのフル パス Excel 5.0
IBM DB2 データベース Microsoft DB2 の OLE DB Provider Any DB2OLEDB MicrosoftOLE DB Provider FOR DB2 のドキュメントを参照してください。 DB2 データベースのカタログ名

1 リンクサーバーを設定するこの方法では、リンクサーバーの名前がのリモートインスタンスのネットワーク名と同じになり SQL Server ます。 Data_source を使用してサーバーを指定します。

2 "任意" は、製品名を任意の名前にすることを示します。

Microsoft SQL Server Native Client OLE DB プロバイダーは、 SQL Server プロバイダー名が指定されていない場合、または SQL Server が製品名として指定されている場合に、で使用されるプロバイダーです。 以前のプロバイダー名を指定した場合でも、SQLOLEDB は、カタログに永続化するときに SQLNCLI に変更されます。

Data_sourcelocationprovider_string、および catalog パラメーターは、リンクサーバーが指すデータベースを識別します。 これらのいずれかのパラメーターが NULL に設定されると、対応する OLE DB 初期化プロパティは設定されません。

クラスター環境では OLE DB データソースを指すようにファイル名を指定する場合は、汎用名前付け規則名 (UNC) または共有ドライブを使用して場所を指定します。

ストアドプロシージャは、 sp_addlinkedserver ユーザー定義のトランザクション内では実行できません。

重要

現在、Azure SQL Managed Instance では、リモートデータソースとして SQL Server、SQL Database、およびその他の SQL のみがサポートされています。

重要

を使用してリンクサーバーを作成すると sp_addlinkedserver 、すべてのローカルログインに対して既定の自己マッピングが追加されます。 以外のプロバイダーの場合 SQL Server 、認証されたログインは、 SQL Server サービスアカウントでプロバイダーにアクセスできる可能性があり SQL Server ます。 管理者は、を使用してグローバルマッピングを削除することを検討する必要があり sp_droplinkedsrvlogin <linkedserver_name>, NULL ます。

アクセス許可

ステートメントには sp_addlinkedserverALTER ANY LINKED SERVER 権限が必要です。 SQL Server Management Studio([新しいリンクサーバー ] ダイアログボックスは、固定サーバーロールのメンバーシップを必要とする方法で実装され sysadmin ます。)

A. Microsoft SQL Server OLE DB プロバイダーの使用

次の例では、SEATTLESales というリンク サーバーを作成します。 製品名は SQL Server で、プロバイダー名は使用されません。

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

次の例では、 S1_instance1 OLE DB ドライバーを使用して、のインスタンスにリンクサーバーを作成し SQL Server SQL Server ます。

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

次の例では、 S1_instance1 SQL Server Native Client OLE DB プロバイダーを使用して、のインスタンスにリンクサーバーを作成し SQL Server ます。

重要

SQL Server Native Client OLE DB プロバイダー (SQLNCLI) は非推奨のままであり、新しい開発作業での使用はお勧めできません。 代わりに、新しい Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) を使用します。これは、最新のサーバー機能で更新されます。

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

B. Microsoft OLE DB Provider for Microsoft Access を使用する

Microsoft Jet OLEDB プロバイダーは、2002-2003 形式を使用する Microsoft Access データベースに接続します。 次の例では、SEATTLE Mktg というリンク サーバーを作成します。

注意

この例では、 Microsoft アクセスとサンプルデータベースの両方がインストールされていること Northwind と、 Northwind データベースが SQL Server インスタンスと同じサーバー上の C:\Msoffice\Access\Samples に存在することを前提としています。

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. Data_source パラメーターを指定して Microsoft OLE DB Provider for ODBC を使用する

次の例では、 SEATTLE Payroll Microsoft OLE DB PROVIDER for ODBC ( MSDASQL ) および data_source パラメーターを使用するという名前のリンクサーバーを作成します。

注意

リンク サーバーを使用する前には、指定した ODBC データ ソース名をサーバーのシステム DSN として定義する必要があります。

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

D. Microsoft OLE DB Provider for Excel スプレッドシートを使用する

Jet の OLE DB Provider を使用して Microsoft 1997-2003 形式の Excel スプレッドシートにアクセスするリンクサーバー定義を作成するには、まず、選択する Excel ワークシートの列と行を指定して Excel に名前付き範囲を作成します。 こうすると、分散クエリで範囲の名前をテーブル名として参照できるようになります。

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

Excel スプレッドシートのデータにアクセスするには、セルの範囲を名前に関連付けます。 先に設定したリンク サーバーを使って、テーブルとして指定されている名前付き範囲 SalesData にアクセスするときには、次のクエリを使用できます。

SELECT *  
   FROM ExcelSource...SalesData;  
GO  

SQL Serverがリモート共有へのアクセス権を持つドメインアカウントで実行されている場合は、マップされたドライブの代わりに UNC パスを使用できます。

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

E. Microsoft OLE DB Provider for Jet を使用してテキストファイルにアクセスする

次の例では、Access .mdb ファイル内のテーブルとしてテキスト ファイルにリンクするのではなく、直接テキスト ファイルにアクセスするリンク サーバーを作成します。 プロバイダーはで、 Microsoft.Jet.OLEDB.4.0 プロバイダーの文字列は Text です。

データソースは、テキストファイルが格納されているディレクトリの完全なパスです。 テキストファイルの構造を記述する schema.ini ファイルは、テキストファイルと同じディレクトリに存在する必要があります。 schema.ini ファイルの作成方法の詳細については、Jet データベースエンジンのドキュメントを参照してください。

まず、リンクサーバーを作成します。

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

ログインマッピングを設定します。

EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL;  

リンクサーバー内のテーブルを一覧表示します。

EXEC sp_tables_ex txtsrv;  

4つの部分で構成される名前を使用して、いずれかのテーブルに対してクエリを実行し file1#txt ます。

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

F. Microsoft OLE DB Provider for DB2 を使用する

次の例では、を使用するという名前のリンクサーバーを作成し DB2 Microsoft OLE DB Provider for 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. Azure SQL データベースクラウドとオンプレミスのデータベースで分散クエリを使用するために、をリンクサーバーとして追加する

Azure SQL データベース をリンク サーバーとして追加し、内部設置型データベースとクラウド データベースにまたがる分散クエリでそのサーバーを使用することができます。 これは、オンプレミスの企業ネットワークと Azure クラウドにまたがるデータベースハイブリッドソリューションのコンポーネントです。

Box 製品には、 SQL Server 分散クエリ機能が含まれています。この機能を使用すると、 SQL Server リンクサーバーとして定義されているリモートソース (データ以外のソースからのデータを含む) からローカルデータソースとデータのデータを結合するクエリを作成できます。 Azure SQL データベース(論理サーバーのデータベースを除く master ) ごとに個別のリンクサーバーとして追加し、データベースアプリケーションで他のデータベースとして直接使用することができます。

を使用する利点としては、 Azure SQL データベース 管理性、高可用性、スケーラビリティ、使い慣れた開発モデルの使用、リレーショナルデータモデルなどがあります。 データベースアプリケーションの要件によって、クラウドでの使用方法が決まり Azure SQL データベース ます。 すべてのデータを一度に移動するか、データの一部を段階的に移動し Azure SQL データベース ながら、残りのデータをオンプレミスに維持することができます。 このようなハイブリッドデータベースアプリケーションでは、を Azure SQL データベース リンクサーバーとして追加できるようになりました。また、データベースアプリケーションは分散クエリを発行して、 Azure SQL データベース オンプレミスのデータソースとデータを結合することができます。

分散クエリを使用してに接続する方法を説明する簡単な例を次に示し Azure SQL データベース ます。

まず、using SQL Server Native Client を使用して、1つの Azure SQL Database をリンクサーバーとして追加します。

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

このリンクサーバーに資格情報とオプションを追加します。

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

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

次に、リンクサーバーを使用して、4部構成の名前を使用してクエリを実行します。新しいテーブルを作成し、データを挿入する場合でも同様です。

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; 

4つの部分で構成される名前を使用してデータを照会します。

SELECT * FROM LinkedServerName.DatabaseName.SchemaName.TableName; 

H. マネージド ID Azure AD 認証を使用した SQL Managed Instance リンク サーバーの作成

マネージ id 認証を使用してリンクサーバーを作成するには、次の T SQL を実行します。 認証方法は、 ActiveDirectoryMSI パラメーターでを使用し @provstr ます。 任意でを使用して @locallogin = NULL すべてのローカルログインを許可することを検討してください。

EXEC master.dbo.sp_addlinkedserver
@server     = N'MyLinkedServer',
@srvproduct = N'',
@provider   = N'MSOLEDBSQL',
@provstr    = N'Server=mi.35e5bd1a0e9b.database.windows.net,1433;Authentication=ActiveDirectoryMSI;';

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

Azure SQL Managed Instance 管理対象 id (旧称はマネージドサービス id) がリモートマネージインスタンスにログインとして追加されている場合、前の例のようにリンクサーバーを使用して管理対象 id 認証を行うことができます。 システム割り当てとユーザー割り当てのマネージド id の両方がサポートされています。

プライマリ id が設定されている場合はそれが使用されます。それ以外の場合は、システムによって割り当てられたマネージド id が使用されます。 マネージ id が同じ名前で再作成された場合は、新しいマネージ id アプリケーション ID と Managed Instance サービスプリンシパル SID が一致しなくなったため、リモートインスタンスでのログインも再作成する必要があります。 これら2つの値が一致することを確認するには、次のクエリを使用して SID をアプリケーション ID に変換します。

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

I. パススルー Azure AD 認証を使用した SQL Managed Instance リンク サーバーの作成

パススルー認証を使用してリンクサーバーを作成するには、次の T-sql SQL を実行します。

EXEC master.dbo.sp_addlinkedserver
@server     = N'MyLinkedServer',
@srvproduct = N'',
@provider   = N'MSOLEDBSQL',
@datasrc    = N'Server=mi.35e5bd1a0e9b.database.windows.net,1433';

パススルー認証では、ローカルログインのセキュリティコンテキストがリモートインスタンスに引き継がれます。 パススルー認証では、ローカルとリモートの両方の Azure SQL Managed Instance で、AAD プリンシパルをログインとして追加する必要があります。 どちらのマネージインスタンスも、 サーバー信頼グループに存在する必要があります。 要件が満たされると、ユーザーはローカルインスタンスにサインインして、リンクサーバーオブジェクトを介してリモートインスタンスに対してクエリを実行できます。

こちらもご覧ください