August 2012 Edition

How can you restrict TCP/IP connections to a SQL Anywhere database?

Secure installations of SQL Anywhere may require the ability to provide extra security by denying database access from an Internet Protocol address (IP) or group of IPs.

The examples below show how to either disable all network connectivity to the network server or to only allow database connections from a set of IP addresses.

Restricting connections to the local machine only

Local machine connections are only allowed when using the personal database server (dbeng12). When using the network database server, you can specify only to use local shared memory connections by using the “dbsrv12 -x none” switch to turn off TCP/IP communications.
For some interface communication protocols (such as TDS over jConnect), a TCP/IP connection is required to access the database server. In these situations, it may be required to expose TCP/IP communications to a larger TCP/IP network, but only allow connections from the local computer. This can be achieved by using “LOCALONLY=YES” TCP/IP parameter with the dbsrv12 -x switch:

e.g.

dbsrv12 -x tcpip(LOCALONLY=YES) –n Stocks stocks.db –n Stocks

Any user trying to connect from across the network will receive a “Database server not found.” message.

Without restricting all remote network operations, it is sometimes desirable to restrict only certain IP addresses. This example shows how to restrict user connections to only certain IP addresses by combining the ‘login_procedure’ database option and the ‘NodeAddress’ connection property.

login_procedure option

login_procedure is a database option and that specifies a custom stored procedure to execute on connection start-up. The default value is ‘sp_login_environment’.

e.g. This statement sets a custom ‘sp_login_check’ stored procedure as the ‘login_procedure’ option for all users in the PUBLIC group:

NodeAddress – the database connection option

NodeAddress is a connection option that returns the node location (IP address) for the client in a client/server connection. For a local connection, this property is empty.

Restricting database access by IP address

In order to restrict connection to only certain IP addresses, we can combine the login_procedure and NodeAddress property with a single custom stored procedure (e.g. “sp_login_check()”) to check incoming connections. To do this we can create the stored procedure, set the database server option to the stored procedure name, and all connections that are coming from an IP address which are not present in a created ‘ip_allow’ table are going to be refused.

Database setup

Connect to your database with dbisql and run following SQL script (you may want to change the INSERT statement to use a different IP address to reflect your local network):

-- Create an 'ip_allow' table that will store IP addresses
-- from which users are allowed to connect

CREATE TABLE "DBA"."ip_allow" (
"ip_address" VARCHAR(30) NOT NULL,
PRIMARY KEY ( "ip_address" ASC )
) IN "SYSTEM";

-- Populate the 'ip_allow' table with an IP address from which
-- connections should be allowed

INSERT INTO "DBA"."ip_allow" ("ip_address") VALUES('111.222.333.444');

-- Create the 'sp_login_check' stored procedure that will verify
-- new connections

CREATE PROCEDURE "DBA"."sp_login_check"( )
BEGIN

DECLARE INVALID_LOGON EXCEPTION FOR SQLSTATE '28000';

IF NOT EXISTS( SELECT 1 FROM ip_allow where ip_address = CONNECTION_PROPERTY('NodeAddress') )
    THEN
      MESSAGE '*** LOGIN FAILURE ***: USERID : ' || CONNECTION_PROPERTY('Userid' ); ;
      MESSAGE '*** LOGIN FAILURE ***: APPINFO : ' || CONNECTION_PROPERTY('AppInfo');
    MESSAGE '*** LOGIN FAILURE ***: NODEADDRESS : ' || CONNECTION_PROPERTY('NodeAddress') ;

    SIGNAL INVALID_LOGON;
ELSE
      MESSAGE '*** LOGIN SUCCESS *** USERID : ' || CONNECTION_PROPERTY('Userid' ); ;
      MESSAGE '*** LOGIN SUCCESS *** APPINFO : ' || CONNECTION_PROPERTY('AppInfo'); ;
      MESSAGE '*** LOGIN SUCCESS *** NODEADDRESS : ' || CONNECTION_PROPERTY('NodeAddress') ;

-- Always remember to call sp_login_environment at the end
CALL sp_login_environment;
END IF;
END;

-- Set the 'login_procedure' option to sp_login_check
SET OPTION PUBLIC.login_procedure='DBA.sp_login_check';

Test connectivity

Both successful and failed logins are now recorded in the server’s console log which can be viewed with the sa_get_server_messages() stored procedure.

e.g.