IIS 7 and Above
Novice - strange behaviour of website accessing SQL data
Last post Nov 07, 2018 07:51 AM by SinSilla
Nov 06, 2018 01:34 PM|SinSilla|LINK
I am currently responsible for a (pilot) customer project using IIS among other things. Unfortunately, I am an absolute beginner and am confronted with a complex problem in an complex environment (large Network, 5000+ AD users).
So please be patient with me.
Server 1 (Windows Server 2012 R2) provides IIS8. In addition there is a fleet management software running on the same Server which provided the WebSite application via an installer. The fleet management software is connected to Server 2 (SQL Server) and uses
SQL credentials for the connection.
ASP.net is installed. The Windows authentication role is also installed.
The WebSite application is intended to display certain data from the database and uses the SQL credentials and server data stored in the fleet management software for this purpose.
The WebSite application has its own application pool.
I configured the authentication for the WebSite as Application User (pass-through), because I have to configure whitelisting of certain data on user level.
The WebSite is accessible both from a workstation PC in the domain and from the server. The WebSite shows the currently logged in user. So the pass-through login seems to work.
What does not work?
Now it's getting a little crazy!
I can access the website from the server, but I can never display data (the page provides links for reports in different file formats). The website only returns a generic error (We can't execute your request.)
The behavior on the workstation PC is exactly the same.
BUT. If I open the website on the server before accessing the website from the Client, and try to retrieve data there unsuccessfully, the access from the workstation PC works afterwards without Problems!
In this order:
1. accessing the website from the client. Success.
2. retrieval of data from the website (still on the client). No success.
3. accessing the website from the server. Success.
4. retrieving data from the website (still on the server). No success.
5. accessing the website from the client. Success.
6. retrieving data from the website (still on the client). Success.
This process works 100% of the time. It looks as if I would trigger the access via the unsuccessful call via the server. Extremely strange.
If I use a specific user (local admin of the server) for authentication instead of path-through authentication, access from both the desktop PC and the server works without problems directly. However, as mentioned above, I am dependent on path-through.
I have added the specific AppPool of the application to the Local Path permissions without any improvement.
Can anyone interpret the behavior and have hints for me what I overlooked as a beginner or just don't understand? I can provide all necessary missing information.
Thank you in advance!
Greetings from Germany.
Nov 07, 2018 02:50 AM|Brando Zhang|LINK
According to your description, it seems there is sql user permission error ouccured when you use client PC to access the web sites.
Could you please post the details error message? Not just We can't execute your request?
Besides, I suggest you could also use sql server profiler to check the details sql error message.
More details about how to use sql server profiler, you could refer to below article.
Nov 07, 2018 07:51 AM|SinSilla|LINK
thank you very much for taking the time to reply!
I was able to solve the problem in the meantime.
I just had to Disable ASP.NET Impersonation in the authentication for the WebSite.
Now eveything works fine.