Skip to Content

Smart Data Access (SDA) is slightly older feature in HANA .I recently got a chance to setup for SQL Server12 . I read lot of documents/blogs/you tube videos but still running into issues and couldn’t implement in first go. It took me two or three iterations to get it installed correctly. I decided to put exact steps one should follow to implement SDA for SQL Server. I believe with these steps one should be able to install SDA for SQL Server in first go without wasting much time.


SDA Definition:

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 from data sources. It is possible to write queries in HANA combining Hana native tables and virtual tables.


SDA Architecture:


/wp-content/uploads/2016/01/fig_1_873515.png


We are configuring HANA Server SH1 and connecting to SQLSERVER and another HANA Database.


Steps to Configure SDA :

a.      Linux Users

b.      Download / INSTALL unixODBC Driver Manager

c.      Download / INSTALL SQL Server Drivers

d.      SETUP .odbc.ini file

e.      Testing

f.      Setup SDA for SQL server in HANA STUDIO



a. Linux Users

I am sure everyone knows about users but just wanted to reiterate .When you install Hana with root user it automatically creates a user <SID>User. If you don’t know the <SID>User you can run following command and know the <SID>User.


>> cat /etc/passwd

Root User:

With root user you will setup complete configuration of HANA System that including  UnixODBC and other ODBC drivers.

<SID>user:

<SID>User may not have permissions to setup configurations files unless he is given special permissions. Generally all HANA configurations are done with root user.

Hana Studio is owned by <SID>User  so some of the configurations you did as root user the others users in the system can’t see those configuration files .

 

b. Download / INSTALL unixODBC.2.3.0 drivers Manager.

      

Download the update the unixODBC driver to 2.3.0

http://www.unixodbc.org/


/wp-content/uploads/2016/01/fig_2_873517.png

Once you download this driver (unixODBC-2.3.0.tar.gz)  move it to HANA Server (can use FileZilla or other file transfer s/w) .Go to the directory when you have copied this file.

Login as root user and go to the directory when you copied the drivers.

>>  gunzip unixODBC-2.3.0.tar.gz

>>  tar xvf  unixODBC-2.3.0.tar

>>  ls 

 

Will show you the unixODBC-2.3.0 folder , type the following command to install Unixodbc drivers .

>> cd unixODBC-2.3.0

>> ./configure

>> make

>> make install

Once the making of binaries are done check if the drivers installed properly.

>> isql –version

The output should be unixODBC 2.3.0


c . Download /Installing SQL Server Drivers :

Download MS SQL SERVER Driver for Suse Linux:


(google for other flavors of Linux drivers for SQLSERVER)

http://www.microsoft.com/en-us/download/details.aspx?id=34687


/wp-content/uploads/2016/01/fig_3_873521.png

Once you download , copy the SQL server drivers to HANA Server ( May be at same location where you downloaded /installed UnixODBC file using filezilla  or other file transfer software)

INSTALL MS SQL server drivers:  unzip the file:

Login as root user

>> gunzip msodbcsql-11.0.2260.0.tar.gz

>> tar –xvf msodbcsql-11.0.2260.0.tar

This will install a folder with msodbcsql-11.0.2260.0 and also it will install in /mnt file directory

to check whether it installed SQL server drivers properly check the following path sql odbc directory.

>> cd /mnt/drivers/msodbcsql-11.0.2260.0

Verify the version and install:

>> ./install.sh verify


/wp-content/uploads/2016/01/fig_4_873523.png


>> ./install.sh install


/wp-content/uploads/2016/01/fig_5_873525.png

Check again to see if it’s installed.

>> ./install.sh verify

/wp-content/uploads/2016/01/fig_6_873526.png


d. SETUP .odbc.ini file

Check the odbc.ini file by using the command

Login as root user

>> odbcinst –j

/wp-content/uploads/2016/01/fig_7_873527.png


Odbc.ini file is visible at two locations .One at root directory and one at /etc/unixODBC/odbc.ini

Note: if you want all the users to have access to .odbc.ini file then add config in root user and add entries in “/root/.odbc.ini” file

>> vi  /root/.obdc.ini       — Open the file and enter following and save it

—————————-  MS SQL SERVER DRIVERS ————————–

[MSSQL]

Server=SQLSVR,1433

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

———————————————————————————————-

e.  TESTING

Login to Hana Server with Root User (in Putty)  and test connectivity for Sql Server :

Login As root user

>> isql  -v  MSSQL sa  Welcome1


/wp-content/uploads/2016/01/fig_8_873528.png

This works for root user .


f.   Add data source from HANA studio

/wp-content/uploads/2016/01/fig_9_873529.png

  1. Error : SAP DBtech JDBC L403) Internal error Cannot remote source objects [unixODBC] Data Source name not found and no default driver specified.

Is you see the above error which tells there is a problem with unixODBC drivers but the error message is misguiding .This means the  HANA Studio cannot  see some of the configuration files in HANA HOME Directory.Remember .Hana Studio is owned by <SID>USER..

Do the following

Log in HANA Server as root user in putty

>> cp /root/.odbc.ini  /usr/sap/<SID>/home    (ex:  cp /root/.odbc.ini  /usr/sap/SHI/home)

Logout of Hana Server and come to HANA Studio and try to connect SQL Server .I got success when I do this process.

   

Configuring  Other Hana Server (Server sid name : FDC )

Login to HANA Server (SH1 ) as root user

>> vi /root/.odbc.ini

—————————-  MS SQL SERVER DRIVERS ————————–

[MSSQL]

Server=SQLSVR,1433

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

——————————— HANA SYSTEM (FDC) —————————————

Server=FDC: 30015

Driver=/usr/sap/FDC/hdbclient/libodbcHDB.so

>> cp /root/.odbc.ini  /usr/sap/SHI/home ( For HANA Studio to see this file )

Connecting Other Databases:  Will update this section when I configure .

 

Conclusion:

Now we have connected to SQL Server and different HANA Server. This worked for me and hope it works for you as well in your first go. Whenever I add other databases as sources I will update the process with my findings in this blog and thanks for reading my blog. 🙂

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Bhamini Jha

    Thankyou Shivaji , This is a really good post! Many things that are missing in other documents are covered here . I still haven’t completed the configuration on my end, but this has helped me 🙂

    (0) 
  2. Khardu Baba

    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

    (0) 

Leave a Reply