A way to monitor and control SAP NetWeaver using SQL Anywhere. It is based on my setup of SQL Anywhere running on my Raspberry Pi but it will work on other platforms supported by SQL Anywhere.
⇑
My blog covers a way to monitor and control SAP NetWeaver using SQL Anywhere functionality. It is based on my setup of SQL Anywhere running on my Raspberry Pi but it will work on other platforms supported by SQL Anywhere. I use other tools such as Node, OpenUI5 and Bash scripts on my Pi to enable each part.
I chose to use a SQL Anywhere running on my Raspberry Pi initially for IOT however as I think SQL Anywhere is a genius database I had to start with some of my other ideas to get up and running with it on my Pi. SQL Anywhere can link up with IOT on the SAP Cloud Platform as covered in this blog link but I have not used it in this way ...yet. I use only the developer edition of SQL Anywhere and would recommend you try out this edition if you have not already done so. The Pi version does not support all the features of SQL Anywhere. There is no support for an oData server (I covered this feature here) and no external environments ( I covered this feature for Java here).
My intention was to use my Pi computer to control SAP NetWeaver as that idea appealed to me; that a small computer could control such a high level system as NetWeaver. I do use the Java and windows based consoles for remote control and manual monitoring of SAP NetWeaver systems (SAP Management Console & SAP Microsoft Management Console: Windows). There is also Solution Manager for full monitoring of SAP NetWeaver systems. Although the idea of my small computer having the power to stop/start NetWeaver won me over. I wouldn't recommend opening the management of NetWeaver to public/wider access and consideration of the security risk should be reviewed if you do! However, that does not stop me from trying out a demo as it was useful for me as a learning exercise. SAP note "1439348 - Extended security settings for sapstartsrv" is a good place to start. (I did take some steps to control my NetWeaver trial system over the internet via my Pi but just for testing ).
I wanted to use the SAPControl Web Service to set up a simple UI5 based application to use SQL Anywhere’s functionality to control and display alerts of an SAP NetWeaver system (also it involved using Nodejs and openUI5). In addition, I created some bash scripts that use SQL Anywhere functions to control the Twitter api to alert me on the availability of my demo ABAP developer system NPL. The details in this blog have been tested on SAP NetWeaver Java and ABAP systems versions 7.50-7.51. This blog however uses screenshots from the 7.51 demo ABAP installation. Also, it is worth checking out the protected web methods of the SAPControl service as this can change defaults between NetWeaver releases.
The focus of my blog is SQL Anywhere however I do need to cover (briefly) an overview of encryption and the management ports of SAP NetWeaver. This is important as I use basic authentication to control SAP NetWeaver from SQL Anywhere. Using plain http port 5xx13 did not appeal as this would not encrypt the required username or password. The https port 5xx14 is used in all the following examples in my SQL Anywhere procedures and functions. (The xx is the NetWeaver Instance number and is 00 for the dev edition NPL) This does need the root certificate in all relevant NetWeaver PSEs - SAP note "1642340 - sapcontrol SSL usage" covers the relevant parts of sapstartsrv and related PSEs. This means some work for each PSE - for the server (sapssls.pse), client (sapsslc.pse) and anonymous (sapssla.pse) by importing root certificates into their certificate lists. See the later sections of this blog for some screenshots from my NPL demo.
⇑
I have found the SQL Anywhere tool createcert fantastic for my setup of a certificate authority in signing and using certificates in my demo setups previously as in here and here.
However, there is an issue with Subject Alternate Names (SAN) in x509 certificates with my browser of choice Chrome. Createcert cannot/does not add any SAN to my knowledge. I do not claim any special expertise of SQL Anywhere, just an enjoyment and passion for using SQL Anywhere. I may be wrong about these details but my version of SQL Anywhere 1053 these limitations do limit / force my choice of setup with SQL Anywhere controlling SAP NetWeaver. Also there is an alternative to basic authentication in sapstartsrv for SAP NetWeaver to use client X.509 certificates (service/sso_admin_user_<x>) but my findings/tests I failed to setup this scenario with SQL Anywhere (I failed to set up mutual authentication with SQL Anywhere’s procedures and functions based on a client x.509 certificate and this failure may well be due to my understanding of the setup with SQL Anywhere - although my setup works perfectly with Basic Authentication)
⇑
The SAP NetWeaver SAPControl Web Service can be used with SOAP. SQL Anywhere has in built SOAP capabilities and can assist with creating SOAP envelopes
I had some major issues using the standard SOAP calls with UI5. The below procedure is my setup of a standard SOAP call with the "GetSystemInstanceList" function of the SAPControl service.
This returns the following XML in SQL Anywhere 17 from my NetWeaver NPL system.
This is not a valid XML structure (checked here https://www.xmlvalidation.com/) that can be used in openUI5. I may not have used the correct format of this SOAP call and I could not make it work. So, I created my own web service and manually built the SOAP call.
BUILDING SOAP ENVELOPES
(is not nice thing to do so) I use the SOAPUI (the open source version) to help format requests based on the SAP Control WSDL definition. On the NPL system the WSDL can be accessed via HTTPS and HTTP ports. The HTTPS port is 5xx14 and the HTTP port 5xx13 where the xx is the SAP NetWeaver Instance number. For NPL the instance is 00 and an example link to the wsdl as follows for the developer edition NPL.
https://vhcalnplci.dummy.nodomain:50014/?wsdl (**)
http://vhcalnplci.dummy.nodomain:50013/?wsdl (**)
**The hostname vhcalnplci.dummy.nodomain is from the blog Newbie Guide to install NPL and the ports 50014/50013 would need to be added to the port forwarding section in that blog for remote access. My personal setup added a bridged network adapter to Virtualbox and I could bypass all the port forwarding that the Newbie blog covers. I have direct access to my installation via host name vhcalnplci.mydomain.com. Therefore my SQL Anywhere functions use this URL https://vhcalnplci.mydomain.com:50014 (how I added the network adapter is covered here in my personal setup of the NPL system).
Using SOAP UI to create sample requests to use with SQL Anywhere - I left the “Create Requests” selection as default.
As SOAPUI has created example requests I can use these to build my own requests with SQL Anywhere. Example above is the GetSystemInstanceList SOAP call.
⇑
My SQL Anywhere SOAP based service to stop and start SAP NetWeaver
First the function to submit the POST request to the SAP Control Web Service. All my examples will use the https port which requires a root certificate - I go through what I did to set this up later in the blog. Plain HTTP (port 5xx13) can avoid the requirement for any certificates but I would not recommend using it.
The certificate line extracted below is important as this is my root certificate used to sign all my certificates. - see later in the blog for my experience in setting this up.
certificate 'file=/home/pi/sql17pi/controlSAP/CA/demoCA/cacert1.pem'
The actual SQL function is..
The procedure I use to either STOP or START SAP NetWeaver is below. The first requirement is an operating system user to stop/start the SAP NetWeaver system. See below for my setup of NPL with a user "sap751srv", this user's password (sap751srv) is base64 encoded for basic authentication. You can base64 encode with the base64_encode SQL Anywhere function. In interactive SQL I ran "select base64_encode('sap751srv:sap751srv')" for the "set h" parameter in the code that follows.
And the controlling Web Service that my UI5 application will call to control the NetWeaver System. I have enabled authentication against the SQL Anywhere DBA user,
In my UI5 code I use an ajax call to use the above service. (The node code I use to control this is covered later on.) To control the system I setup a basic UI5 app with a screen shot below highlighting the start/stop icons. The UI5 code lists each SAP NetWeaver instance and the below example shows the two instances from the dev edition NPL.
In my table view I setup the Stop /Start icons to call various functions in my code.
In my controller code I setup a basic ajax call to the SQL Anywhere web service. The Stop function code as an example below. (for start the ajax link would be /sql17pi/CONTROLNPL/START).
I think I should add a prompt asking, "are you sure?" before actually stopping and starting my NetWeaver system ;).
I use the SAP Control Web Service GetSystemInstanceList to get the status of each NetWeaver Instance.
First the function to POST the XML call to the SAP Control Web Service
The SQL Anywhere Procedure is as follows
I again setup a controlling web service in SQL Anywhere
This web service again would need a password and Chrome would prompt as follows. At this prompt I would enter the SQL Anywhere user DBA and its password (and use Chrome's in built save password feature)
The oModel definition calls the web service CPnpl listed above.
The UI5 code I call a web service and use the oModel.loadData in the refresh button call.
One point that keeps catching me out with XML views is to drop the root from the path. It is documented (but often forgotten by me :))
For example this is the full XPATH from the SAP Control Web Service call GetSystemInstanceList
/SOAP-ENV:Envelope/SOAP-ENV:Body/SAPControl:GetSystemInstanceListResponse/instance/item/
From the documentation
“Note
For the XML model the root must not be included in the path.”
https://sapui5.netweaver.ondemand.com/sdk/#/topic/a53e71d85fae4d0887a8b58431197a27.html#loiob8a2c243...
This means for my XML table view I drop the SOAP-ENV:Envelope
⇑
I really enjoy SQL Anywhere and the flexibility and options that are possible with it.
Therefore, I thought to convert the XML based SAP Control Web service to JSON output with SQL Anywhere. To do this conversion I would use an SQL Anywhere openxml call to build the JSON response. The service I chose to use is GetAlertTree as this would list all NetWeaver Alerts with a status RED YELLOW or GREEN. I would use a filter to select each alert status and return a JSON result. I could then create a UI5 view to allow me to monitor the various types of alerts.
**the following procedure uses the same function listed above msapNPL_f
From the code above I will pick out the openxml call as this requires the full xpath to extract the required values, whereas with UI5 the root has to be dropped - as already mentioned.
select * from openxml( msapNPL_f(u, h, b) , '/SOAP-ENV:Envelope/SOAP-ENV:Body/SAPControl:GetAlertTreeResponse/tree/item',
I created a web service (this time anonymous no user/password required).
Calling this web service directly will convert the XML to JSON and filtered to the RED alerts only with the parameter SAPControl-RED - as shown below.
With UI5 code I created the following screen, where I can select the alert type with the icons in the top right of the screen.
For the UI5 code I can now use JSON binding.
In the controller
For the button presses
In the view code - bind to the root
⇑
I wanted to set up an alert status on the availability of the system. To do this I chose to use twitter api to send a private direct message to my twitter account (I didn’t want to publicly tweet that my SAP instance was not available :)). I could have chosen to use some of the mail features of SQL Anywhere but wanted to use twitter again with SQL Anywhere. I had previously used SQL Anywhere and twitter to analyse the best time to publish blogs on SCN.
PI SQL Anywhere does not support external environments so I can't use JAVA based twitter library as I did here. Therefore, I setup the following SQL Anywhere procedures and functions to step through an oAuth 1.1 twitter process that sends the direct message in Twitter. I rely on a great blog from Eric Farrar and I use the HMAC function in Eric’s blog as “HMAC_copy” - this function is required for my entire code to send a direct message tweet :). If you want to follow my code below then you need to copy this function code as well.
https://blogs.sap.com/2009/12/10/calculating-hash-based-message-authentication-codes-with-sql-anywhe...
The above SQL Anywhere procedure again uses openxml call and filters against the GetSystemInstanceList for any instance not in a GREEN status. If it is GREEN that is good and the NPL system is running. As there are two NPL instances the SQL loops through each via a cursor and at the end calls the twitterDM_p_v1 procedure to send the direct message via the twitter api. This code is only a demo it could fail on many issues and expects the sapstartsrv process to be running, a full implementation would require a lot more checks and balances.
⇑
The procedure uses the following function to send the direct message tweet.
Twitter is based over HTTPS so another certificate file is required for the API calls to be successfull.
This line below is from my Rasberry Pi implementation and points to the list of root certificates for the Pi that came with my installation. You can extract the twitter HTTPS certificated required on its own as I done elsewhere however I used the operating system defaults this time.
The relevant certificate line in my function below is
certificate 'file=/etc/ssl/certs/ca-certificates.crt'
The operating system on my Pi is "cat /etc/os-release"
PRETTY_NAME="Raspbian GNU/Linux 8 (jessie)"
I found this link useful to find the file that held all the root certificates https://manpages.debian.org/jessie/ca-certificates/update-ca-certificates.8.en.html
The Twitter Direct Message Function is...
The Procedure used to create the Direct Message is..
The above code is a sends a Direct Message (DM) to my auto Twitter account inXSc_rjruss which I setup when I looked at the stats for this site here while using the SAP cloud platform here. Change all occurances of inXSc_rjruss to send a DM to that account.
Important changes to be made to the following parameters to actually send the twtiter direct messages. In my previous blog here I discuss these parameters and link to an external blog which I followed to setup a twitter bot. The related permissions for the Twitter app require read/write permissions to work.
DECLARE consumekey long nvarchar = {consumekey};
DECLARE consumesec long nvarchar = {consumesec};
DECLARE accesstok long nvarchar = {accesstok};
DECLARE accesssec long nvarchar = {accesssec};
**
Point of interest also is 😉
To call the procedure I use HTTP_ENCODE as this captures most special characters - however it does not capture all encoding requirements for the twitter api in my experience.
call twitterDM_p_v1(HTTP_ENCODE(itw));
In the procedure I created above I manually added some replace functions (line below) to some special characters. It meets my requirements and I can successfully send Direct Messages but the code may not cover all possibilities for encoding characters.
set tper = replace(replace(replace(tper1, '/', '%2F'),'@','%40'),'=','%3D');
(I have tested the above Twitter related DM process on my Windows, Mac and Pi SQL Anywhere installations.)
Finally, to control the process I setup a script on my PI to control the monitoring of my SAP instance.
The above script uses the PI command dbisqlc (I have read that this command is depreciated however it is the only command I found that could be used on my PI installation).
Running the script while the SAP NPL instance was down or restarting (and not up and running ;)) would send me a Direct Message Tweet as shown below.
⇑
My NPL setup I do the following
Added the following parameters to the NPL instance profiles
The profiles are found in the /sapmnt/NPL/profile directory and need to be in both instance files.
Below I am using the grep command to select these parameters in my profile files.
vhcalnplci:/sapmnt/NPL/profile # grep "service/" NPL_D00_vhcalnplci NPL_ASCS01_vhcalnplci
NPL_D00_vhcalnplci:service/admin_users=sap751srv npladm
NPL_D00_vhcalnplci:service/trace=3
NPL_ASCS01_vhcalnplci:service/admin_users=sap751srv npladm
NPL_ASCS01_vhcalnplci:service/trace=3
The service/admin_users parameter allows my chosen Linux based user sap751srv to use the SAPControl Web Service
The service/trace=3 parameter is useful for troubleshooting the process. It is best to remove this parameter if there are no issues. There is a LOT of detail added to the trace file at trace level 3.
I needed a Linux user for my SQL Anywhere procedures and functions to work (as earlier I base64 encoded this user and password).
vhcalnplci:~ # useradd -s /bin/false sap751srv
vhcalnplci:~ # passwd sap751srv
The commands above create the user as a “service” type user and sap751srv cannot logon to the Linux system - as I set its shell to false.
As user npladm I restarted the SAP start service for both instances
Check the parameters have been set by checking each instance and parameter (as an example I use the sapcontrol ParameterValue command as follows)
Check the new user can restart the service
sapcontrol -nr 01 -function RestartService -user sap751srv sap751srv
RestartService
OK
* Any issues and we would get a permission problem
RestartService
FAIL: Permission denied
⇑
I previously setup a node reverse proxy with SQL Anywhere to consume the earth observation service here. That blog covers the setup of an https reverse proxy using node. The major difference on my PI version is that node also acts as the ui5 server - I picked up the ui5 server part from the openUI5 blog http://openui5.blogspot.com/p/nodejs.html.
To resolve CORS as covered previously in my use of Node with SQL Anywhere and the Earth Observation blog I use a reverse proxy. All my UI5 code interacts with SQL Anywhere with the /sql17pi prefix. My example oModel line from earlier is repeated below with the sql17pi in bold.
oModel.loadData("/sql17pi/ALERTNPL/SAPControl-RED");
The Nodejs script I use is as follows
⇑
As mentioned I wanted to use HTTPS and the chrome browser and found I could no longer use createcert. (I could still use createcert and just accept the errors that Chrome would display because of missing subject alternate names (SAN) in the certificates - but I chose to create my own certificates with SAN option added).
My chosen process was a script that comes with openssl called CA.pl. The help page for this script is here
https://www.openssl.org/docs/manmaster/man1/CA.pl.html
The help page does list the following -
“The script is intended as a simple front end for the openssl program for use by a beginner. Its behaviour isn't always what is wanted. For more control over the behaviour of the certificate commands call the openssl command directly.”
After using the CA.pl script for a while I wanted a bit more control over the options and how it signed the certificates and ended up with a wrapper script for the frontend script CA.pl :).. It can be found here as a gist and comes with the same statement - “For more control over the behaviour of the certificate commands call the openssl command directly”. This script has worked for me on Suse and PI now. The script is intended for Linux based systems (not windows or Mac).
My setup looks like the following and the CA.pl based private certificate authority image indicates where I needed a certificate.
For my setup I needed to run various options with CA.pl
** Worthwhile to point out that the CA.pl certificate files need to be adapted to be used.
The text file for the certificate should look like the text just below. The BEGIN / END CERTIFICATE part is required (and the bit in between these two lines). This needs to be without any extra text at the start of the file (just copy the file and remove the text - or delete it from the original 🙂 I wouldn’t 😛 modify the original )
-----BEGIN CERTIFICATE-----
[encoded data]
-----END CERTIFICATE-----
SAP PSEs - CERTIFICATE LIST IMPORTS
CA.pl creates the root certificate in directory demoCA called cacert.pem
Using an edited text file as above - this needs to be imported
Import the edited cacert.pem file into "SSL server Standard" , "SSL client SSL Client (Standard) and Anonymous" - all three PSEs should contain the cacert.pem certificate in their "Certificate List" as shown below for the Client (Anonymous) PSE in transaction STRUST.
That's the end of my blog - thanks for reading.
.
Background
Using SQL Anywhere’s Createcert Command to Setup a Certificate Authority
SAPControl Web Service & SQL Anywhere SOAP
SQL Anywhere - Start/Stop NetWeaver
SQL Anywhere - Monitor SAP NetWeaver Alerts
SQL Anywhere - Alert on SAP NetWeaver Availability
Links to Individual Detail sections
- SQL Anywhere - using the Twitter API to send a Direct Message
- My Setup of the SAP Start Service for NPL
- Raspberry PI and UI5 with a HTTPS based reverse proxy on Node.js
- Setup TLS/SSL/HTTPS Certificates
BACKGROUND
⇑
My blog covers a way to monitor and control SAP NetWeaver using SQL Anywhere functionality. It is based on my setup of SQL Anywhere running on my Raspberry Pi but it will work on other platforms supported by SQL Anywhere. I use other tools such as Node, OpenUI5 and Bash scripts on my Pi to enable each part.
I chose to use a SQL Anywhere running on my Raspberry Pi initially for IOT however as I think SQL Anywhere is a genius database I had to start with some of my other ideas to get up and running with it on my Pi. SQL Anywhere can link up with IOT on the SAP Cloud Platform as covered in this blog link but I have not used it in this way ...yet. I use only the developer edition of SQL Anywhere and would recommend you try out this edition if you have not already done so. The Pi version does not support all the features of SQL Anywhere. There is no support for an oData server (I covered this feature here) and no external environments ( I covered this feature for Java here).
My intention was to use my Pi computer to control SAP NetWeaver as that idea appealed to me; that a small computer could control such a high level system as NetWeaver. I do use the Java and windows based consoles for remote control and manual monitoring of SAP NetWeaver systems (SAP Management Console & SAP Microsoft Management Console: Windows). There is also Solution Manager for full monitoring of SAP NetWeaver systems. Although the idea of my small computer having the power to stop/start NetWeaver won me over. I wouldn't recommend opening the management of NetWeaver to public/wider access and consideration of the security risk should be reviewed if you do! However, that does not stop me from trying out a demo as it was useful for me as a learning exercise. SAP note "1439348 - Extended security settings for sapstartsrv" is a good place to start. (I did take some steps to control my NetWeaver trial system over the internet via my Pi but just for testing ).
I wanted to use the SAPControl Web Service to set up a simple UI5 based application to use SQL Anywhere’s functionality to control and display alerts of an SAP NetWeaver system (also it involved using Nodejs and openUI5). In addition, I created some bash scripts that use SQL Anywhere functions to control the Twitter api to alert me on the availability of my demo ABAP developer system NPL. The details in this blog have been tested on SAP NetWeaver Java and ABAP systems versions 7.50-7.51. This blog however uses screenshots from the 7.51 demo ABAP installation. Also, it is worth checking out the protected web methods of the SAPControl service as this can change defaults between NetWeaver releases.
The focus of my blog is SQL Anywhere however I do need to cover (briefly) an overview of encryption and the management ports of SAP NetWeaver. This is important as I use basic authentication to control SAP NetWeaver from SQL Anywhere. Using plain http port 5xx13 did not appeal as this would not encrypt the required username or password. The https port 5xx14 is used in all the following examples in my SQL Anywhere procedures and functions. (The xx is the NetWeaver Instance number and is 00 for the dev edition NPL) This does need the root certificate in all relevant NetWeaver PSEs - SAP note "1642340 - sapcontrol SSL usage" covers the relevant parts of sapstartsrv and related PSEs. This means some work for each PSE - for the server (sapssls.pse), client (sapsslc.pse) and anonymous (sapssla.pse) by importing root certificates into their certificate lists. See the later sections of this blog for some screenshots from my NPL demo.
Using SQL Anywhere’s Createcert Command to Setup a Certificate Authority
⇑
I have found the SQL Anywhere tool createcert fantastic for my setup of a certificate authority in signing and using certificates in my demo setups previously as in here and here.
However, there is an issue with Subject Alternate Names (SAN) in x509 certificates with my browser of choice Chrome. Createcert cannot/does not add any SAN to my knowledge. I do not claim any special expertise of SQL Anywhere, just an enjoyment and passion for using SQL Anywhere. I may be wrong about these details but my version of SQL Anywhere 1053 these limitations do limit / force my choice of setup with SQL Anywhere controlling SAP NetWeaver. Also there is an alternative to basic authentication in sapstartsrv for SAP NetWeaver to use client X.509 certificates (service/sso_admin_user_<x>) but my findings/tests I failed to setup this scenario with SQL Anywhere (I failed to set up mutual authentication with SQL Anywhere’s procedures and functions based on a client x.509 certificate and this failure may well be due to my understanding of the setup with SQL Anywhere - although my setup works perfectly with Basic Authentication)
SAPControl Web Service & SQL Anywhere SOAP
⇑
The SAP NetWeaver SAPControl Web Service can be used with SOAP. SQL Anywhere has in built SOAP capabilities and can assist with creating SOAP envelopes
I had some major issues using the standard SOAP calls with UI5. The below procedure is my setup of a standard SOAP call with the "GetSystemInstanceList" function of the SAPControl service.
ALTER PROCEDURE "dba"."SAPcontrol_751_p"( in "Options" nvarchar )
result( "output" nvarchar )
url 'https://vhcalnplci.mydomain.com:50014/SAPControl.cqi'
header 'Authorization:Basic bnBsYWRtOnNhcDc1MQ=='
type 'SOAP:DOC'
set 'SOAP(OP=GetSystemInstanceList)'
certificate 'file=/home/pi/sql17pi/controlSAP/CA/demoCA/cacert1.pem'
namespace 'urn:SAPControl'This returns the following XML in SQL Anywhere 17 from my NetWeaver NPL system.
<item>
<hostname>vhcalnplci</hostname>
<instanceNr>0</instanceNr>
<httpPort>50013</httpPort>
<httpsPort>50014</httpsPort>
<startPriority>3</startPriority>
<features>ABAP|GATEWAY|ICMAN|IGS</features>
<dispstatus>SAPControl-GREEN</dispstatus>
</item>
<item>
<hostname>vhcalnplci</hostname>
<instanceNr>1</instanceNr>
<httpPort>50113</httpPort>
<httpsPort>50114</httpsPort>
<startPriority>1</startPriority>
<features>MESSAGESERVER|ENQUE</features>
<dispstatus>SAPControl-GREEN</dispstatus>
</item>This is not a valid XML structure (checked here https://www.xmlvalidation.com/) that can be used in openUI5. I may not have used the correct format of this SOAP call and I could not make it work. So, I created my own web service and manually built the SOAP call.
BUILDING SOAP ENVELOPES
(is not nice thing to do so) I use the SOAPUI (the open source version) to help format requests based on the SAP Control WSDL definition. On the NPL system the WSDL can be accessed via HTTPS and HTTP ports. The HTTPS port is 5xx14 and the HTTP port 5xx13 where the xx is the SAP NetWeaver Instance number. For NPL the instance is 00 and an example link to the wsdl as follows for the developer edition NPL.
https://vhcalnplci.dummy.nodomain:50014/?wsdl (**)
http://vhcalnplci.dummy.nodomain:50013/?wsdl (**)
**The hostname vhcalnplci.dummy.nodomain is from the blog Newbie Guide to install NPL and the ports 50014/50013 would need to be added to the port forwarding section in that blog for remote access. My personal setup added a bridged network adapter to Virtualbox and I could bypass all the port forwarding that the Newbie blog covers. I have direct access to my installation via host name vhcalnplci.mydomain.com. Therefore my SQL Anywhere functions use this URL https://vhcalnplci.mydomain.com:50014 (how I added the network adapter is covered here in my personal setup of the NPL system).
Using SOAP UI to create sample requests to use with SQL Anywhere - I left the “Create Requests” selection as default.
As SOAPUI has created example requests I can use these to build my own requests with SQL Anywhere. Example above is the GetSystemInstanceList SOAP call.
SQL Anywhere - Start/Stop NetWeaver
⇑
My SQL Anywhere SOAP based service to stop and start SAP NetWeaver
First the function to submit the POST request to the SAP Control Web Service. All my examples will use the https port which requires a root certificate - I go through what I did to set this up later in the blog. Plain HTTP (port 5xx13) can avoid the requirement for any certificates but I would not recommend using it.
The certificate line extracted below is important as this is my root certificate used to sign all my certificates. - see later in the blog for my experience in setting this up.
certificate 'file=/home/pi/sql17pi/controlSAP/CA/demoCA/cacert1.pem'
The actual SQL function is..
CREATE FUNCTION "dba"."msapcontrolNPL_f"( in "u" long varchar,in "h" long varchar,in "body" long varchar )
returns long varchar
url '!u'
--certificate 'file=/var/tmp/rootCAsuse751'
certificate 'file=/home/pi/sql17pi/controlSAP/CA/demoCA/cacert1.pem'
type 'HTTP:POST:text/xml; charset=UTF-8'
header '!h'The procedure I use to either STOP or START SAP NetWeaver is below. The first requirement is an operating system user to stop/start the SAP NetWeaver system. See below for my setup of NPL with a user "sap751srv", this user's password (sap751srv) is base64 encoded for basic authentication. You can base64 encode with the base64_encode SQL Anywhere function. In interactive SQL I ran "select base64_encode('sap751srv:sap751srv')" for the "set h" parameter in the code that follows.
ALTER PROCEDURE "dba"."msapcontrolNPL_p"(in "OPT" nvarchar) result ( html_string LONG VARCHAR )
BEGIN
DECLARE K long VARCHAR ;
DECLARE S long VARCHAR ;
DECLARE BKS long VARCHAR ;
DECLARE h long VARCHAR;
DECLARE u long VARCHAR;
DECLARE b long VARCHAR;
DECLARE o long varchar;
CALL sa_set_http_option('SessionTimeout', '5');
SET TEMPORARY OPTION remote_idle_timeout = 100;
call dbo.sa_set_http_header( 'Content-Type', 'application/xml');
set u = 'https://vhcalnplci.mydomain.com:50014/SAPControl.cqi';
set h = string ('Authorization: Basic ', 'c2FwNzUxc3J2OnNhcDc1MXNydg==' );
IF "OPT" = 'STOP' THEN
SET o = '<m:StopSystem>
<m:options>ALL</m:options>
</m:StopSystem>';
ELSE
SET o = '<m:StartSystem>
<m:options>ALL</m:options>
</m:StartSystem>';
END IF;
set b = '<?xml version="1.0"?>
<SOAP-ENV:Envelope
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:m="urn:SAPControl">
<SOAP-ENV:Body>' + o + '</SOAP-ENV:Body>
</SOAP-ENV:Envelope>';
--Use function to get the Bearer Token from Twitter
select msapcontrolNPL_f(u, h, b);
ENDAnd the controlling Web Service that my UI5 application will call to control the NetWeaver System. I have enabled authentication against the SQL Anywhere DBA user,
CREATE SERVICE "CONTROLNPL" TYPE 'RAW' SECURE ON USER "dba" URL ELEMENTS METHODS 'HEAD,GET' AS call "msapcontrolNPL_p"(:url1);In my UI5 code I use an ajax call to use the above service. (The node code I use to control this is covered later on.) To control the system I setup a basic UI5 app with a screen shot below highlighting the start/stop icons. The UI5 code lists each SAP NetWeaver instance and the below example shows the two instances from the dev edition NPL.
In my table view I setup the Stop /Start icons to call various functions in my code.
<headerToolbar>
<Toolbar>
<Title text="SAP Instance" level="H2"/>
<ToolbarSpacer />
<Button
icon="sap-icon://stop"
press="handleStopPress" />
<Button
icon="sap-icon://begin"
press="handleStartPress" />
<Button
icon="sap-icon://refresh"
press="handleRefreshPress" />
</Toolbar>
</headerToolbar>In my controller code I setup a basic ajax call to the SQL Anywhere web service. The Stop function code as an example below. (for start the ajax link would be /sql17pi/CONTROLNPL/START).
I think I should add a prompt asking, "are you sure?" before actually stopping and starting my NetWeaver system ;).
handleStopPress : function (evt) {
$.ajax({
type: 'GET',
url : "/sql17pi/CONTROLNPL/STOP",
success: function(data,textStatus,jqXHR) {
console.log('success');
},
error : function(jqXHR,textStatus,errorThrown) {
console.log('error');
}})
},I use the SAP Control Web Service GetSystemInstanceList to get the status of each NetWeaver Instance.
First the function to POST the XML call to the SAP Control Web Service
ALTER FUNCTION "dba"."msapNPL_f"( in "u" long varchar,in "h" long varchar,in "body" long varchar )
returns long varchar
url '!u'
--certificate 'file=/var/tmp/rootCAsuse751'
certificate 'file=/home/pi/sql17pi/controlSAP/CA/demoCA/cacert1.pem'
type 'HTTP:POST:text/xml; charset=UTF-8'
header '!h'The SQL Anywhere Procedure is as follows
ALTER PROCEDURE "dba"."msapNPL_p"() result ( html_string LONG VARCHAR )
BEGIN
DECLARE K long VARCHAR ;
DECLARE S long VARCHAR ;
DECLARE BKS long VARCHAR ;
DECLARE h long VARCHAR;
DECLARE u long VARCHAR;
DECLARE b long VARCHAR;
CALL sa_set_http_option('SessionTimeout', '5');
SET TEMPORARY OPTION remote_idle_timeout = 100;
call dbo.sa_set_http_header( 'Content-Type', 'application/xml');
set u = 'https://vhcalnplci.mydomain.com:50014/SAPControl.cqi';
set h = string ('Authorization: Basic ', 'c2FwNzUxc3J2OnNhcDc1MXNydg==' );
set b = '<?xml version="1.0"?>
<SOAP-ENV:Envelope
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:m="urn:SAPControl">
<SOAP-ENV:Body>
<m:GetSystemInstanceList>
<m:options>ALL</m:options>
</m:GetSystemInstanceList>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>';
--Use function to get the Bearer Token from Twitter
select msapNPL_f(u, h, b);
ENDI again setup a controlling web service in SQL Anywhere
CREATE SERVICE "CPnpl" TYPE 'RAW' SECURE ON USER "dba" URL ON METHODS 'GET' AS call "msapNPL_p"();This web service again would need a password and Chrome would prompt as follows. At this prompt I would enter the SQL Anywhere user DBA and its password (and use Chrome's in built save password feature)
The oModel definition calls the web service CPnpl listed above.
var oModel = new sap.ui.model.xml.XMLModel("/sql17pi/CPnpl/ALL", false);The UI5 code I call a web service and use the oModel.loadData in the refresh button call.
handleRefreshPress : function (evt) {
var oModel =this.getView().getModel()
oModel.loadData("/sql17pi/CPnpl/ALL");
Binding XML
One point that keeps catching me out with XML views is to drop the root from the path. It is documented (but often forgotten by me :))
For example this is the full XPATH from the SAP Control Web Service call GetSystemInstanceList
/SOAP-ENV:Envelope/SOAP-ENV:Body/SAPControl:GetSystemInstanceListResponse/instance/item/
From the documentation
“Note
For the XML model the root must not be included in the path.”
https://sapui5.netweaver.ondemand.com/sdk/#/topic/a53e71d85fae4d0887a8b58431197a27.html#loiob8a2c243...
This means for my XML table view I drop the SOAP-ENV:Envelope
items="{
path: '/SOAP-ENV:Body/SAPControl:GetSystemInstanceListResponse/instance/item',
SQL Anywhere - Monitor SAP NetWeaver Alerts
⇑
I really enjoy SQL Anywhere and the flexibility and options that are possible with it.
Therefore, I thought to convert the XML based SAP Control Web service to JSON output with SQL Anywhere. To do this conversion I would use an SQL Anywhere openxml call to build the JSON response. The service I chose to use is GetAlertTree as this would list all NetWeaver Alerts with a status RED YELLOW or GREEN. I would use a filter to select each alert status and return a JSON result. I could then create a UI5 view to allow me to monitor the various types of alerts.
**the following procedure uses the same function listed above msapNPL_f
ALTER PROCEDURE "dba"."msapALERT_p"(in filta long nvarchar) result ( AlTime timestamp, "name" LONG NVARCHAR, ActualValue LONG NVARCHAR, AlDescription LONG NVARCHAR )
BEGIN
DECLARE K long VARCHAR ;
DECLARE S long VARCHAR ;
DECLARE BKS long VARCHAR ;
DECLARE h long VARCHAR;
DECLARE u long VARCHAR;
DECLARE b long VARCHAR;
CALL sa_set_http_option('SessionTimeout', '5');
SET TEMPORARY OPTION remote_idle_timeout = 100;
--call dbo.sa_set_http_header( 'Content-Type', 'application/json');
set u = 'https://vhcalnplci.mydomain.com:50014/SAPControl.cqi';
set h = string ('Authorization: Basic ', 'c2FwNzUxc3J2OnNhcDc1MXNydg==' );
set b = '<?xml version="1.0"?>
<SOAP-ENV:Envelope
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:m="urn:SAPControl">
<SOAP-ENV:Body>
<m:GetAlertTree>
<m:options>ALL</m:options>
</m:GetAlertTree>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>';
--Use function to get the Bearer Token from Twitter
select * from openxml( msapNPL_f(u, h, b) , '/SOAP-ENV:Envelope/SOAP-ENV:Body/SAPControl:GetAlertTreeResponse/tree/item',1,
'<root xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:SAPControl="urn:SAPControl" xmlns:SAPCCMS="urn:SAPCCMS" xmlns:SAPHostControl="urn:SAPHostControl" xmlns:SAPLandscapeService="urn:SAPLandscapeService" xmlns:SAPMetricService="urn:SAPMetricService" xmlns:SAPOscol="urn:SAPOscol" xmlns:SAPDSR="urn:SAPDSR"/>'
)
with(
AlTime timestamp 'AlTime',
"name" long nvarchar 'name' ,
ActualValue long nvarchar 'ActualValue' ,
AlDescription long nvarchar 'AlDescription'
) where ActualValue = filta and AlTime is not null order by AlTime desc;
--where ActualValue = 'SAPControl-RED' order by AlTime desc;
END
From the code above I will pick out the openxml call as this requires the full xpath to extract the required values, whereas with UI5 the root has to be dropped - as already mentioned.
select * from openxml( msapNPL_f(u, h, b) , '/SOAP-ENV:Envelope/SOAP-ENV:Body/SAPControl:GetAlertTreeResponse/tree/item',
I created a web service (this time anonymous no user/password required).
CREATE SERVICE "ALERTNPL" TYPE 'JSON' AUTHORIZATION OFF USER "dba" URL ELEMENTS METHODS 'HEAD,GET' AS call "msapALERT_p"(:url1);Calling this web service directly will convert the XML to JSON and filtered to the RED alerts only with the parameter SAPControl-RED - as shown below.
With UI5 code I created the following screen, where I can select the alert type with the icons in the top right of the screen.
For the UI5 code I can now use JSON binding.
In the controller
var oModel = new JSONModel("/sql17pi/ALERTNPL/SAPControl-RED", false);
For the button presses
var oModel =this.getView().getModel()
oModel.loadData("/sql17pi/ALERTNPL/SAPControl-RED");In the view code - bind to the root
items="{
path: '/'SQL Anywhere - Alert on SAP NetWeaver Availability
⇑
I wanted to set up an alert status on the availability of the system. To do this I chose to use twitter api to send a private direct message to my twitter account (I didn’t want to publicly tweet that my SAP instance was not available :)). I could have chosen to use some of the mail features of SQL Anywhere but wanted to use twitter again with SQL Anywhere. I had previously used SQL Anywhere and twitter to analyse the best time to publish blogs on SCN.
PI SQL Anywhere does not support external environments so I can't use JAVA based twitter library as I did here. Therefore, I setup the following SQL Anywhere procedures and functions to step through an oAuth 1.1 twitter process that sends the direct message in Twitter. I rely on a great blog from Eric Farrar and I use the HMAC function in Eric’s blog as “HMAC_copy” - this function is required for my entire code to send a direct message tweet :). If you want to follow my code below then you need to copy this function code as well.
https://blogs.sap.com/2009/12/10/calculating-hash-based-message-authentication-codes-with-sql-anywhe...
ALTER PROCEDURE "dba"."msapTweetNPL_p"() result ( sres LONG NVARCHAR )
BEGIN
DECLARE K long VARCHAR ;
DECLARE S long VARCHAR ;
DECLARE BKS long VARCHAR ;
DECLARE h long VARCHAR;
DECLARE u long VARCHAR;
DECLARE b long VARCHAR;
DECLARE stw long NVARCHAR;
DECLARE itw long NVARCHAR;
DECLARE qry LONG NVARCHAR;
DECLARE rowcnt INT;
CALL sa_set_http_option('SessionTimeout', '5');
SET TEMPORARY OPTION remote_idle_timeout = 100;
call dbo.sa_set_http_header( 'Content-Type', 'application/xml');
set u = 'https://vhcalnplci.mydomain.com:50014/SAPControl.cqi';
set h = string ('Authorization: Basic ', 'c2FwNzUxc3J2OnNhcDc1MXNydg==' );
set b = '<?xml version="1.0"?>
<SOAP-ENV:Envelope
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:m="urn:SAPControl">
<SOAP-ENV:Body>
<m:GetSystemInstanceList>
</m:GetSystemInstanceList>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>';
set qry = 'select string(hostname, '' '', instanceNr, '' '', features, '' '' ,dispstatus) from openxml( msapNPL_f(u, h, b) , ''/SOAP-ENV:Envelope/SOAP-ENV:Body/SAPControl:GetSystemInstanceListResponse/instance/item'',1,' +
'''<root xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:SAPControl="urn:SAPControl" xmlns:SAPCCMS="urn:SAPCCMS" xmlns:SAPHostControl="urn:SAPHostControl" xmlns:SAPLandscapeService="urn:SAPLandscapeService" xmlns:SAPMetricService="urn:SAPMetricService" xmlns:SAPOscol="urn:SAPOscol" xmlns:SAPDSR="urn:SAPDSR"/>'' ' +
') with( hostname long nvarchar ''hostname'', "instanceNr" long nvarchar ''instanceNr'', features long nvarchar ''features'' , dispstatus long nvarchar ''dispstatus'') where dispstatus <> ''SAPControl-GREEN''' ;
BEGIN
DECLARE crsr CURSOR USING qry;
OPEN crsr;
lp: LOOP
FETCH crsr INTO stw;
IF SQLCODE <> 0 THEN LEAVE lp END IF;
set itw = string(itw,'\n',stw);
SET rowcnt = rowcnt + 1;
END LOOP;
CLOSE crsr;
END;
--select itw from dummy
IF itw is not NULL THEN
call twitterDM_p_v1(HTTP_ENCODE(itw));
ELSE
select string('Everything is fine or MAJOR one! sapstartsrv maybe down!!!!') from dummy;
END IF;
END
The above SQL Anywhere procedure again uses openxml call and filters against the GetSystemInstanceList for any instance not in a GREEN status. If it is GREEN that is good and the NPL system is running. As there are two NPL instances the SQL loops through each via a cursor and at the end calls the twitterDM_p_v1 procedure to send the direct message via the twitter api. This code is only a demo it could fail on many issues and expects the sapstartsrv process to be running, a full implementation would require a lot more checks and balances.
SQL Anywhere - using the Twitter API to send a Direct Message
⇑
The procedure uses the following function to send the direct message tweet.
Twitter is based over HTTPS so another certificate file is required for the API calls to be successfull.
This line below is from my Rasberry Pi implementation and points to the list of root certificates for the Pi that came with my installation. You can extract the twitter HTTPS certificated required on its own as I done elsewhere however I used the operating system defaults this time.
The relevant certificate line in my function below is
certificate 'file=/etc/ssl/certs/ca-certificates.crt'
The operating system on my Pi is "cat /etc/os-release"
PRETTY_NAME="Raspbian GNU/Linux 8 (jessie)"
I found this link useful to find the file that held all the root certificates https://manpages.debian.org/jessie/ca-certificates/update-ca-certificates.8.en.html
The Twitter Direct Message Function is...
ALTER FUNCTION "dba"."twitterDM_f"( in "u" long varchar,in "h" long varchar )
returns long varchar
url '!u'
certificate 'file=/etc/ssl/certs/ca-certificates.crt'
type 'HTTP:POST'
header '!h'The Procedure used to create the Direct Message is..
ALTER PROCEDURE "dba"."twitterDM_p_v1"(in textin LONG NVARCHAR ) result ( html_string LONG NVARCHAR)
BEGIN
DECLARE consumekey long nvarchar = {consumekey};
DECLARE consumesec long nvarchar = {consumesec};
DECLARE accesstok long nvarchar = {accesstok};
DECLARE accesssec long nvarchar = {accesssec};
DECLARE epn long nvarchar = datediff(ss, 'Jan 01 1970', CURRENT_TIMESTAMP ) ;
DECLARE nonsense long nvarchar = SUBSTRING(NEWID(), 1, 6 );
DECLARE t2 long nvarchar = 'oauth_consumer_key=' + consumekey ;
DECLARE t3 long nvarchar = '&oauth_nonce=' + nonsense;
DECLARE t4 long nvarchar = '&oauth_signature_method=HMAC-SHA1';
DECLARE t5 long nvarchar = '&oauth_timestamp=' + epn;
DECLARE t6 long nvarchar = '&oauth_token=' + accesstok;
DECLARE t7 long nvarchar = '&oauth_version=1.0';
DECLARE t8 long nvarchar = '&screen_name=inXSc_rjruss';
DECLARE t9 long nvarchar = '&text=' + textin;
DECLARE t10 long nvarchar = '&user_id=inXSc_rjruss';
DECLARE tall long nvarchar;
DECLARE tper long nvarchar;
DECLARE tper1 long nvarchar;
DECLARE baseu long nvarchar;
DECLARE s1 long nvarchar;
DECLARE outp long nvarchar;
DECLARE sk1 long nvarchar;
DECLARE sk2 long nvarchar;
DECLARE skey long nvarchar;
DECLARE skeyhmac long nvarchar;
DECLARE tsignature long nvarchar;
DECLARE tsig1 long BINARY ;
DECLARE h long nvarchar;
DECLARE h1 long nvarchar;
DECLARE h2 long nvarchar;
DECLARE h3 long nvarchar;
DECLARE h4 long nvarchar;
DECLARE h5 long nvarchar;
DECLARE h6 long nvarchar;
DECLARE h7 long nvarchar;
DECLARE twithead long nvarchar;
set tall = STRING(t2 , t3 , t4 , t5 , t6 , t7 , t8 , t9 , t10);
set tper1 = HTTP_ENCODE(tall);
set tper = replace(replace(replace(tper1, '/', '%2F'),'@','%40'),'=','%3D');
CALL sa_set_http_header('Content-Type', 'text/javascript');
SET TEMPORARY OPTION remote_idle_timeout = 100;
set baseu = 'https://api.twitter.com/1.1/direct_messages/new.json?screen_name=inXSc_rjruss&text=' + textin + '&user_id=inXSc_rjruss';
set s1 = 'POST&https%3A%2F%2Fapi.twitter.com%2F1.1%2Fdirect_messages%2Fnew.json&';
set outp = s1 + tper;
set sk1 = HTTP_ENCODE(consumesec);
set sk2 = HTTP_ENCODE(accesssec);
set skey = sk1 + '&' + sk2;
set tsignature = replace(replace( BASE64_Encode(HEXTOBIN(HMAC_copy(skey, outp , 'SHA1'))), '=', '%3D'),'+','%2B') ;
set h1 = 'OAuth oauth_consumer_key="' + consumekey + '"';
set h2 = ',oauth_token="' + accesstok + '"';
set h3 = ',oauth_signature_method="HMAC-SHA1"';
set h4 = ',oauth_timestamp="' + epn + '"';
set h5 = ',oauth_nonce="' + nonsense + '"';
set h6 = ',oauth_version="1.0"';
set h7 = ',oauth_signature="' + tsignature + '"';
set twithead = h1 + h2 + h3 + h4 + h5 + h6 + h7;
set h = string('Authorization: ',twithead);
select twitterDM_f(baseu, h);
--select string(baseu, h) from dummy
endThe above code is a sends a Direct Message (DM) to my auto Twitter account inXSc_rjruss which I setup when I looked at the stats for this site here while using the SAP cloud platform here. Change all occurances of inXSc_rjruss to send a DM to that account.
Important changes to be made to the following parameters to actually send the twtiter direct messages. In my previous blog here I discuss these parameters and link to an external blog which I followed to setup a twitter bot. The related permissions for the Twitter app require read/write permissions to work.
DECLARE consumekey long nvarchar = {consumekey};
DECLARE consumesec long nvarchar = {consumesec};
DECLARE accesstok long nvarchar = {accesstok};
DECLARE accesssec long nvarchar = {accesssec};
**
Point of interest also is 😉
To call the procedure I use HTTP_ENCODE as this captures most special characters - however it does not capture all encoding requirements for the twitter api in my experience.
call twitterDM_p_v1(HTTP_ENCODE(itw));
In the procedure I created above I manually added some replace functions (line below) to some special characters. It meets my requirements and I can successfully send Direct Messages but the code may not cover all possibilities for encoding characters.
set tper = replace(replace(replace(tper1, '/', '%2F'),'@','%40'),'=','%3D');
(I have tested the above Twitter related DM process on my Windows, Mac and Pi SQL Anywhere installations.)
Finally, to control the process I setup a script on my PI to control the monitoring of my SAP instance.
#!/bin/sh
#-zoc /root/log/weblog.txt
. /opt/sqlanywhere17/bin32/sa_config.sh
cd /home/pi/sql17pi/
export T=`date +"%HH%MM"`
echo ${T} >>/home/pi/sql17pi/msapmonitorNPLout.log
dbisqlc -q -c "UID=DBA;PWD={PWD}" "call msapTweetNPL_p()">>/home/pi/sql17pi/msapmonitorNPLout.logThe above script uses the PI command dbisqlc (I have read that this command is depreciated however it is the only command I found that could be used on my PI installation).
Running the script while the SAP NPL instance was down or restarting (and not up and running ;)) would send me a Direct Message Tweet as shown below.
My Setup of the SAP Start Service for NPL
⇑
My NPL setup I do the following
Added the following parameters to the NPL instance profiles
service/admin_users=sap751srv npladm
service/trace=3The profiles are found in the /sapmnt/NPL/profile directory and need to be in both instance files.
Below I am using the grep command to select these parameters in my profile files.
vhcalnplci:/sapmnt/NPL/profile # grep "service/" NPL_D00_vhcalnplci NPL_ASCS01_vhcalnplci
NPL_D00_vhcalnplci:service/admin_users=sap751srv npladm
NPL_D00_vhcalnplci:service/trace=3
NPL_ASCS01_vhcalnplci:service/admin_users=sap751srv npladm
NPL_ASCS01_vhcalnplci:service/trace=3
The service/admin_users parameter allows my chosen Linux based user sap751srv to use the SAPControl Web Service
The service/trace=3 parameter is useful for troubleshooting the process. It is best to remove this parameter if there are no issues. There is a LOT of detail added to the trace file at trace level 3.
I needed a Linux user for my SQL Anywhere procedures and functions to work (as earlier I base64 encoded this user and password).
vhcalnplci:~ # useradd -s /bin/false sap751srv
vhcalnplci:~ # passwd sap751srv
The commands above create the user as a “service” type user and sap751srv cannot logon to the Linux system - as I set its shell to false.
As user npladm I restarted the SAP start service for both instances
sapcontrol -nr 00 -function RestartService
sapcontrol -nr 01 -function RestartServiceCheck the parameters have been set by checking each instance and parameter (as an example I use the sapcontrol ParameterValue command as follows)
sapcontrol -nr 00 -function ParameterValue service/admin_users
sap751 npladmCheck the new user can restart the service
sapcontrol -nr 01 -function RestartService -user sap751srv sap751srv
RestartService
OK
* Any issues and we would get a permission problem
RestartService
FAIL: Permission denied
Raspberry PI and UI5 with a HTTPS based reverse proxy on Node.js
⇑
I previously setup a node reverse proxy with SQL Anywhere to consume the earth observation service here. That blog covers the setup of an https reverse proxy using node. The major difference on my PI version is that node also acts as the ui5 server - I picked up the ui5 server part from the openUI5 blog http://openui5.blogspot.com/p/nodejs.html.
To resolve CORS as covered previously in my use of Node with SQL Anywhere and the Earth Observation blog I use a reverse proxy. All my UI5 code interacts with SQL Anywhere with the /sql17pi prefix. My example oModel line from earlier is repeated below with the sql17pi in bold.
oModel.loadData("/sql17pi/ALERTNPL/SAPControl-RED");
The Nodejs script I use is as follows
//Robert Russell 2016
//rob.roosky@yahoo.com
var express = require('express');
var app = express();
var httpProxy = require('http-proxy');
const fs = require('fs');
var https = require('https');
const options = {
key: fs.readFileSync('/home/pi/sql17pi/controlSAP/CA/pirjruss.blogsite.org/newkey.pem'),
cert: fs.readFileSync('/home/pi/sql17pi/controlSAP/CA/pirjruss.blogsite.org/newcert.pem'),
ca: fs.readFileSync('/home/pi/sql17pi/controlSAP/CA/demoCA/cacert1.pem')
};
var apiProxy = httpProxy.createProxyServer({changeOrigin: true});
var serverOne = 'https://pisql17.rjruss.local:8443'
app.all("/test/*", function(req, res) {
apiProxy.web(req, res, {target: serverOne, ca: fs.readFileSync('/home/pi/sql17pi/controlSAP/CA/demoCA/cacert1.pem')});
});
//SQL ANY WEB SERVER CALLS
app.all("/sql17pi/*", function(req, res) {
apiProxy.web(req, res, {target: serverOne, ca: fs.readFileSync('/home/pi/sql17pi/controlSAP/CA/demoCA/cacert1.pem')});
});
///http://openui5.blogspot.com/p/nodejs.html
var year = 60 * 60 * 24 * 365 * 1000;
app.use('/www', express.static('www', { maxAge: year, dotfiles: 'allow' }));
var httpsServer = https.createServer(options, app);
httpsServer.listen(5443);
Setup TLS/SSL/HTTPS Certificates
⇑
As mentioned I wanted to use HTTPS and the chrome browser and found I could no longer use createcert. (I could still use createcert and just accept the errors that Chrome would display because of missing subject alternate names (SAN) in the certificates - but I chose to create my own certificates with SAN option added).
My chosen process was a script that comes with openssl called CA.pl. The help page for this script is here
https://www.openssl.org/docs/manmaster/man1/CA.pl.html
The help page does list the following -
“The script is intended as a simple front end for the openssl program for use by a beginner. Its behaviour isn't always what is wanted. For more control over the behaviour of the certificate commands call the openssl command directly.”
After using the CA.pl script for a while I wanted a bit more control over the options and how it signed the certificates and ended up with a wrapper script for the frontend script CA.pl :).. It can be found here as a gist and comes with the same statement - “For more control over the behaviour of the certificate commands call the openssl command directly”. This script has worked for me on Suse and PI now. The script is intended for Linux based systems (not windows or Mac).
My setup looks like the following and the CA.pl based private certificate authority image indicates where I needed a certificate.
For my setup I needed to run various options with CA.pl
- I needed a primary root certificate so the option newca was the first step
- CA.pl by itself does not add SAN, it respects the OPENSSL_CONF environment variable that openssl uses though. So, I created my own script see the gist link to see how I do this.
- For my SQL Anywhere installation I used the encrypted key options newreq for my pisql17.rjruss.local hostname
- For my Node.JS script I do not use an encrypted key so the newreq-nodes was appropriate for my Node pirjruss.blogsite.org hostname
- For my setup of SAP NetWeaver NPL system I needed to sign CSR requests. That means the CA.pl option sign was appropriate. I documented my process to update the NPL SSL/TLS setup and cover a wrapper script I use to control the CA.pl process here . I added two SANs to the certificate to cover my setup *.dummy.nodomain and *.mydomain.com.
** Worthwhile to point out that the CA.pl certificate files need to be adapted to be used.
The text file for the certificate should look like the text just below. The BEGIN / END CERTIFICATE part is required (and the bit in between these two lines). This needs to be without any extra text at the start of the file (just copy the file and remove the text - or delete it from the original 🙂 I wouldn’t 😛 modify the original )
-----BEGIN CERTIFICATE-----
[encoded data]
-----END CERTIFICATE-----
SAP PSEs - CERTIFICATE LIST IMPORTS
CA.pl creates the root certificate in directory demoCA called cacert.pem
Using an edited text file as above - this needs to be imported
Import the edited cacert.pem file into "SSL server Standard" , "SSL client SSL Client (Standard) and Anonymous" - all three PSEs should contain the cacert.pem certificate in their "Certificate List" as shown below for the Client (Anonymous) PSE in transaction STRUST.
That's the end of my blog - thanks for reading.
.
- SAP Managed Tags:
1 Comment
