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: