SQL 6.5 and ASP.NET/IIS - SQL Server does not exist or access denied
I am experiencing a problem using ASP.NET Web Services to access my SQL 6.5
database. I have found many posts on many forums with people experiencing a
similar problem, but no one has provided a workable solution.
Here is the synposis:
I have a .Net assembly that makes a SQL call to my 6.5 db. When I call
that assembly from a command line app on my local machine, it works
perfectly. However, when I wrap that same assembly into my Web Services, I
get the dreaded error "ConnectionOpen (Connect()).]SQL Server does not exist
or access denied".
I have narrowed this down to a permissions issue, but I'm unable to get
any deeper than that. After doing a little packet-capturing, I am sure that
contact is being made to the SQL server, but I do not believe it is making
it as far as SQL authentication. I know that the ASP worker process runs
under the user [localmachine]\ASPNET. I do not believe that this user
account has addequate priviledges to access my server. However, since we
run Active Directory, I have no way of adding that local (machine) user to
any security group that would be allowed access.
Is there a way I can change the ASP worker process user? Am I on the
right track here?
BTW, the SQL 6.5 server is setup for Standard Authentication. I have seen a
couple posts that suggest switching to Mixed Mode may fix this issue, but
since I'm using SQL authentication currently, that does not make any sense
to me.
This issue has baffled out entire IT department. Any advice would be
greatly appreciated.
-James Divine