Querying Indexing Services from ASP.Net page

[C Jay Pemberton Jr]

I am attempting to do the following, query indexing services through SQL
Server

SQL Server 2000/Visual Studio 2003

I have a linked server created called Web_service that points to a Server
running Indexing Services

I have created a stored procedure that is as follows:

CREATE procedure spQueryActivitiesWithIndex

@ActToDate varchar(20) = NULL,
@ActFromDate varchar(20)= NULL,
@CaseNumber varchar(13) = NULL,
@FullText varchar(3000) = NULL,
@OrderBy varchar(50) = NULL

as

declare @SQLStr varchar(2000)

Set @SQLSTR = 'SELECT va.*, LEFT(FILENAME, 21) AS Filename,
LEFT(REVERSE(SUBSTRING(REVERSE(DIRECTORY), 1, CHARINDEX(''\'',
REVERSE(DIRECTORY)) - 1)) ' +
' + ''/'' + FILENAME, 40) AS Relative_Filename, RANK,
HITCOUNT, CHARACTERIZATION FROM OPENQUERY(web_content,' +
'''SELECT FileName, Directory, Rank, Hitcount,
Characterization FROM psc017.docket..SCOPE() WHERE '

if len(@FullText) > 0
Set @SQLSTR = @SQLSTR + ' CONTAINS(''''' + @FullText + ''''') AND '

Set @SQLSTR = @SQLSTR + ' filename <> ''''index.htm'''' ORDER BY rank
desc'') Q' +
' INNER JOIN [Docket].[dbo].[ViewCaseActivityWithFileNameExt] VA on
VA.txtScanFileName = Q.FileName ' +
' WHERE VA.txtActivityType = ''Order'''

if len(@CaseNumber) > 0
Set @SQLSTR = @SQLSTR + ' AND VA.txtCaseNumber like ''%' + @CaseNumber +
'%'''

if Len(@ActToDate) > 0
begin
if Len(@ActFromDate) > 0
Set @SQLSTR = @SQLSTR + ' AND VA.dteActivity between ''' + @ActToDate
+ ''' and ''' + @ActFromDate + ''''
else
Set @SQLSTR = @SQLSTR + ' AND VA.dteActivity = ''' + @ActToDate + ''''
end

if Len(@Orderby) > 0
Set @SQLSTR = @SQLSTR + ' ORDER BY VA.' + @OrderBy

--print @SQLSTR
EXEC (@SQLSTR)

GO

I can run this fine from SQL Server becuase I have admin rights, but when I
run this from within my ASP.Net app with the following code, I get the
following error message.

DsIndex1.Tables("spQueryActivitiesWithIndex").Clear()

daspQueryActivitiesWithIndex.Fill(DsIndex1, "daspQueryActivitiesWithIndex")



Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user
'(null)'. Reason: Not associated with a trusted SQL Server connection.

I am new to ASP.NET so any help would greatly be apreciated.

 

[Mr Newbie]

You need to Add the ASPNET account to the Database you have created the
Index in.

 

[Jay Pemberton]

Thank you for your prompt reply. I am sorry that I am nieve, but can you
provide links to the steps to add ASPNET user to SQL Server/Database?

Thanks

 

[Jay Pemberton]

I went into SQL Server and tried to add the ASPNET user from my local
machine, but it gave me an error saying Unknown User/Group, so do I need
to have dotnet installed on the SQL Server so that I can use the ASPNET
account there? Is there anything that I have to do on the application
side to facilitate using the ASPNET account besides changing the
machine.config file, I tried that to no avail. Any additional help
would be appreciated.

 

[Mr Newbie]

This is a Bultin Account so it will be something like BUILTIN\ASPNET

 

[Jay Pemberton]

I looked at the SQL Server through enterprise manager and tried to add a
user and did a seach for ASPNET and it didnt find a user called
BUILTIN\ASPNET, so does that mean that it dotnet isn't installed on the
SQL Server?

 

[Jay Pemberton]

I looked at the SQL Server through enterprise manager and tried to add a
user and did a seach for ASPNET and it didnt find a user called
BUILTIN\ASPNET, so does that mean that it dotnet isn't installed on the
SQL Server?

 

[Mr Newbie]

That is possible, but more likely you have not found the account yet.
Alternatively, it may be NT Authority\Network Service