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

asked 12 Apr '22, 06:23

Baron's gravatar image

Baron
2.1k134146175
accept rate: 46%

edited 12 Apr '22, 06:23

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.

(12 Apr '22, 07:20) Volker Barth
Replies hidden

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 !

(12 Apr '22, 10:56) Baron

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

(12 Apr '22, 11:48) Volker Barth

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.

permanent link

answered 12 Apr '22, 09:09

Reg%20Domaratzki's gravatar image

Reg Domaratzki
7.7k343118
accept rate: 37%

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?

(12 Apr '22, 11:06) Baron
Replies hidden
2

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';
(12 Apr '22, 12:57) Reg Domaratzki
1

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

(13 Apr '22, 07:59) Baron
Replies hidden

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

(13 Apr '22, 08:28) Volker Barth

No. I didnt

(13 Apr '22, 09:22) Baron

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 - 

(04 May '22, 10:50) Baron
Replies hidden

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?

(04 May '22, 11:53) Reg Domaratzki

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

(04 May '22, 16:10) Baron
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
(05 May '22, 03:30) Baron
Replies hidden
1

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.

(17 May '22, 05:46) Baron
showing 4 of 10 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×78
×24
×10

question asked: 12 Apr '22, 06:23

question was seen: 593 times

last updated: 17 May '22, 05:46