An advantage by using Windows Authentication is that I do not need to provide a username and password to log into the server as the user currently logged into Windows. But in a specific case I would like to log into the SQL Server as another user, using the same Windows Authenticated ODBC connection.
So, the question: Is it possible to log into an SQL Server using Windows Authentication, but not as the user currently logged into Windows (without a dialog asking for username and password)?
The simple answer to your question is no, without a dialog being presented, it's not possible to login to SQL Server using another Windows account.
The slightly more complex answer is; You can login as another user (WITH a dialog being presented) by right-clicking the Query Analyser icon and choosing "Run As" (Might require a CTRL or SHIFT key held down to make RUN AS appear.
There's also the RUNAS command line executable which you could look at, so you could create a shortcut that contains the RUNAS command, the credentials and the path to Query Analyser. That would eliminate being asked for a username or password on execution.
No matter what, if you are not using the credentials of the logged in user, you will need to pass other credentials--short of asking for them directly, the only other option is to store them..
And once you are doing that, it may be worth considering mixed-mode authentication and use SQL login/pw for this specific situation.
Runas does not let you provide a password on the command line and will produce a popup to gather it. A little searching let me to an artical on technet that may help out while not it's intended use..
http://technet.microsoft.com/en-us/library/bb727155.aspx
http://blogs.msdn.com/oldnewthing/archive/2004/11/29/271551.aspx
