Connect ESP to SQL Any with OEM Authentication

[Former Member]

Hi Experts,

we currently develop an Application based on ESP and SQL Anywhere based on the OEM licence model. For that the SQL Any DB is registered for an Application (as described here: DocCommentXchange). Noe all connected applications need to authenticate themselve when connection to that particular DB (see this: DocCommentXchange).

And here is my problem, I could not find any hint in the ESP documents or in SDN on how to set this Option and when (during ESP-Startup? or Application Start?)

Anyone had the same issue?

Thanks in Advance,

Magnus

[Former Member · Accepted solution]

Hi Magnus,

ESP only has access to the SQL Anywhere ODBC Driver (I assume that is how you have ESP configured to connect to SQL Anywhere).  The only way I see how you can do this is to configure the ODBC driver with the "InitString" property:

DocCommentXchange

So I created a DSN like this but I don't have a SQL Anywhere environment to test against:

  dbdsn -y -wu "DENN_cluster2" -c "UID=espdbadm;PWD=Password1;DBN=esp_cluster;ServerName=esp_cluster_esp1_denn00530262a;INT=NO;InitString='SET TEMPORARY OPTION connection_authentication=''Company=MyCo;Application=MyApp;Signature=0fa55157edb8e14d818e'''"

If you are not able to use the "InitString" method, you should probably open a technical support case and we can log a new feature request for you.

Thanks,

Neal

[Former Member · Accepted solution]

Hi Neal,

thanks for your answer, unluckily it did not work out. As it looks, the InitString option is used, but the Authentication then fails.

Maybe there is a wrong charset, as there is an German Umlaut in the Authentication literal . When authenticating the DB I was able to set correct charset and do the authentication then. Is there another option to change the charset used in the odbc connection?

I tested already with isql and iusql, but both failed. When I try iusql -v DSN then first message is "[unixODBC][Sybase][ODBC Driver][SQL Anywhere]Authentication failed", when using the non-authenticated DSN the same error raises when trying to set the authentication.

In the ESP logs I could not find a corresponding message till now, but when I test the application, the SQL Insert fails due to Authentication Failure.

Any further idea? Or shall I try the support?

Thanks

Magnus

[Former Member · Accepted solution]

Hello,

Could you add "Charset=UTF8;" (or whatever character set is appropriate) to the command you use to create the ODBC DSN?  Then you end up with something like this in your ".odbc.ini" file:

[DENN_cluster2]

UID=espdbadm

PWD=Password1

DatabaseName=esp_cluster

ServerName=esp_cluster_esp1_denn00530262a

Integrated=NO

Charset=UTF8

InitString=SET TEMPORARY OPTION connection_authentication='Company=MyCo;Application=MyApp;Signature=0fa55157edb8e14d818e'

Driver=libdbodbc16.so

If that doesn't do it for you, before you log a support case, you might try asking the SQL Anywhere community:

They would be much more knowledgeable about the different combinations for the SQL Anywhere ODBC driver than anyone in the ESP community.  If it comes down to the point where you can't find a configuration of the SQL Anywhere ODBC that works and you need a new feature added to ESP, feel free to log a technical support case.

Thanks,

Neal

[Former Member · Accepted solution]

Hi Neal,

I gave it a try, but same result as before. So I will move to the SQL Anywhere group....

Thanks for advice

Magnus

[Former Member]

The InitString Parameter in the ODBC.ini is the correct solution for this question. All other issues had been based on wrong character sets....