Can Azure SQL Preserve Original Time Zone for Auditing?

Short answer

Azure SQL Database cannot reconstruct a user's original time zone from stored data alone. AT TIME ZONE converts values correctly only when you supply the zone, and datetimeoffset stores the offset but not the zone name. The reliable audit model is: store the instant as UTC (or datetimeoffset normalized to UTC) plus the original zone name in a separate column, and convert at read time.

Answering your three questions

1. Does AT TIME ZONE preserve original wall-clock context?

No — it preserves the instant, not the context. When applied to a datetimeoffset, AT TIME ZONE returns the same point in time expressed in the target zone, applying that zone's DST rules for the date in question. That is exactly what you want for display, but it tells you nothing about which zone the user originally picked. Two users in different zones who entered the same UTC instant are indistinguishable afterward. Also note the trap you flagged: applying AT TIME ZONE to a plain datetime (no offset) interprets the value as already being in the named zone and attaches an offset — it does not convert. Chaining it (AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') is the correct two-step pattern for converting a naive value.

2. Mapping Windows to IANA names

SQL Server's sys.time_zone_info exposes Windows names (Eastern Standard Time), while most application stacks use IANA names (America/New_York). The standard mapping source is the Unicode CLDR windowsZones.xml data. In .NET, TimeZoneInfo.FindSystemTimeZoneById accepts both on modern .NET 6+ (cross-platform), and libraries like TimeZoneConverter wrap the CLDR mapping explicitly. Keep the mapping in application code, not in SQL, and treat it as versioned data — mappings and DST rules change over time.

3. Which storage model gives the best audit trail?

Store both: a UTC-normalized datetimeoffset (or datetime2 in UTC) for the unambiguous instant, and a text column with the original zone name (preferably IANA). The offset alone is insufficient because an offset of -05:00 could mean US Eastern in winter or fixed-offset Bogotá year-round — after a DST change you cannot re-derive what the user saw. For recurring schedules this is even more important: the recurrence must be evaluated against the named zone's rules, not a frozen offset.

Verification

-- Zones available on this server
SELECT name, current_utc_offset, is_currently_dst
FROM sys.time_zone_info
WHERE name = 'Eastern Standard Time';

-- Round-trip test across a DST boundary
DECLARE @dto datetimeoffset = '2024-03-09 12:00:00 -05:00';
SELECT @dto AT TIME ZONE 'Eastern Standard Time' AS original_zone,
       @dto AT TIME ZONE 'UTC' AS as_utc;

Test values on both sides of a known transition (e.g., 2024-03-10 for US zones) and confirm the local wall-clock result matches expectation before committing to the design.

Caveats

Windows-to-IANA mapping is not always one-to-one; a few Windows zones map to multiple IANA zones, so store the IANA name captured from the client rather than reverse-mapping later. DST rules themselves change by legislation, so historical conversions reflect the rules in the server's current time zone data — flag this if your auditors require rules-as-of-the-event-date fidelity.