Message Server over HTTPS

[Baron]

I am trying to implement DBRemote between 2 Databases over HTTPs.

I use a self made certificate and have 2 pem files (mycert.pem & mykey.pem), in addition I have a password for the certificate mypwd.

I follow the instructions in the tutorial to set a replication system with the consolidated database as message server.

1- I start the consolidated database (consdb.db):
dbsrv17 -xs https(Identity=mycert.pem;Identity_Password=mypwd;port=8033) consdb.db
2- set up the replication on consdb.db:
 - grant connect to consuser;
 - grant publish to consuser;
 - create remote message type file address 'consfolder';
 - set remote file option public.directory='c:\\temp\\messages';
 - grant connect to remuser identified by rempwd;
 - grant remote to remuser type file address 'remfolder';
 - --create publication, sbuscription.....
3- set up message server on consdb.db:
 - create role for user consuser;
 - set remote http option consuser.root_directory = 'c:\\temp\\messages';
 - call sr_add_message_server('consuser');
4- set up replication on remote database (remdb.db)
 - create remote type "HTTP" address 'remfolder';
 - grant consolidate to "consuser" type "HTTP" address 'consfolder';
 - set remote HTTP option public.user_name='remuser';
 - set remote HTTP option public.password='rempwd';
 - set remote HTTP option public.https='yes';
 - set remote HTTP option public.url='rempc:8033';
 - set remote HTTP option public.certificate='file=c:\\temp\\mycert.pem';

Once I start dbremote against remdb.db I get the following error: SQL statement failed: (-1588) The specified certificate is not valid

Could maybe someone tell me whether I missing some point? I am not sure whether I use the certificate correctly.

Thanks in advance

[VolkerBarth]

I don't know - but I would suggest to

• try using SQL Remote with HTTP only (unless you have tested that already),
• try adding the "debug" SQL Remote option - see here and
• try adding log options to the SQL Anywhere web service - see here

The latter hopefully help to find out what requests are sent to the web server.

You could also try to use a simple webservice with HTTPS to find out whether the server's certificate does work when called by a simple web client.

[Baron]

Thanks for the reply.

• it works with HTTP fine.
• debug option is "ON" on both remote and cons., and I see nothing more than the above error
• I've added the option, nothing will be written in the file

I've built a simple webservice in consdb.db, and I can't get any response while calling this webservice. I think the problem is with starting the consdb.db, noticeable is that the Database can start even even I use an incorrect Identity_Password !

[VolkerBarth]

AFAIK, 17.0.9 is still using OpenSSL whereas 17.0.10 has changed to SAP CommonCryptoLib, which behave somewhat different.

How exactly have you created the self-signed certificate, and what key usage was specified? (Please replace company information and passwords, apparently...)

Here's a FAQ about the required certificate chain for OpenSSL clients (and I would assume that DBRemote 17.0.9 is such one when using HTTPS a message system)...

[regdomaratzki · Accepted solution]

I'm going to assume that you are using SQL Anywhere v17.0.10 or higher in this answer. If that is incorrect, please let me know.

The problem is likely because between the time the SQL Remote tutorial was written and today, we've started doing certificate name checks to ensure that the common name of the server certificate (mycert.pem in your example) matches the fully qualified domain name of the computer on which the server is running. I'm guessing this is not the case in your example.

Please try changing the certificate option for the HTTP message system at the remote to the following and see if that resolves the issue.

set remote HTTP option public.certificate='file=c:\\temp\\mycert.pem;skip_certificate_name_check=on';

Thanks,
Reg

PS: See the documentation for a description of the skip_certificate_name_check protocol option, and some alternatives to using it if you're worried about skipping this check.

[Baron · Accepted solution]

I am using the version 17.0.9.4803

The computer on which the server is running is not in domain, and it is not called mycert.pem.

I add the skip_certificate_name_check option, but nothing changed, I still get the same error (SQL statement failed: (-1588) The specified certificate is not valid).

As stated below, the problem is most probably in starting the consdb.db, do you think that I should merge both certificate and key files?

[regdomaratzki · Accepted solution]

It's hard to give advice on what you should do with the certificate files with the information that we have. Can you provide some details on how mycert.pem & mykey.pem were created?

If I run :

createcert -t rsa -b 2048 -sc CA -sst Ontario -sl Waterloo -so SAP -sou  Sybase -scn sr17https -x -m "" -v 20 -ca 1 -u 1,6,7 -co public.cer -ko  private.cer -kp sql -io identity.cer

Then my dbsrv17 start line looks like :

dbsrv17 -xs https(port=8033;identity=identity.cer;identity_password=sql) cons.db

and my HTTP message options would look like :

