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

open LDAP via MSSQLServer

Hi!
How can i access an open ldap server via MSSQLServer2000)?
Is there an oledb provider to connect to open ldap via linked server (for example)?
Thanks!
greets
chrisChris-

I believe the answer is NO. I did some research a while ago about the same. Just in case someone tells you otherwise, I will be interested too...

- CB

Originally posted by come_get_some
Hi!

How can i access an open ldap server via MSSQLServer2000)?

Is there an oledb provider to connect to open ldap via linked server (for example)?

Thanks!

greets

chris|||Hi!

Thanks for your answer.

I have to get open ldap data into SQLServer.......
I need to think about how to do it...

greetz

chris

Open default DB in Enterprise Manager upon connecting to server (SQL2000)

Hello,
1. My hosting provider hosts ~500 databases on the server where my DB
resides. To open my database I use SQL Server 2000 Enterprise Manager.
While server connection timeout is short enough (about 1-2 seconds), when I
click "Databases" folder in server hierarchy, trying to enlist all
databases, it takes and extremely long period of time to set up a list.
Moreover - 60% of attempts usually fail. - Enterprise Manager hangs deadly
and its process could be terminated only by Task Manager.
My first question is: why does EM take such a timeout to display a list of
databases and why in most cases it halts?
2. To open my database and to start working with it I each time have to
perform the above steps. However I find it rather silly - to enlist all
databases first and ONLY THEN select mine. Logical approach to the process
of database connection suggests NOT to enlist all databases (I really need
only one!), but configure server (client?) that way that upon connecting to
server it automatically defaults to my DB.
My second question is:
Has SQL Server 2000 Enterprise Manager such a feature? Can I configure it
not to enlist all databases upon connecting to server, but switch directly
to mine by default?
Thank you in advance.
Vsevolod.Hi
"Vsevolod Ukrainsky" wrote:
> Hello,
> 1. My hosting provider hosts ~500 databases on the server where my DB
> resides. To open my database I use SQL Server 2000 Enterprise Manager.
> While server connection timeout is short enough (about 1-2 seconds), when I
> click "Databases" folder in server hierarchy, trying to enlist all
> databases, it takes and extremely long period of time to set up a list.
> Moreover - 60% of attempts usually fail. - Enterprise Manager hangs deadly
> and its process could be terminated only by Task Manager.
> My first question is: why does EM take such a timeout to display a list of
> databases and why in most cases it halts?
> 2. To open my database and to start working with it I each time have to
> perform the above steps. However I find it rather silly - to enlist all
> databases first and ONLY THEN select mine. Logical approach to the process
> of database connection suggests NOT to enlist all databases (I really need
> only one!), but configure server (client?) that way that upon connecting to
> server it automatically defaults to my DB.
>
Enterprise manager lists only what is there, 500 databases on a single
instance is an unusually high number of databases. Every database on that
instance will be using the same system databases, therefore potentially
causing significant blocking of other users.
You should consider asking your ISP to have multiple instances with fewer
databases rather than getting MS to change their product for something that
should not be common practice.
> My second question is:
> Has SQL Server 2000 Enterprise Manager such a feature? Can I configure it
> not to enlist all databases upon connecting to server, but switch directly
> to mine by default?
No, and at this point in the life cycle of SQL 2000 it is (IMO) unlikely to
happen but you could log a feature request at
https://connect.microsoft.com/SQLServer
> Thank you in advance.
> Vsevolod.
John|||Thank you so much for your help!
"Vsevolod Ukrainsky" <vsevolod@.ukrainsky.ru> wrote in message
news:eobGc2ALHHA.780@.TK2MSFTNGP03.phx.gbl...
> Hello,
<SKIPPED>

Open default DB in Enterprise Manager upon connecting to server (SQL2000)

