SSO is setup  at the server level and configured in Central Administration for
the whole farm. You then make use of the service when creating things like Data
Sources in SharePoint Designer or BDC application definitions. To use it in a
second Site Collection simply make a new Data Source and point it at the SSO
service. There is no Site Collection specific  configuration necessary.

That's what I thought but when I try and add a new database
connection in SharePoint Designer I do not get an option for SSO as
an authentication method. Screenshots follow below.

Site collection  1 - working  and pulls from SQL

http://simcoxsouth.homestead.com/files/ss1.jpg

Site collection 2 - no option for SSO under authentication

http://simcoxsouth.homestead.com/files/ss2.jpg

Are both site  collections in the same farm, and both installed using
MOSS Enterprise media and licensing?

Yes, both sites are in the same farm and were installed using the
MOSS Enterprise Ed DVD. The only difference between the two I can
think of is that they are using seperate content databases.

Yes both site  collections are in the same farm and we are using the
Enterprise edition. The only difference I can think of is that they
are using different content databases.