rights issues with linked server

I have a web server and database server which are not on a domain. The
web server has an app that is running on under the IUSR account and
there is a corresponding IUSR account on the database server. The web
app connects to the SQL server with the windows authentication and
everything works fine.

I also have a stored proc which does a file search using a linked
server to an index server on the very same web server. For now assume
that cannot change, the files must be on the web server (TRA is the
machine name). When attempting to execute the following query from the
database server, doing a RunAs on the SQL 2005 Management studio as the
IUSR account from the database server, everything works fine:

SELECT
Q.*
FROM
 OPENQUERY(FTIndexPM,
 'SELECT path, characterization, rank, hitcount
FROM TRA.CatalogTest..SCOPE(''DEEP TRAVERSAL OF "C:\Program
 Files\dir1\Files\Pro
ject757\DocMgmt"'') WHERE CONTAINS(''"test"'') > 0
OR FileName LIKE ''%test%'' ORDER BY rank DESC') AS Q

The IUSR_TRA account is an administrator on both the database and web
server for debugging reasons until we figure this out, but clearly it
shouldn't be a rights issue if both users are administrators on both
machines.

Now for the tricky, part. When the same query as above is executed from
the web server, it fails with the the below error:

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

Using SQL Profiler, I have verified that both queries are actually
running under the IUSR_TRA account. Any ideas why the exact same query
running under the exact same NT login would behave differently when
executed from a web app as opposed to through a clinet request from a
web app?

As for the last question, I meant through the SQL 2005 query tool as
opposed to the web app. Same command, same executing windows user,
different results.

Anyone know where this should be posted where it might get a response?

pb648174 (google@webpaul.net)  writes:
> Anyone know where this should be posted where it might get a response?

Maybe an IIS or ASP group. It's completely beyond my domain, so I don't
really know.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Could you perhaps comment on the last question, i.e. if in SQL Profiler
they are both running under the same login, could anything be different
about their access rights? In SQL 2005, when I try to change the
authentication for a linked server, it seems to have no effect as well.
I can change the user/password to sdfsdf/sdfsdf and it still works...
Seems like there are issues with linked server authentication in SQL
2005.

pb648174 (google@webpaul.net)  writes:
> Could you perhaps comment on the last question, i.e. if in SQL Profiler
> they are both running under the same login, could anything be different
> about their access rights? In SQL 2005, when I try to change the
> authentication for a linked server, it seems to have no effect as well.
> I can change the user/password to sdfsdf/sdfsdf and it still works...
> Seems like there are issues with linked server authentication in SQL
> 2005.

There is still a web server involved. And probably some Windows networking.
Both fall outside my field of expertise.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx