Skip to Content
Author's profile photo Former Member

Configuring Remote Source connection (SDA) from HANA to MSSQL

I went through couple of blogs on the same topic , but didnt found anything specific to MSSQL configuration. Hence, i would like to present it in more details for configuring remote source connection from HANA to MSSQL.

What is HANA Smart Data Access (SDA) ?

SAP HANA smart data access enables remote data to be accessed. It enables remote data to be accessed as if they are local tables in SAP HANA, without copying the data into SAP HANA. Specifically, in SAP HANA, you can create virtual tables which point to remote tables in different data sources. Customers can then write SQL queries in SAP HANA, which could operate on virtual tables.

More details on SDA can be found in the below blog

Smart Data Access- A new feature by HANA

In this blog, I have defined the configuration of connecting the HANA DB to external MSSQL DB (HANA Admin View).

Steps for Configuring:

  1. Download the drivers Unix ODBC & Microsoft SQL
  2. Installing the drivers
  3. creating the .odbc.ini file and setting up the configuration.
  4. Restart the HANA
  5. Configure the remote source connection in HANA Studio.

Download the Drivers :

1. Goto link Download Microsoft® ODBC Driver 11 for SQL Server® – SUSE Linux Community Technology Preview from Official Microsoft Dow…

2. Download the Microsoft SQL Server ODBC Driver 11 for SQL Server by clicking one of the download buttons.

Installing the Drivers:

Install the unixODBC 2.3.0 Driver Manager


1. After downloading the drivers, you will be able to see msodbcsql-11.0.2260.0.tar.gz

2. Copy the downloaded drivers it to Linux server where HANA is installed.

3. Login as root user and decompress msodbcsql-11.0.2260.0.tar.gz –

    tar -zxvf msodbcsql-11.0.2260.0.tar.gz

4. Change to the msodbcsql-11.0.2260.0 directory

    cd ./msodbcsql-11.0.2260.0


5. You can install the driver manager by executing the following command

    ./build_dm.sh


6. Type “YES” to proceed with unpacking the files. This part of the process can take up to five minutes to complete

7. After the script stops running, follow the instructions on the screen to install the unixODBC Driver Manager.

This will complete the installation of UnixODBC drivers.

P.S: check the version of Unix ODBC by executing command >isql –version -> The output should be unixODBC 2.3.0

Install the Microsoft SQL Server ODBC Driver 11 for SQL Server


1. Change to the msodbcsql-11.0.2260.0 directory, where you can run install.sh to install the driver:

    cd ./msodbcsql-11.0.2260.0

2. run the install script

    ./install.sh install

3. After reviewing the license agreement, type “YES” to continue with the installation.

4.Verify that Microsoft SQL Server ODBC Driver 11 For SQL Server was registered successfully:
     odbcinst -q -d -n “ODBC Driver 11 for SQL Server”

5. To check the successful installation of drivers…type the below command

creating the .odbc.ini file and setting up the configuration:

As HANA requires the .odbc.ini file for communicating with remote source MSSQL. Hence, we need to create .odbc.ini file in $HOME directory.

1. Login with sidadm user and create .odbc.ini file -> vi .odbc.ini file

2. Create DSN as MSSQL

3. Server=nave of the server you want to connect and port number

4. Driver = Location of the ODBCsql driver.

P.S: your .odbc.ini file should look like above

5. Goto Home directory and open .sapenv.sh file and add the below line (Update the LD_LIBRARY_PATH)

    export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/opt/microsoft/msodbcsql/lib64

6. Restart SAP HANA

Configure the remote source connection:

From HANA Studio -> Provisioning -> Remote Source -> right click new remote source

Provide username and password of the remote MSSQL server for logon.

P.S: The following privileges are required to manage remote sources:

This completes the Installation and configuration of Remote source connection to remote MSSQL server from HANA.

References & Known Issues:

2141242 – Error “Can’t open lib ‘/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2260.0’ : file not found” when using Smart Data Access

1968653 – Smart Data Access – generic odbc – file not found  

2151882 – ODBC Error Using HANA Smart Data Access to MSSQL 


Download Microsoft® ODBC Driver 11 for SQL Server® – SUSE Linux Community Technology Preview from Official Microsoft Dow…

