Looks like this also nabs WSS 2003 on the same server.
Reading the 7 Development Projects with the 2007 Microsoft Office System and Windows SharePoint Services 2007 eBook (Microsoft Press) yesterday, I was pleased to read that we’re moving off of the sometimes wildly inaccurate results-giving Full Text Search technology that SQL Server provides for Windows SharePoint Services 2003 and using the same technology the Portal is using in the SharePoint 2003 version for both Portal and WSS in 2007.
Just a little note. I’m pleased as Punch. I hope the administration/configuration has been tweaked to be a bit more intuitive, though.
Also, there appear to be a bunch of other books available to buy about 2007 these days. I should hie to a bookstore.
(Note: Links to KB Articles open in new windows)
So I have a client who is security conscious enough to ask that we make SharePoint work even when the servers’ Local Administrators (members of BUILTIN\Administrators) are not members of the System Administrators Server Role in SQL Server. This is the default configuration for SQL Server – the BUILTIN\Administrators group should have a checkbox enabled next to the “System Administrators” role in the Server Roles tab of the Login properties for SQL Server.
After this helped break Full Text Search (KB Article 317746) and we fixed it, all was well and good, or so we thought.
It’s not completely obvious, but in SharePoint Portal Server 2003 running on Windows 2003 Enterprise SP1 and above (KB Article 555309), it’s required that the SharePoint Service Account be part of the servers’ (in the farm) Local Administrators. This would appear to convey the idea that SharePoint Service Accout should also be a System Administrator (role) in the SQL Server that hosts the configuration and content databases.
I believe, by running into it head first, we have corroborated this assertion.
Moving from a bad situation (where all three of our environments – Dev/Pilot, QA and Production – are running on the same service account whose password will expire in about 2 months), to a better one (we plan 2 service accounts for each environment), means we have to change the service accounts for both SQL Server services, and for SharePoint services in each environment.
(By the way: It’s actually recommended that if possible, service accounts associated with Enterprise servers like SharePoint be set with passwords that don’t expire. If your security policy requires it, you can then plan to change the password on a regular basis, but you won’t get caught out if you simply don’t have time to do it. The slightly more antagonistic policy is to make the automatic experation/reset loom over your operations people like an unassailable threat that motivates them to change the password regularly, manually, before the deadline. If you live in a more antagonistic sort of place like we do, at least have the common decency not to keep folks from changing the password early. Making the password change be required to happen on a certain day is the kind of sadism that breeds MAD IT CONSULTANTS! MAD, I TELL YOU!)
In doing so, we ran up against the problem of not having the SharePoint service account be a System Administrator in the Server Roles in SQL Server. We followed the KB Articles (SharePoint: KB Article 837813, SQL: KB Article 283811 – For SQL we actually used the SQL Enterprise Manager – it’s the easier way – try SQL Books Online for guides) for changing the service accounts, and when we got to checking the SharePoint Central Administration (to finish up the account changing process), instead of the SharePoint Central Administration, like we thought we’d get, we got a prompt to disconnect from the configuration database.
So, in a fit of troubleshooting pique, we followed the prompt and got ourselves into a deeper hole. Because at the instant that we disconnected, the server we were working on got out of synch from the rest of the server farm.
And how did we get out?
First, the primary fix is to make sure that if you’re not going to give all the Local Administrators System Administrator access in SQL Server, at least make sure that the SharePoint Service Account has System Administrator access. That’s the first thing.
Second, we need to fix the mess. If we hadn’t disconnected from the configuration database when the opportunity presented itself, restoring the System Administrator access for the service account in question would fix the whole kit and kaboodle (requiring, perhaps, closing and opening IE, or forcing a cache refresh [hold down Ctrl and click Refresh button] to see the results in IE).
Instead, since we disconnected the database at probably the worst time, the server that disconnected thinks it’s disconnected, but the rest of the farm doesn’t. Weirdly enough, the broken server acts like it thinks it’s both part and not part of the farm. I managed to get the server to the point where it serves content just fine from WSS, but SharePoint Central Administration thinks it isn’t connected to the configuration database (i.e. it’s broken).
When you try to connect to the existing configuration database, you get a weird and inappropriate error. Usually you get the error, “Unexpected error occurred.” (This error actually tells you a lot. If you google for it, you’ll find that most of the issues associated with this symptom are to do with network connectivity – this isn’t utterly bizarre – from SharePoint’s perspective, a SQL permissions error can look a lot like a network error, and whatever’s bunged up in the broken synchronization is also probably interfering and giving SharePoint weird results, which probably just go in the network bucket, because SharePoint can’t figure out where else to put it.)
Weirder still, it seems like the whole of the problem exists somewhere in the broken server’s configuration, with none of the actual problem on the working parts of the farm at all.
Why do I say this? Because the fix is really simple:
- Create a new configuration database with the broken server.
- Disconnect from the new configuration database.
- Connect to the old configuration database.
- You probably want to delete the new, unused configuration database too, but the things are pretty small, storage-wise.
If the synch error really were partly on the original configuration database and partly on the broken server, this shouldn’t fix the problem, because it would still exist in some part on the original configuration database. But in fact, this works.
Now, if, like us, you were in the middle of changing accounts for your farm, just continue on your merry way. Since you only need to make that System Administrator Server Role for the SharePoint Service Account fix once (assuming you’re only using one SQL Server in the farm), you should be good to go with the rest of the servers in the farm.
Since I should be doing this tomorrow in our QA environment (Dev’s down right now), I’ll let you know if there’s more to know, when I’ve figured it out.
Wish me luck!
I already have a Livejournal, where posts of a more personal, less technical nature, will go. My current work cycle is very focused on System Engineering/Architecture/Maintenance with SharePoint in mind, and may be devoid of .NET tips for quite some time. I also don’t wish to create posts just to post.
So when things come up that I think are new or niggly enough that they’re not in MSDN (I will have checked) or Technet (same) or on other blogs, I’ll try to post them here. I already have one post in mind, which is about permissions on SharePoint, the back end SQL Server, and the arcane task of changing the service accounts in both while a farm is still up and running.
Having run into some issues, I’m here to tell you that it’s tricky but possible to even recover from a point where Microsoft’s recommendation would be to just throw it all out and reinstall SPS/WSS completely.
Anyhow, this post is to talk about my goals (done), and plans (will do). It’s also to note that being this is a new WordPress blog, it’ll be a bit before I install the really heinously aggressive spam control measures, but I’ve already specified that comments must be held and approved, so if you post a comment, please give me time to approve it before it shows up.
See y’all in the blogosphere!