We have a Python Flask app hosted on IIS 8 with Windows Authentication is turned on.
The ApplicationPool for this IIS app has Identity set to a Custom Account. Let's say for example,
super_usr
This account has SQL Authorization for the SQL Server Databases it is going to access.
Let's say a certain Local User is trying to access the Flask application.
local_usr
The Flask app is able to get the local user's name using
request.environ['REMOTE_USER']
The way PyODBC is used to connect to the database is it uses
TrustedConnection=yes
as a part of the SQL Connection String.
DRIVER={SQL Server};SERVER=XYZ;PORT=123;Trusted_Connection=yes;
So PyODBC opens the connection under the context of the super_usr and NOT the local_usr as IIS has super_usr running the Server.
Now we are looking for a way so that PyODBC opens connection as the local_usr. We don't want to ask the local_usr for their password and keep the Windows Authentication on.
This is for the purpose of having SQL Authorization for the Windows Authenticated User [local_usr] and not the Account running on IIS Application Pool [super_usr].
Is there a way in IIS when the SQL Server are not on the Same Box to authorize the authenticated user instead of passing a certain account to the Application Pool Identity?
Has anyone faced this problem before? And if yes, what's the ideal way to get around this?
Thanks in Advance.
