cancel
Showing results for 
Search instead for 
Did you mean: 

How do I determine if the server software requires databases to be authenticated?

Breck_Carter
Participant
4,775

Foxhound ships as an unauthenticated SQL Anywhere 11.0.1.2276 database. It is up to the customer to provide a properly licensed copy of SQL Anywhere 11 to run the Foxhound database.

What SQL can I code in the DatabaseStart event to detect if the SQL Anywhere 11 software is an "OEM Edition"?

I want to do this to prevent Foxhound from running on such software.

Will the following work in V11? ...it's hard to experiment when one doesn't own any OEM software :)...

IF STRING ( ' ', PROPERTY ( 'ServerEdition' ), ' ' ) LIKE '% OEM %' 
OR STRING ( ' ', PROPERTY ( 'ServerEdition' ), ' ' ) LIKE '% AUTHENTICATED %' THEN ...

MCMartin
Participant
0 Kudos

@Breck: I am wondering what is the reason behind your attempt to prevent Foxhound from running on an OEM Database Server?

Breck_Carter
Participant
0 Kudos

Because I don't want anyone to get in trouble with respect to license agreements. Foxhound does not ship with an OEM copy of SQL Anywhere. Customers are required to obtain SQL Anywhere on their own. AFAIK it is not proper to use an OEM copy intended for use with some other application, to run this application (Foxhound). Perhaps Mr. Kleisath can jump in with a deeper explanation.

Breck_Carter
Participant
0 Kudos

And the real reason? When a new Foxhound database tries to make a proxy table connection to the old Foxhound database in order to upgrade the data when installing a new version, the connection attempt gets "Authentication failed" when OEM/Authenticated SQL Anywhere software is being used. Prior to that point (normal use as a web server) Foxhound apparently doesn't see the problem, and I want to tell the user about the problem up front.

VolkerBarth
Contributor
0 Kudos

@Breck: The alternative being to add an "Authenticate database/connection" command where the user can supply both values? (Well, surely not helpful in the cases where an OEM customer doesn't have both properties available, more meant for the OEM suppliers when testing...)

Accepted Solutions (1)

Accepted Solutions (1)

Former Member

Breck, your query that utilizes the ServerEdition property will work fine.

VolkerBarth
Contributor
0 Kudos

@Karim: Can you please clarify if the "Authenticated" properties work as expected on non-OEM servers? - See my answer attempt:)

Former Member

Volker, the Authenticated properties are only useful if you have determined that you are using an authenticated server. If the server is an authenticated server, then the authenticated properties will tell you whether or not the database/connection has been properly authenticated. For non-authenticated servers, the thinking is that you can never have a properly authenticated database/connection (due to the fact that authentication is not required) and hence the answer is always NO. The readme excerpt that you indicated below is incorrect and will be corrected.

VolkerBarth
Contributor

@Karim: Thanks for the clarification. So Breck's query is the correct way to test for an OEM version? Wouldn't a server property like "IsOemEdition" be much more appropriate than a error-prone string comparison?

Former Member

Yes Volker, you are probably correct. We will see if we can add an appropriate server property for testing whether or not the server is an OEM edition server.

Chris_Kleisath
Participant

The reason for the string is that there are several different types of servers: OEM, Developer, Educational, Evaluation, Advanced, Standard and Workgroup. A property like "IsOEMEdition" would have to be replicated for each...."IsDeveloper", "IsEducation"... That said, perhaps there is a better approach.

VolkerBarth
Contributor
0 Kudos

@Chris: As long as those strings are documented, that's alright for me. However, I remember a situation where an app should run both on OEM and non-OEM servers (just in our own test servers where we casually had both kinds of servers with V10), and so the app had to behave differently w.r.t. authentication. In that scenario, a property seems "smooth". - I don't know if there are cases where apps would require different behaviour w.r.t. the other types of versions. - But the "string classification" should generally work in all those cases, and properties would just be a nice-to-have.

Breck_Carter
Participant

@Chris: I vote for the string as now defined... as long as the values remain single words, and as long as the values are fully documented in the Help. Right now I'm not sure if all the values are LISTED let alone described individually... the single-Help-topic-for-all-properties is (IMO) an invitation to write sparse descriptions (but that's a separate rant 🙂

Answers (1)

Answers (1)

VolkerBarth
Contributor
0 Kudos

Starting with SA 11.0.1, you can use the Authenticated database and connection property. That should tell you if the connection could be authenticated or doesn't need to be authenticated (value 'Yes) or if the authentication failed ('No'). Note: It does not tell you if the database server is an OEM version (as I had formerly expected).

So, as your app don't try to authenticate, a value of 'No' should tell you that you are running on an OEM version and that there's need for a valid authentication string supplied by the user.

At least that's my understanding, I don't have an V12 OEM edition yet:)


EDIT:

I can confirm Breck's tests with my own non-OEM Servers on V 11.0.1 and V 12.

Unfortunately, the current docs are quite silent about the values of these properties on non-OEM servers. However, the original introduction of these new properties seems to be in 11.0.0 with EBF 1409 with the following readme quote:

================(Build #1409 - Engineering Case #528793)================

A new connection and database property called "Authenticated" has now been added. The use of these two new properties is as follows:

For OEM servers, once an application has executed the "SET TEMPORARY OPTION CONNECTION_AUTHENTICATION=" statement, the application can then turn around and execute a "SELECT connection_property( 'Authenticated')" statement.

If the result is "YES", then the connection was properly authenticated and all is well. If, however, the result is "NO", then the application can execute a "SELECT db_property( 'Authenticated')" statement. If the result of this statement is "YES", then the database has been properly authenticated and the connection authentication failed because the CONNECTION_AUTHENTICATION string is incorrect.

If, on the other hand, the result of querying the Authenticated database property is "NO", then the connection authentication failed because the database has not been properly authenticated. In this case, the customer should examine the DATABASE_AUTHENTICATION string to determine what is wrong.

For non-OEM servers, the result of querying these new properties will always be "YES".

Note: I remember to have asked for such properties because of problems with a wrongly authenticated connection on a V10 OEM server - something that was really hard to check without these properties. So I guess I have remembered this description. But I seem to be wrong (or the implementation in 11.0.1 and 12.0.0 has changed for non-OEM servers).

Resume:

I guess these properties as implemented are not helpful for your case:(

Breck_Carter
Participant
0 Kudos

Sadly, both return 'No' when run on a normal unauthenticated database running on a normal non-OEM server... so a value of 'No' does not tell me what I want to know.

VolkerBarth
Contributor
0 Kudos

@Breck: Yes, I can confirm your tests - see my edited answer - which is somehow now a non-answer...