linked server problem on SQL Server 2000 sp3

Hello
im trying to connect to a remote server over an internet vpn connection
i asked admin of the remote location to open port 1433 in his firewall, he
did and im able to register the server successfully by IP address on
enterprise manager
i can also make a connection with query analyzer with no problem and select
from tables normally
however when i try to establish a linked server using OLEDB for SQL server ,
connection fails , properties of the connection is ,
product name: empty
data source: the IP address of the server
provider string and catalog: empty
security: no rows in local server login to remote server login mapping but
chose last option in
for a login not defined in the list above connection will: i chose
be made using this security context and i wrote sa and password i used
before in registering server as a test
what is wrong , does linking a server require opening additional ports in
firewall ' which query analyzer does not need if server is registered
normally not linked?
any help very appreciated
BassamBassam (bassam@.nptco.com.eg) writes:
> im trying to connect to a remote server over an internet vpn connection
> i asked admin of the remote location to open port 1433 in his firewall,
> he did and im able to register the server successfully by IP address on
> enterprise manager i can also make a connection with query analyzer with
> no problem and select from tables normally
> however when i try to establish a linked server using OLEDB for SQL
> server , connection fails , properties of the connection is ,
> product name: empty
> data source: the IP address of the server
> provider string and catalog: empty
And the error message is?
You could consider defining a provider string for the linked server. That's
all I can suggest without knowledge about the error.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Error msg is :
SQL Server does not exist or access denied.
i tried to define a provider string also by format
srever=ip address;database=xx;user id=xx ; password=xx
but did not work either
question is do i have to open port UDP 1434 also in the firewall ?
Regards
Bassam
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9760ACD558EC9Yazorman@.127.0.0.1...
> Bassam (bassam@.nptco.com.eg) writes:
> And the error message is?
> You could consider defining a provider string for the linked server.
> That's
> all I can suggest without knowledge about the error.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Bassam (bassam@.nptco.com.eg) writes:
> Error msg is :
> SQL Server does not exist or access denied.
> i tried to define a provider string also by format
> srever=ip address;database=xx;user id=xx ; password=xx
> but did not work either
> question is do i have to open port UDP 1434 also in the firewall ?
I don't think that would matter.
Is SQL Server running on the same machine as you run QA/EM from? If not,
maybe the remote DBA only opened the firewall for one IP address?
Under which Windows account do SQL Server run? You can check this in
Computer Management/Services. If SQL Server is running under LocalSystem
try changing it to domain account, and restart SQL Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Linked server issue

http://support.microsoft.com/default...b;en-us;285833
Search for the sentence:
"The workaround is to map the SQL Server logins to the Admin account of the
Access database and"
I cant seem to figure out how to do this "map" thing! Ive looked around in
Access and SQL Server for hours. Im using Access 2003 but my access database
itself is in Access 2000 format.
Thanks,
Girish
Hi Girish,
From your descriptions, I understood that you would like to know how to map
SQL Server logins to the Admin account of the Access database. Have I
understood you? If there is anything I misunderstood, please feel free to
let me know.
I tested on my side following these steps:
1. Add a new linked server using SQL Server Enterprise Manager.
2. In the Linked Server Properties dialog box of SQL Enterprise Manager.
Click the Security tab. Choose the Local login as "sa" and click on
"Impersonate" and also choose the last option: "Be made using this security
context:"
Remote login: Admin
With password: <blank> -- leave this blank.
(NOTICE that Access default Login is Admin and its password is NULL)
It worked fine.
Additionally, the following document might be helpful to you
PRB: Error 7399 When You Run a Linked Server Query That Uses the OLE DB
http://support.microsoft.com/?id=814398
In sum, MAP here just means, the login name used must have the admin
authority in Access system.mdw (default)
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||My access database is password protected too. I tried the below and it does
not work.
It gives me a authentication failed error.
Girish
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:lkuybiDhEHA.3636@.cpmsftngxa06.phx.gbl...
> Hi Girish,
> From your descriptions, I understood that you would like to know how to
map
> SQL Server logins to the Admin account of the Access database. Have I
> understood you? If there is anything I misunderstood, please feel free to
> let me know.
> I tested on my side following these steps:
> 1. Add a new linked server using SQL Server Enterprise Manager.
> 2. In the Linked Server Properties dialog box of SQL Enterprise Manager.
> Click the Security tab. Choose the Local login as "sa" and click on
> "Impersonate" and also choose the last option: "Be made using this
security
> context:"
> Remote login: Admin
> With password: <blank> -- leave this blank.
> (NOTICE that Access default Login is Admin and its password is NULL)
> It worked fine.
> Additionally, the following document might be helpful to you
> PRB: Error 7399 When You Run a Linked Server Query That Uses the OLE DB
> http://support.microsoft.com/?id=814398
> In sum, MAP here just means, the login name used must have the admin
> authority in Access system.mdw (default)
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Girish,
I have noticed you had create a new thread "Another issue with Linked
Server" discussing this issue. I had added a reply to that thread. Do you
still need I add some value to this one?
It is highly appreciated you had separated the issue, thanks once more.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
sql

