The CSV file URL has to be public and direct.
A direct URL means:
It is not a link to a file previewer webpage. Try to open the link in a private window on your browser. A CSV file should start downloading.
If you’re trying to import a private URL, the login page will be fetched. And that’s why you might this error.
Please note that to get a direct URL in Google Sheets, you would need to change it to something like this: