sp_addlinkedserver(Transact-SQL)

적용 대상:SQL ServerAzure SQL Managed Instance

연결된 서버를 만듭니다. 연결된 서버는 OLE DB 데이터 원본에 대해 다른 유형의 분산 쿼리에 대한 액세스를 제공합니다. 연결된 서버를 사용하여 sp_addlinkedserver만든 후에는 이 서버에 대해 분산 쿼리를 실행할 수 있습니다. 연결된 서버가 SQL Server의 인스턴스로 정의된 경우 원격 저장 프로시저를 실행할 수 있습니다.

참고 항목

Microsoft Entra ID 는 이전에 Azure AD(Azure Active Directory)라고 했습니다.

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 기본값NULL인 nvarchar(128)입니다. 값이 SQL Server, @provider, @datasrc, @location, @provstr 및 @catalog 지정할 필요가 없습니다.

[ @provider = ] N'provider'

이 데이터 원본에 해당하는 OLE DB 공급자의 고유한 PROGID(프로그래밍 식별자)입니다. @provider 현재 컴퓨터에 설치된 지정된 OLE DB 공급자에 대해 고유해야 합니다. @provider 기본값NULL인 nvarchar(128)입니다.

• 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 생략하면 예기치 않은 동작이 발생할 수 있습니다.

Important

SQL Server Native Client(약칭 SNAC)는 SQL Server 2022(16.x) 및 SSMS(SQL Server Management Studio) 19에서 제거되었습니다. SQL Server Native Client OLE DB 공급자(SQLNCLI 또는 SQLNCLI11)와 레거시 Microsoft OLE DB Provider for SQL Server(SQLOLEDB)는 모두 새로운 개발에 권장되지 않습니다. 앞으로 SQL Server용 새 Microsoft OLE DB 드라이버(MSOLEDBSQL)로 전환합니다.

[ @datasrc = ] N'datasrc'

OLE DB 공급자가 해석한 데이터 원본의 이름입니다. @datasrc 기본값NULL인 nvarchar(4000)입니다. @datasrc OLE DB 공급자를 DBPROP_INIT_DATASOURCE 초기화하는 속성으로 전달됩니다.

[ @location = ] N'location'

OLE DB 공급자가 해석한 데이터베이스의 위치입니다. @location 기본값NULL인 nvarchar(4000)입니다. @location OLE DB 공급자를 DBPROP_INIT_LOCATION 초기화하는 속성으로 전달됩니다.

[ @provstr = ] N'provstr'

고유한 데이터 원본을 식별하는 OLE DB 공급자별 연결 문자열. @provstr 기본값NULL인 nvarchar(4000)입니다. 인수 provstr 는 IDataInitialize에 전달되거나 OLE DB 공급자를 초기화하는 속성으로 DBPROP_INIT_PROVIDERSTRING 설정됩니다.

SQL Server Native Client OLE DB 공급자에 대해 연결된 서버를 만들 때 키워드(keyword) SERVER=servername\instancename 사용하여 SERVER SQL Server의 특정 인스턴스를 지정하여 인스턴스를 지정할 수 있습니다. 서버 이름은 SQL Server가 실행 중인 컴퓨터의 이름이며 인스턴스 이름은 사용자가 연결될 SQL Server의 특정 인스턴스 이름입니다.

• 미러 데이터베이스에 액세스하려면 연결 문자열 데이터베이스 이름을 포함해야 합니다. 이 이름은 데이터 액세스 공급자의 장애 조치(Failover) 시도를 지원하는 데 필요합니다. @provstr 또는 @catalog 매개 변수에서 데이터베이스를 지정할 수 있습니다. 필요에 따라 연결 문자열 장애 조치(failover) 파트너 이름을 제공할 수도 있습니다.

• 로컬 로그인 또는 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 .

원격 OLE DB 데이터 원본	OLE DB 공급자	@srvproduct	@provider	@datasrc	@location	@provstr	@catalog
SQL Server	SQL Server Native Client OLE DB 공급자	SQL Server 1 (기본값)
SQL Server	SQL Server Native Client OLE DB 공급자		SQLNCLI	SQL Server의 네트워크 이름(기본 인스턴스의 경우)			데이터베이스 이름(선택 사항)
SQL Server	SQL Server Native Client OLE DB 공급자		SQLNCLI	서버 이름\인스턴스 이름 (특정 인스턴스의 경우)			데이터베이스 이름(선택 사항)
Oracle, 버전 8 이상	OLE DB용 Oracle 공급자	모두	OraOLEDB.Oracle	Oracle 데이터베이스의 별칭
Access/Jet	Microsoft OLE DB Provider for Jet	모두	Microsoft.Jet.OLEDB.4.0	Jet 데이터베이스 파일의 전체 경로
ODBC 데이터 원본	Microsoft OLE DB Provider for ODBC	모두	MSDASQL	ODBC 데이터 원본의 시스템 DSN
ODBC 데이터 원본	Microsoft OLE DB Provider for ODBC	모두	MSDASQL			ODBC 연결 문자열
파일 시스템	Microsoft OLE DB Provider for Indexing Service	모두	MSIDXS	인덱싱 서비스 카탈로그 이름
Microsoft Excel 스프레드시트	Microsoft OLE DB Provider for Jet	모두	Microsoft.Jet.OLEDB.4.0	Excel 파일의 전체 경로		Excel 5.0
IBM DB2 데이터베이스	Microsoft OLE DB Provider for DB2	모두	DB2OLEDB			Microsoft OLE DB Provider for DB2 설명서를 참조하세요.	DB2 데이터베이스의 카탈로그 이름

