SQL 6.5 and ASP.NET/IIS - SQL Server does not exist or access denied

Question

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

Answer 1

woo hoo... I solved the little bast%#$rd... the answer is: Make the following change to the machine.config file for ASP.Net: The reason... as I had figured, the default local user that ASP.Net uses (mycomputer\ASPNET) does not have adequate priviledges to interact on the network. Using the impersonate property of the identity allows the ASPNET worker process to inherit that users access rights. ...James does a little touchdown dance.... thanks for the help Mike!!

Answer 2

Well, 6.5 is pretty old, I'm not working with it for about 6 years. However, I remeber that there were problems with connecting SQL Serer using Named Pipes. I used TCP/IP to avoid this. Here is a note I found in SQL 2000 documentation, so it may not really apply to your case: Note When connecting to an instance of SQL Server running on Windows NT 4.0 or Windows 2000 using Named Pipes, the user must have permission to connect to the Windows NT Named Pipes IPC, \\\IPC$. If the user does not have permission to connect, it is not possible to connect to an instance of SQL Server using Named Pipes unless either the Windows NT 4.0 or Windows 2000 guest account on the computer is enabled (disabled by default), or the permission "access this computer from the network" is granted to their user account. BTW, SQL 2000 does not have pure SQL Authentication mode, it has Windows and Mixed.

Answer 3

Yes, I am, but I think this authentication problem doesn't reach SQL Server, its more of a windows authentication issue saying that the [localmachine]\ASPNET user does not have authorization to connect to the server itself. The only difference between running the assembly from a command line app and from IIS is that the WindowsIdentity changes from my username to [localmachine]\ASPNET.

Answer 4

James, I'm a little confused by your posting. If you use SQL Authentication, then you are specifying SQL Login and Password in you connection string, are you? Mike