Saturday 19 March 2022

Integrating Software & Data Thoughts

Problem: Systems need to communicate, the simplest is a one-way communication (Mono directional) done on a scheduled basis, the hardest is 2-way comms, in real time. 

Hypothesis: Figure out you requirement, this sounds odd but 2 way with both being the primary causes headaches.  Adding multiple systems and security at a user level makes integration more complex. 
Keep it simple figure out: 
  • Apps to connects - Can be an N-to-M set of relationships 
  • Direction/Parent - One way, 2 way. 
  • Re-occurrence - scheduled, events, real-time. 
Your application will generally be the spoke and not the hub.  So if you are the hub the generally accepted way for SaaS is for the hub to offer an Open API and the hub controls the schedule/event to get or push the data to the hub.

Possible resolutions
  • Central Sw that has connectors can be a good option for complex multisystem communication: https://www.codat.io
  • Always understand the API's hopefully you don't need to use exports to get data but sometimes you need to fall back to this level.
  • Also ensuring transactions are completed over 2 systems need to validate data is committed, use Saga or 2PC pattern.

Sunday 13 March 2022

Generating a pdf from a word binary - Power Platform

Overview: Move a word document into a pdf stream in a Power Automate flow.

Solution: I am triggering a flow when a word document is created in Dataverse.  I get the word document in a stream and use OneDrive for Business (OD4B) to persist the docx to OneDrive.  I use the Power Automate Word for Business connector to convert the docx in OD4B into a pdf binary stream in my flow.  


Tip: The Location (OneDrive site collection) gets converted to a guid, so if you need a separate site collection for OneDrive or SharePoint, you can use the MS Graph and I believe this URL also works: 
https://radimaging.sharepoint.com/personal/paulbeck/_api/v2.0/drives.

Tuesday 1 March 2022

TLS 1.2 & Wireshark Refresher

Overview:  I have lost count of the number of TLS upgrades I have worked on.  The upgrades are easy as long as you understand the basics.  I always throw a test rig together to check settings, update settings and get the configuration and the ability to check the traffic.  

Understanding Server and Client Registry Settings for TLS

  • "Client" registry keys govern outbound protocol availability.
  • "Server" registry keys govern which protocols are available for inbound connections.

Check TLS 1.2. connections from a client to a SQL Server:

The client machine host a Console/C# Test Rig that is connecting to SQL Server that supports only TLS1.2.  Then on the client Windows registry settings you are interested in the TLS1.2/Client/Enabled setting as shown above.  

  • Below is a screen shot of a C# test rig, used to ensure the registry settings are correct (I restart the test rig after I make a change to the client/TLS registry settings.  Running the "Test Connection", tells me outbound traffic is using TLS1.2, the successful connection tells me that SQL Server is accepting TLS1.2 traffic.

If unsuccessful or you want proof: 
Wireshark - replicates the network traffic packets going thru the machines drivers/adapters.
  • Wireshark is a great tool, and can be used to verify the underlying traffic, in this scenario I have Wireshark on the client machine to capture and analyze the TLS traffic going to the SQL Server generated by the C# test rig.
  • Ensure you have the adapters you want to monitor selected, and I have added a  filter to only show traffic being sent to the SQL Server using the IP address.  "hosts 10.1.x,25"

Capture the traffic, you'll see the connection being setup in the packet transfer.  The protocol will show TLSv1.2 in Wireshark.

TLS1.2 traffic

TLS1.0 Outbound Traffic

Use Netsh or Pktmon to capture local adapter traffic, then load the logs in Wireshark.  c:> netsh trace start capture=yes from the admin run cmd prompt to capture packets.   Packet Monitor (Pktmon) is also an option.

tcpdump is a good tool also. 

More Info:

https://social.technet.microsoft.com/wiki/contents/articles/41703.sql-server-securing-network-communications.aspx

Sunday 27 February 2022

Delphi TLS 1.2 to SQL Server connectivity issue

Thanks to Akshay for  figuring the issue out & working on this with me.

