修复 SQL Server 阻止访问 OPENROWSET 和 OPENDATASOURCE 语句的错误

解决 SQL Server 中 OPENROWSET 和 OPENDATASOURCE 访问被阻止的问题

发布于

在 SQL Server 数据库管理中，OPENROWSET 和 OPENDATASOURCE 是两个非常实用的函数，它们允许你直接从远程数据源查询数据而无需预先建立链接服务器。这两个函数为数据库管理员和开发人员提供了极大的灵活性，能够轻松实现跨服务器数据访问和异构数据源集成。然而，出于安全考虑，SQL Server 默认会阻止对这些函数的访问，本文将详细介绍如何解决这个问题以及这些函数的使用方法。

OPENROWSET 和 OPENDATASOURCE 的作用

OPENROWSET 和 OPENDATASOURCE 是 SQL Server 提供的特殊函数，它们允许临时访问远程数据源而不需要预先配置链接服务器。这在需要一次性查询或临时数据集成场景中特别有用。

OPENROWSET 可以看作是一个增强版的 OPENDATASOURCE，它不仅能连接远程 SQL Server 实例，还能访问各种格式的数据文件，如 Excel、CSV 等。而 OPENDATASOURCE 则主要用于连接其他 SQL Server 实例。

解决访问被阻止的问题

默认情况下，SQL Server 出于安全考虑会禁用这些函数。要启用它们，需要使用 SQL Server 的 Surface Area Configuration 工具或直接执行 T-SQL 命令：

-- 启用 OPENROWSET 和 OPENDATASOURCE
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

执行这些命令后，需要重启 SQL Server 服务使更改生效。需要注意的是，启用这些功能可能会带来安全风险，因为它们允许直接访问外部数据源。

OPENROWSET 函数的使用方法

OPENROWSET 函数的基本语法如下：

SELECT * FROM OPENROWSET('provider_name',
'datasource';'user_id';'password',
'query')

连接远程 SQL Server

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=远程服务器名;Trusted_Connection=yes;',
     'SELECT * FROM 数据库名.架构名.表名') AS a;

从 Excel 文件读取数据

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\data\文件.xlsx;HDR=YES',
'SELECT * FROM [Sheet1$]')

导入 CSV 文件数据

SELECT * FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\data;',
'SELECT * FROM 数据.csv')

OPENDATASOURCE 函数的使用方法

OPENDATASOURCE 提供了另一种连接远程 SQL Server 的方式：

SELECT *
FROM OPENDATASOURCE('SQLNCLI',
    'Data Source=远程服务器名;Integrated Security=SSPI').数据库名.架构名.表名

跨服务器查询示例

SELECT a.*, b.*
FROM 本地表 a
JOIN OPENDATASOURCE('SQLNCLI',
    'Data Source=远程服务器名;Integrated Security=SSPI'
    ).远程数据库.架构.表 b ON a.id = b.id

性能与安全注意事项

虽然 OPENROWSET 和 OPENDATASOURCE 非常方便，但在使用时需要注意以下几点：

  1. 性能影响：这些函数每次调用都会建立新的连接，频繁使用可能导致性能问题。对于经常需要访问的远程数据，考虑设置链接服务器。

  2. 安全风险：这些函数可能暴露敏感信息，如连接字符串中的凭据。确保适当保护这些信息。

  3. 权限管理：限制只有需要这些功能的用户才能使用它们，避免过度授权。

  4. 替代方案：对于长期需求，考虑使用 SQL Server Integration Services (SSIS) 或配置正式的链接服务器。

总结

OPENROWSET 和 OPENDATASOURCE 是 SQL Server 中强大的临时数据访问工具，能够帮助开发人员快速实现跨服务器数据查询和异构数据源集成。通过正确配置服务器设置，可以解除对这些函数的访问限制。然而，在使用这些功能时，必须权衡便利性与安全性，遵循最佳实践以确保数据库环境的安全和性能。对于频繁的数据访问需求，建议考虑更持久的解决方案如链接服务器或 ETL 工具。