Regards,

Pavan Gunda

Assigned tags

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

      can you explain how to do this on windows?

      Author's profile photo Lars Breddemann
      Lars Breddemann

      These configuration steps are to be done on the SAP HANA server side, which is always and exclusively on Linux. No need for a "Windows" version of this guide.

      Author's profile photo Michael Healy
      Michael Healy

      For the SERVER configuration, what are the configuration parameter changes if the OS is running on UNIX? Using a comma gives the error:

      SAP DBTech JDBC: [403]: internal error: Cannot get remote source

      objects: [Microsoft][ODBC Driver 11 for SQL Server]Invalid attribute

      value

      Thank you,

      Michael

      Author's profile photo Lars Breddemann
      Lars Breddemann

      Ok, I think you need to clarify here. SAP HANA is available on LINUX only.

      MS SQL Server is available on MS Windows only.

      What part are you installing on UNIX?

      And at what exact step are you getting the above error message?

      Looks like you copy/pasted it from SAP HANA studio; if so, did the step with isql work for you?

      - Lars

      Author's profile photo Michael Healy
      Michael Healy

      Hello Lars, thank you for getting back so quickly.

      So a remote source connection was configured. When the connection is tested an error appears in HANA

      SAP DBTech JDBC: [403]: internal error: Cannot get remote source

      objects:

      [unixODBC][Microsoft][ODBC Driver 11 for SQL Server]Login timeout

      expired

      Indexserver shows:

      [21448]{205343}[264/-1] 2015-07-01 13:21:04.159509 e FedTrace         odbcaccess.cpp(03423) : ODBC error: connected: 0 state: HYT00 code: 0 [unixODBC][Microsoft][ODBC Driver 11 for SQL Server]Login timeout expired

      [21448]{205343}[264/-1] 2015-07-01 13:21:19.169829 e FedTrace         odbcaccess.cpp(03423) : ODBC error: connected: 0 state: HYT00 code: 0 [unixODBC][Microsoft][ODBC Driver 11 for SQL Server]Login timeout expire

      So I looked at the ODBC.ini file and see:

      [MSSQL]

      Server=xqlxxcaxs10xx.hosting.xxx:1433

      I went to change to:

      MSSQL] 

      Server=xqlxxcaxs10xx.hosting.xxx,1433

      I then get the following error:

      SAP DBTech JDBC: [403]: internal error: Cannot get remote source 

      objects: [Microsoft][ODBC Driver 11 for SQL Server]Invalid attribute

      value

      My .odbc.ini in /usr/sap/<SID>/HDBXX looks like this

      [MSSQL]

      Server=xqlxxcaxs10xx.hosting.xxx,1433

      Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2260.0

      Thank you and Best Regards,

      Michael

      Author's profile photo Bojan Dujic
      Bojan Dujic

      Hi Mike,

      I went for

      [DSN]

      ....

      Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2260.0

      Server=hostname

      Port=1433

      Database = <sid_of_MSSQL_DB>

      As Lars already mentioned: does the isql command work from OS level?

      (isql -v <datasourcename> <user> <pw>)

      BR, Bojan

      Author's profile photo Michael Healy
      Michael Healy

      That did the trick Bojan ūüôā . Thanks for that

      Author's profile photo Amit Kumar
      Amit Kumar

      Hi Pavan ,

      I followed the steps as mentioned in the blog . After Configure the remote source connection when i try to expand it it  gives error :

      [403]: internal error: Cannot het remote source objects:[unixODBC][Microsoft][ODBC Driver 11 for SQL Server]login timeout expired

      Can you please help me here .

      Thanks

      Amit

      Author's profile photo Valentino Paderni
      Valentino Paderni

      Hi Pavan,

      i get the scenario working. Driver installed and checked to work with expected DSN. Isql launched and select on a table of databese with schema dbo properly working with expected table content.

      On Hana studio remote data suorce created ad MSSql and ok. Expanding the node i get the db name ad expected. Expanding more i get the schema assigned... Dbo. Expanding dbo I should get table list but NOTHIG. What do I miss?

      thank you for and help

      VAlentino

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      HI Valentino,

      This might be authorization issue. I am able to see the list of tables under DBO.

      Regards,

      Pavan Gunda

      Author's profile photo James Redman
      James Redman

      Hi Valentino,

      Did you get this fixed? I am hitting the same problem of not being able to list the tables.

      Cheers,

      James.

      Author's profile photo Former Member
      Former Member

      Hi,

      I am unable to add SDA connection in SAP HANA Studio. It gives following error:

      SAP DBTech JDBC: [403]: internal error: Cannot get remote source objects: [unixODBC][Driver Manager]Data source name not found, and no default driver specified

      I am able to connect to SQL Server from the Linux, but I have to use sudo.

      hxeadm@hxehost:/usr/sap/HXE/home> isql MSSQLTest sa PassWord15
      [ISQL]ERROR: Could not SQLConnect

      hxeadm@hxehost:/usr/sap/HXE/home> sudo isql MSSQLTest sa PassWord15
      +---------------------------------------+
      | Connected!                            |
      |                                       |
      | sql-statement                         |
      | help [tablename]                      |
      | quit                                  |
      |                                       |
      +---------------------------------------+
      SQL>

      Problem seems to be the permission to access the /opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2260.0

      hxeadm@hxehost:/usr/sap/HXE/home> cat /etc/odbc.ini
      cat: /etc/odbc.ini: Permission denied

      hxeadm@hxehost:/usr/sap/HXE/home> sudo cat /etc/odbc.ini
      [MSSQLTest]
      Driver=ODBC Driver 11 for SQL Server
      Description=SQL Server 2012 test instance
      Server=192.168.1.103,1433
      Database=IDS

      I gave full r.w.x permission to every user in linux, still face the same error while creating SDA connection. I also tried giving root permission to hana user (hxeadm), doing so doesn't allow me even start the database.

      I am trying to get around this error for about ten days, but in vain. Search everywhere on google, watched every single video on youtube. Tried setting up HANA VM 50 times, but failed.

      I can also ping the HANA VM box from Windows and vice versa. I can query the database tables from linux using root user or using sudo with hanauser (hxeadm).

      Any help please? What am I missing?

      Regards,
      khardu

      Author's profile photo Former Member
      Former Member

      hi Former Member,

      we are having SSL enable issue between DPAgent (windows) and SQL Server (windows), its not recognizing SSL secstore even though I imported DPAgent certificate into remote source SQL Server. wondering if you have any BKMs?

      we have SSL between DPAgent & DPServer working fine though.
      Regards
      Chandra

       

      Author's profile photo StraVis Basis
      StraVis Basis

      Hi Pavan/All,

      I have configured  SDA and I am able to connect to my MSSQL using SQLCMD. However when I am using Studio to connect using the provisioning, I have an error while fetching children : The Studio will show Pending for remote source:

      I have also tried with the latest version of Studio no luck:

      An internal error occurred during: "Fetching children ...".
      while trying to invoke the method java.sql.Connection.prepareCall(java.lang.String) of a null object loaded from local variable 'connection'

      java.lang.NullPointerException: while trying to invoke the method java.sql.Connection.prepareStatement(java.lang.String) of a null object loaded from local variable 'connection'java.lang.NullPointerException: while trying to invoke the method java.sql.Connection.prepareStatement(java.lang.String) of a null object loaded from local variable 'connection' at com.sap.ndb.studio.federation.services.ds.impl.sql.FederationSQLService.executeStatement(FederationSQLService.java:181) at com.sap.ndb.studio.federation.services.ds.impl.sql.FederationSQLService.refreshServerFederationVersion(FederationSQLService.java:102) at com.sap.ndb.studio.federation.catalog.navigator.FederationWorkbenchAdapter$1.run(FederationWorkbenchAdapter.java:128) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:55)

      Thanks,

      Abhi

      Author's profile photo Former Member
      Former Member

      Hi Abhi/ StraVis,

      we are also getting the same error while configuring SDA.

      An internal error occurred during: "Fetching children ...".
      while trying to invoke the method java.sql.Connection.prepareCall(java.lang.String) of a null object loaded from local variable 'connection'

      Please let us know the solution for this issue.

      Thanks

      Shiva