Skip to Content
Author's profile photo Werner Daehn

Hana Smart Data Integration – SQL Server LogReader Adapter

This SDI adapter is used to read and load SQL Server data and supports batch and realtime. It is based on the Sybase RepAgent for realtime capture of changes.

SDA usage

The remote source object in Hana is pretty much what one would expect. It asks for the SQL Server hostname and port, login credentials and a database name. The same parameters entered when connecting to a SQL Server database using any other tool.

Note: We talk about the MSsqlLogReaderAdapter here, not the ODBC driver!


But let’s go though the settings slowly.

  • Rest should be self explanatory.The Instance Name is any arbitrary text. The logreader adapter will create a subdirectory with all its settings for this remote source. And since a remote source points to one database of a SQL Server Instance, there might be multiples.
  • Administraton Port is the port to be used by the RepAgent. Again, any will do as long as this port (plus the next higher number) is available. A common choice for the first remote source on this SQL Server instance is 13456, meaning it will be using the port 13456 and 13457.
  • Data Server used by the adapter. Typically the logreader adapter is installed on the SQL Server computer itself. Therefore, as seen from the adapter, the SQL Server instance is installed on the local host. Remember: The communication between Hana and the adapter hapens via the DPAgent. When configuring it, the remote server name was specified.
  • Port Number of the SQL Server instance. The default port for SQL Server’s first instance is 1433. To find out for sure, see below the installation chapter.
  • DAC Port Number: By default one higher than the standard port. Certain SQL Server operations are not allowed via a regular SQL connection, an Direct Administration Communication protocol is required for that. For pure SDA use cases it is actually not used.
  • Database Name with the data. This remote source points to one database of the SQL server instance only, an existing database has to be specified.
  • The Use Remote Database setting is again used for realtime only, it hints to the adapter if the adapter runs on the SQL Server computer or somewhere remote.

To find out the SQL Server Port being used, open the SQL Server Configuration Manager.


With these settings the remote source can be browsed and all virtual table be created.


The SDA part of the adapter is fairly simple. It gets a SQL statement in Hana syntax and translates it into SQL Server syntax, data is converted from source into Hana datatypes.

Hence the adapter supports all typical pushdowns, joins, where clauses, functions,…. really extensive.

It even supports insert/update/delete statements executed against the virtual table.

Installation of the Adapter

The adapter itself is part of the DPAgent installer, but its JDBC driver is missing as SAP cannot distribute software owned by somebody else. Therefore the MSSQLServer JDBC driver in version 4.0 has to be downloaded, extracted and then the JAR file is copied into the <DPAgent>/lib/ folder.

The download page is here:

On the next page the 4.0 version should be selected for download and once available locally, the content can be extracted – the exe files are self packed archives either.

Locate the sqljdbc4.jar file and copy it into the lib folder of the location where the DPAgent was installed – by default that would be c:\usr\sap\dataprovagent.


Now the Adapter can be deployed via the AgentConfig Tool (see here how to install it).



SQL Server supports one method to identify changes in the database, the CDC API. This adds a change table for every source table, thus every change in the original table causes one full row to be written into the change table. And when reading all changes, every single change table has to be queried to find out what kind of changes there had been. For a handful of tables that is okay but scanning 1000’s of change tables every second just to find out that nothing was changed? This is a huge overhead.

SQLServer also supports reading the transaction log, which contain all the changes the database made anyhow. So this would be a much better starting point. The command “dbcc LOGINFO” provides some insight and there are more hidden options (described e.g. here). But this is more for recovery purpose. One major issue with that other than being hard to decode the data is the fact that SQL Server might truncate the transaction log at any time. In case the reading of the log is too slow that would mean data can be lost. Not acceptable of course. But there is another SQL server feature, the Replication option, which works on transaction logs as well and for that Microsoft had to solve the log transaction problem as well – they do not truncate the log until the replication process is done. This proofs the fact that transaction log reading is an option, just very difficult.

Sybase has solved that problem already, hence the Logreader Adapter can utilize the RepAgent code as second option.

So the adapter supports two replication technologies, “Native Mode” means reading the transaction log or “MSSQL CDC Mode” using the SQL Server CDC API.

For the MSSQL CDC Mode nothing has to be done. The adapter does issue the required SQL Server commands and starts reading the changes.