SET REMOTE HTTP OPTION public.url = 'localhost:8033';
SET REMOTE HTTP OPTION public.https = 'yes';
SET REMOTE HTTP OPTION public.certificate = 'file=public.cer;skip_certificate_name_check=on';

[Baron · Accepted solution]

@Reg Domaratzki, thank you very much for the help. It has worked after merging my both files (certificate + Private key)

[VolkerBarth · Accepted solution]

So you had not initially created an identity file by the createcert tool?

[Baron · Accepted solution]

No. I didnt

[Baron · Accepted solution]

What could be the reason that sometimes the remote cant exchange messages with the message server?

On the remote I see the folloginw error:

Server 'dbremote_msgs_server': ) 

on the cons. (message server), I see that everything is OK

"GET /dbremote/control/ping/Rem HTTP/1.1" - 200 OK - 2148 - 
"GET /dbremote/Rem HTTP/1.1" - 200 OK - 621 - 
"PUT /dbremote/Cons/011.4 HTTP/1.1" - 200 OK - 735 - 
"GET /dbremote/control/disconn/Rem HTTP/1.1" - 200 OK - 589 - 
"GET /dbremote/control/ping/Rem HTTP/1.1" - 200 OK - 2148 - 
"PUT /dbremote/Cons/011.4 HTTP/1.1" - 200 OK - 735 - 
"GET /dbremote/control/disconn/Rem HTTP/1.1" - 200 OK - 589 - 
"GET /dbremote/control/ping/Rem HTTP/1.1" - 200 OK - 2148 - 
"PUT /dbremote/Cons/011.4 HTTP/1.1" - 200 OK - 735 - 
"GET /dbremote/control/disconn/Rem HTTP/1.1" - 200 OK - 589 - 
"GET /dbremote/control/ping/Rem HTTP/1.1" - 200 OK - 2148 - 
"PUT /dbremote/Cons/011.4 HTTP/1.1" - 200 OK - 735 - 
"GET /dbremote/control/disconn/Rem HTTP/1.1" - 200 OK - 589 - 
"GET /dbremote/control/ping/Rem HTTP/1.1" - 200 OK - 2148 - 
"PUT /dbremote/Cons/011.4 HTTP/1.1" - 200 OK - 735 - 
"GET /dbremote/control/disconn/Rem HTTP/1.1" - 200 OK - 589 - 
"GET /dbremote/control/ping/Rem HTTP/1.1" - 200 OK - 2148 - 
"GET /dbremote/control/disconn/Rem HTTP/1.1" - 200 OK - 589 - 

[regdomaratzki · Accepted solution]

I don't recognize the error you are posting on the remote. Can you give a little more context in the SQL Remote output log around this message?

[Baron · Accepted solution]

The error was about: Failed sending message.

The error is to be seen from time to time (so the configuration per se has to be correct).

[Baron · Accepted solution]

I. HTTPWriteMessage "rem.4"
E. Server 'dbremote_msgs_server': ) 
I. HTTPWriteMessage: failure -- filename  "rem.4"
I. HTTPDisconnect
I. HTTPConnect: user: rem_user, url: cons_host:1234
I. HTTPWriteMessage "rem.4"
E. Server 'dbremote_msgs_server': ) 
I. HTTPWriteMessage: failure -- filename  "rem.4"
I. HTTPDisconnect
I. HTTPConnect: user: rem_user, url: cons_host:1234
I. HTTPWriteMessage "rem.4"
E. Server 'dbremote_msgs_server': ) 
I. HTTPWriteMessage: failure -- filename  "rem.4"
I. HTTPDisconnect
I. HTTPConnect: user: rem_user, url: cons_host:1234
I. HTTPWriteMessage "rem.4"
E. Server 'dbremote_msgs_server': ) 
I. HTTPWriteMessage: failure -- filename  "rem.4"
I. HTTPDisconnect
I. HTTPConnect: user: rem_user, url: cons_host:1234
I. HTTPWriteMessage "rem.4"
E. Server 'dbremote_msgs_server': ) 
I. HTTPWriteMessage: failure -- filename  "rem.4"
I. HTTPDisconnect
I. HTTPConnect: user: rem_user, url: cons_host:1234
E. Fehler beim Senden der Nachricht
I. Anforderungen erneuter Sendungen werden in die Warteschlange gesetzt
I. HTTPDisconnect
I. Ausführung abgeschlossen

[Baron · Accepted solution]

Problem is solved, it was due to incorrect file address, i.e. in the following statements on cons.:

• create remote message type file address 'consfolder';

• grant remote to remuser type file address 'remfolder';

The mistake was that I set the full pathname in both 'remfolder' & 'remfolder' instead of the folder names.

Since I built my own procedure to execute those statements, then it was sometimes correct and sometimes not.

Anyway the error in the log file was confusing.