Linked server - admin vs non-admin windows login

Any chance there was ever a resolution to this problem? We're having the
same issue and I cannot find the answer.
In our SQL 2000 server, it was a permissions problem to the sybase drivers
folder. On the server, we had to give "Authenticated Users" read access to
this folder. Once we did that, it worked. However, on our SQL 2005 server
we did this same thing and the initialization error went away but now our
queries just hang and never return. We have "Authenticated Users" in the
Users group on the server but if we add them to the Administrators group then
the queries run fine.
Any ideas would be greatly appreciated.
Thanks
Hi Doug
SQL 2005 has a tighter security model so making them a sysadmin would
probably bypass any more granular permissions you have. I would guess it is
more likely to be system objects that are causing the problems than user
ones. You could run profiler on the remote system to see what it does when
you have the permissions and where it stalls when you don't
John
"Doug" wrote:

> Any chance there was ever a resolution to this problem? We're having the
> same issue and I cannot find the answer.
> In our SQL 2000 server, it was a permissions problem to the sybase drivers
> folder. On the server, we had to give "Authenticated Users" read access to
> this folder. Once we did that, it worked. However, on our SQL 2005 server
> we did this same thing and the initialization error went away but now our
> queries just hang and never return. We have "Authenticated Users" in the
> Users group on the server but if we add them to the Administrators group then
> the queries run fine.
> Any ideas would be greatly appreciated.
> Thanks
>
|||Thanks for the reply!
You mentioned "sysadmin" but what I'm referring to is if we add the user to
the Administrators local group on the SQL server. It's not DB related at all
in my opinion, it's permissions at the O/S level related to the ODBC driver.
For example, on SQL 2000 we had to give the Users local group (which contains
NT Authenticated users such as in this case) on the server read permissions
to the folder that contains the Sybase ODBC driver otherwise we got an error.
We got that same error on the SQL 2005 and giving the User read permissions
to the Sybase ODBC driver folder got rid of that error but now the query just
hangs. However, if someone in the local Adminsstrators group runs the same
linked server query not only does it run fine for them, but it also runs fine
for the same the user that was previously getting an error. But it will only
work for a short period of time and then it stops working again. To me, what
seems to be happening is that when the person who is a local Admin on the
server runs the query it loads the driver into memory so that when the other
user runs the query it's already loaded. After a while, the driver gets
unloaded and then when the user runs the query again they have the problem.
It just seems related to OS permissions on the driver based on the behavior
we are seeing.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Doug
> SQL 2005 has a tighter security model so making them a sysadmin would
> probably bypass any more granular permissions you have. I would guess it is
> more likely to be system objects that are causing the problems than user
> ones. You could run profiler on the remote system to see what it does when
> you have the permissions and where it stalls when you don't
> John
> "Doug" wrote:
|||Hi
Local administrators are usually a sysadmin by default because of the
BUILTIN/Administrators group. I can't remember what I believed the problem
was in this post, but it may be that I though that if you were a sysadmin
the linked server ran it as yourself, but you weren't it ran it under the
service account, therefore the account which needed the directory permissions
depended on whether you were a sysadmin. I could have been wrong as I don't
know if this got resolved!
John
"Doug" wrote:
[vbcol=seagreen]
> Thanks for the reply!
> You mentioned "sysadmin" but what I'm referring to is if we add the user to
> the Administrators local group on the SQL server. It's not DB related at all
> in my opinion, it's permissions at the O/S level related to the ODBC driver.
> For example, on SQL 2000 we had to give the Users local group (which contains
> NT Authenticated users such as in this case) on the server read permissions
> to the folder that contains the Sybase ODBC driver otherwise we got an error.
> We got that same error on the SQL 2005 and giving the User read permissions
> to the Sybase ODBC driver folder got rid of that error but now the query just
> hangs. However, if someone in the local Adminsstrators group runs the same
> linked server query not only does it run fine for them, but it also runs fine
> for the same the user that was previously getting an error. But it will only
> work for a short period of time and then it stops working again. To me, what
> seems to be happening is that when the person who is a local Admin on the
> server runs the query it loads the driver into memory so that when the other
> user runs the query it's already loaded. After a while, the driver gets
> unloaded and then when the user runs the query again they have the problem.
> It just seems related to OS permissions on the driver based on the behavior
> we are seeing.
> "John Bell" wrote:

Linked server - admin vs non-admin windows login

Two windows logins - one admin and another - non-admin, are both setup in
sysadmin roles. Both can create a linked server (e.g. Access .mdb file using
Jet OLEDB 4.0).
However while ADMIN login CAN successfully execute SELECT queries from this
linked server, non-admin can not, generating:
<QUOTE>
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
"linked_server_1" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "linked_server_1".
</QUOTE>
Any suggestions re: possible cause?
All replies are very welcome.Hi
This look like a permissions issue with the non-administrator account. This
may help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_5dx5.asp
John
"Lolik" wrote:
> Two windows logins - one admin and another - non-admin, are both setup in
> sysadmin roles. Both can create a linked server (e.g. Access .mdb file using
> Jet OLEDB 4.0).
> However while ADMIN login CAN successfully execute SELECT queries from this
> linked server, non-admin can not, generating:
> <QUOTE>
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
> "linked_server_1" returned message "Unspecified error".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "linked_server_1".
> </QUOTE>
> Any suggestions re: possible cause?
> All replies are very welcome.|||Thanks for the link.
What could I do with permissions though?
It seems it is necessary for OLEDB to access some folder where non-admin
login has insufficient permissions.
Any ideas what they are?
"John Bell" wrote:
> Hi
> This look like a permissions issue with the non-administrator account. This
> may help:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_5dx5.asp
> John|||Hi
The may help further, it is not necessarilly the directory where the access
database is but the temp directory for the service account.
http://support.microsoft.com/kb/814398
I would also look at KB 285833 "PRB: Need to map to default Admin account
and use NULL for password to query linked server to Access database", but
this does not seem to be available. I think it says run a command similar to
the following: http://tinyurl.com/ox9h7
John
"Lolik" wrote:
> Thanks for the link.
> What could I do with permissions though?
> It seems it is necessary for OLEDB to access some folder where non-admin
> login has insufficient permissions.
> Any ideas what they are?
>
> "John Bell" wrote:
> > Hi
> >
> > This look like a permissions issue with the non-administrator account. This
> > may help:
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_2_5dx5.asp
> >
> > John|||Tried the Temp/Tmp trick, it did not work.
Could there be any other directories where the non-admin a/c would not have
the necessary permissions?
I did run sp_addlinkedsrvlogin command. The linked server works OK under
admin login.
I needed the linked server for development, not production. So it is not a
major problem for me at the moment.
Many thanks for your help!
"John Bell" wrote:
> Hi
> The may help further, it is not necessarilly the directory where the access
> database is but the temp directory for the service account.
> http://support.microsoft.com/kb/814398
> I would also look at KB 285833 "PRB: Need to map to default Admin account
> and use NULL for password to query linked server to Access database", but
> this does not seem to be available. I think it says run a command similar to
> the following: http://tinyurl.com/ox9h7
> John|||Hi
Assuming that you have added permissions to the database then there should
only be the directory in which it resides. Have you tried logging in as the
service account to access the access database?
John
"Lolik" wrote:
> Tried the Temp/Tmp trick, it did not work.
> Could there be any other directories where the non-admin a/c would not have
> the necessary permissions?
> I did run sp_addlinkedsrvlogin command. The linked server works OK under
> admin login.
> I needed the linked server for development, not production. So it is not a
> major problem for me at the moment.
> Many thanks for your help!
>
> "John Bell" wrote:
> > Hi
> >
> > The may help further, it is not necessarilly the directory where the access
> > database is but the temp directory for the service account.
> >
> > http://support.microsoft.com/kb/814398
> >
> > I would also look at KB 285833 "PRB: Need to map to default Admin account
> > and use NULL for password to query linked server to Access database", but
> > this does not seem to be available. I think it says run a command similar to
> > the following: http://tinyurl.com/ox9h7
> >
> > John
>|||"logging in as the service account "
- do you mean log in to SQL Server Management Studio as a service a/c?
How would I do this? There is only choice between Windows login and SQL
Server logins when connecting to SQL Server Management Studio ..
"John Bell" wrote:
> Hi
> Assuming that you have added permissions to the database then there should
> only be the directory in which it resides. Have you tried logging in as the
> service account to access the access database?
> John
>|||Hi
The service account is the windows account that the service is set to run
under, you can see this in the configuration tool or the services applet on
the administrators menu. You may have it configured as local system, in which
case using a domain account may help. If it is already a domain account use
this account to log into the server and open the access database.
Requirements for a service account are detailed in Books Online or at
http://msdn2.microsoft.com/en-us/library/ms143504.aspx
John
"Lolik" wrote:
> "logging in as the service account "
> - do you mean log in to SQL Server Management Studio as a service a/c?
> How would I do this? There is only choice between Windows login and SQL
> Server logins when connecting to SQL Server Management Studio ..
>
> "John Bell" wrote:
> > Hi
> >
> > Assuming that you have added permissions to the database then there should
> > only be the directory in which it resides. Have you tried logging in as the
> > service account to access the access database?
> >
> > John
> >
>|||The server runs on a desktop (not on a domain or any network altogether)..
I think I understood you:
when setting up the .mdb file as a linked server, I specified 'Admin' in
sp_addlinkedsrvlogin as @.locallogin parameter. Perhaps I should try '[pc
name]\[user a/c login]' with a NULL password instead?
"John Bell" wrote:
> Hi
> The service account is the windows account that the service is set to run
> under, you can see this in the configuration tool or the services applet on
> the administrators menu. You may have it configured as local system, in which
> case using a domain account may help. If it is already a domain account use
> this account to log into the server and open the access database.
> Requirements for a service account are detailed in Books Online or at
> http://msdn2.microsoft.com/en-us/library/ms143504.aspx
> John|||Hi
Usually you would not have to map windows logins with sp_addlinkedsrvlogin
as there should be a default mapping see sp_addlinkedsrvlogin in Books
Online. What does the acount information say on your service?
John
"Lolik" wrote:
> The server runs on a desktop (not on a domain or any network altogether)..
> I think I understood you:
> when setting up the .mdb file as a linked server, I specified 'Admin' in
> sp_addlinkedsrvlogin as @.locallogin parameter. Perhaps I should try '[pc
> name]\[user a/c login]' with a NULL password instead?
>
> "John Bell" wrote:
> > Hi
> >
> > The service account is the windows account that the service is set to run
> > under, you can see this in the configuration tool or the services applet on
> > the administrators menu. You may have it configured as local system, in which
> > case using a domain account may help. If it is already a domain account use
> > this account to log into the server and open the access database.
> > Requirements for a service account are detailed in Books Online or at
> > http://msdn2.microsoft.com/en-us/library/ms143504.aspx
> >
> > John
>|||The server runs as a network service..
"John Bell" wrote:
> Hi
> Usually you would not have to map windows logins with sp_addlinkedsrvlogin
> as there should be a default mapping see sp_addlinkedsrvlogin in Books
> Online. What does the acount information say on your service?
> John|||Hi
But in the services applet if you double click the SQL Server service what
does it say on the log on tab?
John
"Lolik" wrote:
> The server runs as a network service..
>
> "John Bell" wrote:
> > Hi
> >
> > Usually you would not have to map windows logins with sp_addlinkedsrvlogin
> > as there should be a default mapping see sp_addlinkedsrvlogin in Books
> > Online. What does the acount information say on your service?
> >
> > John
>|||if i go to SQL Server Configuration Manager..
..SQL Server 2005 Services
.. SQL Server (MSSQLSERVER)
and double-click on it,
a) the log on tab says:
Log on as:
built-in account:
Network Service
b) Service tab says:
Name SQL Server (MSSQLSERVER)
SQL Service Type SQL Server
"John Bell" wrote:
> Hi
> But in the services applet if you double click the SQL Server service what
> does it say on the log on tab?
> John
> "Lolik" wrote:
> > The server runs as a network service..
> >
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Usually you would not have to map windows logins with sp_addlinkedsrvlogin
> > > as there should be a default mapping see sp_addlinkedsrvlogin in Books
> > > Online. What does the acount information say on your service?
> > >
> > > John
> >
> >|||Hi
This may be related to your other problem of not being able to change the
service accounts.
John
"Lolik" wrote:
> if i go to SQL Server Configuration Manager..
> ..SQL Server 2005 Services
> .. SQL Server (MSSQLSERVER)
> and double-click on it,
> a) the log on tab says:
> Log on as:
> built-in account:
> Network Service
> b) Service tab says:
> Name SQL Server (MSSQLSERVER)
> SQL Service Type SQL Server
>
> "John Bell" wrote:
> > Hi
> >
> > But in the services applet if you double click the SQL Server service what
> > does it say on the log on tab?
> >
> > John
> >
> > "Lolik" wrote:
> >
> > > The server runs as a network service..
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > Usually you would not have to map windows logins with sp_addlinkedsrvlogin
> > > > as there should be a default mapping see sp_addlinkedsrvlogin in Books
> > > > Online. What does the acount information say on your service?
> > > >
> > > > John
> > >
> > >|||Any chance there was ever a resolution to this problem? We're having the
same issue and I cannot find the answer.
In our SQL 2000 server, it was a permissions problem to the sybase drivers
folder. On the server, we had to give "Authenticated Users" read access to
this folder. Once we did that, it worked. However, on our SQL 2005 server
we did this same thing and the initialization error went away but now our
queries just hang and never return. We have "Authenticated Users" in the
Users group on the server but if we add them to the Administrators group then
the queries run fine.
Any ideas would be greatly appreciated.
Thanks|||Hi Doug
SQL 2005 has a tighter security model so making them a sysadmin would
probably bypass any more granular permissions you have. I would guess it is
more likely to be system objects that are causing the problems than user
ones. You could run profiler on the remote system to see what it does when
you have the permissions and where it stalls when you don't
John
"Doug" wrote:
> Any chance there was ever a resolution to this problem? We're having the
> same issue and I cannot find the answer.
> In our SQL 2000 server, it was a permissions problem to the sybase drivers
> folder. On the server, we had to give "Authenticated Users" read access to
> this folder. Once we did that, it worked. However, on our SQL 2005 server
> we did this same thing and the initialization error went away but now our
> queries just hang and never return. We have "Authenticated Users" in the
> Users group on the server but if we add them to the Administrators group then
> the queries run fine.
> Any ideas would be greatly appreciated.
> Thanks
>|||Thanks for the reply!
You mentioned "sysadmin" but what I'm referring to is if we add the user to
the Administrators local group on the SQL server. It's not DB related at all
in my opinion, it's permissions at the O/S level related to the ODBC driver.
For example, on SQL 2000 we had to give the Users local group (which contains
NT Authenticated users such as in this case) on the server read permissions
to the folder that contains the Sybase ODBC driver otherwise we got an error.
We got that same error on the SQL 2005 and giving the User read permissions
to the Sybase ODBC driver folder got rid of that error but now the query just
hangs. However, if someone in the local Adminsstrators group runs the same
linked server query not only does it run fine for them, but it also runs fine
for the same the user that was previously getting an error. But it will only
work for a short period of time and then it stops working again. To me, what
seems to be happening is that when the person who is a local Admin on the
server runs the query it loads the driver into memory so that when the other
user runs the query it's already loaded. After a while, the driver gets
unloaded and then when the user runs the query again they have the problem.
It just seems related to OS permissions on the driver based on the behavior
we are seeing.
"John Bell" wrote:
> Hi Doug
> SQL 2005 has a tighter security model so making them a sysadmin would
> probably bypass any more granular permissions you have. I would guess it is
> more likely to be system objects that are causing the problems than user
> ones. You could run profiler on the remote system to see what it does when
> you have the permissions and where it stalls when you don't
> John
> "Doug" wrote:
> > Any chance there was ever a resolution to this problem? We're having the
> > same issue and I cannot find the answer.
> >
> > In our SQL 2000 server, it was a permissions problem to the sybase drivers
> > folder. On the server, we had to give "Authenticated Users" read access to
> > this folder. Once we did that, it worked. However, on our SQL 2005 server
> > we did this same thing and the initialization error went away but now our
> > queries just hang and never return. We have "Authenticated Users" in the
> > Users group on the server but if we add them to the Administrators group then
> > the queries run fine.
> >
> > Any ideas would be greatly appreciated.
> > Thanks
> >
> >|||Hi
Local administrators are usually a sysadmin by default because of the
BUILTIN/Administrators group. I can't remember what I believed the problem
was in this post, but it may be that I though that if you were a sysadmin
the linked server ran it as yourself, but you weren't it ran it under the
service account, therefore the account which needed the directory permissions
depended on whether you were a sysadmin. I could have been wrong as I don't
know if this got resolved!
John
"Doug" wrote:
> Thanks for the reply!
> You mentioned "sysadmin" but what I'm referring to is if we add the user to
> the Administrators local group on the SQL server. It's not DB related at all
> in my opinion, it's permissions at the O/S level related to the ODBC driver.
> For example, on SQL 2000 we had to give the Users local group (which contains
> NT Authenticated users such as in this case) on the server read permissions
> to the folder that contains the Sybase ODBC driver otherwise we got an error.
> We got that same error on the SQL 2005 and giving the User read permissions
> to the Sybase ODBC driver folder got rid of that error but now the query just
> hangs. However, if someone in the local Adminsstrators group runs the same
> linked server query not only does it run fine for them, but it also runs fine
> for the same the user that was previously getting an error. But it will only
> work for a short period of time and then it stops working again. To me, what
> seems to be happening is that when the person who is a local Admin on the
> server runs the query it loads the driver into memory so that when the other
> user runs the query it's already loaded. After a while, the driver gets
> unloaded and then when the user runs the query again they have the problem.
> It just seems related to OS permissions on the driver based on the behavior
> we are seeing.
> "John Bell" wrote:
> > Hi Doug
> >
> > SQL 2005 has a tighter security model so making them a sysadmin would
> > probably bypass any more granular permissions you have. I would guess it is
> > more likely to be system objects that are causing the problems than user
> > ones. You could run profiler on the remote system to see what it does when
> > you have the permissions and where it stalls when you don't
> >
> > John
> >
> > "Doug" wrote:
> >
> > > Any chance there was ever a resolution to this problem? We're having the
> > > same issue and I cannot find the answer.
> > >
> > > In our SQL 2000 server, it was a permissions problem to the sybase drivers
> > > folder. On the server, we had to give "Authenticated Users" read access to
> > > this folder. Once we did that, it worked. However, on our SQL 2005 server
> > > we did this same thing and the initialization error went away but now our
> > > queries just hang and never return. We have "Authenticated Users" in the
> > > Users group on the server but if we add them to the Administrators group then
> > > the queries run fine.
> > >
> > > Any ideas would be greatly appreciated.
> > > Thanks
> > >
> > >

