cancel
Showing results for 
Search instead for 
Did you mean: 

setting the environment variables for sql anywhere 16

3,828

We are in the process of upgrade sql anywhere 12 to sql anywhere 16.

We use .net C#.

Instead of installing sql anywhere on user's machine, we use sql anywhere deployment package.

So, in our code, we start a new process which runs SAConnection with a connection string that specifies db name, user id and password etc. When using sql anywhere 12, we specify the environment variables as following:

myConverter = new Process();

string sqlAnywhereEnvDirectory = Path.Combine(myParameters.ResourceFolder, "SQL Anywhere 12") + @".";

myConverter.StartInfo.EnvironmentVariables["SQLANY12"] = sqlAnywhereEnvDirectory;

myConverter.StartInfo.EnvironmentVariables["SQLANYSH12"] = sqlAnywhereEnvDirectory;

The above code works for sql anywhere 12. but now we want to use sql anywhere 16, in my imagination, I just changed the reference to 12 to 16, hoping it could work, but it does not.

After fair bit of struggle, it appears that if the db is not using the default user name(i.e. dba) and not using the default password (i.e. sql), it does not work. The error I am getting is Connection error: Found server but communication error occurred

Does anyone know what to do to solve the problem? i.e. is there any other environment variable that we need to set?

Thanks in advance.

chris_keating
Product and Topic Expert
Product and Topic Expert

This appears to be a connectivity issue so it is unrelated to environment variables.

To troubleshoot, add the connection parameter LOG=<filespec> where <file_spec> is a filename (and path) to log client connection debug log information. Optionally, I would configure the server to run with debug information. If you can restart the server, adding -z to its startup (and -o <file_spec> if you are not already using an engine console output log) or from a dbisql connection, execute:

 call sa_server_option('DebuggingInformation','yes')

When you have captured a failed client connection, you can turn off server logging by executing

  call sa_server_option('DebuggingInformation','no')

Please review both the client and server logs (if generated) to see if it helps explain the problem. If it is not obvious in those logs, you can send me an email to first_name.last_name@sap.com with the logs or post relevant log contents (please mask any info that is sensitive).

0 Kudos

Thanks Chris:

The log file on the client shows the following :

10:15:33 Communication function i_cs_HandleSQLPresError code 4

10:15:33 Communication function StrmGetInd code 7

10:15:33 Database is running, but could not connect to it

Does anyone know anything about these codes? Thanks.

MarkCulp
Participant
0 Kudos

The i_cs_HandleSQLPresError code 4 simply means that there is an "indicator" that has been raised on the connection that needs to be handle (i.e. "4" means get the indicator and process it).

The StrmGetInd code 7 means that the connection was marked as "dead". This can happen for a number of reasons by primarily if liveness has timed out (i.e. the other side has not responded within the timeout period)... but also any abnormal situation during a connection attempt can also raise this error.

--- Revision:

I see from the log file that you sent to Chris that the error that you have posted occurred 5 minutes after the connection attempt had started... therefore I would believe that the "dead" connection indicator was raised due to the liveness timeout.

Make sure that your database does not need to go through recovery that will stall the connection attempt - i.e. if the server is trying to recover the database then the connection attempt may be waiting for this operation to complete? (just a guess!)

0 Kudos

Thanks Mark.

After bit further digging, it appears that problem exists for OEM database.

With sql anywhere 12, we can establish a connection(without specify anything to do with authentication), then immediately, we do a sql command on the connection to provide the authentication information for the connected session. However, with sql anywhere 16, with the way we are using sql(i.e. in a deployment folder), we cannot connect to any OEM database without providing authentication information.

Does anyone know what I need to add to the connection string(i.e. the syntax) to solve the problem ? or is there any more environment variable that I need to set for authentication? Thanks in advance.

0 Kudos

Assuming the database_authentication database option has already been set, you can set the connection_authentication connection option within thirty seconds of the connection starting. The database/connection key pair can be requested here if you don't yet have one for your application.

chris_keating
Product and Topic Expert
Product and Topic Expert
0 Kudos

Authentication requires that both the database be authenticated and the connection be authenticated. I suspect that the database has not been authenticated i.e., it is missing the database_authentication option value. Because setting options requires write access, you will not be able to set that option (or the connection_authentication option) on an authenticated engine if the database is not authenticated. If you are creating or rebuilding the database on an authenticated engine, you will need to use authenticate.sql to supply the database_authentication value for the database file. See http://dcx.sap.com/index.html#sa160/en/dbadmin/running-s-4632203.html. If you are starting an existing database on an authenticated engine, you will need to ensure that the database_authentication option has been correctly set prior to running on an authenticated engine.

You can verify if the database is authenticated in the engine console output. During the start of a database, the authentication details for the database file is reported when running on an authenticated engine. If you are not seeing information that matches your database_authentication string, the database may not authenticated. You can also execute the query

     select * from sysoption 
        where "option" = 'database_authentication'

which must return a row with setting value that matches the database_authentication string for your application.

0 Kudos

Thanks Chris.

I created the database by using the developer version of sql anywhere 16, i.e. I used Sybase Central to create the test database, I verified that the database authentication option is set by running the following sql: SELECT CONNECTION_PROPERTY ( 'DATABASE_AUTHENTICATION' ) It shows the authentication information I set, i.e. signature, company name etc. Then I double confirmed that the authentication option is set by simply opening the db file in Notepad, search for company name that I set. So after all that, I am sure that the this database has the authentication option set.

When we connection to the database, immediately after the connection, we run the following sql statement to provide the authentication information: SET TEMPORARY OPTION CONNECTION_AUTHENTICATION = 'xxx.......'

The problem I am having here is not being able to connect, therefore, the command to set the connection authentication option is never run.

0 Kudos

Finally the problem is solved by my colleague.

The deployment package seems to have unknown problem which I built on a Virtual machine. The fact it works for no authentication database and not work on authenticated database confused me.

So my colleague built it on a different machine, and the new package works.

Thanks again for everyone's help.

Accepted Solutions (0)

Answers (0)