SAP HANA Cloud federation to on-premise Microsoft SQL Server Instance
Shorter: When creating SAP HANA Cloud remote sources to on-premise Microsoft SQL Server database instances, those instances may be managed by the Microsoft SQL Server Browser service and the standard port of 1433 should not be specified for a remote source.
I learned something new the other day about creating remote sources in SAP HANA Cloud when connecting to an on-premise Microsoft SQLServer instance. Specifically: there are configurations where you should not specify the standard port of 1433 on the remote connection.
- We have an SAP HANA Cloud trial instance
- We downloaded and installed the SAP SDI Data Provisioning Agent on a stand-alone Microsoft Windows server
- We connected the SAP SDI Data Provisioning Agent to our SAP HANA Cloud instance using the following agent CLI command:
- We downloaded and installed the Microsoft SQL Server JDBC driver into the SAP HANA SDI Data Provisioning Agent “/lib” directory.
- We have an on-premise Microsoft SQL Server installation with the Microsoft SQL Server Browser service managing (this is very important) connections to database instances
- Our Microsoft SQL Server has more than one instance, our target instance is \OIR
Until presented with this particular set of configuration values, we had successfully created SAP HANA Cloud remote sources for multiple other Microsoft SQL Server databases. For these successful remote sources, we simply specified the hostname and the default Microsoft SQL Server port of 1433 and everything worked as expected. When we started working with a new group of users with their own Microsoft SQL Server environment, we immediately got authentication errors (highlighted in yellow):
While troubleshooting the error, we made the mistake of focusing on the specific message, i.e., “Error: Login failed for user.” We spent a good amount of time validating the hostname, username, and password values for the Microsoft SQL Server instance. To ensure there were no firewall issues, we also created and successfully tested ODBC connections on the SAP SDI Data Provisioning Agent server. We also looked in the SAP SDI Data Provisioning Agent log file to see if there were more clues about the issue; the messages in the log were the same as we saw in GUI.
We stumbled on the answer by accident while doing an endless cycle of testing. By accident, we forgot to put a port value into the remote connection:
Instantly, the remote connection started working and we could see the remote content:
Here’s the key: when Microsoft SQL Server is running multiple instances on the same host, specifying the default port of 1433 is unlikely to connect to the correct instance. Going back to our connection string, mssql.server.sap.com\OIR, notice the \OIR suffix. This suffix notifies the JDBC driver to ask the Microsoft SQL Server Browser service for the correct port to use for the \OIR database. In other words, by specifying port 1433, we were connecting to the wrong instance. Technically, the login error is correct: we were using username and password values on the wrong instance/database.
What we deduced is that by including our instance specification of \OIR, we are asking the JDBC driver to use the default port of 1434 to connect to the Microsoft SQL Server Browser service. Normally, the browser service returns the port for the \OIR instance and the connection is established. By specifying both an instance (\OIR) and the default port of 1433, we overrode the browser service and tried to connect to the Microsoft SQL Server database listening on port 1433; this is not our target instance and our credentials failed.
Please refer to the Microsoft SQL Server documentation for details on using instance specifications and the default behavior of the connection life-cycle in a JDBC environment.