How to limit the remote access to the database server?

[MCMartin]

I have seen the thread about how you can get in full control of your db server (Sample code: How to get full control over a SQL Anywhere server). My question is a little different, as I don't want to refuse any new connection to the database but only remote ones. So local connections should be still allowed, so that all your administrative tools still work on the server.

[MarkCulp · Accepted solution]

Breck has given you the easy answer - use dbengX instead of dbsrvX - but you if want to continue to use the network server (and get full use of all CPUs), then you can start the server with:

dbsrvX -x tcpip{localonly=yes} ...other-switches-and-options...

If you just want to temporarily disallow network connections, then you can start your server normally and create a login procedure that checks the CommLink connection parameter.

Example:

SET OPTION public.disallow_non_local_connections = 'NO';

CREATE PROCEDURE DBA.login_check( )
BEGIN
      DECLARE INVALID_LOGON EXCEPTION FOR SQLSTATE '28000';
      // check if non-local connections are allowed
      IF CONNECTION_PROPERTY( 'CommLink' ) != 'local'
         AND ( select setting 
                 from sysoption 
                where "option" = 'disallow_non_local_connections' ) = 'YES'
      THEN
          SIGNAL INVALID_LOGON;
      ELSE
          CALL sp_login_environment;
      END IF;
END;

GRANT EXECUTE ON DBA.login_check TO PUBLIC;

SET OPTION public.login_procedure='DBA.login_check';

Then to disallow non-local connections temporarily, execute:

SET OPTION public.disallow_non_local_connections = 'YES';

And to re-enable non-local connecitons, execute:

SET OPTION public.disallow_non_local_connections = 'NO';

[VolkerBarth · Accepted solution]

Ah, the usage of user-defined options - something to remember now and then:)

[Former Member · Accepted solution]

Nice! Me likey!

[MCMartin · Accepted solution]

Thanks Mark, the solution for the temporary case was what I am looking for.

[VolkerBarth]

AFAIK, there's a third possibility besides dbengX and dbsrvX -x TCPIP{localonly=yes}:

You can restrict TCP/IP connections altogether by dbsrvX -x none.

That enables only shared memory connections and won't even enable local TDS connections (as those require TCP/IP, e.g. jConnect). Take care as this may not support all variants of connections between local clients and services/terminal services, cf. the docs.

[Breck_Carter]

The easy way is to stop dbsrv*.exe, start dbeng*.exe which doesn't allow network connections.

Alas, the personal server is also limited to using one CPU. For all the differences see http://dcx.sybase.com/1101en/dbadmin_en11/introductionrunning.html

Or... did you mean WAN-versus-LAN?

I gave you a "network-versus-same-machine-connection" answer.