Connect SAP HANA via ODBC

[Former Member]

Hi,

I installed HANA client on a windows 7(32 bit) OS and I am able to create a DSN via HANA driver and successfully connect to a HANA database located in another Suse Linux server.  But the problem is I need enter the database username and password every time when I want to create the connection. Could you help advise how should I have either database or DSN to remember my username and password?

Thanks

-Jack

Message was edited by: Tom Flanagan

[patrice_vigier]

Hello,

I have the same problem with Foxpro VFP9.

I do enter in the code server ip, login and password but this window keep popping up

It shows what you have entered in your code and you just have to click on "OK"!!

It is not practical....

Any idea how to remove this window?

Thank you

[patrice_vigier]

Connect with Visual FoxPro:

So fare I could only connect without VFP prompting when connecting by:

1) Creating with C:\Windows\SysWOW64\odbcad32.exe (under W7) a DSN with my database parameters.

2) Creating in a VFP database a connection (for example: "connect1") using the DSN created in Windows.

3) Calling the "connect1" from within VFP like this:

OPEN DATABASE "YourVFPDatabaseName.dbc" SHARED

DBGETPROP("connect1","CONNECTION","DispLogin")

Public vconnect

vconnect=SQLCONNECT("connect1")

4)Then now that you are connected you want to upload HANA's data.

The syntax is very important so here is an example in VFP:

SQLExec(vconnect, 'SELECT "CardName" FROM YouTableName.OCRD', 'CursorDestinationName')

Be careful by using the " and ' like I did otherwise it does not work.

5)Do not forget to close the connection :=)

SQLDisconnect(vconnect)

[igor_afanasyev]

This is how connect to HANA via ODBC in C++:

SQLRETURN result = SQLDriverConnect(connectionHandle, GetDesktopWindow(), "DRIVER={HDBODBC};SERVERNODE=ipaddress:port;UID=username;PWD=password", SQL_NTS, nullptr, 0, nullptr, SQL_DRIVER_NOPROMPT);

[Former Member]

Thanks setting SQL_DRIVER_NOPROMPT in creating the connection worked for me.

[lbreddemann]

Hi Jack

storing the ODBC logon data needs to be done by the application that uses it, e.g. in MS Excel you can save the data connection together with the logon/password.

Alternatively, you can use the SAP HANA user store to save system connection details including the password.

See my old blog

for an example.

- Lars

[Former Member]

Thanks Lars for the relay!

Actually i think i did not make myself clearly. The thing is I indeed tried using program(VFP 9.0) to create the connection to sap hana, but still popping up window to ask for the database username and password although i specified them in the coding. 

I also tried sap hana user store to save the info of database server and username/password to a key but that still does not work.

Do you think if there is any security check on hana database side?

here is my VFP program:

----------------------------------------------------------------------------------------------------------------

Public gnConnhandle

gnConnhandle=SQLSTRINGCONNECT("driver=HDBODBC32;server=208.108.13.1:30015;uid=SYSTEM;pwd=123456")

SQLSETPROP(0,'DispLogin',3)

SQLSETPROP(0,"IdleTimeout",0)

IF gnConnhandle<=0 

   =MESSAGEBOX("Connect to database successfully！",0+48,"Message")

ELSE

   =MESSAGEBOX("Fail to connect database！",0+48,"Message")

ENDIF

----------------------------------------------------------------------------------------------------------------

-Jack

[lbreddemann]

Jack Huang wrote:


Do you think if there is any security check on hana database side?

How should I know?

Did you check the indexserver trace or the INVALID_CONNECT_ATTEMPTS view?

- Lars

[Former Member]

Just checked, there is no invalid connect attempts for requested user.

Is there anything i need configure on hana system(not client side) to allow me to create connection via odbc without enter password every time since i have already provided username/password in my odbc connection string.

Thanks for your kind advice.

-Jack

[lbreddemann]

Nope, the SAP HANA database server always needs to get the information from the client.

So the question is: why does the ODBC client seem to "forget" your connection details.

As I have exactly zero (0) clue about FoxPro, I recommend enabling ODBC tracing to see, what information are actually passed to the ODBC driver.

Whenever I used ODBC for SAP HANA and stored the logon data I had not been asked for the information again.

- Lars

[Former Member]

Is it possible that you can give me a sample of your program to connect HANA database via ODBC?

Thanks

-Jack

[lbreddemann]

Right now I only have my R code...

ch<-odbcDriverConnect(connection =

                          "DRIVER=HDBODBC;

                           SERVERNODE=<hostname>:30015;

                           UID=LARS;

                           PWD=**** my password ***"

                     )

Looking back at your connect string I believe that you should have used SERVERNODE instead of SERVER.

- Lars

[Former Member]

I tried JDBC  and everything is ok.

For ODBC, instead of VFP, i was using C# and here is my connection string and output, by the way, my hana instance number is '01' and client OS is windows 7 32 bit.  Can help advise if anything wrong here?

----------------------------------- 

ODBC connection string:

string connectionString= "Driver={HDBODBC32};ServerNode=server IP:30115;uid=SYSTEM;pwd=mypwd;"

OdbcConnection hanaConn = new OdbcConnection(connectionString);

hanaConn.Open();

Output:

'test_hana.vshost.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll'

'test_hana.vshost.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\Microsoft.VisualStudio.HostingProcess.Utilities\9.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.HostingProcess.Utilities.dll'

'test_hana.vshost.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Windows.Forms\2.0.0.0__b77a5c561934e089\System.Windows.Forms.dll'

'test_hana.vshost.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll'

'test_hana.vshost.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Drawing\2.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll'

'test_hana.vshost.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\Microsoft.VisualStudio.HostingProcess.Utilities.Sync\9.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.HostingProcess.Utilities.Sync.dll'

'test_hana.vshost.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\Microsoft.VisualStudio.Debugger.Runtime\9.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.Debugger.Runtime.dll'

'test_hana.vshost.exe' (Managed): Loaded 'E:\Projects\test_hana\test_hana\bin\Debug\test_hana.vshost.exe'

'test_hana.vshost.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Core\3.5.0.0__b77a5c561934e089\System.Core.dll'

'test_hana.vshost.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Xml.Linq\3.5.0.0__b77a5c561934e089\System.Xml.Linq.dll'

'test_hana.vshost.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Data.DataSetExtensions\3.5.0.0__b77a5c561934e089\System.Data.DataSetExtensions.dll'

'test_hana.vshost.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll'

'test_hana.vshost.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Deployment\2.0.0.0__b03f5f7f11d50a3a\System.Deployment.dll'

'test_hana.vshost.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll'

The thread 0x964 has exited with code 0 (0x0).

'test_hana.vshost.exe' (Managed): Loaded 'E:\Projects\test_hana\test_hana\bin\Debug\test_hana.exe', Symbols loaded.

'test_hana.vshost.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll'

'test_hana.vshost.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_32\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll'

The program '[2924] test_hana.vshost.exe: Managed' has exited with code 1 (0x1).

----------------------------------------------

Thanks

-Jack

[lbreddemann]

What about the ODBC trace?

Any current error messages?

Are you still being asked for the logon details?

BTW: NEVER use user SYSTEM for anything else than setting up the system in the first place.

[patrice_vigier]

Jack,

I cannot connect from VFP9 to SAP HANA, did you find a solution ?

[patrice_vigier]

The solution is to take the ODBC driver in 32 bits.

You must create DSN from this program on Windows 7:

C:\Windows\SysWOW64\odbcad32.exe

NOT from

C:\Windows\system32\odbcad32.exe

Then the connection is possible...