Can't enable Full Text on database

Got a strange problem.
Had a SQL 7.0 Box with FTS on a database. Worked fine except for one quirk
we never tracked down. Starting up the MSSearch Service would fail 1/2 the
time due to timeout. Other 1/2 would take a long time (like sometimes an
hour) to start.
Upgraded to SQL 2000, no SP.
Upgrade went fine, similar startup problems.
Applied SP4.
Got the usual warning about having to recreate indices, etc. Not a problem
since we were releasing new code specifically for SQL 2000 FT.
Now... well it just doesn't work.
For example, if I try to start an index build on a table, I get Error 15601:
Full-Text Search is not enabled for the current database...
Ok, no problem.. try
sp_fulltext_database 'enable'
get
Server: Msg 7635, Level 16, State 1, Procedure sp_fulltext_database, Line 46
The Microsoft Search service cannot be administered under the present user
account
First thought was try to synchronize the passwords (i.e. start/stop SQL
Server, change to local account and then back to the domain account so that
MSSearch can talk to it.)
No Joy.
So, at this point I'm either facing calling Microsoft, or taking the server
offline for a day while I uninstall SQL 2000, reinstall it and re-attach the
databases and hope that works.
Suggestions?
--
Greg D. Moore
President Green Mountain Software
Personal: http://stratton.greenms.com
SQL Server Consulting sql at greenms.com
Hi Greg,
Yep, I remember your SQL 7.0 box & the related FTS problems as FTS in SQL
7.0 was problematic at best...
In regards to error "The Microsoft Search service cannot be administered
under the present user account", have you removed or altered the
BUILTIN\Administrators login? If so, then this breaks FTS as the MSSearch
service uses this account to login into SQL Server. If you cannot add back
this login or cannot alter the login back to all of its default for security
reasons, you can use the following SQL code to enable the localsystem
account to have the proper security as a workaround:
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
See KB article 263712 "INF: How To Prevent Windows NT Administrators From
Administering a Clustered SQL Server" at
http://support.microsoft.com/default...;EN-US;q263712 for details.
Hope that helps,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:8Ln0f.8178$QE1.857@.newsread2.news.atl.earthli nk.net...
> Got a strange problem.
> Had a SQL 7.0 Box with FTS on a database. Worked fine except for one
> quirk
> we never tracked down. Starting up the MSSearch Service would fail 1/2
> the
> time due to timeout. Other 1/2 would take a long time (like sometimes an
> hour) to start.
> Upgraded to SQL 2000, no SP.
> Upgrade went fine, similar startup problems.
> Applied SP4.
> Got the usual warning about having to recreate indices, etc. Not a
> problem
> since we were releasing new code specifically for SQL 2000 FT.
> Now... well it just doesn't work.
> For example, if I try to start an index build on a table, I get Error
> 15601:
> Full-Text Search is not enabled for the current database...
> Ok, no problem.. try
> sp_fulltext_database 'enable'
> get
> Server: Msg 7635, Level 16, State 1, Procedure sp_fulltext_database, Line
> 46
> The Microsoft Search service cannot be administered under the present user
> account
> First thought was try to synchronize the passwords (i.e. start/stop SQL
> Server, change to local account and then back to the domain account so
> that
> MSSearch can talk to it.)
> No Joy.
> So, at this point I'm either facing calling Microsoft, or taking the
> server
> offline for a day while I uninstall SQL 2000, reinstall it and re-attach
> the
> databases and hope that works.
> Suggestions?
>
>
> --
> --
> Greg D. Moore
> President Green Mountain Software
> Personal: http://stratton.greenms.com
> SQL Server Consulting sql at greenms.com
>
|||"John Kane" <jt-kane@.comcast.net> wrote in message
news:eDz9GhVyFHA.720@.TK2MSFTNGP15.phx.gbl...
> Hi Greg,
> Yep, I remember your SQL 7.0 box & the related FTS problems as FTS in SQL
> 7.0 was problematic at best...
Oh good. :-)

> In regards to error "The Microsoft Search service cannot be administered
> under the present user account", have you removed or altered the
> BUILTIN\Administrators login?
Not that I am aware of.

> If so, then this breaks FTS as the MSSearch
> service uses this account to login into SQL Server. If you cannot add back
> this login or cannot alter the login back to all of its default for
security
> reasons, you can use the following SQL code to enable the localsystem
> account to have the proper security as a workaround:
> 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
Tried this, still no joy.
Did notice the database in question the name dbo was mapped to a login
called MONITOR (one of our internal accounts) instead of sa or
domain\administrator.
I've removed that mapping. So far hasn't helped.

> See KB article 263712 "INF: How To Prevent Windows NT Administrators From
> Administering a Clustered SQL Server" at
> http://support.microsoft.com/default...;EN-US;q263712 for
details.
>
Nothing here stood out.
Thanks though.

> Hope that helps,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message[vbcol=seagreen]
> news:8Ln0f.8178$QE1.857@.newsread2.news.atl.earthli nk.net...
an[vbcol=seagreen]
Line[vbcol=seagreen]
user
>
|||Hi Greg,
Hmm... It is possible it is something new &/or affected by SP4. I take it
you upgraded to SP4 directly from RTM, and not RTM to SP3 then to SP4.
Correct? I've not heard of any new FTS related bugs in SP4 that are
directly related to this error. However, there are two FIX kb articles
(http://support.microsoft.com/?kbid=900390 - post-SP4 and
http://support.microsoft.com/default...b;en-us;890043 - post SP3)
that I've blogged about in August, neither are well written IMHO.
The below workaround has in the past resolved this issue, however, in all
cases it was pre-SP4. As this is related to a possible SP4 issue, you might
want to open that support issue with MS PSS SQL Server support... :-(
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:zFP0f.9074$vw6.4605@.newsread1.news.atl.earthl ink.net...
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:eDz9GhVyFHA.720@.TK2MSFTNGP15.phx.gbl...
> Oh good. :-)
>
> Not that I am aware of.
> security
> Tried this, still no joy.
> Did notice the database in question the name dbo was mapped to a login
> called MONITOR (one of our internal accounts) instead of sa or
> domain\administrator.
> I've removed that mapping. So far hasn't helped.
>
> details.
> Nothing here stood out.
> Thanks though.
> message
> an
> Line
> user
>