1. 首页
  2. 文章中心
  3. SQL Server

SQL Server链接服务器 Linked Server

59 阅读 0 评论
我是靠谱客的博主 温婉月光，最近开发中收集的这篇文章主要介绍SQL Server链接服务器 Linked Server，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

链接服务器（Linked Server）允许访问针对OLE DB数据源的分布式异构查询， 通过使用sys.sp_addlinkedserver创建链接服务器后，可以对此服务器运行分布式查询。 如果链接服务器被定义为SQL Server实例，则可以执行远程存储过程。

一，链接服务器

使用 sys.sp_addlinkedserver 添加链接服务器，除了必须在本地自定义一个链接服务器的别名（Alias）之外，还必须指定链接服务器的产品名称（Product Name）、提供程序名称（Provider Name）、数据源（Data Source）、提供程序字符串（ Provider String）等参数，添加成功之后，系统自动创建一个默认登陆映射，用于访问链接服务器。

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

参数解释：

  • @server= 'server' ：用于指定Linked Server的别名，没有默认值；
  • @srvproduct='product_name' ：用于指定OLE DB 数据源的产品名称，默认值是NULL；
  • @provider= 'provider_name' ：OLE DB提供程序的唯一编程标识符（PROGID），provider_name对于当前计算机上安装的指定OLE DB提供程序必须是唯一的。如果省略provider_name，则使用SQLNCLI。
  • @datasrc= 'data_source'：由OLE DB提供程序解释的数据源名称
  • @provstr='provider_string'：指定OLE DB提供程序的连接字符串，用于唯一标识一个数据源。当针对SQL Server Native Client OLE DB提供程序创建链接服务器时，可以使用SERVER关键字指定实例作为SERVER = servername instancename来指定特定的SQL Server实例。 servername是运行SQL Server的计算机的名称，instancename是用户将连接到的SQL Server的特定实例的名称。

1，OLEDB 提供程序

用户需要手动安装OLE DB提供程序，系统中已经安装的OLE DB提供程序可以从Linked Servers 目录下查看。

例如，添加SQL Server作为Linked Server，把参数@srvproduct 设置为 N'' ：

exec sys.sp_addlinkedserver @server= N'ServerAlias'
    ,@srvproduct= N''
    ,@provider= N'SQLNCLI'
    ,@datasrc=  N'HostSQLServerInstanceName'

例如，添加Microsoft Access作为Linked Server，把参数 @srvproduct 设置为 N'OLE DB Provider for Jet'：

EXEC sp_addlinkedserver   
   @server = N'SEATTLE Mktg',   
   @provider = N'Microsoft.Jet.OLEDB.4.0',   
   @srvproduct = N'OLE DB Provider for Jet',  
   @datasrc = N'C:MSOfficeAccessSamplesNorthwind.mdb';

例如，添加Excel作为Linked Server，把参数@srvproduct设置为'Jet 4.0'，参数@provstr设置为'Excel 5.0'：

 

EXEC sp_addlinkedserver 
    @server='ExcelSource',  
    @srvproduct='Jet 4.0',  
    @provider='Microsoft.Jet.OLEDB.4.0',  
    @datasrc='c:MyDataDistExcl.xls',  
    @provstr='Excel 5.0';

 

2，查看添加的链接服务器

使用sys.servers查看添加到本地服务器的Linked Server，其中，server_id 是 0，代表本地服务器。

select *
from sys.servers
where name=N'ExcelDataSource'

二，链接服务器的登陆（Login）

当用户创建了一个链接服务器，SQL Server创建一个默认的登陆，映射到public服务器角色，这意味着，所有的登陆都可以查看该链接服务器。为了限制用户查看这些链接服务器，管理员需要移除默认的登陆映射。

1，创建链接服务器的登陆

当用户登陆到本地服务器执行访问链接服务器上的数据的分布式查询时，本地服务器必须代表该用户登陆到链接服务器，才能访问连接服务器上的数据，管理员通过使用sys.sp_addlinkedsrvlogin 指定本地服务器登陆到链接服务器的登陆账户。如果链接服务器是SQL Server，那么必须使用SQL Server验证模式登陆，不能使用Windows 验证的域账户登陆。

存储过程sys.sp_addlinkedsrvlogin用于创建或更新SQL Server本地实例上的登录和远程服务器上的安全帐户之间的映射。

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname' 
     [ , [ @useself = ] 'TRUE' | 'FALSE' | NULL ] 
     [ , [ @locallogin = ] 'locallogin' ] 
     [ , [ @rmtuser = ] 'rmtuser' ] 
     [ , [ @rmtpassword = ] 'rmtpassword' ]

参数解释：

  • @rmtsrvname =  'rmtsrvname' ：用于指定链接服务器的别名；
  • @useself =  'TRUE' | 'FALSE' | 'NULL' ：通过模拟本地登录或显式提交登录名和密码来确定是否连接到rmtsrvname，默认值是TRUE。默认情况下，Login使用自己的凭证链接rmtsrvname；如果设置为FALSE，意味着，使用参数 rmtuser和rmtpassword作为验证的用户名和密码去连接rmtsrvname；
  • @locallogin = 'locallogin' ：本地服务器的Login，默认值是NULL。默认情况下，使用本地Login连接rmtsrvname，如果该参数不是NULL，那么使用该参数指定的Login登陆rmtsrvname；
  • @rmtuser =  'rmtuser' ,@rmtpassword =  'rmtpassword' ：当参数@useself为FALSE时，使用@rmtuser和@rmtpassword去连接rmtsrvname。注意，rmtuser 是SQL Server登陆。

 注意，执行存储过程sp_addlinkedserver会自动创建本地服务器上所有登录和链接服务器上远程登录之间的默认映射。默认映射指出，当代表登录名连接到链接服务器时，SQL Server使用本地登录名的用户凭据。 这相当于对链接服务器执行

exec sys.sp_addlinkedsrvlogin @rmtsrvname='remote server', @useself='TRUE'

使用sp_addlinkedsrvlogin只能更改默认映射或为特定的本地登录添加新映射。 要删除默认映射或任何其他映射，请使用sp_droplinkedsrvlogin。  

2，添加链接服务器Login的示例

2.1  使用User自己的凭证去登陆Linked Server

这种方式要求显式将每个User Login mapping到Linked Server，使其有权限登录。

EXEC sp_addlinkedsrvlogin 'Accounts', 'true';

2.2 特定的用户使用特定的账户登陆链接服务器

把Windows域账户“DomainMary” 映射到 SQL Server验证的账户"MaryP"，使其有权限登陆到Linked Server。

EXEC sp_addlinkedsrvlogin 'Accounts', 'false', 'DomainMary', 'MaryP', 'd89q3w4u';

2.3，所有的用户都使用相同的账户登陆链接服务器

设置参数@locallogin 为null，使所有本地Login都能使用 “MaryP” 来登陆Linked Server。

EXEC sp_addlinkedsrvlogin 'Accounts', 'false', null, 'MaryP', 'd89q3w4u';

3，查看链接服务器的Login

系统视图sys.linked_logins ，为每一个链接服务器Login映射返回一行数据：

  • server_id：0是指本地服务器，大于0的整数值是指链接服务器的ID。
  • local_principal_id：如果local_principal_id 不为0，其值是参数@locallogin对应的服务器级别的Principal，使用 sys.server_principals 查看该ID对应的主体；如果local_principal_id=0，表示@locallogin是NULL。
  • uses_self_credential：1是指使用自己的凭证访问链接服务器；0是指使用相同的用户和密码访问链接服务器。
  • remote_name：远程用户名，是指当登陆到链接服务器时 ，使用 remote_name 来登陆Linked Server，即登陆Linked Server的 Login。

4，删除链接服务器的Login

删除本地服务器和链接服务器上的登陆之间的映射，映射一旦删除，本地服务器上的Login就不能登陆到链接服务器。

sp_droplinkedsrvlogin [ @rmtsrvname= ] 'rmtsrvname' , 
   [ @locallogin= ] 'locallogin'

参数解释：

@rmtsrvname =  'rmtsrvname'：指定链接服务器的别名

@locallogin =  'locallogin' ：指定本地的Login， 

4.1 删除本地登陆 Mary访问链接服务器的映射：

EXEC sp_droplinkedsrvlogin 'remote server', 'Mary'

4.2 删除本地服务器访问链接服务器的默认登陆映射

EXEC sp_droplinkedsrvlogin 'remote server', null

 

https://www.cnblogs.com/mybky/p/3448126.html

--创建链接服务器 
exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' 
exec sp_addlinkedsrvlogin  'ITSV ', 'false ',null, '用户名 ', '密码 ' 
 
--查询示例 
select * from ITSV.数据库名.dbo.表名 
 
--导入示例 
select * into 表 from ITSV.数据库名.dbo.表名 
 
--以后不再使用时删除链接服务器 
exec sp_dropserver  'ITSV ', 'droplogins ' 
 
--连接远程/局域网数据(openrowset/openquery/opendatasource) 
--1、openrowset 
 
--查询示例 
select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) 
 
--生成本地表 
select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) 
 
--把本地表导入远程表 
insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) 
select *from 本地表 
 
--更新本地表 
update b 
set b.列A=a.列A 
 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b 
on a.column1=b.column1 
 
--openquery用法需要创建一个连接 
 
--首先创建一个连接创建链接服务器 
exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' 
--查询 
select * 
FROM openquery(ITSV,  'SELECT *  FROM 数据库.dbo.表名 ') 
--把本地表导入远程表 
insert openquery(ITSV,  'SELECT *  FROM 数据库.dbo.表名 ') 
select * from 本地表 
--更新本地表 
update b 
set b.列B=a.列B 
FROM openquery(ITSV,  'SELECT * FROM 数据库.dbo.表名 ') as a  
inner join 本地表 b on a.列A=b.列A 
 
--3、opendatasource/openrowset 
SELECT   * 
FROM   opendatasource( 'SQLOLEDB ',  'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta 
--把本地表导入远程表 
insert opendatasource( 'SQLOLEDB ',  'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名 
select * from


参考文档：

sp_addlinkedserver (Transact-SQL)

sp_addlinkedsrvlogin (Transact-SQL)

作者：悦光阴

原文出处：https://www.cnblogs.com/ljhdo/p/5171006.html

本文版权归作者和博客园所有，欢迎转载，但未经作者同意，必须保留此段声明，且在文章页面醒目位置显示原文连接，否则保留追究法律责任的权利。

最后

以上就是温婉月光为你收集整理的SQL Server链接服务器 Linked Server的全部内容，希望文章能够帮你解决SQL Server链接服务器 Linked Server所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(39)

相关文章

JAVA功能代码《7》----Java创建ZIP和JAR文件
JAVA功能代码《7》----Java创建ZIP和JAR文件
请执行sp_addlinkedserver 将该服务器添加到sysserver解决办法
请执行sp_addlinkedserver 将该服务器添加到sysserver解决办法
Zip压缩继续分析
Zip压缩继续分析
5.Java 存储全局常量so easy
5.Java 存储全局常量so easy
SQL Server链接服务器 Linked Server
SQL Server链接服务器 Linked Server
LinkedServer链接服务器的使用
LinkedServer链接服务器的使用
linkedserver 的使用
linkedserver 的使用
在sysservers 中找不到服务器
在sysservers 中找不到服务器 "名字 " 执行 sp_addlinkedserver 将该服务器添加到 “名字”

评论列表共有 0 条评论

立即
投稿
返回
顶部