Why is stdin closed when using a heredoc as input to shell

You cannot have multiple simultaneous stdin per process, so the here document passed as input to bash cannot contain a read -p.

With bash script, the running script is a subprocess of the Bash shell and there is no concurrent stdin to read, so it will work as intended.

In both cases, the read command inherits its standard input from the bash process that executes it.

In the first example, that means the here document that actually contains the read command. Depending on how much of the document bash has already read (usually all of it), nothing remains for read to read, and so it exits with a non-zero exit status.

In the second example, bash opens the named file on a different file descriptor. read still inherits its standard input from the bash process, but this time bash hasn't read from it at all, so the read command gets the next available line. Standard input here is the terminal, so read blocks until the user enters a line.

As read is a bash builtin, it inherits stdin from bash as is mentioned above. In the first case bash has a here document as stdin (or more professionally, File Descriptor 0) and there's nothing available for read to read.
read only reads from stdin (fd0) while in the second case, bash opens another file descriptor rather than stdin to read scripts from script, which does NOT conflict with stdin passed to read, so that read can work as intended.

You can try this to test out.

$ bash << EOFls -l /proc/$$/fdEOF

and

$ cat script.shls -l /proc/$$/fd$ bash script.sh