How do I link SQL Server to SA17?

[former_SQLA_member1694946]

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)

[chris_keating]

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

[former_SQLA_member1694946]

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

[chris_keating]

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.

[former_SQLA_member1694946]

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)

[former_SQLA_member1694946]

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.

[VolkerBarth]

What SQL Server version do you use?

[chris_keating]

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?