Windows to Mac database connection intermittently available after Windows Security prompt
The issue appears to have been caused by the Mac going into Sleep mode.

It appears as though trying to connect using Windows Explorer from a Windows machine is issuing a Wake On Lan request, which wakes the target Mac for 30 seconds or so. During this 'wake' period I was then able to connect.

By implementing a Wake On Lan request from my application, I now seem to be able to reliably connect again to the target mac.

I would not have expected the target 'server' to be put into sleep mode, however, because we don't control the environment in which our software operates, we need to account for all possibilities really.

Because the target mac and windows computers are on different sub-nets, I've only been able to issue the wake on lan directly to the target mac's IP address, rather than completing a broadcast wake on lan.

I was able to modify the code available here to transmit the WOL to the target mac.