sql server - Unable to query Indexing Services with a SQL Authentication User -

with windows 2003 end-of-life data looming next month, migrating legacy application windows 2008. has gone surprisingly smoothly exception of our connection indexing services.

we have 2 servers, db server running sql 2008 , web server. our web application allows users search document store. here's process:

1. user types in query web application
2. web application sends query database server
3. the query references web application linked database server , loads retrieved paths temporary table
4. the database server joins paths against table in sql server
5. the database server sends results web application
6. the web application shows results user.

the web application logs in via sql authentication db server perform query, fails error:

an error occurred while preparing query "select path "10.0.1.89".mycatalog..scope('deep traversal of "c:\documents"') (freetext(contents, 'introduction')) or (freetext(filename, 'introduction'))" execution against ole db provider "msidxs" linked server "filesystem".

the same error displayed when attempt execute query when logged in user on db server through ssms, information:

ole db provider "msidxs" linked server "filesystem" returned message "invalid catalog name 'mycatalog'. sqlstate=42000 ".

msg 7399, level 16, state 1, line 1

the ole db provider "msidxs" linked server "filesystem" reported error.

access denied.

msg 7321, level 16, state 2, line 1

an error occurred while preparing query "select path "10.0.1.89".mycatalog..scope('deep traversal of "c:\documents"') (freetext(contents, 'introduction')) or (freetext(filename, 'introduction'))" execution against ole db provider "msidxs" linked server "filesystem".

however, when log in ssms windows authentication account, able perform same query , returns result. username , password must same account on web server - if change password on web server, error thrown, not same one:

ole db provider "msidxs" linked server "filesystem" returned message "unspecified error".

ole db provider "msidxs" linked server "filesystem" returned message "invalid catalog name 'mycatalog'. sqlstate=42000 ".

msg 7321, level 16, state 2, line 1 error occurred while preparing query "select path "10.0.1.89".mycatalog..scope('deep traversal of "c:\documents"') (freetext(contents, 'introduction')) or (freetext(filename, 'introduction'))" execution against ole db provider "msidxs" linked server "filesystem".

i created linked server query, appears match configuration of legacy system:

exec sp_addlinkedserver filesystem, 'index server', 'msidxs', 'web'; exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname = n'filesystem', @locallogin = null, @useself = n'false', @rmtuser = n'cataloguser', @rmtpassword = n'xxx'; 

i created user on web server name cataloguser, , set password same query above.

when connect windows authentication however, information seems ignored , windows authentication data used instead. if change password anything, query still succeeds.

i have tried enabling cataloguser log on service account, , has had no effect.

the 2 machines not on domain, neither 2 windows 2003 machines, , works totally fine.

i must have missed set-up between two. unfortunately, these machines set long before people started keeping documentation stuff this, i'm not sure missing step is.

there isn't information issue on internet, presumably due age of software, @ least can start build knowledge base here. wonder if more people having issues software end of life on horizon.

i have resolved issue. didn't know this, when run program under nt authority\local system, authenticate on network anonymous user.

to resolve this, created new sqlserver user on both web server , db server same password. configured sql server instance run under new user, , started authenticating correctly.

it seems security information entered msidxs backend ignored, although perhaps wouldn't if joined domain. other documentation read suggested way this, wasn't case in original setup , isn't case here.