sp_addlinkedserver (Transact-SQL)

[アーティクル]
03/03/2023

適用対象: 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 = ] '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)です。

SQL Server 2022 (16.x) より前のバージョン@providerでは、を省略すると、SQLNCLI が使用されます。 SQLNCLI を使用すると、SQL Server が最新バージョンの SQL Server Native Client OLE DB プロバイダーにリダイレクトされます。 OLE DB プロバイダーは、指定の PROGID を使用してレジストリに登録されることが想定されています。 SQLNCLI の代わりに、MSOLEDBSQL をお勧めします。
SQL Server 2022 (16.x) 以降では、プロバイダー名を指定する必要があります。 MSOLEDBSQL をお勧めします。

重要

SQL Server Native Client (SNAC と略されることがよくあります) は、SQL Server 2022 (16.x) と SQL Server Management Studio 19 (SSMS) から削除されました。 SQL Server Native Client OLE DB プロバイダー (SQLNCLI または SQLNCLI11) とレガシ Microsoft OLE DB Provider for SQL Server (SQLOLEDB) はどちらも、新しい開発には推奨されません。今後は、新しい Microsoft OLE DB Driver (MSOLEDBSQL) for SQL Server に切り替えてください。

[ @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の特定のインスタンスを指定できます。 servername は、SQL Serverが実行されているコンピューターの名前であり、instancename はユーザーが接続するSQL Serverの特定のインスタンスの名前です。

注意

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

[ @catalog = ] 'catalog'

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

リターンコードの値

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

結果セット

[なし] :

解説

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

リモート OLE DB データソース	OLE DB プロバイダー	product_name	provider_name	data_source	provider_string	catalog
SQL Server	Microsoft SQL Server Native Client OLE DB プロバイダー	SQL Server ¹ (既定値)
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 OLE DB Provider for DB2	Any	DB2OLEDB		Microsoft OLE DB Provider for DB2ドキュメントを参照してください。	DB2 データベースのカタログ名

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

² "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_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を作成します。

重要

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 部構成の名前を使用して、いずれかのテーブル (この場合 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 Database をリンクサーバーとして追加し、オンプレミスデータベースとクラウドデータベースにまたがる分散クエリで使用できます。これは、オンプレミスの企業ネットワークと 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 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'mi.35e5bd1a0e9b.database.windows.net,1433';

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

sp_addlinkedserver (Transact-SQL)

構文

引数

[ @server = ] 'server'

[ @srvproduct = ] 'product_name'

[ @provider = ] 'provider_name'

[ @datasrc = ] 'data_source'

[ @location = ] 'location'

[ @provstr = ] 'provider_string'

[ @catalog = ] 'catalog'

リターンコードの値

結果セット

解説

アクセス許可

例

A. Microsoft SQL Server OLE DB プロバイダーを使用する

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

C. data_source パラメーターで Microsoft OLE DB Provider for ODBC を使用する

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

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

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

G. クラウドおよびオンプレミスデータベースの分散クエリで使用するリンクサーバーとしてAzure SQL データベースを追加する

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

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

関連項目

その他のリソース

その他のリソース

sp_addlinkedserver (Transact-SQL)

構文

引数

[ @server = ] 'server'

[ @srvproduct = ] 'product_name'

[ @provider = ] 'provider_name'

[ @datasrc = ] 'data_source'

[ @location = ] 'location'

[ @provstr = ] 'provider_string'

[ @catalog = ] 'catalog'

リターン コードの値

結果セット

解説

アクセス許可

例

A. Microsoft SQL Server OLE DB プロバイダーを使用する

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

C. data_source パラメーターで Microsoft OLE DB Provider for ODBC を使用する

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

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

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

G. クラウドおよびオンプレミス データベースの分散クエリで使用するリンク サーバーとしてAzure SQL データベースを追加する

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

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

関連項目

その他のリソース

その他のリソース

リターンコードの値

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

G. クラウドおよびオンプレミスデータベースの分散クエリで使用するリンクサーバーとしてAzure SQL データベースを追加する

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

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