For several years I have used a .bat file to launch several SQL Server processes. It was originally created on SQL Server 2005, which was later upgraded to SQL Server 2008. The server OS was Windows Server 2003 Standard SP2. Last week we moved to a new server with Windows Server 2008 R2 Enterprise OS. Now the .bat cannot be launched by non-admins. Administrative users can launch the process by double clicking the .bat file, but when a non-admin user tries we get an error like "login failed for user WINDERHQ\dtuser". Here is the content of the .bat file:
sqlcmd -S GP -d wndr -Q "truncate table wndr_inv_trxfr"
"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec" /f "C:\SSISPackages\SSIS Packages\SSIS Packages\WinderInventoryTransfers.dtsx"
sqlcmd -S GP -d wndr -Q "EXECUTE udp_ValidationChecks"
sqlcmd -S GP -d wndr -Q "EXIT(SET NOCOUNT ON SELECT MSG FROM INV_TRXFR_MSG)"
If I use the -U and -P switches with an admin user id, of course, it works regardless of the status of the logged in user, but I don't want to do that. I have created a proxy account and a credential. The proxy user is a domain administrator and a SQL administrator. I have also used the proxy account in place of the local account to run SQL server and SQL Server Agent. The SQL Server instance is set up for mixed authentication. What can I do to make this available for use by non-admins, without having to expose passwords with the -U and -P switches?