Setting up a linked server connection between MS SQL and SAP HANA
You want to use SAP HANA database as a datasource for a MS SQL Server database through Linked Servers.
- SAP HANA database;
- MS SQL Server 2008 and higher;
- Download the SAP HANA Client from the SAP software download center and install where the MS SQL Server instance is installed. This includes the SAP HANA ODBC driver that is needed to connect to the SAP HANA database server.
- Run the SAP HANA Client installation on the MS SQL Server Windows server.
- After the installation has finished open the ODBC Data Source Administrator on the Windows server.
- Open the Drivers tab and check that there is a driver installed with the name HDBODBC
- Open the System DSN tab and add a new System DSN by clicking on Add…
- In the new window click on HDBODBC and choose Finish.
- In the now opening window define a Data Source Name (that will be used later on), Description and enter your HANA Server (hostname or IP) and Port. For example “hanaserver:30015”:
- If you’re connecting to a SAP HANA 1.0 MDC or SAP HANA 2.0, you need to specify the Tenant you’re connecting to, as described in KBA 2278249. Click “Settings …” and add a “Special property setting” with Property: “DATABASENAME” and the Tenant name on Value, example:
- Click on Connect and enter the SAP HANA database username and password that you want to use for the connection. You should receive a popup window with the message “Connect successful!”
- Now open Microsoft SQL Server Management Studio. In the next step you need to create a linked server connection and authentication with the user you used in step 8.
- You can do this by following the steps of this msdn help page https://msdn.microsoft.com/en-us/library/ff772782.aspx (Create Linked Servers (SQL Server Database Engine));
- Or you can run this 2 statements:
@server=’Anynamecanbeused’, — this is just a descriptive name
@srvproduct=’Any name can be used’, — this is just a descriptive name
@provider=’MSDASQL’, –>This is a fixed value as this is the standard name for this type of provider connection
@datasrc=’Datasoucename’; –>Here needs the Data Srouce Name to be entered that was used in step 7
@useself = ‘FALSE’,
@rmtsrvname = ‘Anynamecanbeused’, — You need to use the name that you have used in the sp_addlinkedserver as @server name
@locallogin = NULL
@rmtuser = ‘This is the HANA server username that you use for the connection and which was previously defined in the ODBC Data Source Administrator’
@rmtpassword = ‘This is the password of the user used to connect to SAP HANA’
- Mark both statements (complete text) and execute them. The result must be “Command(s) completed successfully”.
- Accessing SAP HANA database as a datasource is limited to use OPENQUERY. The ‘MSDASQL’ provider limits the usage INSERT, UPDATE and DELETE commands on SAP HANA tables – although they can work under some conditions, they are not supported.
- Using the Multipart Names (SELECT *A nynamecanbeused..”TESTSCHEMA”.”EMPLOYEES”) is not fully supported, as SQL Server doesn’t recognize some of SAP HANA metadata. The following error might occur while reading data:
Msg 7356, Level 16, State 1, Line 4
The OLE DB provider “MSDASQL” for linked server “Anynamecanbeused” supplied inconsistent metadata for a column. The column “EMPOYEEID” (compile-time ordinal 3) of object “”TESTSCHEMA”.”EMPLOYEES”” was reported to have a “DBTYPE” of 16 at compile time and 17 at run time.
- Now you can run HANA SQL commands on the MS SQL Server, you just need to follow the OPENQUERY documentation. For example:
SELECT * FROM OPENQUERY (Anynamecanbeused, ‘SELECT * FROM “TESTSCHEMA”.”EMPLOYEES”‘) SELECT * FROM OPENQUERY (Anynamecanbeused, ‘SELECT * FROM “TESTSCHEMA”.”EMPLOYEES” WHERE EMPOYEEID = ”SF876”’)