Full Text Catalog Item Count 0

Question

Post reply

Full Text Catalog Item Count 0

Ned

Ten Centuries

Points: 1214
More actions
April 20, 2005 at 2:30 pm

#164241

I have a full text catalog that has an item count of 0 and there is definitely data in the columns that are being indexed. There are no errors in the event log or sql error log that give any hint as to what the problem is. I found a post on google that led me to add the [NT Authority\System] account and make it sa temporarily. I did this, and ran an incremental population and it worked. The item count is greater than zero. Obviously I can't leave the [NT Authority\System] as a login. Any ideas as to what the problem is?
Thanks,
Noel

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 1

Hi Noel,

Can I ask whar's in the columns that you're full text indexing? text, blob, etc

John Kane SSCrazy Points: 2810 More actions · Answer 2

Noel,

The problem you've encountered is well known as the BUILTIN\Administrators account MUST exist for the external "Microsoft Search" (mssearch.exe) server to access SQL Server and this is by design. A workaround as you found on Google, is to remove the BUILTIN\Administrators account and run the following SQL code:

exec sp_grantlogin N'NT Authority\System'

exec sp_defaultdb N'NT Authority\System', N'master'

exec sp_defaultlanguage N'NT Authority\System','us_english'

exec sp_addsrvrolemember N'NT Authority\System', sysadmin

The basic fact is in order to use FTS, the external MSSearch service must have sysadmin rights to access SQL Server either via the BUILTIN\Administrators account or the above SQL code and this is by design for SQL Server 2000.

Thanks,

John

SQL Full Text Search Blog

http://spaces.msn.com/members/jtkane/

John T. Kane

Ned Ten Centuries Points: 1214 More actions · Answer 3

Thanks John,

So following that logic then, could I set the Microsoft Search Service to run under the SQL Service account?

Are there any security concerns to doing this?

Setting the search service to run under the SQL Service account seems like much less of a risk than adding [NT Authority\System] to sysadmin. Correct me if I'm wrong but that would give any service running under local system sysadmin to the SQL Server.

Noel

Ned Ten Centuries Points: 1214 More actions · Answer 4

Ned

Ten Centuries

Points: 1214

April 21, 2005 at 8:15 am

#554095

Mike the variables are nvarchar and text.

Noel

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 5

Cheers Noel, but seeing as you've got John on the case I'll bow out entirely.

What he doesn't know about full text indexing isn't worth knowing

Hope you're keeping well John and they're not working you too hard.

John Kane SSCrazy Points: 2810 More actions · Answer 6

Noel,

Q. So following that logic then, could I set the Microsoft Search Service to run under the SQL Service account?

A. While logical this may seem correct, in practical terms this is incorrect. The "Microsoft Search" (mssearch.exe) service MUST be started & running under the system account, i.e., LocalSystem account.

Q. Are there any security concerns to doing this?

A. Yes, there are some, but then there are security concerns when you give your DBA's sa permissions too, in the end you must trust someone. Unfortunately, this is by design as the MSSearch service must run under LocalSystem in order to access SQL Server. Note, this does change in SQL Server 2005.

The "Microsoft Search" (mssearch.exe) service MUST be started & running under the system account, i.e., LocalSystem account AND you must either have the BUILTIN\Admisrators account established with sysadmin rights or grant the LocalSystem sysadmin rights. Both are required for the full functionality of Full Text Search in SQL Server 2000.