Setting up Transaction Log reading

For the transaction log reading, that is where SQL Server has no direct support and requires a few steps hence. Actually, the documentation describes these steps quite nicely, hence the steps are copied only but augmented with images and hints.

The first step is to create a user called DP_USER and make it a sysadmin.

use master


create login DP_USER with password =‘<password>


use <your database>


create user DP_USER for login DP_USER


EXEC sp_addsrvrolemember ‘DP_USER’, ‘sysadmin’


Aforementioned Direct Administration Connection (DAC) is used by the RepAgent to execute statements not possible via a regular SQL connection, hence needs to be enabled.

Log on to Microsoft SQL Server using the DP_USER login and change the Microsoft SQL Server Remote Admin Connections Configuration option to enable DAC to allow remote connections.

sp_configure ‘remote admin connections’, 1




Verify the remote admin connection string.

sp_configure ‘remote admin connections’


Make the SQL Server log files available to RepAgent

In Windows Explorer, navigate to the sybfilter driver installation directory. This directory is located at <DPAgent_root>\LogReader\sybfilter\system\winx64, where <DPAgent_root> is the root directory of the Data Provisioning Agent installation.

Right-click the sybfilter.inf and select “install” to install the sybfilter driver.

Under any directory <DPAgent_root>\LogReader\sybfilter\system\winx64 create a file named LogPath.cfg.

Add a system environment variable named RACFGFilePath and set its value to the full path of the configuration file.

In Windows Explorer, navigate to <DPAgent_root>\LogReader\sybfilter\bin\, and right-click the sybfiltermgr.exe file and then select “Run as administrator” to start the sybfilter driver management console.

To start the sybfilter driver, enter “start” in this screen.

Add the log file path to the sybfilter driver by typing “add serverName dbName logFilePath” in the sybfiltermgr program. For example, to add log file named pdb1_log.ldf for the database pdb1 on the data server PVGD50857069A\MSSQLSERVER, use this:

add PVGD50857069A\MSSQLSERVER pdb1 C:\Mssql2012\MSSQL11.MSSQLSERVER\MSSQL\DATA\pdb1_log.ldf

Restart Microsoft SQL Server to make the log file readable.

Execute the “check” command in the sybfiltermgr console screen.


Open the Windows Services screen and stop all services related to the database instance, the database, the agent, all…

Right-click your Microsoft SQL Server instance and choose Properties

Under Start parameters, enter -m

Click Start to restart the instance in single user mode

Connect to Microsoft SQL Server using dedicated administrator connection (DAC)

Start SQL Server Management Studio without logging in and select File -> New -> Database Engine Query.

In the Connect to Database Engine dialog box, in the Server name box, type ADMIN: followed by the name of the server instance. For example, to connect to a server instance named ACCT\PAYABLE, type ADMIN:ACCT\PAYABLE.

Complete the Authentication section, providing credentials for a member of the sysadmin group and then click Connect.

In case this connection is the first one the login will succeed and the connection has full control of the SQL Server instance including hidden features. To validate the server truly is in Single User Mode, type

use mssqlsystemresource


In case this hidden database cannot be found, either the server is not in single user mode or the connection is not using DAC.

Copy the script contents from <DPAgent_root>\LogReader\scripts\mssql_server_init.sql into the SQL Server Studio Query screen and execute the script to initialize the server for replication.

Stop and restart Microsoft SQL Server service back to normal mode by removing the -m option from above.

Using the realtime subscriptions

With the adapter being ready, it is just a matter of setting up a realtime subscription for the virtual table and doing something with the data. For this there are multiple options, starting from creating a .hdbreptask to simply replicate a few tables from this source database into Hana or using the previously imported virtual table in a realtime .hdbflowgraph.

Or simply using SQL commands which the UIs do under the cover as well.

create column table t_test1 like v_test1;

create remote subscription s_test1 on v_test1 target table t_test1;

alter remote subscription s_test1 queue;

insert into t_test1 select * from v_test1;

alter remote subscription s_test1 distribute;

select * from v_test1;

select * from t_test1:

In above example a table in Hana is created with the exact same structure and primary key as the source.

Then a remote subscription is created loading all change data into the target table.

With the alter remote subscription .. queue command the adapter is told to start capturing changes in the source.

Then the current data is copied from the source into the target table – else a source update statement would not find a row to update in the target.

And finally with the distribute the changes are really starting to be applied to the target table and from now on the target table will be in sync with the SQL Server table.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Nice post 🙂

      Do you have anything for Oracle log Reader adapter ?? Real-time integration ??

      Author's profile photo Werner Daehn
      Werner Daehn
      Blog Post Author
      Author's profile photo saurabh vaidya
      saurabh vaidya

      Hi Werner,

      This really nice post.

      Even I could understand the step by step ( I am not a Basis consultant) 🙂

      it will be of great help if you could help me on below issue.

      I have 2 VMs one is HANA VM and another is ECC VM (ECC is on SQL server2014). I am trying to get the data of ECC to SDI using "MssqlLOgReaderAdapter", I am getting below error.


      Please guide


      Thanks and Regards

      Saurabh Vaiday

      Author's profile photo Paul Joubert
      Paul Joubert

      I am getting and error when I execute sybfiltermgr.exe ERROR: The config file is inaccessible. I am sure it refers to LogPath.cfg. What might make it inaccessible. Windows authorizations seams to be fine.

      Author's profile photo Charles Archibong
      Charles Archibong

      I had the same error but after including LogPath.cfg in the system variable full path and restarting the machine the error is gone. Some users suggested a restart was not neccesary.

      Author's profile photo Former Member
      Former Member

      Thanks Werner, great article,  I got all the steps working apart from one.  I wonder if you or anyone have seen this error before when connecting an sql2014 db and trying the generate the virtual tables in the remote data sources. 


      Do I not need to add any ID&PW?,  I have tried all sorts of things but cannot get past this.

      I'm only working on the trial HCP account so not important.

      Thanks again Werner.


      Author's profile photo Former Member
      Former Member

      Is this supported in an AlwaysOn configuration, if so do you set up this Adapter on Primary or any of the Secondary Replica's?

      Author's profile photo Former Member
      Former Member

      Hi Werner,

      I a getting below error while i am trying to create remote source using MssqlECCAdapter. Any input would be a great help. Thanks

      SAP DBTech JDBC: [403]: internal error: Cannot get remote source objects: SDA open() error: String index out of range: 0



      Author's profile photo Amarildo Guimaraes De Oliveira
      Amarildo Guimaraes De Oliveira

      HIi Pankaj,


      Did you solve this issue? i have de same message for oracle adapter..




      Author's profile photo Former Member
      Former Member


      We're trying to use replication tasks in HANA via a RDA to CDC-enabled SQL Server to update physical tables in HANA. We have successfully installed the Data Provisioning Agent and the changes to the data are visible in the virtual tables, but replication is not occurring into the physical tables. We're fairly sure that we have the DPA set up correctly (as we can see the changes in the virtual tables) and that CDC is running correctly in SQL Server as the CDC tables are populating, but when executing the replication task with replication behaviour in realtime, not only do we not see replication into the physical tables, it seems to be stopping CDC and appears to delete the CDC tables in SQL Server! Upon checking the repagent.log file the following error occurs:

      I. 2017/02/01 00:53:54.781 INFORMATION com.sybase.ra.ltm.LTM Replication Agent changed to <ADMIN> state.
      I. 2017/02/01 00:53:54.781 INFORMATION Transaction LogReader is starting the <T_LRINIT> Thread.
      W. 2017/02/01 00:53:54.828 WARNING The Replication Agent DDL user is not configured. Therefore, the dsi_replication_ddl configuration in Replication Server must be enabled for DDL replication to succeed.
      I. 2017/02/01 00:53:54.828 INFORMATION Transaction LogReader is starting the <T_LRSENDER> Thread.
      I. 2017/02/01 00:53:54.844 INFORMATION Transaction LogReader is starting the <T_TRANREADER> Thread.
      I. 2017/02/01 00:53:54.860 INFORMATION Transaction LogReader is Replicating logged Transactions.
      I. 2017/02/01 00:53:54.860 INFORMATION Transaction LogReader Internal Thread <T_LRINIT> Exited.
      T. 2017/02/01 00:53:54.969 Device com.sybase.ds.mssql.device.Device Opening device at <D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SON_DB_REPL_log.ldf>.
      E. 2017/02/01 00:53:54.985 ERROR com.sybase.ds.mssql.log.device.LogDevice The log file <D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SON_DB_REPL_log.ldf> is being locked by SQL Server process.
      E. 2017/02/01 00:53:54.985 ERROR com.sybase.ds.mssql.log.device.LogDevice Use sybfilter driver to break the lock first.
      E. 2017/02/01 00:53:54.985 ERROR com.sybase.ds.mssql.log.device.LogDeviceConta Failed to synchroniz log device with message com.sybase.ds.mssql.device.FileLockedException.
      E. 2017/02/01 00:53:54.985 ERROR com.sybase.ds.mssql.log.device.LogDeviceConta Failed to synchroniz log device with message com.sybase.ds.mssql.log.device.SyncDeviceException.
      E. 2017/02/01 00:53:54.985 ERROR com.sybase.ds.mssql.log.MSSqlLogReader com.sybase.ds.mssql.log.device.SyncDeviceException

      Has anyone come across this problem before or know of a solution?

      Author's profile photo Rajendra Chandrasekhar
      Rajendra Chandrasekhar


      Did you find solution for this issue ? Did you try MSSQL CDC Mode Option in the Adapter setting ?

      Author's profile photo Former Member
      Former Member


      I’ve followed the steps and I can setup the remote source, create virutal tables, query them, etc. But, when I get to the final realtime subscription scripts I get the following error


      Could not execute ‘alter remote subscription s_test1 queue’
      Error: (dberror) 256 – sql processing error: QUEUE: S_TEST1: Failed to add subscription for remote subscription S_TEST1[id = 162561] in remote source MYSOURCE[id = 162540]. Error: exception 151050: CDC add subscription failed: Cannot call addSubscription method because the adapter is in FIRST_SUBSCRIPTION_ADDED state


      I tried the steps suggested in this KB article but get the same results.

      Any help is appreciated.






      Author's profile photo Former Member
      Former Member


      I have used mssqlECC Adapter. I am able to connect the on-premise  ECC Utilities system from HCP. I am getting all the ECC related tables but not getting Utilities related tables(data models).

      any help is appreciated.


      Author's profile photo Fabio Brai
      Fabio Brai

      thank you for your post.

      The system gives me the following error when trying to make a new remote datasource:

      "SAP DBTech JDBC: [403]: internal error: Cannot get remote source objects: Adapter validation failed. Failed to validate the version of SQL Server Driver."

      Author's profile photo saurabh vaidya
      saurabh vaidya


      can you please help me with solution, I am also getting same issue.

      I have 2 VMs one is HANA DB VM and another is ECC VM (ECC is on SQL server 2014)


      Author's profile photo Tikkana Akurati
      Tikkana Akurati

      Followed all the steps above successfully. Thank you for the above.

      Our developers use eclipse and they are stuck at below step:

      Select data sources -> New -> Source system :

      Enter BW Project, Name, Description -> Connection Type: SAP HANA Smart Data Access -> Select remote source : able to see here the remote source -> Select remote database : ?? HERE NOT ABLE TO SEE ANY DATABASES??

      Any idea why the databases of the remote source not getting populated in the matching items? (even with ? =any char or * =any string search pattern )?


      Author's profile photo Samuel Juarez
      Samuel Juarez

      Hi Werner, thanks for the post.

      I've tried to setup the scenario with a SQL Server Express Edition, I followed all the steps successfully but when execute a replication task with real time support I have the next error

      Error: XLog failed to automatically execute creation scripts in database <ECC> because: This edition of SQL Server does not support publications. 

      Is it possible to setup a realtime scenario with a Mssql Express Edition?


      Author's profile photo Werner Daehn
      Werner Daehn
      Blog Post Author

      Does not seem like it. The Express Edition does not support CDC.

      Standard Edition and higher but not Web and Express edition.

      Author's profile photo Karthikeyan Rajendran
      Karthikeyan Rajendran

      Nice blog Werner, very detailed as well.

      I have one question, hope you can answer. Our MSSQL database as well as our HANA DB are residing in Azure cloud. The EIM installation and configuration guide says the real time replication isn't supported for Azure and AWS platforms. Do you think we should be able to do a "Real time" replication using MSSQLLogReaderAdapter if we perform the initial setup as per the guide?

      Author's profile photo Werner Daehn
      Werner Daehn
      Blog Post Author

      Let’s look at it from the technical point of view, starting with SQL Server. The LogReader supports two modes, the SQL Server native CDC mechanism and one that reads the database transaction log files directly. The first one needs a couple of special statements to create tables – see SQL Server CDC manual. The other one needs to run a process on the database server, needs to read the database transaction log files and more. In a DBaaS case I would be pretty sure the latter is not supported. But the native CDC method could be worth a try.

      The other extreme would be if you just used an EC2 server and you installed a database there yourself. Then I would see less of a problem as you have full control of the OS.

      The statement that Hana is not supported in the cloud does surprise me, I have to admit. I cannot see a reason for that. The Hana adapter is based on database triggers and as long as you can create tables and triggers (and maybe a schema), that should work. You might want to create a support ticket asking for the reasons why it is not supported, request that it should be supported.

      Author's profile photo yuvaraj Subramani
      yuvaraj Subramani

      Hello ,

      We are setting up the Sybase Replication on MSSQL source database using the similar process.

      Logged in as Dedicated Admin Port with sysadmin privileges & the Replication Agent MSSQL is able to connect to PDB & accessing the mentioned database.

      use mssqlsystemresource



      Post connecting to the DB,when the server initiation script getting executed , facing the below permission issue,


      E. 2019/01/09 20:16:36.772 ERROR Alter database mssqlsystemresource set Read_Write
      E. 2019/01/09 20:16:36.772 ERROR Work is being rolled back because of problems executing script: Unable to open the physical file "D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\mssqlsystemresource.mdf". Operating system error 5: "5(Access is denied.)".
      E. 2019/01/09 20:16:36.772 ERROR XLog failed to automatically execute creation scripts in database <RRI> because: Unable to open the physical file "D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\mssqlsystemresource.mdf". Operating system error 5: "5(Access is denied.)".


      Having Full sysadmin rights for MSSQL Logins.

      Do we require to give full permission for OS level user to the above mentioned file?



      Yuvaraj Subramani


      Author's profile photo Neo Kung
      Neo Kung

      Hello Yuvaraj,

      I have the same issue with you after running initialization sql script.

      What I did is to alter the permission of MSSQL folder.
      In your case, it should be D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL

      Go to that folder and right click. Choose properties and go to security tab.

      You should see MSSQLSERVER with only 3 permissions allowed,
      Read & Execute / List Folder Content / Read.

      Give full permission to MSSQLSERVER  and the script should work.



      Author's profile photo yuvaraj Subramani
      yuvaraj Subramani


      Tried initialization after starting MSSQL server in an elevated command prompt and tried . Initialization was successful.



      Author's profile photo Cassandra Long
      Cassandra Long

      @Werner Daeh

      Thank you for this article, this really covers all you need to get started - I have configured all these steps and the connection works, but how do I build a virtual table to contain the data from the database table - like I would do for the ABAPAdapter. When I look at the Remote Connection, I can't see the table structure, only the trn log. I want to replicate the data from my MS SQL Server database table into a HANA table. Is this possible with this adapter?


      Author's profile photo Adrian Wummel
      Adrian Wummel

      Hello Community,


      We have tried setting up Real-Time Replication from a Microsoft SQL Server 2019 to our SAP HANA System (both on-prem).

      After a few issues we got to the point, where we can set up the Remote Source with a Replication Task and have the initial data load work properly.

      The Real-Time Part was however not working as intended. Changes in Tables on the Source System (MSSQLSERVE2019) were only reflected in the Virtual Table, not in the Replication Table.

      As per I queried the Table REMOTE_SUBSCRIPTION_EXCEPTIONS. The Result pointed towards the Log File in the Data Provisioning Agent. The relevant Content is as follows:



      DPA Error Log


      Just like from the Table REMOTE_SUBSCRIPTION_EXCEPTIONS we can pick up, that the relevant Transaction Log of the SQL Server Database was not found. The highlighted Row likely points to the cause of that Error.

      Apparently, the Process of the Replication Task in HANA is not able to access the Log File, because a different Process was using it. Perhaps there is a Change of Settings to be made with the Transaction Log Files, or with a certain Process that is using it.

      However, from this point on there is no Documentation to be found, as far as I am aware.


      If someone has encountered this Error before, kindly provide me with Insights on how to resolve this.


      Best Regards,

      Adrian Wummel