Create external data source in Azure Synapse Analytics (Azure SQL Data warehouse) to Oracle

1
votes

I am trying to create external data source in Azure Synapse Analytics (Azure SQL Data warehouse) to Oracle external database. I am using the following code in SSMS to do that:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'myPassword';
CREATE DATABASE SCOPED CREDENTIAL MyCred WITH IDENTITY = 'myUserName', Secret = 'Mypassword';
CREATE EXTERNAL DATA SOURCE MyEXTSource
WITH (
LOCATION = 'oracle://<myIPAddress>:1521',
CREDENTIAL = MyCred
)

I am getting the following error:

CREATE EXTERNAL DATA SOURCE statement failed because the 'TYPE' option is not specified. Specify a value for the 'TYPE' option and try again.

I understand from the below that TYPE is not a required option for Oracle databases.

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=azure-sqldw-latest

Not sure how what the problem is here, is this feature still not supported in Azure Synapse Analytics (Azure DW) when it is already available in MS SQL Server 2019? Any ideas are welcome.

1
external-tablesazure-sql-data-warehousepolybaseexternal-data-sourceazure-synapse
Any update on this @Ani? – wBob

1 Answers

0
votes

Polybase has different versions across the different products with different capabilities. Most of these are described here:

Polybase products

The ability to connect to Oracle is only present in the SQL Server versions, currently 2019. The documentation is quite clear that is only applies to SQL Server and not to Azure Synapse Analytics (formerly Azure SQL Data Warehouse):

Polybase and Oracle

https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-configure-oracle?view=sql-server-ver15

In summary, Azure Synapse Analytics and its version of Polybase does not currently support to access external Oracle tables at this time.