Can I connect a SQL Server Linked Server to Azure Data Factory?

1
votes

I have access to a Linked Server on SQL Server, lets call it LS1. It's connected to an SAP HANA database. I do not have direct access to the SAP HANA database; I only have access through the Linked Server on SQL Server.

Linked Server

In SSMS, I can access the table with the simple query:

Select * from LS1..<schema>.<table>

However, when I try that setup in Azure Data Factory (as shown below), I get the error Invalid object name 'LS1..<schema>.<table>' when I try to preview data.

Failed query

Interestingly, I get that same "error" as an IDE tooltip in SMSS, even though the query runs without problems. Has anyone been able to connect an Linked Server through ADF? Specifically an SAP HANA Linked Server with the stange .. in the table path?

EDIT: I should have mentioned earlier that I have only select and insert rights on the SQL Server. I cannot create views or stored procedures.

1
sql-serverazure-data-factoryhanalinked-server
FYI the .. is really 2 object delimiters with a zero length string between, meaning that for the blank value the default will be used; in this case the LOGIN's default database. It can also be used in other places, such as for the schema, where you may see SELECT * FROM MyDatabase..MyTable;. Here the USER's default schema would be used; commonly dbo. It's use, however, is often frowned upon and the object should be specified. - Larnu

1 Answers

2
votes

Create a view or a synonym to give the remote table a local object name.