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.
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 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.
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):
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.