Friday, March 23, 2012

OpenDataSource

We are using OPENDATASOURCE to select data from an Access db using the MS Je
t
OLEDB provider. My question is regarding the authorities needed to execute
this command. When I run this command using windows authentication or
integrated security everything seems to work fine—even with users that do
NOT
have symin rights on our SQL Server. However if I try to login using a
SQL Server login, rather than the windows login. I get the error: “Ad hoc
access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You mus
t
access this provider through a linked server.” This occurs even if the sq
l
login has dbo authorities to the database with the Stored proc call to
OPENDATASOURCE.
What authorities must the SQL login possess and why does the Windows login
with very few SQL privileges have access to this method? Is it possible to
run OPENDATASOURCE from a SQL login without symin rights?
We are running Win2K SP4 and the DisallowAdhocAccess key only exists in the
SQLOleDb provider, where it is set to 0.
Our goal is to be able to pull data from a variable Access file and put it
into our SQL db tables and vice versa (import/export), so if you have anothe
r
method we are open to that. It must be able to work with SQL Server and MSD
E
and connect to a variable Access file. Our application is VB6 and uses ADO
to access the SQL Server. We have looked into the linked server method some
,
but since we need to access different files, the OPENDATASOURCE method seeme
d
more applicable.
Thanks for any help.Have a look at MS KB 328569
Adrian
LisaConsult wrote:
> We are using OPENDATASOURCE to select data from an Access db using the MS
Jet
> OLEDB provider. My question is regarding the authorities needed to execut
e
> this command. When I run this command using windows authentication or
> integrated security everything seems to work fine—even with users that d
o NOT
> have symin rights on our SQL Server. However if I try to login using a
> SQL Server login, rather than the windows login. I get the error: “Ad h
oc
> access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You m
ust
> access this provider through a linked server.” This occurs even if the
sql
> login has dbo authorities to the database with the Stored proc call to
> OPENDATASOURCE.
> What authorities must the SQL login possess and why does the Windows login
> with very few SQL privileges have access to this method? Is it possible t
o
> run OPENDATASOURCE from a SQL login without symin rights?
> We are running Win2K SP4 and the DisallowAdhocAccess key only exists in th
e
> SQLOleDb provider, where it is set to 0.
> Our goal is to be able to pull data from a variable Access file and put it
> into our SQL db tables and vice versa (import/export), so if you have anot
her
> method we are open to that. It must be able to work with SQL Server and M
SDE
> and connect to a variable Access file. Our application is VB6 and uses AD
O
> to access the SQL Server. We have looked into the linked server method so
me,
> but since we need to access different files, the OPENDATASOURCE method see
med
> more applicable.
> Thanks for any help.
>|||Hi Adrian
Thanks for the comment, but what does MS KB 328569 stand for? I tried
google, but got no answer :-(
Cheers, Mohamed
Message posted via http://www.webservertalk.com|||microsoft knowledge base
go to http://www.msdn.microsoft.com/ and enter the number below into the sea
rch box on the top right
and click 'go'
Article ID : 328569
Last Review : October 30, 2003
Revision : 1.0
"Mohamed Azzouzi via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:7f3f9ed681a1436f8098611b3eea8b0d@.SQ
webservertalk.com...
Hi Adrian
Thanks for the comment, but what does MS KB 328569 stand for? I tried
google, but got no answer :-(
Cheers, Mohamed
Message posted via http://www.webservertalk.com

No comments:

Post a Comment