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.

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.

More Info:

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