Windows Service not connecting to SQL Anywhere DB

[Former Member]

Hi,

I had built an application to connect to SyBase - SQL Anywhere DB using VB.NET and appropriate ODBC connection. This was working fine until we had to make this application a service which keeps running in the background irrespective of any users logged in.

I built the application into a service and after installation I am getting the following error when connecting to SyBase (please note the connection string is exactly same as used in the application)

ERROR [08001] [Sybase][ODBC Driver][SQL Anywhere]Database server not found

The project installer - service process installer 1 is configured as localsystem.

The creepiest thing here is, when the same application was not a service, connecting to sybase was successful. Once it was made into a service it started failing. I have a Windows 7 64-bit workstation and VS 2010.

I have been trying to do every single thing for the last one week to fix it but nothing seems to be working. Any advice would be highly appreciated.

Thanks,

Aaron

[VolkerBarth]

More details would be helpful, such as:

Is the database server itself running? On the same machine as the application?

What's your connection string - is it using an ODBC DSN (which might need to be a system DSN in order to be usable by a service)?

[Former Member]

Hi Volker,

I tried using 2 types of connection strings, both work when run as application

connectionString="Dsn=sybdsn;userid=dba;databasename=db1;servername=db1;passwor??d=sql" and connectionString="Dsn=sybdb;Uid=dba;Pwd=sql"

I am using System DSN ODBC configuration of SQL Anywhere V12.00.01.3457. The DB is running on the same server where the application is running (localhost). The connection was successful from DSN test when SQL Anywhere was running

[johnsmirnios]

Aaron, can you please confirm that the database server is also running as a service?

[Former Member]

Hi John,

I think the DB server is not a service. I have a DB file & user credentials provided by the client. I open Sybase Central, SQL Anywhere, connect to database and this file.

[johnsmirnios]

A client running as a service cannot connect via shared memory to a database server that is not running as a service on Vista and later OS's. That's just a property of the OS -- the shared memory offered by the database server in the user session isn't visible to the client running in the services session. The other way works though: a client in a user session can look into the services session to find shared memory offered by a server there. You need to use TCPIP or make the database server a service too.

[Former Member]

Thanks John, this makes real sense. Can you please direct me to some links which will help me made the database server a service rather than running from a file. Thanks in advance.

[johnsmirnios]

http://dcx.sybase.com/index.html#1201/en/dbadmin/dbsvc-service-dbutilities.html*d5e60666

[Former Member]

Hi John,

Thanks for this. I created a person server and running the DB

Running Windows 7 Build 7601 Service Pack 1 on X86_64 Server built for X86_64 processor architecture 5924K of memory used for caching Minimum cache size: 5924K, maximum cache size: 7448528K Using a maximum page size of 4096 bytes Multiprogramming level: 20 Starting database "SYDB" (C:SHARESYDB.db) at Wed Sep 12 2012 12:09 Starting checkpoint of "SYDB" (SYDB.db) at Wed Sep 12 2012 12:09 Finished checkpoint of "SYDB" (SYDB.db) at Wed Sep 12 2012 12:09 Database "SYDB" (SYDB.db) started at Wed Sep 12 2012 12:09 Database server started at Wed Sep 12 2012 12:09 Trying to start SharedMemory link ... SharedMemory link started successfully Now accepting requests Cache size adjusted to 21956K

Thats the log. But DSN works fine - no issues, but the service still fails. I have made the server local system. Is that right?

[johnsmirnios]

When you say 'DSN works fine' do you really mean 'connection from a non-service works fine'? You are using a DSN whether your app is a service or not, right?

You do need to ensure that your connection string has a servername parameter: database servers running as a service will not register themselves as a "default" database server. You must specify a name to connect. If you are using the same connection string from the desktop and from your app-running-as-a-service then I'm running out of ideas. I have done this in the past and it worked although I was using an ESQL app rather than ODBC & DSN's. There might yet be an issue with those system DSNs. In any case, start by confirming you have a servername parameter in your connection string.

[Former Member]

Yes - Irrespective of APP being/not being a service DSN works

ServerName parameter is present in the connection string, but the error which I am getting is changed now. It says

ERROR [08001] [Sybase][ODBC Driver][SQL Anywhere]Specified database not found

This is quite confusing as the DB name is mentioned in the connection string.

connectionString="Dsn=SYDBDSN;userid=dba;databasename=SYDB;servername=SYDB;password=sql"

[Former Member]

You also checked that you did use the correct ODBC Administrator?
There is a 32-bit and a 64 bit one and you find the 32 bit one under "C:WindowsSysWOW64" and the 64 bit one under "C:WindowsSystem32" and both are named "odbcad32.exe". These path names can be a bit irritating and there is no visible sign i know of to show you which version you started (only the drivers obviously differ). A 32 bit application won't see a 64 bit DNS and vice versa.
Isn't really related to service/application behaviour diffference, but could be worth checking anyway.

[graeme_perrow]

I suspect you are trying to connect with shared memory, which will not work when the server is running as a service. Make sure the server starts TCP (use the -x tcpip server switch), and then either add LINKS=tcpip or HOST=localhost (if using SQL Anywhere 12.x) to your connection string.

Update: As John said in his comment, shared memory will work if both the client and server are running as services. It's only if one of them is a service and the other is not that you need to use TCP/IP.

[johnsmirnios]

It should work if both the client & the database server are services.

[Former Member]

Not sure about the shared memory thing 😞

[former_SQLA_member1694868]

We often have trouble with services running as the "local service" account because that account by design does not have rights to do some things that typical users can do. We usually run into file access limitations (database cannot read/write files to a location because "local service" user does not have rights to that location).

I am not sure if this is applicable to your situation here, but it might help you narrow it down to run the application's service as a different user just to see if that changes the behavior.

[Former Member]

Hi Siger,

I exactly thought that was happening. I have a DB file and connect using SQL Anywhere. I changed my service to get service login and ran it as the administrator, but still got the same error. Is there something I can do to overcome this limitation, any advice? thanks