Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

I created a new SQL-Anywhere 17 database on a machine on which OEM Version is installed using the following command: dbinit.exe" -z "UTF8BIN" -zn "UCA" -i -dba dba,123456 -pd -t mydb.log mydb.db

When I try to change the password of dba on the OEM machine, I get Authentication violation!!! grant connect to "dba" identified by "12345678"

I copy the same database (which was created on an OEM machine) to another developer machine, then everything works fine (no authentication violation).

Could you please tell what happens here? At the end I need to change the password on the OEM machine.

asked 03 Aug '21, 02:30

Baron's gravatar image

Baron
2.2k144152181
accept rate: 48%


For the OEM version of sql anywhere you need the corresponding authentication settings.

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.12.0.1/dbadmin/running-s-4314706.html

So basically, beside the login credentials, you will also need to: - Set the OEM "key" in the database - Provide the connection authentication

Step 1:

SET OPTION
PUBLIC.database_authentication='company=company-name;
     application=application-name;
     signature=database-signature';

Step 2:

SET TEMPORARY OPTION
connection_authentication='company =
company-name;
     application=application-name;
     signature=application-signature';

If you don't have this info from the OEM Database, then for development purposes just use the developper edition of sql anywhere

permanent link

answered 04 Aug '21, 03:49

Andr%C3%A9%20Schild's gravatar image

André Schild
105227
accept rate: 0%

Do I need the signature even with EXEs of SQL-Anywhere? I am connecting to the DB using dbisql.exe

(04 Aug '21, 04:36) Baron
Replies hidden

To quote the docs:

All the database tools included with SQL Anywhere, including Sybase Central, Interactive SQL, and the utilities, such as dbbackup, are self-authenticating. They are unrestricted in their operations against any authenticated database. If the database itself is not authenticated, the tools act in a restricted, read-only fashion.

So I would assume the database itself is not authenticated here? Typically, you would use an authenticate.sql script file in the setup of the according machine.

(04 Aug '21, 05:37) Volker Barth

This means that on an OEM-Version I have restricted connection to a not-authenticated database when I use dbisql.exe, this is somehow weird.

(04 Aug '21, 08:49) Baron
1

> on an OEM-Version

If you don't want OEM restrictions, don't use an OEM copy of the software... it's not even a little bit weird: with lower prices comes higher restrictions :)

(04 Aug '21, 09:08) Breck Carter

The database_authentication must be set in the database in order to run on an Authenticated engine if you want non-read only access to the database with SQL Anywhere tools such as DBISQL and Sybase Central. The SQL Anywhere tool connections are self authenticating -- the database is not. If you are creating a database in an Authenticated environment, you will need to use authenticate.sql as documented to set the database_authentication option for the database.

(05 Aug '21, 23:24) Chris Keating
Replies hidden

Is it also correct that the Authentication violation happens sporadically?

(06 Aug '21, 03:32) Baron
1

For non-authenticated connections to authenticated databases there's the following detail:

An authenticated application must set the connection_authentication database option immediately after connecting.

The option must be set on every connection immediately after the connection is established. ODBC or JDBC applications query the database about its capabilities, and you may not have control over these actions. For this reason, every connection has a thirty second grace period before the restrictions apply. The grace period allows an application to authenticate regardless of which development tool is being used.

Might this "grace period" explain the "sporadic" issue? AFAIK, it should not apply to the builtin tools, as they are self-authenticating, but certainly to other connections unless/until those are authenticated.

(06 Aug '21, 03:55) Volker Barth

The AUTHENTICATION_VIOLATION is not sporadic. It is reported at times when the authentication rules were violated. This means at any time that a write operation occurs on an connection that has not been authenticated. And it will always be reported on write operations that occur on an unauthenticated database.

My experience with sporadic cases generally are missed cases of setting the connection_authentication and often tied to the grace period that is provided.

(16 Aug '21, 11:10) Chris Keating
showing 2 of 8 show all flat view

after setting both options i get Authentication failed, i'm trybg to do the connection thru nhibernate like this.

IDbConnection conn = Driver.CreateConnection(); try { conn.ConnectionString = ConnectionString; conn.Open();

            var optionCommand = "SET OPTION public.database_authentication='company=company-name;
 application=application-name;
 signature=database-signature'";

            using (IDbCommand command = conn.CreateCommand())
            {
                command.CommandText = optionCommand;
                command.ExecuteNonQuery();
            }

            var optionCommandTemp = "SET TEMPORARY OPTION public.connection_authentication = 'company=company-name;
 application=application-name;
 signature=database-signature'";

            using (IDbCommand commandTemp = conn.CreateCommand())
            {
                commandTemp.CommandText = optionCommandTemp;
                commandTemp.ExecuteNonQuery();
            }
        }
permanent link

answered 10 Aug '23, 10:59

delas001's gravatar image

delas001
111
accept rate: 0%

2

You really should be opening a new question rather than posting an "answer" to a old question.

The DATABASE_AUTHENTICATION must be set in the database before running the database on an OEM engine. You would not set this from application code. If you plan to create databases on an OEM engine, you should put the DATABASE_AUTHENTICATION option in the authenticate.sql file so that it is set during the datatabase initialization.

(10 Aug '23, 11:19) Chris Keating

Thanks for your reply, sorry i want to follow the same thread as it looks related. I'm new with sql anywhere, what you are saying is that i need to set the authorize on the DB Creation? Accessing the DB from Interactive SQL does not set the connection to read only and works ok, i only have the issue accessing thru Nhibernate. Thanks lot!

(10 Aug '23, 13:46) delas001
1

Let me step back and ask some background questions which I have assumed 1. You are an OEM customer for SQL Anywhere. In other words, you are producing a product that embeds SQL Anywhere and are required to use the OEM edition? 2. You have requested and received your authentication signatures? 3. You have set the DATABASE_AUTHENTICATION option in the database only. It should not be done at the application level. 4. You can perform write operations in dbisql after 30s has elapsed - there is a 30s grace period before the database becomes read-only. If this is true, the DATABASE_AUTHENTICATION should be set. 5. The application executes the CONNECTION_AUTHENTICATION option before any write operations are performed.

Please refer to the documentation: https://help.sap.com/docs/SAP_SQL_Anywhere/61ecb3d4d8be4baaa07cc4db0ddb5d0a/814c333c6ce2101481bfd6f13a982ee9.html?q=database_authentication

If you continue to have problems, please start a new thread or open an incident with Support.

(10 Aug '23, 21:19) Chris Keating
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×30
×24

question asked: 03 Aug '21, 02:30

question was seen: 2,146 times

last updated: 10 Aug '23, 21:19