SSIS runs runs on a remote (production) machine that is part of a workgroup (no domain). SQL Server 2005 Enterprise (with SP2)
Users also use Excel (or desktop-based cube browsers like NovaView by Panorama) to connect to remote SSIS using Integrated Security i.e. logging in with same local username/password that is running SSIS on remote machine.
Since SSIS runs in a workgroup, any local user with same login and password authenticates itself on SSIS server.
The problem is with password synchronization. On the production machine, passwords are changed regularly for security reasons and this breaks the connection of Excel/Desktop-based cube browsers. SSIS does not allow SQL Authentication for such applications.
I was thinking of a solution whereby all cube browser requests go through a single gateway application and that application authenticates itself against SSIS (similar to what IIS does for web clients). But I don't know how to go about it, any custom solutions available? Anybody here have any idea?