SQL Server login for SharePoint site login errors SSO

Question

I'm having a very confusing error between SharePoint and SQL Server 2k5.

My SQL Server acting as backend to my MOSS farm has several logins in it which correspond to the web front end servers in my farm, with the pattern: {my-domain}{my-machine}$

Now, those accounts do not exist in AD anywhere, despite the login name syntax, and were generated somehow (assume by MOSS, but can't confirm). One (and only one) of the servers is throwing login failures every 2 minutes; that server was the first in the farm and holds most of the services, just not search and indexing.

I did a number of traces in SQL Profiler, and all I can tell is that the failure is a type 16 error on 'master'; so the login exists but doesn't have rights to 'master'.

Having found that, I went back in and gave it progressively greater rights on Master, including db_owner, and eventually making it a sysadmin. Still no joy, same error.

Diggin further w/ tracing, I found that the actual failure was due to the SSO db not existing; probably b/c it wasn't configured in MOSS. When I tried configuring the error, I got a "Sorry, you're not authorized to do that" error in Central Admin, even though I was logged in as the farm admin, who's also a forest-level admin w/ rights to everything I can think of.

Turning off SSO as a windows service worked, but I'm concerned about my inability to configure it in MOSS, so I dont' want to leave that as a solution.

I'm out of ideas, anyone else have thoughts or experience on this?

Thanks

Answer 1

Try and follow this to configure SSO:

http://technet.microsoft.com/en-us/library/cc262932.aspx

Answer 2

The {my-domain}{my-machine}$ account is an alias for the NETWORK SERVICE built-in local machine account. NETWORK SERVICE is a low privilege predefined account that was introduced in Windows 2003. It has network credentials and can therefore connect to remote databases (as long as they're within the same domain).

It sounds like you've created your SharePoint web applications with the default application pool identity. This will create the logins named {my-domain}{my-machine}$ in SQL Server. So yes, SharePoint created the SQL logins, but they're based on the built-in NETWORK SERVICE machine accounts on the servers in your farm.

I'd check that the account you're using to configure SSO has the rights to create the SSO database. Have a look at the table in Plan for single sign-on. It lists all the privileges required for all the different types of SSO accounts. For the configuration account, the document lists:

SSO configuration account:

• Must be a user domain account. Cannot be a group account.
• The user account must be a server farm administrator.
• Must be a member of the Administrators group on the encryption-key server computer.
• Must be a member of the following SQL Server security roles on the computer running SQL Server:
  • Dbcreator
  • Securityadmin
• Must be either the same as the SSO administrator account, or be a member of the group account that is the SSO administrator account.

If that doesn't help, follow Alex Angas' advice and post this question to serverfault.com.

Answer 3

We had this same problem - the source of your "Not authorized to do that" message when you configure SSO is that you need to be logged into Sharepoint Central Admin as the SSO user (in our case, it was DOMAIN\SSO_Proxy). This allowed us to make the changes we needed.

Good luck!