¹ 연결된 서버를 설정하는 이 방법으로 연결된 서버의 이름은 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 는 사용자 정의 트랜잭션 내에서 실행할 수 없습니다.

Important

Azure SQL Managed Instance는 현재 SQL Server, SQL Database 및 기타 SQL 관리되는 인스턴스만 원격 데이터 원본으로 지원합니다.

Important

연결된 서버를 사용하여 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 만듭니다.

Important

SQLNCLI(SQL Server Native Client OLE DB 공급자)는 더 이상 사용되지 기본 새 개발 작업에는 사용하지 않는 것이 좋습니다. 대신 최신 서버 기능으로 업데이트되는 새로운 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 Access용 Microsoft OLE DB 공급자 사용

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. 매개 변수와 함께 datasrc Microsoft OLE DB Provider for ODBC 사용

다음 예제에서는 Microsoft OLE DB Provider for ODBC(MSDASQL) 및 @datasrc 매개 변수를 사용하는 연결된 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가 원격 공유에 액세스하는 do기본 계정으로 실행되는 경우 매핑된 드라이브 대신 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;

이 경우 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 데이터 원본의 데이터 포함)의 데이터와 로컬 데이터 원본의 데이터를 결합하는 쿼리를 작성할 수 있는 분산 쿼리 기능이 포함되어 있습니다. 논리 서버의 master 데이터베이스를 제외한 모든 Azure SQL 데이터베이스를 개별 연결된 서버로 추가한 다음 데이터베이스 애플리케이션에서 다른 데이터베이스로 직접 사용할 수 있습니다.

Azure SQL Database 사용의 이점에는 관리 효율성, 고가용성, 확장성, 친숙한 개발 모델 작업 및 관계형 데이터 모델이 있습니다. 데이터베이스 애플리케이션의 요구 사항에 따라 클라우드에서 Azure SQL Database를 사용하는 방법이 결정됩니다. 모든 데이터를 한 번에 Azure SQL Database로 이동하거나, 데이터를 온-프레미스에 다시 기본 유지하면서 일부 데이터를 점진적으로 이동할 수 있습니다. 이러한 하이브리드 데이터베이스 애플리케이션의 경우 이제 Azure SQL Database를 연결된 서버로 추가할 수 있으며 데이터베이스 애플리케이션은 분산 쿼리를 실행하여 Azure SQL Database 및 온-프레미스 데이터 원본의 데이터를 결합할 수 있습니다.

다음은 분산 쿼리를 사용하여 Azure SQL 데이터베이스에 연결하는 방법을 설명하는 예제입니다.

먼저 SQL Server Native Client를 사용하여 하나의 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;

이제 연결된 서버를 사용하여 네 부분으로 구성된 이름을 사용하여 쿼리를 실행하고 새 테이블을 만들고 데이터를 삽입합니다.

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;

네 부분으로 구성된 이름을 사용하여 데이터를 쿼리합니다.

SELECT * FROM LinkedServerName.DatabaseName.SchemaName.TableName;

H. 관리 ID 인증을 사용하여 Azure SQL Managed Instance 연결된 서버 만들기

참고 항목

Microsoft Entra ID 는 이전에 Azure AD(Azure Active Directory)라고 했습니다.

관리 ID 인증을 사용하여 연결된 서버를 만들려면 다음 T-SQL을 실행하여 사용자 고유의 SQL 관리형 인스턴스로 대체 <managed_instance> 합니다. 인증 방법은 @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 애플리케이션 ID 및 SQL Managed Instance 서비스 주체 SID가 더 이상 일치하지 않으므로 원격 인스턴스에 대한 로그인도 다시 만들어야 합니다. 이러한 두 값이 일치하는지 확인하려면 다음 쿼리를 사용하여 SID를 애플리케이션 ID로 변환합니다.

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

9\. 통과 Microsoft Entra 인증을 사용하여 SQL Managed Instance 연결된 서버 만들기

통과 인증을 사용하여 연결된 서버를 만들려면 다음 T-SQL을 실행하여 사용자 고유의 SQL 관리형 인스턴스 서버로 대체 <managed_instance> 합니다.

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)