Technical Articles
SAP Analytics Cloud – Connect MS SQL Server Versions, Workaround, Setup, Live & Import Connection
Introduction
MS SQL Servers is a supported data source in SAP Analytics Cloud. Import connection is available via SAC and if you are interested in Live connection then go option is available connection via HANA SDA
This blogpost shows a simple way to go around agent validation rule and connect SAC to newer / MS SQL Server 2019. This validation rule only allows specific versions of SQL servers. For instance at the moment of writing this blogpost MS SQL Server 2019 is not yet added as supported DB version. You can check the entire list from this link
Supported SQL Server Versions
Available Connection Option
- Import Connection – SAC Import Connection To MS SQL Server This requires Cloud connector and Agent installation and configuration as prerequisite.
- Live Connection via leveraging HANA SDA as middle layer – SAC Live Connection To MS SQL Server This required HANA SDA setup and Creation of Calculation view on top of virtual table in HANA
We are going to focus on import connection here.
Prerequisite
- Installed Cloud Connector : Installing the SAPCP Cloud Connector Or as part of SAP Analytics Cloud Agent Simple Deployment Kit.
- Installed Cloud Agent : Installing SAP Analytics Cloud Agent Or as part of SAP Analytics Cloud Agent Simple Deployment Kit.
- Installed JDBC driver
- Configured Cloud Connector : Configuring the SAPCP Cloud Connector
- Configured Cloud Agent : Configuring SAP Analytics Cloud Agent
- Create driverconfig.properties file (this is where we mention unsupported SQL version with supported name)
- Set environment variable : SAP_CLOUD_AGENT_PROPERTIES_PATH with full path of driverconfig.properties file.
Issue
If you try to connect a unsupported SQL DB then you will notice that the server name will not appear on SAC side. Even though you follow all (7 steps) the steps mentioned above.
Validation rule filter out any unsupported server via their name mentioned in driverconfig.properties file.
So if you maintain something like below
Notice how system removes MS SQL Server 2019 entry and shows rest
System is filtering out based on the a predefined supported DB list.
Workaround
Step 6 above requires us to maintain a list of server names and their driver location. All we need to do is to replace MS SQL Server 2019 with a supported name (in this case we use MS SQL Server 2016). With a simple name change now you can connect to an unsupported / new version of SQL Server (2019).
SAC Connection list
Other Issue
If you are able to see connection in SAC though none of the DB tables appears, then try updating driver to latest version. We found that SQL server 2019 works quite well with 8.4 driver (or higher).
References
- SAP Analytics Cloud: Expand Live Data Connectivity to Heterogenous Data Sources via SAP HANA
- Import Data Connection to an SQL Database
- SAC: Import data from Database SQL Server 2019
- Supported SAC Connections
Disclaimer:
Please be informed, details shared in this blogpost are based on personal observation(s) and experiences, around product(s) involved. This is no way can be considered as directive or recommendation from SAP. For official guidance and instructions, please check SAP documentations. Beside statements/observations (shared), can be time dependent and change over time. This is general behavior of all IT product, as IT solutions changes over time and this product(s), is no exception.
Thanks for for your time. Feel free to leave comments and share.
Thanks for sharing.
Sometime back I tried this on Oracle 19c, after seeing SAC: Import data from Database SQL Server 2019 and it worked.
Thanks,
Ashok
Thanks! I am yet to test this with Oracle.
Issues mainly revolve around driver compatibility.
Hi Ashok,
We are struggling to connect SAC to the Oracle 19C database. Can you please tell us how you were able to make it work? Since Oracle 19C is not listed in the JDBC driver properties file, we tried(but were unsuccessful) with Oracle JDBC driver version 8.
Thanks,
Manohar Poolla.
Thanks for sharing the trick.
Was looking for something for Oracle DB connection to SAC.
Thanks.
Viraj
Good morning folks:
I am wondering, what are the limitations when using SAC with a SQL connection in particular Freehand SQL?
Can I write any SQL statement beginning with a SELECT statement? Or are there limitations with respect to the WHERE, GROUP BY, HAVING SQL clause in a SELECT statement?
Thanks,
Andreas J.A.