Re: OPENROWSET/OPENDATASOURCE

From: Gary (clgary_at_yahoo.com)
Date: 05/31/05 

Date: Tue, 31 May 2005 14:39:32 -0500

Hi,

It's a bit more complicated that that. If I grant the user executing the
statement sa privs, it will run, however, if i limit it down to select,
insert, update, delete, and execute, the error returned is

"Ad hoc access to OLE DB provider 'MSDAORA' has been denied. You must access
this provider through a linked server."

I thought as long as the reg key for DisallowAdhocAccess was set to 0, it
should work, and rely on the context of the remote server to determine if it
would work

"Hari Prasad" <hari_prasad_k@hotmail.com> wrote in message
news:Oys8VfhZFHA.2884@tk2msftngp13.phx.gbl...
> Hi,
>
> OPENROWSET
> ----------------
>
> SELECT a.*
> FROM OPENROWSET('SQLOLEDB','DSN';'Login';'password',
> 'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
>
> Replace the DSN, Login and password with actual
>
> OPENDATASOURCE
> ----------------------------
>
> SELECT *
> FROM OPENDATASOURCE(
> 'SQLOLEDB',
> 'Data Source=ServerName;User ID=MyUID;Password=MyPass'
> ).Northwind.dbo.Categories
>
>
> See details of both command in sql server books online.
>
> Thanks
> Hari
> SQL Server MVP
>
>
> "Gary" <clgary@yahoo.com> wrote in message
> news:OjRfjbhZFHA.3788@TK2MSFTNGP15.phx.gbl...
> > Hi,
> >
> > Can anyone tell me what are the specific credentials needed for those
> > functions?
> >
> >
>
>

Relevant Pages

  • Re: Disabling ASP.net 2.0 Membership
    ... I checked out the article you referenced and implemented my own provider. ... as a FoxPro database, an Oracle database, or other data sources. ... custom connection string so that I can use the Commerce Server SQL Server ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: ERROR: Provider Management - Could not establish a connection to t
    ... If you have not yet created the SQL Server database, ... Can any data provider be used to ... Exception Details: System.Data.SqlClient.SqlException: A connection ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: .NET data provider or OLEDB provider?
    ... >I don't see where you see a built-in query engine. ... >service provider, ... >OLE DB providers don't have to support SQL however, ... > SQL Server linked server, SQL Server can handle the queries. ...
    (microsoft.public.data.oledb)
  • Re: Need Help: Operating system error 3706
    ... Installing an additional component resolved the problem and the software is now able to connect to the database ... The developer is currently doing a complete rewrite/update of the program, including database functions. ... While this would require a modest re-write, it's not that tough to convert to a managed-code provider instead of the COM-based OLE DB provider. ... If the application is not based on .NET, then make sure the application is upgraded to use the SNAC (SQL Server Native Client) provider which is less trouble-prone than the older MDAC-based providers. ...
    (microsoft.public.sqlserver.connect)
  • Re: ERROR: Provider Management - Could not establish a connection to t
    ... If you have not yet created the SQL Server database, ... Can any data provider be used to ... pipes and I have the server configured to connect using TCP/IP. ...
    (microsoft.public.dotnet.framework.aspnet)
Quantcast