Hello,
1. My hosting provider hosts ~500 databases on the server where my DB
resides. To open my database I use SQL Server 2000 Enterprise Manager.
While server connection timeout is short enough (about 1-2 seconds), when I
click "Databases" folder in server hierarchy, trying to enlist all
databases, it takes and extremely long period of time to set up a list.
Moreover - 60% of attempts usually fail. - Enterprise Manager hangs deadly
and its process could be terminated only by Task Manager.
My first question is: why does EM take such a timeout to display a list of
databases and why in most cases it halts?
2. To open my database and to start working with it I each time have to
perform the above steps. However I find it rather silly - to enlist all
databases first and ONLY THEN select mine. Logical approach to the process
of database connection suggests NOT to enlist all databases (I really need
only one!), but configure server (client?) that way that upon connecting to
server it automatically defaults to my DB.
My second question is:
Has SQL Server 2000 Enterprise Manager such a feature? Can I configure it
not to enlist all databases upon connecting to server, but switch directly
to mine by default?
Thank you in advance.
Vsevolod.
Hi
"Vsevolod Ukrainsky" wrote:

> Hello,
> 1. My hosting provider hosts ~500 databases on the server where my DB
> resides. To open my database I use SQL Server 2000 Enterprise Manager.
> While server connection timeout is short enough (about 1-2 seconds), when I
> click "Databases" folder in server hierarchy, trying to enlist all
> databases, it takes and extremely long period of time to set up a list.
> Moreover - 60% of attempts usually fail. - Enterprise Manager hangs deadly
> and its process could be terminated only by Task Manager.
> My first question is: why does EM take such a timeout to display a list of
> databases and why in most cases it halts?
> 2. To open my database and to start working with it I each time have to
> perform the above steps. However I find it rather silly - to enlist all
> databases first and ONLY THEN select mine. Logical approach to the process
> of database connection suggests NOT to enlist all databases (I really need
> only one!), but configure server (client?) that way that upon connecting to
> server it automatically defaults to my DB.
>
Enterprise manager lists only what is there, 500 databases on a single
instance is an unusually high number of databases. Every database on that
instance will be using the same system databases, therefore potentially
causing significant blocking of other users.
You should consider asking your ISP to have multiple instances with fewer
databases rather than getting MS to change their product for something that
should not be common practice.

> My second question is:
> Has SQL Server 2000 Enterprise Manager such a feature? Can I configure it
> not to enlist all databases upon connecting to server, but switch directly
> to mine by default?
No, and at this point in the life cycle of SQL 2000 it is (IMO) unlikely to
happen but you could log a feature request at
https://connect.microsoft.com/SQLServer

> Thank you in advance.
> Vsevolod.
John

Open default DB in Enterprise Manager upon connecting to server (SQL2000)

Hello,
1. My hosting provider hosts ~500 databases on the server where my DB
resides. To open my database I use SQL Server 2000 Enterprise Manager.
While server connection timeout is short enough (about 1-2 seconds), when I
click "Databases" folder in server hierarchy, trying to enlist all
databases, it takes and extremely long period of time to set up a list.
Moreover - 60% of attempts usually fail. - Enterprise Manager hangs deadly
and its process could be terminated only by Task Manager.
My first question is: why does EM take such a timeout to display a list of
databases and why in most cases it halts?
2. To open my database and to start working with it I each time have to
perform the above steps. However I find it rather silly - to enlist all
databases first and ONLY THEN select mine. Logical approach to the process
of database connection suggests NOT to enlist all databases (I really need
only one!), but configure server (client?) that way that upon connecting to
server it automatically defaults to my DB.
My second question is:
Has SQL Server 2000 Enterprise Manager such a feature? Can I configure it
not to enlist all databases upon connecting to server, but switch directly
to mine by default?
Thank you in advance.
Vsevolod.Thank you so much for your help!
"Vsevolod Ukrainsky" <vsevolod@.ukrainsky.ru> wrote in message
news:eobGc2ALHHA.780@.TK2MSFTNGP03.phx.gbl...
> Hello,
<SKIPPED>