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?

4
  • 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, 2012 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, 2012 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, 2012 at 5:04
  • GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO account_name Feb 26, 2021 at 20:01

1 Answer 1

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

2
  • Thanks, but "Show Advanced Options" was already set to 1 and "Ad Hoc Distributed Queries" doesn't exist in SQL 7.
    – Eli
    Feb 7, 2012 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, 2012 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.