Unable to query Indexing Services with a SQL Authentication User

Question

With the Windows 2003 end-of-life data looming next month, I am migrating a legacy application to Windows 2008. This has all gone surprisingly smoothly with the exception of our connection to Indexing Services.

We have two servers, a DB server running SQL 2008 and a Web server. Our web application allows users to search a document store. Here's the process:

1. User types in query to web application
2. Web application sends a query to the database server
3. The query references the web application as a linked database server and loads the retrieved paths into a temporary table
4. The database server joins those paths against another table in the SQL server
5. The database server sends the results back to the web application
6. The web application shows the results to the user.

The web application logs in via SQL authentication to the DB server to perform the query, but it fails with this error:

An error occurred while preparing the query "SELECT PATH FROM "10.0.1.89".MyCatalog..SCOPE('DEEP TRAVERSAL OF "C:\Documents"') WHERE (FREETEXT(Contents, 'introduction')) OR (FREETEXT(FileName, 'introduction'))" for execution against OLE DB provider "MSIDXS" for linked server "Filesystem".

The same error is displayed when I attempt to execute the query when logged in as this user on the DB server through SSMS, but with some extra information:

OLE DB provider "MSIDXS" for linked server "Filesystem" returned message "Invalid catalog name 'MYCATALOG'. SQLSTATE=42000 ".

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "MSIDXS" for linked server "Filesystem" reported an error.

Access denied.

Msg 7321, Level 16, State 2, Line 1

An error occurred while preparing the query "SELECT PATH FROM "10.0.1.89".MyCatalog..SCOPE('DEEP TRAVERSAL OF "C:\Documents"') WHERE (FREETEXT(Contents, 'introduction')) OR (FREETEXT(FileName, 'introduction'))" for execution against OLE DB provider "MSIDXS" for linked server "Filesystem".

However, when I log in to SSMS with my Windows Authentication account, I am able to perform the same query and it returns result. My username and password must be the same as an account on the web server - if I change my password on the web server, an error is thrown, but not the same one:

OLE DB provider "MSIDXS" for linked server "Filesystem" returned message "Unspecified error".

OLE DB provider "MSIDXS" for linked server "Filesystem" returned message "Invalid catalog name 'MYCATALOG'. SQLSTATE=42000 ".

Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "SELECT PATH FROM "10.0.1.89".MyCatalog..SCOPE('DEEP TRAVERSAL OF "C:\Documents"') WHERE (FREETEXT(Contents, 'introduction')) OR (FREETEXT(FileName, 'introduction'))" for execution against OLE DB provider "MSIDXS" for linked server "Filesystem".

I created the linked server with this query, which appears to match the configuration of the 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 a user on the web server with the name CatalogUser, and I set its password to be the same as the query above.

When I connect with Windows authentication however, this information seems to be ignored and my Windows authentication data is used instead. If I change the password to anything, the query still succeeds.

I have tried enabling CatalogUser to log on as a service account, and this has had no effect.

The two machines are not on a domain, but neither are the two Windows 2003 machines, and that works totally fine.

I must have missed something from the set-up between the two. Unfortunately, these machines were set up long before people started keeping documentation for stuff like this, so I'm not sure what the missing step is.

There isn't very much information about this issue on the internet, presumably due to the age of the software, so at the very least we can start to build a knowledge base here. I wonder if more people are having issues with this software now the end of life is on the horizon.

Answer 1

I have now resolved this issue. I didn't know this, but when you run a program under NT AUTHORITY\LOCAL SYSTEM, it will authenticate over the network as an anonymous user.

To resolve this, I created a new SqlServer user on both the web server and the DB server with the same password. I then configured my SQL Server instance to run under this new user, and it started authenticating correctly.

It seems that any security information entered for the MSIDXS backend is just ignored, although perhaps it wouldn't be if you were joined to a domain. All other documentation I read suggested that was the only way to do this, but it wasn't the case in the original setup and it isn't the case here.