My goal is to create an Excel spreadsheet that connects to an SQL Server database using database credentials. We have created a generic read only database login with access to the data so that we don't have to provide user specific access to the db. However, I can create the spreadsheet, connect to the SQL Server database, retrieve and refresh the information fine but when I give the spreadsheet to a colleague, they can't refresh the data because the database username and password has disappeared.
I've read some posts online where they indicate that there is a "save login/password" somewhere when you create the connection, but I'm guessing these are for older versions of Excel as the walk-throughs they provide don't match up with what I'm seeing in the Office 365 for Business version of Excel I'm using.
It depends on how do you connect. If with Power Query it shall keep the credentials for Basic authentication, for Windows/Organizational one it will use current user's account. With it asks to sign-in user first time and after that shall keep the credentials for that user and same file.