Linked server - admin vs non-admin windows login

Two windows logins - one admin and another - non-admin, are both setup in
sysadmin roles. Both can create a linked server (e.g. Access .mdb file using
Jet OLEDB 4.0).
However while ADMIN login CAN successfully execute SELECT queries from this
linked server, non-admin can not, generating:
<QUOTE>
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
"linked_server_1" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "linked_server_1".
</QUOTE>
Any suggestions re: possible cause?
All replies are very welcome.Hi
This look like a permissions issue with the non-administrator account. This
may help:
http://msdn.microsoft.com/library/d...serr_2_5dx5.asp
John
"Lolik" wrote:

> Two windows logins - one admin and another - non-admin, are both setup in
> sysadmin roles. Both can create a linked server (e.g. Access .mdb file usi
ng
> Jet OLEDB 4.0).
> However while ADMIN login CAN successfully execute SELECT queries from thi
s
> linked server, non-admin can not, generating:
> <QUOTE>
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
> "linked_server_1" returned message "Unspecified error".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "linked_server_1".
> </QUOTE>
> Any suggestions re: possible cause?
> All replies are very welcome.|||Thanks for the link.
What could I do with permissions though?
It seems it is necessary for OLEDB to access some folder where non-admin
login has insufficient permissions.
Any ideas what they are?
"John Bell" wrote:

