Skip to Content
Technical Articles
Author's profile photo Klaus Liu

HANA SDA configuration for MSSQL

SAP HANA smart data access allows you to access remote data as if the data was stored in local tables in SAP HANA, without copying the data into SAP HANA.

In this blog, we will show you how to set up SDA for SQL SERVER 13. The steps are also same for SQL SERVER 12.

 

  1. Download and install Microsoft ODBC driver

    Follow the introduction as the following Microsoft official link(Choose the commands according to your OS):
    https://docs.microsoft.com/en-gb/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017#microsoft-odbc-driver-131-for-sql-serverUsing the “root” user, run the following red commands to install the Microsoft ODBC driver in your HANA server if your HANA OS is SUSE Linux Enterprise Server 12

    sudo su 
    zypper ar https://packages.microsoft.com/config/sles/12/prod.repo
    exit 
    sudo ACCEPT_EULA=Y zypper install msodbcsql 
    # optional: for bcp and sqlcmd 
    sudo ACCEPT_EULA=Y zypper install mssql-tools 
    echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile 
    echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc source ~/.bashrc 
    # optional: for unixODBC development headers 
    sudo zypper install unixODBC-devel

    After the installation, there will be /opt/microsoft/msodbcsql/lib64 generated. And the driver libmsodbcsql-13.1.so.9.2 should be in this dirctiory.

  2. Install and test using unixODBC to check if the ODBC connection can be set well.

    Actually during setting up SDA, we can skip this step in configuring the SDA to MSSQL. But we strongly recommend you installing the unixODBC to test if the ODBC connection can be set correctly. Only when the unixODBC test works, and then the SDA will work.

    a) Go to unixODBC offical page  http://www.unixodbc.org/ -> Download, follow the steps in the page to install the unixODBC.
    Once the unixODBC is installed, check if the drivers installed properly.

    >> isql --version

    The output should be the version of unixODBC “unixODBC 2.3.1”

    b) Using command “odbcinst -j” to know where the odbc.ini is located, and then we need to create the DSN in the odbc.ini file to set up the odbc connection.

    <hostname>:~ # odbcinst -j
    unixODBC 2.3.1
    DRIVERS............: /etc/unixODBC/odbcinst.ini
    SYSTEM DATA SOURCES: /etc/unixODBC/odbc.ini
    FILE DATA SOURCES..: /etc/unixODBC/ODBCDataSources
    USER DATA SOURCES..: /root/.odbc.ini
    SQLULEN Size.......: 8
    SQLLEN Size........: 8
    SQLSETPOSIROW Size.: 8

    Configure the odbc.ini file “/etc/unixODBC/odbc.ini” :
    The format is like below:

    [<DATABASE_NAME>]
    servernode = <client_ip_address>:<sqlserver_port>
    In my example, the DSN is like the following:
    [MSSQL]
    Driver= /opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2
    Server=<SQLSERVER_IP_ADDRESS>,1433

    The port number got from “Sql Server Configuration Manager” -> Protocols for MSSQLSERVER -> TCP/IP -> TCP Port :

    c) Test the ODBC connection by the following command:

    isql -v <DATABASE_NAME> <user> <password>

    Once this has been established successfully, then we can configure the SDA much quickly.

  3. Set up SQL Server ODBC Driver

    a) Change to <sid>adm $HOME directory
    In /usr/sap/<SID>/home create file “.customer.sh” by <sid>adm userAdd the following to the file “.customer.sh”

    export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/microsoft/msodbcsql/lib64
    export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib64

    Then source file “.customer.sh” .

    And check if the path has been added to the enviroment LD_LIBRARY_PATH

    echo $LD_LIBRARY_PATH

    Check if “/opt/microsoft/msodbcsql/lib64” and “/usr/local/lib64” has been added to the environment LD_LIBRARY_PATH . If not, please restart your HANA instance and check it again.

  4. Create a DSN Entry
    Change to <sid>adm $HOME directory
    In /usr/sap/<SID>/home , create “.odbc.ini” file, the same as we create in /etc/unixODBC/odbc.ini file.[MSSQL]
    Driver= /opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2
    Server=<SQLSERVER_IP_ADDRESS>,1433

    And restart HANA instance to apply the changes to the .INI file.

  5. HANA studio -> Provisioning -> Remote Sources -> New remote source
  6. Save the configuration, then the SDA remote connection is configured successfully.
    And then you could add the virtual table to one schema of current HANA DB.

 

For more other detailed information about SDA, you could refer to the latest HANA Admin Guide.

Assigned Tags

      6 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Mango Yin
      Mango Yin

      Useful, Amazing blogs!

      Author's profile photo Denys van Kempen
      Denys van Kempen

      Hi Klaus,

      Back in 2016 for SAP HANA 1.0 SPS 11, I recorded a video tutorial on this topic, cf. https://blogs.sap.com/2016/04/15/microsoft-odbc-driver-for-sql-server-on-linux-by-the-sap-hana-academy/

      Would you know if anything changed since?

      Thanks

      Author's profile photo Klaus Liu
      Klaus Liu
      Blog Post Author

      HI Denys,

      As far as I know, there is no big change since your then. I provide more information on SQL SERVER 13 driver installation and unixodbc installation.

      Bestregards,
      Klaus

      Author's profile photo Sajesh Kanhirakkadavath
      Sajesh Kanhirakkadavath

      Hi Klaus,

      we configured SDA to MSSQL ,sometime it works as expected but most of the time we are getting the below  error

      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.

      Regards,

      Sajesh.

       

      Author's profile photo Marcin Cieślak
      Marcin Cieślak

      Please be aware that port 1433 is only the default one and may not be correct.

      You might be given the instance name instance and this will determine port by connecting to port 1434/UDP and then figuring out the real service TCP port.

      FreeTDS configuration file reference explains it very well, and here is a working freetds.conf example for two servers: SERVER_ONE is connecting via port 1433, SERVER_TWO is connecting via the yet unknown TCP port.

      [SERVER_ONE]
              host = SERVER_ONE.contoso.com
              port = 1433
              database = DB_Reporting
      
      [SERVER_TWO]
              host = SERVER_TWO.contoso.com
              instance = INST02
              database = DB_Reporting
      
      

      To access SERVER_TWO, one will need to give SERVER_TWO.contoso.com\INST02 as the server name and no port, also for ODBC.

      Figuring out the real TCP port may be needed for firewall traversal, this can be detected using FreeTDS tsql client in verbose mode.

      Author's profile photo Andreas Kuhn
      Andreas Kuhn

      IMPORTANT:

      In a scale-out landscape, install the unixODBC driver on all hosts and make sure all modified files (.odbc.ini, .customer.sh) and LD_LIBRARY_PATH are the same on each host.