SQL Anywhere .NET Data Provider throws Authentication violation exception

Question

I have a Sybase database, from which I generated an ADO.Net Entity Data Model. Here's the connectionString from the App.config file:

connectionString="metadata=res://*/Test.csdl|res://*/Test.ssdl|res://*/Test.msl;provider=iAnywhere.Data.SQLAnywhere;provider connection string="userid=testUser;password=testPassword;datasourcename=test"" providerName="System.Data.EntityClient"

I try to add new entities to a table with a single autoincerement attribute in the database:

while (true)
{
    using (var context = new Entities())
    {
        var testEntity = new TestTable();
        context.TestTables.Add(testEntity);
        try
        {
            context.SaveChanges();
        }
        catch (Exception ex)
        {
        }
    }
}

which works fine for the first couple (hundred, thousand) iterations. But after that, I get an iAnywhere.Data.SQLAnywhere.SAException with the message Authentication violation, source "SQL Anywhere .NET Data Provider", when callin context.SaveChanges(). Here's the stacktrace:

at iAnywhere.Data.SQLAnywhere.SACommand._ExecuteReader(CommandBehavior commandBehavior, Boolean isExecuteScalar, Boolean isBeginExecuteReader)
at iAnywhere.Data.SQLAnywhere.SACommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary`2 identifierValues, List`1 generatedValues)
at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)

I'm using VS2012, .Net Framework v4.5, Entity Framework 5.0, SQLAnywhere 12 version 12.1.4216.

Of course this is a simplified scenario, in the real case I'm not calling SaveChanges() in a loop, nonetheless I get the same exception after a couple of calls. Any idea to why this exception is thrown and what is the solution would be welcomed.

Answer 1

Seems like the OEM Edition of SQL Anywhere can have unauthenticated and authenticated connections. The unauthenticated ones can only read (in theory) from the database and somehow write to it (for 30 seconds after the first write, in my case the SaveChanges() call). Here's the code how to authenticate your connection:

private static string AuthCommand = "SET TEMPORARY OPTION CONNECTION_AUTHENTICATION='Company=COMPANY_NAME;Application=APPLICATION;Signature=SIGNATURE'";
private bool AuthCommandIssued = false;

public override int SaveChanges()
{
    if (!AuthCommandIssued)
    {
        Database.ExecuteSqlCommand(AuthCommand);
        AuthCommandIssued = true;
    }
    return base.SaveChanges();
}

More information on this and how to develop authenticated applications and get the authentication parameters can be found here.