It sounds like you’re running into what’s called a “double-hop” issue, which is where the server is not being trusted to pass the client’s credentials on to another box (hop 1 is the credentials to the IIS box, hop 2 is from the IIS box to the SQL Server).
When you’re logged directly into the server, the second hop doesn’t need to take place since it’s just passing credentials directly from the client machine (the IIS server in this scenario) directly to the SQL Server. Likewise, if the SQL Server lived on the IIS box, you wouldn’t have this error either, since the client would only be making the one request to a box that could share the credentials with both IIS and SQL Server.
There are quite a few steps required to get the delegation to work, such as trusting the servers for delegation, creating SPNs and making sure that other proper permissions are given to the account that IIS is using to run the web site. There is a technet article that can help take you through a lot of the required steps here:
https://learn.microsoft.com/en-us/archive/blogs/taraj/checklist-for-double-hop-issues-iis-and-sql-server
Note: if you’re using NTLM and not Kerberos (or another delegatable protocol), it will not work, as the middle server (the IIS server) needs to have a token that it can pass along. Since NTLM is based on negotiation, it won’t work.
2020 Update: if you’re starting to see this issue popup again, and it’s only affecting Windows 10 users, or Windows 2016+ users, it’s likely that “Credential Guard” is being enforced on your users’ machines (see: https://learn.microsoft.com/en-us/windows/security/identity-protection/credential-guard/credential-guard-requirements). One of the things that breaks is Kerberos unconstrained delegation – so if this has happened to you, you’ll likely need to reconfigure the middle box (the IIS server in the example above) to use constrained delegation instead of unconstrained delegation.