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