Requirement: I want to create text file from SQL-Server DB trigger using cmdshell commands(mentioned in title).
Scenarios:
S1: When I am trying to execute cmdshell command(mentioned in title) from trigger to create text file and write some content in it, it will execute properly and file is getting created.
S2: When I am trying to call and execute the same scenario S1 from windows Server 2016 WebAPI service through IIS, we are getting following error in Event Viewer.
xp_CmdShell needs a windows account mapping to successfully execute. If you are logged in with a user that has the sysadmin role (I'm guessing this is your first scenario), the account used will be the one for SQL Server Service account.
If the user isn't sysadmin (this should be the IIS user), then it will impersonate the configured account for a special credential called ##xp_cmdshell_proxy_account##, which you need to configure.
To set this account up, use the SP sp_xp_cmdshell_proxy_account, for example:
EXEC sp_xp_cmdshell_proxy_account 'DOMAIN\SQLServerCMDProxy','sdfh%dkc93vcMt0';
You can check this mapping with:
select * from sys.credentials
You will also need to grant the execute command on xp_cmdshell procedure if you didn't already:
GRANT EXEC ON xp_cmdshell TO 'IISUser'
And you can test if it works correctly by impersonating the login with a user with enough priviledges:
EXECUTE AS LOGIN = 'IISUser' -- Shift priviledges to this login for the current session
EXEC xp_cmdshell 'dir *.exe'; -- Try to execute the xp_cmdshell
REVERT -- Revert to the previous login
PD: Be very careful when enabling the use of xp_cmdshell on your SQL Server as it might open security holes, specially when giving permissions to uncontrolled logins. Even if you encapsulate a particular call in an SP, there are workarounds that the user can do to bypass and execute xp_cmdshell directly. The proper way to do this is via certificate signing with a custom login.
