You are having the right approach. Perhaps you missed out the trailing
g in your regex expression that enables matching every occurence of
_ in each filename.
For use with
rename, I would suggest this two-steps approach:
rename 's/[._]//g' *.tif # remove any . and _ occurence
Sep04170037tif Sep04170202tif Sep04170554tif Sep04170717tif Sep04170841tif
rename 's/tif$/.tif/' *tif # replace ending tif by .tif
Sep04170037.tif Sep04170202.tif Sep04170554.tif Sep04170717.tif Sep04170841.tif
Don't hesitate to try out your regexes online first, for instance on regex101, which also teaches you many things about pcre :)
[EDIT]: if you prefer one-liners:
rename 's/[._]//g;s/tif$/.tif/' *.tif
Will also avoid matching unfortunate neighbouring unrelated files that would unluckily also be ending by
tif during the second step.