rights issues with linked server

[pb648174]

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\Project757\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?

[pb648174]

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.

[pb648174]

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

[Erland Sommarskog]

pb648174 (go****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[pb648174]

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.

[Erland Sommarskog]

pb648174 (go****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[pb648174]

I fixed it by doing the following:

Instead of trying to use Index server on web server, just used index
server on database server and mapped drive for database server index
server to get at files on web server. Works like a champ.
Erland Sommarskog wrote:

pb648174 (go****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx