OAC fails to connect Azure SQL Data Warehouse (Synapse)

Question

Summary

OAC can connect to Azure SQL server not SQL datawarehouse (Synapse)

Content

OAC is not able to connect Azure Synapse data warehouse either through direct connection or through data gateway.

With direct connection by white listing IP, we get following error

[ODBC SQL Server Wire Protocol driver][SQL Server]Setting QUOTED_IDENTIFIER to 'OFF' is not supported..

With data gateway log level high, we get error like ..

020-04-30 19:15:52 FATAL datasource:141 - FAILED_TO_PARSE_TOKEN [JDSError : 4103] 4103 : Failed to parse the security token:

Cause - 4103 : Failed to parse the security token:

Action - 4103 : Failed to parse the security token:

oracle.bi.datasource.service.adf.security.BIADFTokenParser.parse(BIADFTokenParser.java:110)

oracle.bi.datasource.service.adf.server.DatasourceServlet.dumpRequest(DatasourceServlet.java:221)

oracle.bi.datasource.service.adf.server.DatasourceServlet.doGet(DatasourceServlet.java:429)

oracle.bi.datasource.service.adf.server.DatasourceServlet.doPost(DatasourceServlet.java:740)

javax.servlet.http.HttpServlet.service(HttpServlet.java:707)

Version

Data gateway 105.5

Steve F-2 · Answer

To add, the message is clear:

QUOTED_IDENTIFIER to 'OFF' is not supported

This is not supported by DV connections

Set QUOTED_IDENTIFIER to 'ON'

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql?view=sql-server-ver15

Richard Jacobs · Answer

As per the OAC documentation whilst Azure SQL Server is a supported data source Azure SQL datawarehouse (Synapse) is not.