Hi yewee,
The login credentials are stored in the connection string for each individual connection. Since MS Query won't allow shared connections (like Power Pivot does) you'd have to edit each one separately. But you can put the login credentials into the connection string itself. It also does not encrypt passwords like other connections types do for you. For this reason I would stay away from MS Query if you can. Personally I've used it before, but generally when it's an Access connection and I want to return a crosstab query, which can't be returned 'normally' through the UI (can with VBA though, which is a good workaround IMHO, because you can reference the Jet engine and do what you want with it). I think personally I'd rather use a plain vanilla SQL connection over MS Query.
Like Ken asked, I'm assuming you have your SQL server authentication
not on Windows Authentication? This is of course the easiest method (as I'm sure you've read in other SQL-related materials), but a lot of times people choose mixed mode. I'm also unsure how you actually made your connection. Here are some images from a SQL connection I made using MS Query. You'll see how you can save login credentials, as well as edit it.
When you create your data source connection (e.g. Data ribbon tab | From Other Sources | From Microsoft Query, on the 'Databases' tab on '<New Data Source>') you'll see a dialog box like this, where you set your connection up. I just used the AdventureWorks2012 SQL database to test with.
Look at the checkbox at the bottom of the dialog box.
Once you have your connection and you've chosen to save your user ID and password in the definition (read: connection string), you'll get prompted...
Click Yes to that and you go through and set your query up. Once it's setup and you return the data to Excel, if you look at your connections (DATA | Connections), you'll see it listed there, like this...
If you select your query and hit Properties, click on the 'Definition' tab and you'll notice the connection string, like this...
You can edit this any way you want, but you can't encrypt it. (You'll notice my connection string doesn't have a password. I used mixed mode on my SQL Server, so I can login with my user/admin account.) So this is what will allow the connection to pass without being prompted, so long as the security settings are matching on the SQL side, like Ken mentioned.
I think probably the absolute best reference on the web for creating connection strings to almost any kind of data base is
www.connectionstrings.com. They have a page dedicated to SQL Server connections...
http://www.connectionstrings.com/sql-server/
Just find the kind of connection you're looking for and it'll have the syntax. So for standard security (non-trusted) it could look like this...
Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;
If the authentication is correct on the SQL side you shouldn't get prompted for credentials.
In the end it doesn't really matter how you have your query setup, but what your connection string is.
Does that help, or am I too far off the mark?