Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
ShivajiPatnaik
Advisor
Advisor

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:



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/


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


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



>> ./install.sh install


Check again to see if it’s installed.

>> ./install.sh verify


d. SETUP .odbc.ini file

Check the odbc.ini file by using the command

Login as root user

>> odbcinst –j


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


This works for root user .


f.   Add data source from HANA studio

  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. :smile:

3 Comments