sp_addlinkedserver (Transact-SQL)

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

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

Topic link iconTransact-SQL 構文表記規則

構文

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

引数

[ @server = ] 'server'

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

[ @srvproduct = ] 'product_name'

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

[ @provider = ] 'provider_name'

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

Note

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 は、OLE DB プロバイダーを初期化するためのDBPROP_INIT_DATASOURCE プロパティとして渡されます。

[ @location = ] 'location'

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

[ @provstr = ] 'provider_string'

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

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

注意

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

[ @catalog = ] 'catalog'

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

リターン コードの値

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

結果セット

[なし] :

解説

次の表は、OLE DB を介してアクセスできるデータ ソースに対してリンク サーバーを設定する方法を示しています。 リンク サーバーは、特定のデータ ソースに対して複数の方法で設定できます。1 つのデータ ソース型に対して複数の行を指定できます。 次の表は、リンク サーバーの 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 Server 1 (既定値)
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 Indexing Service カタログ名
Microsoft Excel スプレッドシート Microsoft OLE DB Provider for Jet Any Microsoft.Jet.OLEDB.4.0 Excel ファイルのフル パス Excel 5.0
IBM DB2 データベース Microsoft OLE DB Provider for DB2 Any DB2OLEDB Microsoft OLE DB Provider for DB2ドキュメントを参照してください。 DB2 データベースのカタログ名

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

2 "Any" は、製品名が任意であることを示します。

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

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

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

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

重要

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

重要

リンク サーバーを使用して 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  

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

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

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

重要

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

注意

この例では、Microsoft Access とサンプル 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 を使用する

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

注意

リンク サーバーを使用する前には、指定した 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 スプレッドシートを使用する

Microsoft OLE DB Provider for Jet を使用して 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 部構成の名前を使用して、テーブルの 1 つ (この場合 file1#txt) にクエリを実行します。

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

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

次の例では、.. を使用Microsoft OLE DB Provider for DB2するリンク 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 Database をリンク サーバーとして追加し、オンプレミスデータベースとクラウド データベースにまたがる分散クエリで使用できます。 これは、オンプレミスの企業ネットワークと Azure クラウドにまたがるデータベース ハイブリッド ソリューションのコンポーネントです。

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

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

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

最初に、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 を実行します。 認証方法は、パラメーターで@provstr使用ActiveDirectoryMSIします。 必要に応じて、すべてのローカル ログインを許可するために使用 @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 アプリケーション ID とサービス プリンシパル SID が一致しなくなったため、リモート インスタンスでのログインも再作成 Managed Instanceする必要があります。 これら 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 を実行します。

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

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

関連項目