Skip to Content
Technical Articles
Author's profile photo Debjit Singha

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%20SQL%20Server%20Versions

Supported SQL Server Versions

Available Connection Option

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

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.

Assigned tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Ashok Sharma
      Ashok Sharma

      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

      Author's profile photo Debjit Singha
      Debjit Singha
      Blog Post Author

      Thanks! I am yet to test this with Oracle.

      Issues mainly revolve around driver compatibility.

      Author's profile photo Viraj Saha
      Viraj Saha

      Thanks for sharing the trick.
      Was looking for something for Oracle DB connection to SAC.

      Thanks.

      Viraj

      Author's profile photo Andreas J A Schneider
      Andreas J A Schneider

      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.