ODBC connection for Active Directory user who doesn't log in to Windows
Although you cannot login to SQL Server unless you use either a SQL Server Login or a Windows Account with permissions to connect to the SQL Server, you could create a low-permission user that can EXEC certain stored procedures.  Those stored procedures could then contain code that switches into the context of another, more highly privileged account. 

EXECUTE AS Login = 'DOMAIN\User';
SELECT * FROM sys.databases;
REVERT 


Read more about EXECUTE AS on MSDN.