Miscellaneous research points from this morning

Search Link Salad

Stuff for me to remember based on current research (more about searching in WSS 2003, but am finding links related to future configurations of SPS2003 or MOSS 2007 search):

Some documents are not returned in the search results when you use the Advanced Search feature in SharePoint Portal Server 2003 to search for content that has a custom property

How to determine if Windows SharePoint Services or if FrontPage Server Extensions is in use in IIS

Database Migration breaks WSS/SQL Server Full Text Search

So here’s a little-known issue with SharePoint and Full Text Search:

On Joel Oleson’s blog (if you don’t know who this guy is and you’re in SharePoint Operations, find out quickly. Aside from Bill English [the man, his blog], he’s the other Man in SharePoint Managment/Operations – both of these guys regularly present at TechEd conventions), I found a blog entry about how, if your Full Text Search (that’s the one that works in Windows SharePoint Services, and is provided through the back-end SQL Server) isn’t working, and you migrated the database from a different server, the reason would be, possibly/probably, that you migrated the database from a different server.

Yes, I mean either with SPSBackup.exe (which should be your friend by now if you’ve been doing a lot of this and you want to keep the SharePoint Portal Search Index between migrations) or normal SQL Backup/Restore and file operations.

So anyway, there are a couple of stored procedures in the blog you should use to restore your Full Text Search. As far as I can tell, these are either SQL standard stored procedures, or, more likely, SharePoint-installed stored procedures.

Also, rather like part of my previous blog entry, the fix is essentially “turn it off, turn it on”, with some curve-balls in there if things don’t go as expected. It turned out to be thorough enough for us, so maybe it’ll be thorough enough for you.

Since I had to puzzle the blog entry out a little bit, I’ll write what I understood of how to do this here.

  1. Mr. Oleson recommends restarting your SQL Services, but we didn’t find this completely necessary.
  2. Run the following stored procedure: exec proc_DisableFullTextSearch
    1. If you get an error about there not being a Full Text Catalog, then run the following in SQL Query Analyzer and start Step 2 again:

      USE [databasename]
      sp_fulltext_database enable

      Where [databasename] is the name of the _SITE database you’re having the issue with. [But don’t actually type the [] brackets in there or your geek compatriots will laugh at you.]

  3. Run the following stored procedure: exec proc_EnableFullTextSearch

So that should be it. The procedure, as I said, is mostly just turn off, turn on again.

Full Text Search and Account Permissions

This is a more extended writeup of running Windows SharePoint Services 2003 and SQL Full Text Search on a Database box where Local Administrators (BUILTIN\Administrators) don’t have System Admin access in SQL Server 2000. (I mentioned this briefly in the Changing SharePoint Service Accounts article.)

Essentially, you’ll run up against this security policy requirement in some environments. It’s a sensible policy to make in situations/operations where the Local Administrators (of whom many are also Domain Administrators) are folks who are different from the folks who own, run and are responsible for the SQL Servers.

Part of the motivation for this separation is, of course, political. In some organizations you’ll find that folks in one team don’t want to share permissions/rights with other teams who aren’t directly responsible for the upkeep or maintenance of the bit of the sandbox they have dominion over.

The Sensible Computer Security Policy reason is the principle of Least Privileges. When the question, “Do these people/does this group need permissions to this resource?” is answered “No.”, then the principle of Least Privileges dictates that they not be given the access they don’t need. This Security Principle falls under the overall category of Risk Management. The fewer potential risks (i.e. fewer accounts sitting around waiting to be hacked that have permissions they don’t necessarily need), the fewer potential security vulnerabilities sit around waiting to be exploited by Joe Q. Attacker.

It should be noted that in the annals of computer attackers, the long-neglected account that just happens to be a local or domain administrator and just happens to have a really easy to guess password is the holy grail, and almost every computer system has at least one. So do what you can to manage your risks and reduce the number of holy grails that attackers can use to compromise your system.

Anyway, so for whatever reasons, you’ve decided that you wish to implement the policy that Local Administrators on the SQL Server are not allowed to be System Administrators (aka sa) within the SQL Server/Application itself. Note that while it appears that Microsoft “supports” this configuration, it’s not specifically allowed for in Microsoft’s relevant Knowledge Base articles, so if you do go this way, be on the lookout for potential complications. See that other article I mentioned and linked to above for an example of an unexpected consequence.

If you remove BUILTIN\Administrators from your SharePoint 2003 server’s SQL Server Logins, or remove the sa permissions from that group, you will hose up your Full Text Search in SQL Server, which of course (say it with me) will screw up your Full Text Search in your Windows SharePoint Services 2003 sites. (Because Windows SharePoint Services 2003 uses SQL Full Text Search to do its searching.)

How do you fix this?

According to KB Article 317746, if you don’t wish to add BUILTIN\Administrators back to the SQL Server Logins, you still have an out. You must:

  • Add the System Administrators Server Role to the account you are using as the Service Account for SQL Server.
  • Add the Local System account (NT AUTHORITY\System) to the SQL Server Logins.
  • Add the System Administrators Server Role to the Local System account (NT AUTHORITY\System).

You should not have to restart SQL Server after making this change. But you may also need to fix Full Text Search for other reasons, which I will elucidate in a (shortly to follow) article.