Execute xp_cmdshell as Non-Windows User in SQL Server

How can I grant execute permissions to sa user?

You don't need to grant execute anything to sa as sa is in the sysadmin server role and can pretty much do anything. Hence, if you are getting an error executing xp_logevent, then you aren't logging in as sa. Is the connection string using context connection = true; or a regular / external connection? If using context connection = true; then the permissions are based on whoever is executing the SQLCLR stored procedure. If using a regular connection, the permissions are based on the logon account for the SQL Server NT Service (MSSQLSERVER / SQLEXPRESS if it is a default instance) by default, else it can be the permissions of whoever is executing the SQLCLR stored procedure if code has been added to do impersonation.

So, how is the SqlConnection being made? The answer to that question will help determine the appropriate fix. If this SQLCLR stored procedure is to be called by anyone (meaning, Users who are not necessarily in the db_owner database role or the sysadmin server role), then you can do the following to grant this permission to the smallest scope possible.

In the database that contains the SQLCLR stored procedure:

1. Create an Asymmetric Key (see CREATE ASYMMETRIC KEY)
2. Create a User from that Asymmetric Key (see CREATE USER)
3. Add this new User to the db_owner Database Role
4. Create a T-SQL Stored Procedure (maybe call it dbo.LogEvent) that accepts the two parameters you are passing in (@errorNumber and @message) and simply executes EXEC xp_logevent @errorNumber, @message, informational;
5. Grant EXECUTE on this new Stored Procedure to any Users and/or Database Roles that will be calling the SQLCLR Stored Procedure
6. Sign this new Stored Procedure with the same Asymmetric Key used to create the new User (see ADD SIGNATURE)
7. Update your SQLCLR code to execute this new T-SQL Stored Procedure (i.e. dbo.LogEvent) instead of xp_logevent
   1. You can then change your using statement to have new SqlCommand("dbo.LogEvent", connection)
   2. then change cmd.CommandType = CommandType.Text; to be cmd.CommandType = CommandType.StoredProcedure;
   3. remove the const string executeLog line

Other notes:

• Please do not use AddWithValue as there are issues with it. Instead, just create the SqlParameter with an explicitly set datatype and then add it to the SqlCommand.SqlParameters collection.
• No need to call ExecuteScalar if you are not capturing something from a result set. Instead, call ExecuteNonQuery

The problem looks more complex than it is. Since you are using SQL 2014 you are probably being bitten by the new security features introduced in 2012.

The only thing that actually matters is:

Server: {server name}, Package path: \SSISDB\Foo\Bar\foobar.dtsx, Environment reference Id: NULL.   
Description: Login failed for user '{domain user that runs SSIS ETL overnight}'.

Your Proxy user's login most likely doesn't have access to the SSISDB catalog (even though he might have access to SQL Server).
You need to map the login to an SSISDB user and configure access to the SSISDB folders/projects in Integration Services.

Please have a look at this MSDN blog post SSIS Catalog Access Control Tips and SQL 2012 SSIS Catalog Permissions

Once you have the package actually loading you might run into other security context issues but you should get better logging from integration services itself.