Although no more replies here, I figured it out together with my colleague.

One thing that went wrong was the UDP port 1434 should be open instead of 1433.

Another thing I corrected is Windows Authentication for the website was not enabled (but that shouldn't affect the IIS can't locate the SQL server/instance)

This is still a very strange case.

Anyway, it is working now. Not fun but OK because I learn some lesson and I want to share with you guys here.