Problem:  C# is connecting to SQL Server using TLS1.2 & TLS 1.0.  But  Delphi application only connects using TLS1.0.

Initial Hypothesis:  

Ensure the client machine can connect using a C# test rig, or check the traffic works from any app using Wireshark.

SQL Server is accepting TLS 1.2 connections using C# but not Delphi.  SQL is good, and the client machine allows outbound TLS 1.2 data.  Issue must be with Delphi.  Delphi is using SDAC 6 to connect to the database.  Issue must be with Delphi connectivity.  Delphi SDAC uses the "SQL native client" for TLS1.2 but TLS 1.0 works without the Native SQL client.  SDAC tries to use the "Native SQL Client", if it is not there it use OLEDB.  The OLEDB version used by SDAC6 does not support TLS1.2 only TLS1.0.  Wireshark showed the Delphi application trying to connect but can't get a TLS1.2 connection.  Installing the "Native SQL Client", the TLS traffic starts connecting on TLS1.2 using SDAC6.

Resolution: Upgrade Delphi/SDAC9 (or later) or install the "SQL Native Client" on all client laptops/machines.

Sunday 6 February 2022

Azure DevOps Series - Integrating Security into Azure DevOps

Overview: Integrate automate security testing into your CI/CD  Azure Pipelines, this area is of expertise is sometimes refered to as DevSecOps.  Azure DevOps provides build and automation servers.  In the OSAWP 2021, number 8 is Software and Data integrity failures.  This covers securing CI/CD pipelines.

CI/CD Pipeline hardening - Code is written and committed to source code repository, Linting (SonarQube), build , test, and deploy.  Can also include infrastructure and networking setup.  YAML & JSON are common for building pipelines.  All these steps need to be hardened.  

Ensure only authorized intended actors can run/use the pipeline or part within the pipeline.  Eg. ensure only developers can check in code, they must have permissions.  

  • Harden but you have to be pragmatic so developer can do there work but also don't over allow access.
  • Ensure logging is running.  
  • Keep plugins and reference frameworks up to date to avoid weaknesses being exploited.  Ensure OS and containers are up to date.
  • Use dedicate build/service accounts.  
  • Using Azure DevOps (ADO) does a lot of hardening automatically.  
  • Don't expose sensitive information in you logs like pswds as if the logs are hacked you have a problem.
  • Ensure artifacts are correctly locked down.  To get artifacts the pipeline only needs read access.
  • Verify SaaS service you use are secure.  Integrate external security SaaS software.
  • For security there are native tools for security, plugins or external service as mentioned above.  Mend/WhiteSource Bolt is a tool used for scanning packages for vulnerabilities.  AzureDevOps has Mend Bolt as a add-in.  There is a free service but it is fairly limited.  Can also run these scans from Developer level and not just in the pipeline.

Azure DevOps Series Posts:

  1. Azure DevOps Series - Overview 
  2. Azure DevOps Series - Azure Boards 
  3. Missing
  4. Azure DevOps Series - Azure Pipelines 
  5. Azure DevOps Series - DevSecOps (This Post)
Note: AWS Pipelines refered to as CodePipelines is the same as Azure DevOps in AWS world.  

Micro-Frontend Architecture Pattern

Micro-Frontends (MFE) are the technical representation of a business subdomain, they allow independent standalone implementations for a domain area. 


Microsoft Defender for Cloud for B2B SaaS solution based on Azure PaaS

Microsoft Defender for Cloud were previously called Azure Security Center and Azure Defender.  Its function is hardening via suggestions and actively monitoring (and stopping) usage.  Azure SQL has it's own defender, and Microsoft Defender uses the same SQL tooling as part of it.  It's a great tool, while it can be expensive, even turn on in production for set periods and then deactivate.

Two tiers of Microsoft Defender for the Cloud:

  • Free - Continuous assessment & Hardening suggestions
  • Paid - Same as free but also can apply suggestions and active threat prevention.

Enable it for specific services, at the service level it is all or nothing, so if you have 40 app service instances, it's monitor all or nothing $$$

Microsoft Defender for Cloud - an introduction | Microsoft Docs