> Hi
> This look like a permissions issue with the non-administrator account. Thi
s
> may help:
> http://msdn.microsoft.com/library/d...serr_2_5dx5.asp
> John|||Hi
The may help further, it is not necessarilly the directory where the access
database is but the temp directory for the service account.
http://support.microsoft.com/kb/814398
I would also look at KB 285833 "PRB: Need to map to default Admin account
and use NULL for password to query linked server to Access database", but
this does not seem to be available. I think it says run a command similar to
the following: http://tinyurl.com/ox9h7
John
"Lolik" wrote:
[vbcol=seagreen]
> Thanks for the link.
> What could I do with permissions though?
> It seems it is necessary for OLEDB to access some folder where non-admin
> login has insufficient permissions.
> Any ideas what they are?
>
> "John Bell" wrote:
>|||Tried the Temp/Tmp trick, it did not work.
Could there be any other directories where the non-admin a/c would not have
the necessary permissions?
I did run sp_addlinkedsrvlogin command. The linked server works OK under
admin login.
I needed the linked server for development, not production. So it is not a
major problem for me at the moment.
Many thanks for your help!
"John Bell" wrote:

> Hi
> The may help further, it is not necessarilly the directory where the acces
s
> database is but the temp directory for the service account.
> http://support.microsoft.com/kb/814398
> I would also look at KB 285833 "PRB: Need to map to default Admin account
> and use NULL for password to query linked server to Access database", but
> this does not seem to be available. I think it says run a command similar
to
> the following: http://tinyurl.com/ox9h7
> John|||Hi
Assuming that you have added permissions to the database then there should
only be the directory in which it resides. Have you tried logging in as the
service account to access the access database?
John
"Lolik" wrote:

> Tried the Temp/Tmp trick, it did not work.
> Could there be any other directories where the non-admin a/c would not hav
e
> the necessary permissions?
> I did run sp_addlinkedsrvlogin command. The linked server works OK under
> admin login.
> I needed the linked server for development, not production. So it is not a
> major problem for me at the moment.
> Many thanks for your help!
>
> "John Bell" wrote:
>
>|||"logging in as the service account "
- do you mean log in to SQL Server Management Studio as a service a/c?
How would I do this? There is only choice between Windows login and SQL
Server logins when connecting to SQL Server Management Studio ..
"John Bell" wrote:

> Hi
> Assuming that you have added permissions to the database then there should
> only be the directory in which it resides. Have you tried logging in as th
e
> service account to access the access database?
> John
>|||Hi
The service account is the windows account that the service is set to run
under, you can see this in the configuration tool or the services applet on
the administrators menu. You may have it configured as local system, in whic
h
case using a domain account may help. If it is already a domain account use
this account to log into the server and open the access database.
Requirements for a service account are detailed in Books Online or at
http://msdn2.microsoft.com/en-us/library/ms143504.aspx
John
"Lolik" wrote:

> "logging in as the service account "
> - do you mean log in to SQL Server Management Studio as a service a/c?
> How would I do this? There is only choice between Windows login and SQL
> Server logins when connecting to SQL Server Management Studio ..
>
> "John Bell" wrote:
>
>|||The server runs on a desktop (not on a domain or any network altogether)..
I think I understood you:
when setting up the .mdb file as a linked server, I specified 'Admin' in
sp_addlinkedsrvlogin as @.locallogin parameter. Perhaps I should try '[pc
name]\[user a/c login]' with a NULL password instead?
"John Bell" wrote:

> Hi
> The service account is the windows account that the service is set to run
> under, you can see this in the configuration tool or the services applet o
n
> the administrators menu. You may have it configured as local system, in wh
ich
> case using a domain account may help. If it is already a domain account us
e
> this account to log into the server and open the access database.
> Requirements for a service account are detailed in Books Online or at
> http://msdn2.microsoft.com/en-us/library/ms143504.aspx
> John|||Hi
Usually you would not have to map windows logins with sp_addlinkedsrvlogin
as there should be a default mapping see sp_addlinkedsrvlogin in Books
Online. What does the acount information say on your service?
John
"Lolik" wrote:

> The server runs on a desktop (not on a domain or any network altogether)..
> I think I understood you:
> when setting up the .mdb file as a linked server, I specified 'Admin' in
> sp_addlinkedsrvlogin as @.locallogin parameter. Perhaps I should try '[
pc
> name]\[user a/c login]' with a NULL password instead?
>
> "John Bell" wrote:
>
>