469,286 Members | 2,433 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,286 developers. It's quick & easy.

Cant use ODBC data source on SQL SERver 2000 sp3a named Instance

Here's my situation:

I have an ODBC DSN setup for Timberline Data (An accounting package
that uses pervasive.sql) on my sql box. I set up a linked server using
the supplied timberline odbc driver. I have two sql instances setup,
the default instance and a named instance. On the default instance,
the linked server works great no matter who is logged in using it (all
authentication is NT integrated). However on the named instance only
the NT account that the MSSqlserver$NAMED service is logged in under
can utilize the linked server. All others get a ODBC error:

"error 7399: OLE DB Provider 'MSDASQL' reported an error. Access
Denied. OLE DB error trace [OLE/DB Provider 'MSDASQL'
IUnknown::QueryInterface returned 0x80070005: Access Denied.]."

Far as i can tell, both instances are setup the same, except that one
is the default and one is a named instance.

Why the different results for the default instance vs a named instance.
Any ideas?

Thanks

Jul 23 '05 #1
5 5504
(gr******@gmail.com) writes:
I have an ODBC DSN setup for Timberline Data (An accounting package
that uses pervasive.sql) on my sql box. I set up a linked server using
the supplied timberline odbc driver. I have two sql instances setup,
the default instance and a named instance. On the default instance,
the linked server works great no matter who is logged in using it (all
authentication is NT integrated). However on the named instance only
the NT account that the MSSqlserver$NAMED service is logged in under
can utilize the linked server. All others get a ODBC error:

"error 7399: OLE DB Provider 'MSDASQL' reported an error. Access
Denied. OLE DB error trace [OLE/DB Provider 'MSDASQL'
IUnknown::QueryInterface returned 0x80070005: Access Denied.]."

Far as i can tell, both instances are setup the same, except that one
is the default and one is a named instance.

Why the different results for the default instance vs a named instance.


It indeed sounds strange. "Access is denied", in my experience, is
usually due to files being locked by another process. But why in
such case can one user on the instance get through?

I've posted a question to out internal MVP forum, to see if I can get
some tips.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
Thanks for the help.....

It definitely seems to be NT permission related and related to being a
named instance of sql server. I can run reports off of it using SQL
reporting but again only if I specify in the data source to use the
same NT account that is used by the name instance service.

Jul 23 '05 #3
(gr******@gmail.com) writes:
Thanks for the help.....

It definitely seems to be NT permission related and related to being a
named instance of sql server. I can run reports off of it using SQL
reporting but again only if I specify in the data source to use the
same NT account that is used by the name instance service.


I've yet to receive a response.

One thing you could check is whether this happens with any linked
server that uses ODBC. You can add a linked server to access an Excel
file like this:

EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\temp\test.xls',
NULL,
'Excel 5.0'
GO

and access it like this:

select * from ExcelSource...Sheet1$

If this works, it's starting to look like an issue with the Timberland
ODBC driver, in which case it's starting to look difficult...
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
I tried this on both instances and both worked fine. I agree that it
appears to be a Timberline issue. I'll have to go to them
next....ughhh.

Jul 23 '05 #5
(gr******@gmail.com) writes:
I tried this on both instances and both worked fine. I agree that it
appears to be a Timberline issue. I'll have to go to them
next....ughhh.


OK, so I will drop this issue.

It's always a problem when there are several vendors involved like this,
because you always have a feeling that the will point on each other.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Jack Smith | last post: by
1 post views Thread by M. Oakley | last post: by
5 posts views Thread by Alec | last post: by
1 post views Thread by Thijs | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.