Thursday, October 14, 2010

Using HTTPS with your SQL Anywhere-based web server

This article is based on this SQLA question-and-answer, How do I set up a TYPE RAW web service to use HTTPS? and on this not-yet-published Foxhound FAQ: How do I specify the HTTPS transport-layer security for Foxhound?

If you have built TYPE RAW web services in SQL Anywhere, or (I'm guessing) TYPE HTML etcetera, you can support HTTPS without making any changes to your SQL code.

You can do this by modifying the SQL Anywhere startup command line to specify RSA encryption and the HTTPS protocol as follows:
  • Obtain an identity certificate and the associated private key for your server.

  • Store the identity certificate file in a known location.

  • Change the -xs option to specify https on the dbsrv*.exe command line used to start your SQL Anywhere server:
    Specify the identity certificate file and private key in the -xs https identity= and identity_password= parameters.

    Note that the default port for HTTPS is 443.
Here is an example of a dbsrv11.exe command line modified to allow only HTTPS access to Foxhound data using the sample certificate "%SQLANY11%\Bin32\" that comes with SQL Anywhere 11:

(Note: Only the -xs line had to be changed, all the other stuff was there before.)

-c 50p^
-ch 75p^
-gk all^
-gn 120^
-n foxhound1^
-o foxhound1_debug.txt^
-oe foxhound1_debug_startup.txt^
-on 10M^
-sb 0^
-x none^
-xs https(identity="%SQLANY11%\Bin32\";identity_password=test;port=443;maxsize=0;to=600;kto=600)^
-n f

If you want to allow both HTTP and HTTPS access, specify both as follows:

-xs http(port=80;maxsize=0;to=600;kto=600),https(identity="%SQLANY11%\Bin32\";identity_password=test;port=443;maxsize=0;to=600;kto=600)^

To read more about -xs, see Starting the database server with transport-layer security and the -xs dbeng12/dbsrv12 server option in the Help.

How come I don't have to code SECURE ON?

You can add the SECURE ON clause to every single web service if you want...

AS CALL procedure_name (
:parameter2 );

...but you don't have to. If you have complete administrative control over the command line used to start SQL Anywhere, then omitting the SECURE ON clause gives you more flexibility in deciding whether or not, and when, to support HTTP and/or HTTPS.

That's the case with the SQL Anywhere database that is Foxhound: it's up to each customer to decide if they want HTTP (the default) and/or HTTPS, and if they want to lock it down to use only HTTPS they must have control over the command line.

To read more about SECURE ON, see the Help on the CREATE SERVICE statement and Mark Culp's answer to this question in SQLA.

No comments: