Skip to main content
Solved

SOAP request with encryption


​Hi,

I’m sorry for this somewhat lengthy post but I have a problem that I don’t really know how to solve and I’m just looking to see if anyone have run into the same issue, or maybe already solved it.

We are going to fetch data from a time management system that have a SOAP API and all the information I find in the community refers to RSD files, that is not to be used anymore. I don’t know how I would solve the problem that I have even with an RSD file, but that’s somewhat beside the point here. 😊

The issue is that we must take the request XML, encrypt the whole thing using AES, into a fingerprint that in turn will need to be inserted into the actual request that is then sent to the API.

I have received some code in C# that does the following steps:

  1. Generates a timestamp and an MD5 hash of the request XML
  2. Generates the fingerprint XML using the timestamp and the newly created MD5 hash with the schema like in the attached picture fingerprint_schema
  3. Encrypts the fingerprint XML with AES, using the .NET function System.Security.Cryptography.AesCryptoServiceProvider, with the encrypted fingerprint XML, a user password and a user salt as inputs
  4. This then gets inserted into the actual header XML that then can be sent in the request. The flow in the code can be seen in the attachment c_sharp_flow

The system provider has a test application where it’s easier to see the results.
An example request can be seen in the attachment Test_application_example.

You can see that since we have the FromDat and TomDat dates along with the OrganisationId in the Request XML, which will make the fingerprint unique for every request.

We have SQL server as our backend, and I’ve been trying to do some research on how to solve this problem, but I only find references to SQL server and AES encryption when it comes to database or column encryption. In all other circumstances, when using SQL, it only refers to MD(X) or SHA encryption which are only one-way encryption.

The only other options for encryption besides AES, before anyone asks, is Blowfish and TripleDES, both of which are not supported by modern versions of SQL server.

 Constructing the request and header XML will not be that much of a problem if I can only get the encryption bits right. Anyone here who have run into the same issue or have some thoughts about this? Any help or insights would be highly appreciated.

​BR
Tomas

Best answer by rory.smith

Hi,

in your situation I would consider implementing a lightweight C# application and calling it from Powershell if you want to tie it into Orchestration scheduling. That way you could schedule data dumps and then ingest them as XML files. I am pretty sure the TX native connectors don't support this at the moment. This also sounds like a rather niche approach that may not serve many other data sources and make it less likely to be added as a feature.

While it is possible to encrypt text in SQL Server using encryption algorithms, that isn't a feasible route for what you need as the features are meant for data encryption, not interacting with a webservice. (see: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/choose-an-encryption-algorithm?view=sql-server-ver16)

View original
Did this topic help you find an answer to your question?

11 replies

  • Author
  • Participant
  • 21 replies
  • April 23, 2025

Just noticed that the file names for the attachments didn’t get preserved as attachments, but I think you can spot what is what. :)


rory.smith
TimeXtender Xpert
Forum|alt.badge.img+7
  • TimeXtender Xpert
  • 679 replies
  • Answer
  • April 23, 2025

Hi,

in your situation I would consider implementing a lightweight C# application and calling it from Powershell if you want to tie it into Orchestration scheduling. That way you could schedule data dumps and then ingest them as XML files. I am pretty sure the TX native connectors don't support this at the moment. This also sounds like a rather niche approach that may not serve many other data sources and make it less likely to be added as a feature.

While it is possible to encrypt text in SQL Server using encryption algorithms, that isn't a feasible route for what you need as the features are meant for data encryption, not interacting with a webservice. (see: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/choose-an-encryption-algorithm?view=sql-server-ver16)


  • Author
  • Participant
  • 21 replies
  • April 23, 2025
rory.smith wrote:

Hi,

in your situation I would consider implementing a lightweight C# application and calling it from Powershell if you want to tie it into Orchestration scheduling. That way you could schedule data dumps and then ingest them as XML files. I am pretty sure the TX native connectors don't support this at the moment. This also sounds like a rather niche approach that may not serve many other data sources and make it less likely to be added as a feature.

While it is possible to encrypt text in SQL Server using encryption algorithms, that isn't a feasible route for what you need as the features are meant for data encryption, not interacting with a webservice. (see: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/choose-an-encryption-algorithm?view=sql-server-ver16)

Ok. Thank you for the quick reply.
I was afraid that it was going to be something like that. :)

I guess I have to check with one of my colleagues,​​ since I’m not great at that kind of development. 

Would it be possible though for you to make some guide on how to configure the REST connector for SOAP requests? This is not the only source system we have that we will need to move from CDATA and SOAP to the REST connector. 
As I mentioned earlier, all the guides I found are using RSD files.

Thanks again!


rory.smith
TimeXtender Xpert
Forum|alt.badge.img+7
  • TimeXtender Xpert
  • 679 replies
  • April 23, 2025

Hi,

I think ​@Thomas Lind  / ​@Christian Hauggaard  would be the people to ask for a guide on SOAP-REST api - I'm a partner consultant - but in theory SOAP is just XML with formatting rules, so you should be able to decompose the return values with the Table Builder functionality or SQLite. If you take away the SOAP envelope, it is just XML.

If you need to do complex looping across calls to your source, the encryption requirements you have will force you to deal with that in C#. I think the key thing will be avoiding the temptation to do everything in C# if you go down that route.


Thomas Lind
Community Manager
Forum|alt.badge.img+5
  • Community Manager
  • 1057 replies
  • April 24, 2025

Hi ​@thzman 

The RSD files that show soap, shows how you run the SOAP query in that. This is the same in our own TX REST provider, only you add the query in a HTTP request POST call.

How to run the various changes to the query without using the C# code I don’t know, but the dynamic values like date and so can be set beforehand to be used in the query like other dynamic values.


  • Author
  • Participant
  • 21 replies
  • April 24, 2025

Hi ​@Thomas Lind 

Yes, now that you mention it, I remember seeing that in a guide, but I’ve never used it.

I have to revisit that.

I guess our biggest problem will be how to trigger the execution of the C# application in Orchestration with the dynamic values. I have to put some thought into it.


Thomas Lind
Community Manager
Forum|alt.badge.img+5
  • Community Manager
  • 1057 replies
  • April 24, 2025

@thzman 

Does it have to be done in that C# application?


  • Author
  • Participant
  • 21 replies
  • April 24, 2025
Thomas Lind wrote:

@thzman 

Does it have to be done in that C# application?

No, not necessarily, if there might be some other way.
The reason was that we got a prepared C# code file from the provider so that looked like an approachable way. The main point is that we have to encrypt the fingerprint with AES using the dynamic values. How we go about it is less important.
Did you have some other solution in mind?


  • Author
  • Participant
  • 21 replies
  • April 28, 2025

Hi,

Just thought of something but then i don’t know if it’s viable or not. :)
Just a speculation of mine so I haven’t tried it yet.

Let’s say I have my C# application set up and it can be run with parameters från Powershell. 

You can run any SQL code in the Dynamic Values section of the endpoint, right?
And from what I understand, if the result of a query in the Dynamic Values section is a “list” of some kind, the endpoint will iterate over that list, right?


If this is the case, I could more or less create a stored procedure in the database that uses two dates as input. That SP calls the C# application, that makes all the magic with the encryption and stuff, via xp_cmdshell and the end result would be a “table” with the request headers already prepared.

This could then be iterated over, one request at a time, via the HTTP-request and the result would be processed as usual.


Could this be a way forward here?
What I don’t know is how exactly TX handles the dynamic values. 

If the Dynamic Values doesn’t work as I presume here, would it be possible to make a “dummy” connector that only produces the list of requests and use that connector as a dynamic input to the “real” connector?


rory.smith
TimeXtender Xpert
Forum|alt.badge.img+7
  • TimeXtender Xpert
  • 679 replies
  • April 28, 2025

The SQL you can use in data source connectors is an in-memory SQLite. In the past you needed to supply your own SQL database which would have made this kind of parameter passing possible. Note that exposing xp_cmdshell to an external tool is a gigantic security issue waiting to happen. As an alternative you can set up your C# app to have a REST API endpoint and call that from SQL Server 2025.


  • Author
  • Participant
  • 21 replies
  • April 28, 2025

Hi,

Yes, we are using on-prem SQL Server and have used that as a backend to read dates and stuff from the DSA, so that was why I thought that this might work. The xp_cmdshell call would be done from inside the SP. We are using SQL server 2022 for the moment so setting up an API might not be an option.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings