Every BizTalk install is different

here's a new one - a BizTalk server in a different domain to the SQL server.
SSO configuration fails with Login failed for user , even though the account is correct, the correct username and password is supplied and the user is in the right group (which you can see in SQL server).

The state parameter to the error gives a further clue: state 12 = Valid login but server access failure

http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

In our case it was because the groups were set up as Domain Local Groups from the domain that SQL wasn't in - I assume this causes the issue because permissions can't be assigned to the group because its local to another domain.  Changing the groups to domain global fixed it.

http://support.microsoft.com/kb/872924

If you configure BizTalk Server to use domain groups, you must create and specify domain global groups

http://support.microsoft.com/kb/884417

If you plan to use one domain for all your servers and no Wide Area Network (WAN) exists, we recommend that you use domain local groups. 

If you plan to have a multiple-domain topology, and the following conditions are true, we recommend that you use domain global groups:

• The SQL Server-based server is in a data center.
  
• You have a perimeter network (also known as DMZ, demilitarized zone, and screened subnet).

So, the recommendation is Domain Local Groups for single domain installs, and Domain Global Groups for multi-domain installs.