|
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. |
|
For the OEM version of sql anywhere you need the corresponding authentication settings. 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 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
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:
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
|
|
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();
}
}
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
|
