使用 OPENROWSET 通过 SQL Server 访问 Oracle DB
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/448077/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Accessing Oracle DB through SQL Server using OPENROWSET
提问by Ken Paul
I'm trying to access a large Oracle database through SQL Server using OPENROWSET in client-side Javascript, and not having much luck. Here are the particulars:
我正在尝试使用客户端 Javascript 中的 OPENROWSET 通过 SQL Server 访问大型 Oracle 数据库，但运气不佳。以下是详细信息：
- A SQL Server view that accesses the Oracle database using OPENROWSET works perfectly, so I know I have valid connection string parameters. However, the new requirement is for extremely dynamic Oracle queries that depend on client-side selections, and I haven't been able to get dynamic (or even parameterized) Oracle queries to work from SQL Server views or stored procedures.
- Client-side access to the SQL Server database works perfectly with dynamic and parameterized queries.
- I cannot count on clients having any Oracle client software. Therefore, access to the Oracle database has to be through the SQL Server database, using views, stored procedures, or dynamic queries using OPENROWSET.
- Because the SQL Server database is on a shared server, I'm not allowed to use globally-linked databases.
- 使用 OPENROWSET 访问 Oracle 数据库的 SQL Server 视图运行良好，所以我知道我有有效的连接字符串参数。但是，新的要求是针对依赖于客户端选择的极其动态的 Oracle 查询，而且我无法从 SQL Server 视图或存储过程获得动态（甚至参数化）的 Oracle 查询。
- 客户端对 SQL Server 数据库的访问与动态和参数化查询完美配合。
- 我不能指望客户拥有任何 Oracle 客户端软件。因此，必须通过 SQL Server 数据库、使用视图、存储过程或使用 OPENROWSET 的动态查询来访问 Oracle 数据库。
- 因为 SQL Server 数据库在共享服务器上，所以我不允许使用全局链接的数据库。
My idea was to define a function that would take my own version of a parameterized Oracle query, make the parameter substitutions, wrap the query in an OPENROWSET, and execute it in SQL Server, returning the resulting recordset. Here's sample code:
我的想法是定义一个函数，该函数将采用我自己版本的参数化 Oracle 查询，进行参数替换，将查询包装在 OPENROWSET 中，并在 SQL Server 中执行它，返回结果记录集。这是示例代码：
// db is a global variable containing an ADODB.Connection opened to the SQL Server DB
// rs is a global variable containing an ADODB.Recordset
. . .
ss = "SELECT myfield FROM mytable WHERE {param0} ORDER BY myfield;";
OracleQuery(ss,["somefield='" + somevalue + "'"]);
. . .
function OracleQuery(sql,params) {
var s = sql;
var i;
for (i = 0; i < params.length; i++) s = s.replace("{param" + i + "}",params[i]);
var e = "SELECT * FROM OPENROWSET('MSDAORA','(connect-string-values)';"
+ "'user';'pass','" + s.split("'").join("''") + "') q";
try {
rs.Open("EXEC ('" + e.split("'").join("''") + "')",db);
} catch (eobj) {
alert("SQL ERROR: " + eobj.description + "\nSQL: " + e);
}
}
The SQL error that I'm getting is Ad hoc access to OLE DB provider 'MSDAORA' has been denied. You must access this provider through a linked server.which makes no sense to me. The Microsoft explanation for this error relates to a registry setting (DisallowAdhocAccess). This is set correctly on my PC, but surely this relates to the DB server and not the client PC, and I would expect that the setting there is correct since the view mentioned above works.
我得到的 SQL 错误对我来说Ad hoc access to OLE DB provider 'MSDAORA' has been denied. You must access this provider through a linked server.毫无意义。Microsoft 对此错误的解释与注册表设置 ( DisallowAdhocAccess) 相关。这在我的 PC 上设置正确，但这肯定与数据库服务器有关，而不是客户端 PC，我希望那里的设置是正确的，因为上述视图有效。
One alternative that I've tried is to eliminate the enclosing EXEC in the Open statement:
我尝试过的一种替代方法是消除 Open 语句中的封闭 EXEC：
rs.Open(e,db);
but this generates the same error.
但这会产生相同的错误。
I also tried putting the OPENROWSET in a stored procedure. This works perfectly when executed from within SQL Server Management Studio, but fails with the same error message when the stored procedure is called from Javascript.
我还尝试将 OPENROWSET 放在存储过程中。从 SQL Server Management Studio 中执行时，这可以完美运行，但从 Javascript 调用存储过程时失败并显示相同的错误消息。
Is what I'm trying to do possible? If so, can you recommend how to fix my code? Or is a completely different approach necessary?
我正在尝试做的可能吗？如果是这样，你能推荐如何修复我的代码吗？还是需要一种完全不同的方法？
Any hints or related information will be welcome. Thanks in advance.
欢迎提供任何提示或相关信息。提前致谢。
采纳答案by Ken Paul
I'm answering this myself. I found the answer, and I'm not happy with the results. The functions that have worked are being run under my personal user id, and I have db-owner privileges. For the ad hocaccess to work, I need to either set the DisallowAdhocAccessregistry setting to 0, or give db-owner privileges to the user id used in the web access. Because this is a shared server with tight security, I won't be allowed to change the registry setting, which would affect much more than my database. And I consider the second option to be equally dangerous.
我自己回答这个。我找到了答案，但我对结果并不满意。有效的功能正在我的个人用户 ID 下运行，并且我拥有 db-owner 权限。为了使临时访问工作，我需要将DisallowAdhocAccess注册表设置设置为 0，或者为 Web 访问中使用的用户 ID 授予 db-owner 权限。因为这是一个安全性很高的共享服务器，我将不被允许更改注册表设置，这将比我的数据库影响更大。我认为第二种选择同样危险。
As a result, I'm apparently stuck with forcing users to have the Oracle Instant Client installed so I can open an ADO connection to the Oracle database directly in client-side Javascript.
结果，我显然坚持强迫用户安装 Oracle Instant Client，这样我就可以直接在客户端 Javascript 中打开到 Oracle 数据库的 ADO 连接。
I will still welcome any alternative thoughts on this.
我仍然欢迎对此的任何其他想法。
回答by Ken Paul
Opening client-side ADO connections to a database is a huge security no-no. You're essentially giving the user the connection credentials to your database and daring them to find a hole in your database security. Even if your audience is internal to your company you can run into problems with them not having the oracle drivers installed (or an older version). Why not just hide the Oracle query behind a JSON call? This way you can sanitize your database input on the server side, and your user never received the database connection credentials in plain text.
打开到数据库的客户端 ADO 连接是一个巨大的安全禁忌。您实际上是在向用户提供您的数据库的连接凭据，并让他们敢于在您的数据库安全性中找到漏洞。即使您的受众是您公司的内部人员，您也可能会遇到他们没有安装 oracle 驱动程序（或旧版本）的问题。为什么不直接将 Oracle 查询隐藏在 JSON 调用后面？通过这种方式，您可以清理服务器端的数据库输入，并且您的用户从未收到纯文本的数据库连接凭据。
回答by mkr
try to recommand a linked server in your sqlserver box Linked Servers (Database Engine)to be used to get the oracle data
尝试在您的 sqlserver 框中推荐链接服务器Linked Servers (Database Engine)用于获取 oracle 数据
