SAP Analytics Cloud - Connect MS SQL Server Versions, Workaround, Setup, Live & Import Connection

[DebjitSingha]

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

1. Installed Cloud Connector : Installing the SAPCP Cloud Connector Or as part of SAP Analytics Cloud Agent Simple Deployment Kit.


2. Installed Cloud Agent :  Installing SAP Analytics Cloud Agent Or as part of SAP Analytics Cloud Agent Simple Deployment Kit.


3. Installed JDBC driver


4. Configured Cloud Connector :  Configuring the SAPCP Cloud Connector


5. Configured Cloud Agent : Configuring SAP Analytics Cloud Agent


6. Create driverconfig.properties file  (this is where we mention unsupported SQL version with supported name)


7. 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.