Connecting SAP HANA 1.0 to MS SQL Server 2012 for Data Provisioning
There are several Data Provisioning techniques available within SAP HANA. These are “Smart Data Access” (SDA) and “Smart Data Integration” (SDI). Also available is the Hadoop Integration. This Document will cover the connectivity between SAP HANA 1.0 and MS SQL Server 2012 for SDA and SDI.
In the nature of things it is quite tricky to connect a Linux based Application (In our case SAP HANA 1.0) to a Microsoft Windows based Application (In our case MS SQL Server 2012). The official Documentation guides you to the right direction. But, as so often, it tells only half the truth. This Document will show you the rest of the required Information.
Please find here some Information before we start. These Information are Resources, Guides, Links, etc.
This Documents excludes the process of installing and configuring SAP HANA and MS SQL Server 2012. Please consult the official documentation for the correct process.
1.2 Software Versioning
The following Software and its Versions are used:
– SAP HANA 1.0 SPS10 (Rev. 102) on SUSE Linux Enterprise Server 11.3 for SAP
– Microsoft SQL Server 2012 Express Edition on Microsoft Windows Server 2012 R2
– AdventureWorks DW 2012 Sample Database for Microsoft SQL Server 2012
– unixODBC Manager 2.3.0
– Microsoft ODBC Driver 11 for SQL Server – SUSE Linux CTP
1.3 Documentation and Download
The following Documentation helped during the whoe process:
The SAP HANA 1.0 SPS10 Administrators Guide (Page 920)
The “Install Instructions” section of the Microsoft ODBC Driver 11 for SQL Server – SUSE Linux CTP
Download Microsoft® ODBC Driver 11 for SQL Server® – SUSE Linux Community Technology Preview from Official Microsoft Dow…
The unixODBC Manager 2.3.0
Your Downloads should look like this
The following is an Assumption that needs to be considered prior of following these Instructions:
– SAP HANA is installed and working properly
– MS SQL Server is installed and working properly
– Access (root, sidadm/Administrator) to both Hosts is given
– Both Hosts can communicate with each other
– No Firewalls are blocking the connection
– The Microsoft ODBC Driver 11 for SQL Server – SUSE Linux CTP Driver and the unixODBC Manager 2.3.0 is downloaded somewhere
– The System Requirements for the Microsoft ODBC Driver 11 for SQL Server – SUSE Linux CTP have been met. Please check the “System Requirements” section of the Driver for more Information.
2. Installation, Configuration and Testing
In this Chapter you will find the Installation and Configuration steps for the Microsoft ODBC Driver 11 for SQL Server – SUSE Linux CTP. You will also find some steps to test the Installation outside SAP HANA.
2.1 Installation of the unixODBC Manager 2.3.0
First we start with the Installation of the unixODBC Manager 2.3.0.
a. Log In as root.
b. Remove any older Version of the unixODBC Manager.
c. Extract msodbcsql-11.0.2260.0.tar.gz.
tar -xvf msodbcsql-11.0.2260.0.tar.gz
d. Navigate to the msodbcsql-11.0.2260.0 Folder.
e. Start the Installation of the unixODBC Manager:
f. Type “YES“.
g. The Result should look as follows:
h. Navigate to the Folder which is highlighted in the lower red rectangle.
i. Type “make install“.
j. The Result should look as follows:
The installation of the unixODBC Manager 2.3.0 is completed successfully.
2.2 Install the Microsoft ODBC Driver 11 for SQL Server – SUSE Linux CTP Driver
We now continue and instakk the Microsoft ODBC Driver 11 for SQL Server – SUSE Linux CTP Driver.
a. Navigate to the msodbcsql-11.0.2260.0 Folder.
b. Verify if your SAP HANA Host completes all Prerequisites:
c. The Result should look like this:
d. Run the Installation with:
e. Scroll down and type “YES“.
f. The Result should look like this:
The Installation of the Microsoft ODBC 11 for SQL Server – SUSE Linux CTP Driver has been completed successfully. the Driver has been installed to the location “/opt/microsoft/msodbcsql“.
2.3 Configure the Microsoft ODBC Driver 11 for SQL Server – SUSE Linux CTP
Now we can go ahead and configure everything after the Installation.
a. Navigate to “/opt/microsoft/msodbcsql/lib64“.
b. Copy the File “libmsodbcsql-11.0.so.2260.0” to your SAP HANA Directory.
cp libmsodbcsql-11.0.so.2260.0 /usr/sap/HA1/HDB01/exe
c. Navigate to “/etc“.
d. Open the File “odbc.ini” via:
e. Paste the following content after you adjusted it your your Environment:
f. Save and Close the File.
g. Log In as sidadm.
h. Navigate to your Home Directory and open your Profile File.
i. Create the ODBCINI Environment Variable:
j. Save and Close the File.
k. Log Off as sidadm and Log In back again.
l. In order that your SAP HANA Instance takes notice of this Environment Variable you need to restart your Instance.
The configuration ended successfull.
2.4 Test the connectivity
In the next Step we will test the connectivity outside SAP HANA. If it doesnt run on OS Level, it will never run on SAP HANA Level.
a. Log- In as sidadm.
b. Navigate to “/opt/microsoft/msodbcsql/lib64/“.
c. Check the Library deoendency of the “libmsodbcsql-11.0.so.2260.0” file with:
d. The Result should look as follows and you should not see any “not found” entry:
e. Test the connectivity with the “iusql” command from the unixODBC Manager:
iusql -v <DSN> <USERNAME> <PASSWORD>
f. The Result should look like this:
g. By typing “help” we get a list of the content from the AdventureWorks Database:
h. Type “quit” to leave the Application
The Test run successful.
3. Connect SAP HANA to MS SQL Server 2012
Finally we are able to connect our SAP HANA Instance to the MS SQL Server 2012 and import a Table.
3.1 Connect SAP HANA to MS SQL Server 2012
First we connect the two Applications.
a. Launch the SAP HANA Studio.
b. Log In to your Database.
c. Expand “Provisioning“.
d. Right click “Remote Sources” and select “New Remote Source…“.
e. Enter the required Fields:
Please note that “Data Source Name” must match with your DSN entry in the “odbc.ini” File. the DSN is in between the “[ ]“.
f. Save your Changes.
g. You should see the following Result:
h. Click on “Test connection“
i. The Result should look as follows:
The connection to the Applications has been established successful.
3.2 Import a Table
At the End we will import a MS SQL Server based Table to a SAP HANA Schema.
a. Expand your Remote Connection.
b. In our case expant “AdventureWorksDW2012“.
c. Expand the “dbo” Schema. You will see all available Tables:
d. Right cklick the Table you wish and select “Add as Virtual Table“.
e. Give it a Name, select your target Schema within SAP HANA and click “Create“.
f. Click “OK“.
g. Navigate to the Tables of your selected Schema.
h. Right Click your imported Table and select “Open Data Preview“.
i. You will see the MS SQL Server 2012 Data inside the SAP HANA Studio:
The import process completed successful.
Now you can continue to import more Tables and proceed with your Development.
NOTE: In the SQL Server Management Studio Activity Monitor you can now see our open Connection
Find here some Appendix Informations that have been gathered over the Time.
4.1 Appendix 1 – Trace the Driver
If you face Problems during the testing, you can trace the Microsoft ODBC 11 Driver. How you do that can be found here:
Data Access Tracing with the ODBC Driver on Linux
4.2 Appendix 2 – SAP HANA Multi Node Deployment
In a multi Node Setup of SAP HANA you have to install, configure and test the Driver Installation on each Node.
4.3 Appendix 3 – odbc.ini Sample File
In the Attachment you will find a Sample odbc.ini File. This is a very basic one bot does the trick for first connectivity.
If you have useful hints which other Parameters should be added, please feel free to post them here.
Please be reminded that you have to rename the attached File from “odbc.txt.zip” to “odbc.ini” and place it in “/etc“.
I followed all the steps from this blog. Everything looks good. All tests were successful.
But when i expand dbo schema i am not getting any tables list.
i would recommend you open an Incident with the SAP Support or a Thread in the HANA Forum for a greater audience.
great step-by-step explanation. You mentioned an attachment - but I couldn't find it.
"..In the Attachment you will find a Sample odbc.ini File..."
Could you please add this? We have some problems with HANA connect to Azure, and we
have no reference/comparison for a working odbc-file ...
Thanks and cheers
I am trying to install the Microsoft's driver on SLES 12 SP1 on which SAP HANA 1.0 SPS12 is also installed. Based on Microsoft's instructions, the installation is done by zypper and the procedure, at the present time, downloads and installs msodbcsql version 13.0.1.
Before installing, however, the instructions require the removal of the package "unixODBC" which in turn require the removal of the packages "patterns-sap-hana", "java-1_7_1-ibm-jdbc" and the pattern "sap-hana".
Isn't this a conflict? Will the removal of the above packacges influense the operation of SAP HANA?
On the other hand, version 11 of the msodbcsql does not have SUSE 12 SP1 as supported OS.
Hello Sebastian hello Stelios,
I managed to install the driver on SLES11.3 successfully, but I am facing the same issues here - on a plain SLES12.1 installation these packages can be installed without any issues.
As per description the “patterns-sap-hana” only tells the SLES that we have a hana installation. The “java-1_7_1-ibm-jdbc” only provides a "libJdbcOdbc.so". And the unixODBC component provides more and bigger libraries as the standard SLES package.
Has anyone found a solution here?
Thank you for your blog, we managed to successfully configure each step up until 3.1.g while testing the connection from HANA to the remote SQL server we were unable to connect to the remote source. error below:
Even though the odbc.ini file was in the location specified /usr/sap/HDB/HDB02 we continued to get an error. To resolve we renamed the odbc.ini file with a dot.
Then we were successfully able to connect to the remote source and continue with adding virtual tables.
Anyone been able to get this to work with Windows AD authentication? Most of our SQL Servers do not use SQL Server IDs 🙁
First i would like to thank your for the detailed tutorial. It is very clear and easy to apply.
I have followed all the steps successfully but when i try to test the connection to sql i get the following error :
hxeconsohana-vm:/usr/local/etc # isql -v SQL sanaelouzza Pink1234
[S1T00][unixODBC][Microsoft][ODBC Driver 11 for SQL Server]Login timeout expired
[unixODBC][Microsoft][ODBC Driver 11 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
[unixODBC][Microsoft][ODBC Driver 11 for SQL Server]TCP Provider: Error code 0x274C
[ISQL]ERROR: Could not SQLConnect
Could you please give me some suggestions !!
I have installed hana express edition on google cloud and i use MS SQL server 2012 which i verified that its port 1433 is enabled
My odbc.ini is :
Please help me and thank you .
check on your SQL Server if remote connections are allowed.
this below should be without quote marks