Question Re: How do I link SQL Server to SA17? in Technology Q&A

How do I link SQL Server to SA17?

former_SQLA_member1694946 — Mon, 10 Oct 2022 16:11:30 GMT

I set up a linked server using these instructions: https://dcx.sap.com/index.html#sqla170/en/html/3bd909916c5f1014afdcdf4c723b1ec7.html

I get the following error when opening the Table or View nodes in MSSMS:

The OLE DB provider "SAOLEDB.17" for linked server "SQL ANYWHERE 17 CUSTDB" reported an error. Access denied. Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "SAOLEDB.17" for linked server "SQL ANYWHERE 17 CUSTDB". (Microsoft SQL Server, Error: 7399)

Re: How do I link SQL Server to SA17?

chris_keating — Mon, 10 Oct 2022 20:48:13 GMT

Can you verify that you completed step 5 "Allow Inprocess"? The erroris typically because that step has not been completed.

Re: How do I link SQL Server to SA17?

former_SQLA_member1694946 — Tue, 11 Oct 2022 08:55:57 GMT

Yes, I have that set. I set the usual SA userid/password under 'Be made using this security context'.

Re: How do I link SQL Server to SA17?

chris_keating — Tue, 11 Oct 2022 16:28:16 GMT

If that is not working for you, I am at a loss as to what is causing this. From Management Studio, when you expand the Catalogs for the linked server than you have defined, is the name 'default' or the name of the database that you are connecting i.e., for the SQL Anywhere 17 Demo, the catalog name should be 'demo' (In case it is a refresh issue, select View | Refresh or F5). If the value is still default after a refresh and that is not the actual database name, it suggests that "Allow inprocess" is not enabled.

Re: How do I link SQL Server to SA17?

former_SQLA_member1694946 — Wed, 12 Oct 2022 12:55:36 GMT

Yes it is 'default'. Now I am getting a different message but same error number: The OLE DB provider "SAOLEDB.17" for linked server "SQL ANYWHERE 17 CUSTDB" reported an error. Authentication failed. Cannot initialize the data source object of OLE DB provider "SAOLEDB.17" for linked server "SQL ANYWHERE 17 CUSTDB". (Microsoft SQL Server, Error: 7399)

Re: How do I link SQL Server to SA17?

former_SQLA_member1694946 — Wed, 12 Oct 2022 13:08:51 GMT

I tried adding my SQL server userid/password to Custdb and gave it SYS role. Now the link from MSSMS can't even connect with my user or dba. I've given up. Thanks for trying.

Re: How do I link SQL Server to SA17?

VolkerBarth — Wed, 12 Oct 2022 14:32:11 GMT

What SQL Server version do you use?

Re: How do I link SQL Server to SA17?

chris_keating — Wed, 12 Oct 2022 21:55:52 GMT

I learned another case in which "default" will be reported as the catalog name. It can also occur when a connection cannot be established to the linked server - and the new error you report confirms that a connection is not established. By default, MSS running as a service would not be able to connect using shared memory. Are you connecting to a running SQLA server using TCPIP?