Erroneous authentication violation(?)

[Former Member]

Hello.

I'm currently developing a VB.net program which intends to read SQL-scripts from a (SQL-server) database table, and then run those scripts on a SyBase 10.0.1 database. When running this application, I've previously checked the scripts for errors by running them in Interactive SQL, and they do compile nicely, so there are no syntactic errors in them. However, the error I encounter is when executing the second script in the queue. The first script gets processed as I intend it to be, but when running the second, I get a "Authentication violation"-error. This scenario leads me to believe that first query doesn't completely leave the database in a consistent state, or there might be some other anomoly which has to do with my connection method. I've choosen to use a ODBC-source, which obviously works, since the first query runs just fine, so that shouldn't be the problem(?).

Further on, my way of executing these scripts is by using ADO, namely in this way:

-- start code snippet -- 
                SACommand.CommandText = strSql
                SACommand.Connection = SAconn
                SACommand.Prepare()
                SACommand.ExecuteNonQuery()
                SACommand.Dispose()
-- end code snippet --

The error I encounter gets thrown when running the SACommand.ExecuteNonQuery() - line. The scripts I run this way, are a mix of view-declarations, procedures, and DDL-statements, however the error also occurs when trying to run the same (working) first statement twice, which leaves me totally confused.

Any ideas about where I might be missing out something?

Thanks in advance

Jonas

[chris_keating]

There are two elements required in an authenticated database environment. First, the database file must be authenticated using the DATABASE_AUTHENTICATION option and the second is each and every connection must be authenticated with the CONNECTION_AUTHENTICATION option. If the database is not authenticated, an authenticated engine will not allow any operation against that database. Assuming an authenicated db, authentication violations occur if the connection has not been authenticated and an operation that changes the database is issued after the grace period of 30 seconds.

I assume the problem is an unauthenticated connection - based on the info that you perform other operations without error. I usually troubleshoot these problems using request level logging (see -zr in the docs) and look for the connection that throws the error. Once you identify the connection, you can then generally isolate the problem. It is possible that the connection_authentication option is not being set.

[VolkerBarth]

If you getting a SQLCODE -98 "Authentication violation" error, you have tried to run DML statements against an authenticated database (aka OEM edition) that is only allowed to be updated by particular applications:

You attempted to connect to a server that has been authenticated for exclusive use with a specific application.

There's a 30 seconds grace period, and the first statements may have run successfully within that timespan, whereas the latter have not.

If you're allowed to modify that database, you will have to supply the according connection_authentication string.

[Former Member]

Thanks for your reply Volker.

Well, in my example, I'm not talking about a very long timespan between the scripts run, rather 10 sec's when stepping through the code, so I have a hard time believing that is the problem. Below are some additional information about the exception thrown that might come handy. (To me, it's just gibberish I'm afraid.)

{"Authentication violation"} iAnywhere.Data.SQLAnywhere.SAException:
{"Authentication violation"} Data: {System.Collections.ListDictionaryInternal}
HelpLink: Nothing
InnerException: Nothing
Message: "Authentication violation"
Source: "SQL Anywhere .NET Data Provider"
StackTrace:
" at iAnywhere.Data.SQLAnywhere.SACommand.EndExecuteNonQuery(IAsyncResult asyncResult)
at balthzar_server_admin.frmServer_admin.balthzar_server_admin_Monitor_RunThisSQL(String strSql) in C:balthzarbalthzar_server_admin 1.2 Devbalthzar_server_admin 1.2balthzar_server_admin_110705frmServer_admin.vb:rad497"
TargetSite: {System.Reflection.RuntimeMethodInfo}

As I stated in my original post, I assume that I am allowed to modify the database, since the first statement gets executed, however the following ones don't. The thing is, that if I run the working statement twice, I get the "Authentication error" the second time around. If it matters, the first working SQL-statement is a viewdefinition.

Any new thoughts?

Jonas

[VolkerBarth]

You could find out whether OEM authentication might be an issue by querying:

select db_property('Authenticated'),
   connection_property('Authenticated')

If you're not running an authenticaed database, both functions should return 'No', otherwise two 'Yes' will confirm that you're allowed to change the DB contents.