Accessing Oracle DB through SQL Server using OPENROWSET

Question

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:

• 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.

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:

// 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.

One alternative that I've tried is to eliminate the enclosing EXEC in the Open statement:

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.

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.

Answer 1

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 hoc access to work, I need to either set the DisallowAdhocAccess registry 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.

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.

I will still welcome any alternative thoughts on this.