Fingerprints for SFTP Destinations in SAP BusinessObjects BI Platform 4.1
SFTP Destination support is one of the more interesting new features introduced with the recently released SAP BusinessObjects BI Platform 4.1 Support Pack 6.
Quite a lot of customer requests for this one, and it’s finally here!
When you send or schedule a document to a SFTP destination, you will be asked to enter a fingerprint value.
- What is a fingerprint?
- Why is it important?
- How do you determine the fingerprint?
I’ll answer these questions in this blog. Additionally, I’ll describe how I set up a simple environment that I’ve used for internal testing and teaching purposes for the SFTP feature.
SSH File Transfer Protocol (SFTP) Fingerprint
SFTP uses Secure Shell (SSH) to send files securely over the network. It’s a full-fledged transfer and file management system that uses public-private key cryptography to ensure any client may send a file to a server securely.
Sometimes it’s confused with FTP Secure (FTPS) or Simple FTP, but they’re not compatible. FTPS is FTP over SSL and Simple FTP has no security features built in.
Why the need for secure file transfer?
I’ll give the most often sited analogy, to snail mail. Say your company needs to send letters to a bank. You put it in an envelope, address the envelope, and drop it off at your company’s mailroom. The clerk hands it over to the postman for delivery to the bank.
But let’s say the clerk happens to be not-above-board. He steams open the envelope and reads the contents, and uses the information found within for private gain. Your letter is compromised. The clerk puts the letter back in the envelope, seals it, and sends it on its way, no-one the wiser.
To prevent that, the bank mails you special envelopes. Anyone can put contents into the envelope, but only the bank can open the envelope without destroying the contents. The shady clerk’s now thwarted and would no longer be able to read the contents and steal the information.
But say the clerk’s pretty crafty. He knows that the bank envelopes are delivered through his mailroom. So he waylays the package when it comes in. Instead, he has a set of those special envelopes made for himself, that only he can open, and forwards those envelopes to you. You can’t tell the difference between the clerk’s envelope and the bank’s and so you put the letter in the clerk’s envelope and drop it off at the mailroom. The clerk opens the envelope, reads the letter, steals the information, then puts the letter in one of the bank’s envelope, and gives to the postman. Neither you nor the bank are aware that the letter has been compromised.
The clerk is called the man-in-the-middle, and the scheme he plays is called the man-in-the-middle attack.
To thwart a man-in-the-middle, what the bank will do is place a very unique symbol on its envelopes. This symbol would be extremely difficult for others to duplicate. They then publicly publish what this symbol looks like, allowing you to verify that the special envelopes you have is actually from the bank and not the man-int-the-middle.
This symbol is a fingerprint.
Fingerprints are extremely difficult to duplicate, since they’re computed by hashing the public key, the key used for cryptography.
Discover the SFTP Fingerprint that BI Platform Expects
Now that you know the importance of a fingerprint, how do you discover the fingerprint needed, when sending/scheduling a document to SFTP?
If you use a SFTP client tool such as WinSCP or PuTTY, you’ll see that they present a fingerprint value for every SFTP that you connect to. But those fingerprint value won’t work with BI Platform. They won’t work because the hashing algorithm used is different.
Typical client tools use a MD5 hash. BI Platform uses the more secure SHA-1 hash. Because of that, you’ll need some other means to get the fingerprint.
One way is to let BI Platform tell you. When it connects to a SFTP server, it retrieves the public key and computes the SHA-1 fingerprint from it. If that expected fingerprint does not match the fingerprint you’ve entered for the SFTP destination parameters, then an error is entered in the trace files. That error line records both the expected and entered fingerprint values. You can use this to get the expected fingerprint. The steps are described in SAP Note 2183131, but I’ll describe the steps here as well.
Log onto the Central Management Console and enable tracing for the Adaptive Job Server. Log onto BI launch pad, navigate to the public “Web Intelligence Samples” folder, right-click on a WebI document and select from the menu Send->SFTP Location:
Fill out the SFTP Server information, including hostname, port, user name and password. For the fingerprint, just enter a keyword that’ll be easy to remember and search for, say FINDTHEFINGERHERE:
Click Send. Nothing appears to happen (not even an error dialog box pops up), but the document would not have been sent to the SFTP server.
Go to the machine where the BI Platform Adaptive Job Server is running, and navigate to the logging folder for the BI Platform deployment. Find the trace file associated with the Adaptive Job Server Destination Service child process. Open the glf file associated with that Service, and search for the fingerprint keyword you entered above:
Here’s the line:
destination_sftp: exception caught while connecting to sftp server [<hostname>]. Details: [83:89:8c:dd:e8:00:a2:e3:26:63:83:24:47:71:ec:8c:1b:ce:de:25 is admin input.Mis match in fingerprint. i.e hashing server fingerPrint obtained from serverFINDTHEFINGERHERE]
The long sequence of 20 two-digit hex numbers separated by colons is the SHA-1 hash of the public key as received by BI Platform. Enter that value into the FingerPrint box of the Send dialog box:
and you’ll see the document be sent successfully to the SFTP server.
Are we done?
What if I were to ask you whether the fingerprint above is the one for the SFTP server or a man-in-the-middle between your BI Platform deployment and the SFTP server?
You can’t tell by looking at the fingerprint value itself, you need some other independent way to validate it. A good way is to contact the SFTP server maintainer, and ask them “Would you provide us, securely, the SHA-1 fingerprint for your SFTP server?” That’s actually the best way.
But sometimes you encounter Administrators who don’t know how to do that. What then?
Given the public key, a public key you’ve gotten from the SFTP server by secure means, you can compute the fingerprint yourself. I’ll give instructions to do that.
First, let’s set up a trial, simple, SFTP server, so we can see things from the SFTP server side of things.
Generating the Cryptographic Public Key and Private Key
First, generate public and private keys that the SFTP server will use for cryptography. There’s various ways to do this, some SFTP server products have their own ways.
What I’ll use is the popular and common PuTTY tools.
Download the PuTTYgen RSA key generation utility from here.
It’s a fairly easy tool to use. In the “Parameters” section, specify the type and length of key, and click the “Generate” button:
You’ll see that the public key in “OpenSSH format” will be displayed in the text area titled “Public key for pasting into OpenSSH authorized_keys file:” So copy and paste the key into a text file using a text editor, such as Notepad or Notepad++. Save the contents to a file named public_key_openssh.pub. By the way, you see the “Key fingerprint:” value in the above screenshot. Ignore it. That’s a MD5 hash fingerprint, not the SHA-1 fingerprint we want.
Next go to the menu selection Conversions -> “Export OpenSSH key” to export the private key to a file, that I name private_key.key
Why OpenSSH key? It’s because I’m going to use a SFTP implementation that expects private keys to be in OpenSSH format. There are other formats, and you’d need to refer to your SFTP server documentation to find out which one, if you’re going to be using something different from I.
Now that we have the keys, let’s set up the SFTP server.
Setting up the freeFTPd SFTP Server
For simplicity, I’ll use the open-source freeFTPd implementation of the SFTP server. There are others, but freeFTPd is the one I find is easiest to set up and use.
Download and run. First go to the SFTP -> Hostkey page, and specify the private_key.key RSA key you generated previously:
Then go to the Users page and create a test user. Call it testuser:
Now go to the SFTP page and start up the SFTP server, making sure you first set where the SFTP is to store the incoming file in “SFTP root directory” setting:
And finally check the Status to ensure the SFTP us running:
That’s it!
Now connect to this SFTP server using instructions given above, and get the fingerprint value that BI Platform expects. Now, what we want to do is compute the fingerprint directly from the public key file public_key_openssh.pub and verify that the value is correct.
Use OpenSSL tools to Compute the SHA-1 Fingerprint
Let’s have a look at the public key file contents (in OpenSSH format):
ssh-rsa AAAAB3NzaC1yc2EAAAABJQAAAIEAnx3a1iYFDX4HY8Ysf2hOE1UJwha+rLD0iq82gn3+Lgla3ZzPOTuU4R39yQ5cgtzfvQrUq+NIEVEKrw1Vm3CuYVs/UrCUEhDhYOc4AfzszDGaLPnIIJjrZt9i2TnZ+9OeLakno4bgNntVglr8GbL2tryg+FWTzPGcq9O6O5gnavE=
rsa-key-20150626
Now the first line, ‘ssh-rsa’, specifies that the type of key is RSA, and the last line ‘rsa-key-20150626’ is merely an optional comment line (I just had PuTTY denote the type and date when I generated it).
In between, the gibberish, is the Base64 encoded string value for the public key binary value. What we need to do is extract this value from the file, Base64 decode it to get the binary value back, then generate the SHA-1 Digest for this value (in colon-separated hex 2-digit format).
Now, the last step you can do using OpenSSL command-line tools. But if you’d like to make life much easier, you can use command-line tools to accomplish the other two pre-steps.
The easiest, if you’re not on a Unix machine, is to download Unix tools, the Cygwin toolset. The Cygwin command-line tools contain the textfile manipulation and base64 tools to automate the other steps. Go to the Cygwin site, and install the tools (the default install won’t include the OpenSSL toolset, so make sure you manually select those as well during the installation of Cygwin packages).
Now, the way to compute the fingerprint is a single (albeit longish) command-line:
Breaking down the individual commands on the pipe, the command:
cut -d ‘ ‘ -f 2 < public_key_openssh.pub
reads the file public_key_openssh.pub, cuts the contents at whitespace, and streams out the second component. Essentially, it’s extracting the Base64 encoded public key from the public key file. The command:
base64 -d
merely reads the input pipe, base64 decode it, and streams out the binary value. And finally, the command:
openssl dgst -c -sha1
uses the OpenSSL tool to compute the SHA-1 Digest from the binary value.
As you can see, the fingerprint we compute directly from the public key corresponds to the one BI Platform says it got from the SFTP server. The public key the BI Platform is using is the one from the SFTP server, and not from the man-in-the-middle.
Summary
If you require ways to send or schedule BI Platform documents across the network securely, the recommended solution is to upgrade your deployment to BI 4.1 SP6 or higher, and use the new SFTP destination functionality.
One quirk is the fingerprint value. This blog describes how to determine the fingerprint value to use, and how to validate the fingerprint for correctness.
Hope you find this information useful, and you’re able to integrate this new functionality into your BI architecture!
Ted Ueda has supported SAP BusinessObjects BI Platform and its predecessors for almost 10 years. He still finds fun stuff to play with.
Ted, as usual - perfect.
I learned a few things from this. Thanks for sharing with crisp explanation.
Thanks, Ted! Learned some tech insight of this new feature.
Great job Ted. Very detailed
Thanks!
Customers are indeed starting to use this feature.
This article was perfect! Well written and exactly what I needed!! Thank you.
Hi Ted thanks a ton for sharing BI SFTP procedure in detail 🙂
We recently upgraded from BI SP5 to BI SP6 and see the SFTP option.
I tried to do an SFTP using the BI platform fingerprint option but am not getting the 20digit finger print key.
This is what I did
destination_sftp: exception caught while connecting to sftp server [ourhostname]. Details: [unknown error!].
Am I missing anything ?
Please let me know your thoughts.
Thanks,
Rakesh
It's not connecting to the SFTP server. You can try deploying a SFTP client on the macine where the Adaptive Job Server is running to see if you can connect to the SFTP server.
It may be blocked ports or network.
Regards,
Ted Ueda
Thanks for the quick response Ted.
Got the fingerprint value
.The fix was weird though , I sent a report by sftp without specifying the port number and when I looked at the trace file the actual fingerprint was there ..
Regards,
Rakesh
Hi Ted,
Am trying to add a new SFTP user but am getting the below error
connection error. []: [CrystalEnterprise.Sftp]
1. Is there any limit on the number of SFTP connections
2. Do we have different finger print for different SFTP connections
Thanks,
Rakesh
There should be more details in the traces on connection error. No limits and if a fingerprint error the logs should state that.
Regards,
Ted Ueda
Hi Ted,
In the trace file I see errors like "false : Out of memory or unknown type to create!".
Is it possible to SFTP a file without a Directory or folder path in the destination ?
At present we just have the sftp login details but do not have a destination directory.
Could that be the cause ?
Thanks,
Rakesh
That error is a different layer, the TCP/IP Corba layer. So the error message may not be related to your issue.
You do need to specify destination and path. It may be best top open a Support Ticket.
Regards,
Ted Ueda
HI Ted,
Is the fingerprint generation same when we have SHA256 enabled at the SFTP server? Does BO support SHA256?
Regards,
Swetha
Hello Swetha,
One thing to emphasize is that the fingerprint is not the encryption being used between the SFTP server and SAP BI Platform.
It's there to compare the public key being sent by the SFTP server to one that's already known and assured on the BI Platform side.
For the encryption itself, the SFTP client library on SAP BI Platform uses libssh2.
Regards,
Ted Ueda
Hi Ted,
Is the same approach applicable for the Data services also? I tried the normal way and was getting the below error in data services.
Could not authenticate SSH session for file location object <mm_sftp>. Error: <(-16) Invalid key data, not base64 encoded>.
Regards,
Neel
I'm unfamiliar with the SFTP interface that BODS uses, but going by the error message, is requesting the key itself in base64 and not the fingerprint.
Regards,
Ted Ueda
Hi Ted,
I am trying to send file to an SFTP on a Linux server from BI4.1 SP6, I got the finger print, I verified fingerprint value from from the logs with the one provided by the Admin, they match exactly, but I get "deliverResultsToDestination failed". error message.
Is there any other configuration, I am missing? to send file to an SFTP hosted on Linux server.
Please advice.
Thanks
Loki
There might be another issue blocking delivery - there should be error returned from the SFTP server in the traces.
If not, you may wish to open a SAP Support Incident.
Regards,
Ted Ueda
Hello Ted,
SFTP option is not working for us.While trying to schedule the SFTP it runs for 300 sec then fails with the error "Object Failed to run dude to an error while processing the job server"
And on the trace logs we do also see the below error
Destination_sftp:unknown exception caught during login to sftp server{our hostname}
This is on Linux box and we can manually do sftp to the desination.But somehow from BI SFTP is not working
You would need to analyze the Job Server traces.
You may wish to open a Support Incident if the traces do not provide sufficient info.
Regards,
Ted Ueda
Hi,
Can you please check if the SFTP workd with
1. Send to option
2. Import LCM biar files
regards,
Pranav
No it didn't work.in the logs it shows Error initializing SFTP session
Hi Anoop,
If you are still facing the issue, this might help. we faced the same problem in our deployment too since the SFTP host is Linux server. We worked with SAP and eventually found out that SAP BI Platform only supports Password Authentication to a Unix host.
For this SAP recommended a configuration change to sshd_config file, located on the host server.
For a SAP Business Objects reports to be successfully sent to SFTP location hosted on a Linux server, the config file the Parameter “Password Authentication “no” ; needs to be changed Password “Authentication “yes” ; so that host accepts Password Authentication as a form of authentication.
I changed to test, and this worked for us. However, I had to revert the changes due to company policy. I now use WinSCP script to move files to the SFTP drop off locations.
Hope this helps.
Loki
Thanks Lokesh for the update really appreciate your help,Sadly we only support Keyboard Authentication to a Unix Host due to company policy
Anoop,
Same here, company policy wont allow us to keep the configuration set to “Authentication “yes” So, the work around I now use is to schedule the report to another secure drive (File System). From there, my WinSCP script will pick up the file and deliver the it to SFTP drop off location. I have a windows task scheduler, which triggers the batch job at a given time, this batch job will execute upload script, this script connects to the SFTP location and place the file there, it immediately deletes the source file from the secure drive.
You can also use Event server to trigger the batch job, if the report instance title does not change (like no time stamp).
Thanks
Loki
Hi Ted, This is an excellent feature. Thanks for the detailed explanation. Regards, Adil
Amazingly Helpful.. Thank you..
Hi
When scheduling a report, on the SFTP destination window, what's the difference between User Name and Account?
I think I'm missing why there's an additional field for Account.
Thanks.
Hello Joseph,
User is the logon username for the SFTP.
Account is the SFTP Account.
Usually the User only has one default account, and you'd leave Account blank.
But you'd ask the SFTP service provider for the info.
Regards,
Ted Ueda
Question: Is it possible to send files through SFTP to Windows server? Is the process same as unix/linux?
I tried, I am getting Object failed to run due to an error while processing on the Job Server error
Hello Hak Chow,
I used a Windows machine for the above test.
Best Regards,
Ted Ueda
Hi Ted,
our SFTP is successful. however when the file is placed on Linux machine the rights on the file is 600. the user account used for SFTP has the 777 on the directory where the file is sent. where can I makes the changes so the file places with 777 or 644
Hi,
We have the same problem, file transfer from SAP BO to Linux destion with sFTP succesful, but the file placed on the Linux server with the wrong right 600 ipv 644
When we do a transfer (FileZilla) with the same account, the file get CHMOD 644 and thats correct.
Why change the tool in SAP BO, the CMOD on the sending file, and how we can fix that...?
Hi Ted,
BI 4.2 SP3 .
we are facing issue in scheduling webi report as excel/csv to sftp. Most of the times schedule showing failed status,but in sftp I see schedule out put file with 0KB.
error message : connection error. []: [CrystalEnterprise.Sftp] in logs as well in history.
sometimes only out put with less 10 KB is sending to sftp .SFTP team verified there is no restriction on file size,file type ,characters for files .
Did you came through this issue?
Thank you
Sateesh
Hi Sateesh,
Were you able to find a resolution to your issue? We have a similar problem where only very small files make it to the SFTP server.
Thanks,
Sean.
Hi Sean ,
Nope , we couldn't make it from BOBJ .
solution is implemented from BW .
Regards
Sateesh
I just wanted to point out to everyone that if you use the CoreFTP application to connect to your SFTP server it will return the fingerprint right away in the status window. You won't have to look for it in the BOE log files. Then you can use that Fingerprint when scheduling your reports.
Hello,
I have successfully paramtered the SFTP by using the fingerprint. But unfortunately it changes several times per month. Do you know why ?
Regards,
Paul
Hello,
good procedure, it's working well on 4.2SP7 : don't forget to increase the log level of the adaptative job server to high.
Regards
Vincent
How increase the log level of the adaptative job server to high plz?
Thanks in advance.
Go to the Adaptive Job Server's properties and you will find it under TraceLog Service, set to HIGH and then restart the server
Thanks Ted,
The instructions were really helpful.
Regards
Bandula
Hi Ted,
Am trying to schedule a report, but getting below error
connection error. [Error performing SSH handshake with the server.]: [CrystalEnterprise.Sftp]
Was able to connect via WinSCP from BI application server to the destination sftp. Fingerprint is verified correct as well. Log traces doesnt seem to give details with regards to above error though High Level of tracing is already set.
Many thanks,
Lee
Hi Boss,
I'm facing one issue related to generate the sha-1 fingerprint using the CygWin but occurring the error as below.
$ cut -d ‘ ‘ -f 2 <public_key_openssh.pub | base64 -d | openssl dgst -c -sha1
cut: the delimiter must be a single character
Try 'cut --help' for more information.
(stdin)= da:39:a3:ee:5e:6b:4b:0d:32:55:bf:ef:95:60:18:90:af:d8:07:09
I saw the fingerprint but connecting from SAP to my SFTP have failed. Can you share me how fix it.
Looking forward to hearing from you soon.
Thanks.
Just one thing that might help, you will want to enable trace logging on the Adaptive Job server ,set to high and restart the server prior to testing. In my case that was required to capture the failure and recover the fingerprint.
Thanks,