sp_addlinkedserver (Transact-SQL)

適用対象: SQL Server Azure SQL Managed Instance

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

Transact-SQL 構文表記規則

構文

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

引数

[ @server = ] N'server'

作成するリンク サーバーの名前。 @server は sysname で、既定値はありません。

[ @srvproduct = ] N'srvproduct'

リンク サーバーとして追加する OLE DB データ ソースの製品名。 @srvproduct は nvarchar(128) で、既定値は NULL です。 値が SQL Server、 @provider、 @datasrc、 @location、 @provstr、および @catalog を指定する必要はありません。

[ @provider = ] N'provider'

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

• SQL Server 2019 (15.x) 以前のバージョンでは、 @provider を省略すると、 SQLNCLI が使用されます。 SQLNCLIを使用すると、SQL Server が最新バージョンの SQL Server Native Client OLE DB プロバイダーにリダイレクトされます。 OLE DB プロバイダーは、指定の PROGID を使用してレジストリに登録されることが想定されています。 SQLNCLIではなく、MSOLEDBSQLをお勧めします。

• SQL Server 2022 (16.x) 以降では、プロバイダー名を指定する必要があります。 MSOLEDBSQL が推奨されます。 @providerを省略すると、予期しない動作が発生する可能性があります。

[ @datasrc = ] N'datasrc'

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

[ @location = ] N'location'

OLE DB プロバイダーによって解釈されるデータベースの場所。 @location は nvarchar(4000) で、既定値は NULL です。 @location は、OLE DB プロバイダーを初期化するための DBPROP_INIT_LOCATION プロパティとして渡されます。

[ @provstr = ] N'provstr'

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

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

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

• ローカル ログインまたは sysadmin ロールの一部ではないログインからsp_addlinkedserverを実行すると、次のエラーが表示されることがあります。

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

  この問題を解決するには、接続文字列に User ID パラメーターを追加します。 次の例では、myUserは接続文字列に渡されるユーザー ID です。

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

  詳細については、「 ログイン マッピングが存在しないため、リモート サーバーへのアクセスが拒否されるを参照してください。

[ @catalog = ] N'catalog'

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

[ @linkedstyle = ] linkedstyle

単に情報を示すためだけに特定されます。 サポートされていません。 将来の互換性は保証されません。

リターン コードの値

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

結果セット

ありません。

解説

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

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

² "Any" は、製品名が何でもできることを示します。

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

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

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

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

アクセス許可

sp_addlinkedserver ステートメントには、ALTER 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 を作成します。

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 というリンク サーバーを作成します。

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: datasrc パラメーターで Microsoft OLE DB Provider for ODBC を使用する

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

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

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

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

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

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

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 SQL Managed Instance のリンク サーバーを作成する

マネージド ID 認証を使用してリンク サーバーを作成するには、次の T-SQL を実行し、 <managed_instance> を独自の SQL マネージド インスタンスに置き換えます。 認証方法では、@provstr パラメーターでActiveDirectoryMSIを使用します。 必要に応じて、 @locallogin = NULL を使用してすべてのローカル ログインを許可することを検討してください。

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

マネージド ID による認証を有効にするには、Azure SQL Managed Instance に割り当てられたマネージド ID を、リモート マネージド インスタンスへのログインとして追加する必要があります。 システム割り当てとユーザー割り当ての両方のマネージド ID がサポートされています。

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

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

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

パススルー認証を使用してリンク サーバーを作成するには、次の T-SQL を実行し、 <managed_instance> を独自の SQL マネージド インスタンス サーバーに置き換えます。

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

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

関連するコンテンツ

• 分散クエリ ストアド プロシージャ (Transact-SQL)
• sp_addlinkedsrvlogin (Transact-SQL)
• sp_addserver (Transact-SQL)
• sp_dropserver (Transact-SQL)
• sp_serveroption (Transact-SQL)
• sp_setnetname (Transact-SQL)
• システム ストアド プロシージャ (Transact-SQL)
• システム テーブル (Transact-SQL)