[Python] Run python process using IIS ApplicationPoolIdentity

I have a web API that starts off a python process like -

var process = new Process
{
StartInfo = new ProcessStartInfo
{
FileName = PythonPath,
Arguments = arg,
UseShellExecute = false,
RedirectStandardOutput = true,
RedirectStandardError = true,
CreateNoWindow = true
},
EnableRaisingEvents = true
};


I have configured the API to use ApplicationPoolIdentity as its identity in IIS.

I have created a test python script to simplify things that literaly connects to the database and retireves the first row connecting like -

conn = pyodbc.connect(
'Driver={ODBC Driver 17 for SQL Server};'
'Server=localhost;'
'Database='my-db;'
'Trusted_Connection=yes;'
'autocommit=True;')


When running the script manually on the server, it runs as expected. However when running form my web API I am getting the error -

pyodbc.InterfaceError: ('28000', "[28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\\ANONYMOUS LOGON'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\\ANONYMOUS LOGON'. (18456)")


What I have tried

Set the application pool to use admin user as a test and this worked (obviously reverted back).

Added logging to make sure what account the script was running as when started from web API which resulted in -

Whoami: iis apppool\my-app-pool

User: SERVER-NAME$


Created a User for the Login for my app pool to access the database -

USE my-db;
CREATE USER [IIS APPPOOL\my-app-pool] FOR LOGIN [IIS APPPOOL\my-app-pool];
EXEC sp_addrolemember 'db_datareader', 'IIS APPPOOL\my-app-pool';
EXEC sp_addrolemember 'db_datawriter', 'IIS APPPOOL\my-app-pool';

Continue reading...