When are procedural queries absolutely necessary?
In my experiences, I've run into a few times when procedural/iterative approaches were warranted.

API only allows for single-row operation

If I wanted to programmatically change the data type from real to decimal in a table that has 500 mis-typed columns like this SO question asks, a cursor is a fine approach as the DDL does not allow for altering multiple columns in a single statement.

Set based doesn't scale

If you have the SQL Server MVP Deep Dives book, chapter 4 "Set-based iteration: the third alternative" by Hugo Kornelis has some great use cases for combined cursor/set based operations. Two of classic problems that the chapter author references is the Running Totals and Bin Packing.

I used the set-based iteration approach with good success for a poorly designed process I inherited at the last job. In short, there was a process that once a year had to update 50-75M rows and attempting to do so in a single set would blow our logs. By chunking the updates into smaller batches of N rows, it allowed the log to keep up and actually finished faster than the previous year when they just allocated a metric ton more disk space.