Skip to Content

Trying to connect Hana Express to Microsoft SQL Server

I've worked for days now and have been unable to figure this out. I have Hana Express running in a virtual machine. The goal is to add my SQL server instance to the "Remote Sources" on HXE.

I installed the unixODBC drivers but couldn't find an example of an .odbc.ini file that would connect. I get the 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"

When that didn't work, I tried installing the microsoft ODBC linux drivers. They installed and I'm able to connect to my server using the "sqlcmd" command that comes with the MS tools but I can't connect with either isql or add it as a remote server.

It seems like this should be a common thing for people to do but I've only been able to find peices. For example, the Hana Academe video shows how to connect from an instance of HXE to a different instance but not to SQL Server.

Does anyone have a working HXE configured to connect to and SQL server database that can post the .odbc.ini files and other settings they used to connect?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Feb 10, 2017 at 06:04 PM

    I figured out my issues and have been able to get Hana Express talking to my SQL Server. This is what I learned.

    First, you have to install the unixODBC drivers. I did that through the yast package manager at first. There's an example on the SAP Hana Academe

    https://www.youtube.com/watch?v=Zk_Z4rQDTQ0


    In the process of installing and un-installing various drivers I ran into a problem in YAST where the uninstall was asking me to agree to a license. I couldn't enter anything. It was totally frozen so I had to use zypper to do all the install functions.

    I ended up using the Microsoft ODBC Driver 13 for SQL Server on Linux. There are specific instructions for Suse Enterprise 12 here;

    https://docs.microsoft.com/en-us/sql/connect/odbc/linux/installing-the-microsoft-odbc-driver-for-sql-server-on-linux


    Once that's done there are a couple of files you need to create: odbcinst.ini and odbc.ini. The one that defines the odbc *drivers* is odbcinst.ini and on my HXE system it's located here;

    /etc/unixODBC/odbcinst.ini

    Mine looks like this;

    hxeadm@hxehost:/usr/sap/HXE/home> cat /etc/unixODBC/odbcinst.ini
        [ODBC Driver 13 for SQL Server]
        Description=Microsoft ODBC Driver 13 for SQL Server
        Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.4.0
        UsageCount=1

    Note the part in the brackets []. That's the name that you'll need to refer to in the odbc.ini file. The "Driver" line is the path to the MS odbc driver on my HXE system. On my system the odbc.ini file is located in the same directory as the odbcinst.ini. It follows a similar format to the odbc.ini. Here's mine;

    hxeadm@hxehost:/usr/sap/HXE/home> cat /etc/unixODBC/odbc.ini
        [EFSControls]
        Driver = ODBC Driver 13 for SQL Server 
        Description = Connect to EFSControls
        trace = No
        Server = Corp-EISDB01
        database=EFSControls
    
        [JDE_PRODUCTION]
        Driver = ODBC Driver 13 for SQL Server
        Description = Connect to JDE_Production
        trace = No
        Server = JDESQL01
        database=JDE_PRODUCTION

    The important part in the above file is the parts in the brackets. This is the name you need to enter into the "Remote Sources" in Hana Studio when you set it up. The specific steps are;

    1. In Hana Studio (Eclipse Neon in my case) find the "Provisioning" line with "Remote Sources" under it.
    2. Right click on "Remote Sources" and choose "New Remote Source". This should open the remote source setup form.
    3. Under the "Source Name" enter a name. I used the same name as my databases so the first one was called "EFSControls".
    4. In the "Adapter Name" pull down list choose "MSSQL (GENERIC ODBC)"
    5. Down in the properties in the "Data Source Name" enter whatever you have in the brackets of the odcb.ini file. In my case that's "EFSControls".
    6. Enter the username and password in the appropriate lines. My SQL server uses mixed authentication so it will accept an SQL user id and password. If yours doesn't this may not work.

    Once all that was in, I clicked the execute arrow and got a connection!

    To summarize, the /etc/unixODBC/odbcinst.ini file defines the drivers with names in brackets. the /etc/unixODBC/odbc.ini file defines the data sources in brackes with a "driver" line pointing back to the drivers in the odbcinst.ini file.

    As a final note, most of the examples I found used a hidden .odbc.ini file in the HXE home directory (/usr/sap/HXE/home/.odbc.ini). The difference between that file and the /etc/unixODBC/odbc.ini file is that it's technically only visible to the HXE user where the one in the unixODBC directory is system wide. Using a user-specific ini file is probably a better practice but since my system is dedicated to Hana and isn't used for anything else, I wanted to make the odbc connections available everywhere. Since different processes may login using different credentials it seems like user specific ini has a good chance of not working in many cases. If anyone can explain a benefit to a user specific ini file feel free to comment.

    Add comment
    10|10000 characters needed characters exceeded