Tableau Server can use Windows Authentication to login into the SQL Server. Otherwise, yes, a username and password would need to be set up for the users to connect to the data. See the section in the product help linked below about authentication methods to MS SQL Server.
Additionally, Tableau Server allows for SQL Server Impersonation to be used as well, which allows a single account to act on behalf of others when connecting to the SQL Server. More information can be found in the product help linked below.
Additionally, if you are running into issues with setting up Kerberos with Tableau Server, please share the error that was being encountered and I will do my best to provide information on how to resolve it. Given setting the Kerberos environment is still desired.
Hope this helps!