Sql-server – SQLCLR TVF that calls web service is getting error 401: Unauthorized

Pass-through is a feature of Windows authentication. It's not related to SQL Server at all, and it's "on" by default (I'm not sure if there's a way to turn it off, now that I think about it).

Which accounts you use depends on the environment, and how the permissions are configured on the backup share. I'm assuming in this scenario that the copy job will be a "pull" from production to QA (in other words, the backup share exists within the production domain).

If domain trusts are configured such that a principal on the QA domain can authenticate against a production resource, the backup share (folder) permissions can be directly modified to give read access to whichever QA domain account/group is being used (they don't have to match at all). Some environments allow this kind of configuration, while others don't. If you don't know the trust configuration between the two domains, ask your Windows system administrator.

If there are no trusts configured between the two domains, then you'll need to use pass-through authentication. This is set up by creating identical AD accounts (matching user name + password) on both domains. Assuming appropriate permissions are configured on the backup share (folder) for the production account, the Windows authentication system will automatically handle the rest of the process.

See these two TechNet articles for more information about domain trusts and pass-through authentication.

One of our user from another domain (...)

To authenticate against a resource in another domain, either there must be a domain trust established (ask your Windows sysadmins), or pass-through authentication must be used (very likely not in this case). This is true for Windows in general, not just SQL Server.

Do I need to setup my SQL Server 2008 Enterprise in order to allow domain user (...)

If there isn't an existing Windows user or group login for the user, then yes, you'll need to create a new login.

Kindly note that there might be more than 50 domain users that may required to access the web application and therefore it would be inefficient to add each domain user to the database's user rights

I agree -- with this type of scenario, I would recommend setting up a group in AD, and then creating a Windows group login in SQL Server. This way, access is controlled by AD group membership, and you don't have to mess around with changing logins in SQL Server once it's set up.