1

On a SQL Server 7 server, I have a linked server to a Sybase database server running this command:

select * from openquery(SYBASE_SERVER, 'select * from DBA.TESTTABLE')

works fine if I'm logged in as an SA.

If I try to run it while logged in as non-SA, it fails with the error:

Server: Msg 7302, Level 16, State 1, Line 1 Could not create an instance of OLE DB provider 'ASAProv.90'.

What permissions do I need to grant the user to let it work as a NON-sa account?

|improve this question
  • What is the service account for the SQL Server service? What is the non-sa user you are trying to use - windows auth or SQL auth? Also, seriously - SQL Server 7.0? – Mike Walsh Feb 7 '12 at 20:52
  • The service account for MSSQLServer is just a generic domain account we user for our SQL servers. The non-SA user (testuser) is using SQL auth. If I just make testuser a SA, it works, so I know it's just a permission issue. – Eli Feb 7 '12 at 21:07
  • SQL Server 7.0's linked servers were a bit different and I don't have access to SQL 7.0 right at the moment, but can you let us know what settings are selected under the security tab of the linked server settings? Also, what provider options are selected for the provider for the ASA/Sybase provider in the providers list? – Mike Walsh Feb 10 '12 at 5:04
1

IIRC, try this. I think this is how I fixed it some time ago...

exec sp_configure 'Show Advanced Options', 1; reconfigure;
exec sp_configure 'Ad Hoc Distributed Queries', 1; reconfigure;
go

Edit, attempt 2:

Check this. I've seen this at a previous company (I wasn't sysadmin there) and can't remember what teh DBAs had to do for us to get our linked server to work (also Sybase)

http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/08/19/permissions-needed-to-set-up-linked-server-with-out-of-process-provider.aspx

|improve this answer
  • Thanks, but "Show Advanced Options" was already set to 1 and "Ad Hoc Distributed Queries" doesn't exist in SQL 7. – Eli Feb 7 '12 at 22:45
  • Thanks for all the help. Tried Attempt #2, but still no luck. It's got to be SQL Server permissions issue. The query works if my user (a sql authenticated user) is a member of the System Administrators role. It fails if the user is not. – Eli Feb 9 '12 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.