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