Password not saved in PowerPivot connection when using SQL authentication

When using SQL authentication in a PowerPivot connection, I repeatedly found the password was not saved even though I had removed the check mark from “Blank password” box and the “Allow saving password” box was checked. I tried manipulating the advanced properties setting the persist security to “True”.   All to no avail… the password was saved until the spreadsheet was closed, but the user was required to enter the password when the spreadsheet was re-opened and the PowerPivot data refreshed.

Turns out, you need to do the above and set some properties of the connection back in Excel. In the main Excel window in the Data tab, choose Connections, then select the workbook connection that corresponds to your PowerPivot connection. Click on Properties, switch to the definition tab, and mark “Save password” box.

This setting will allow for permanent storage of the SQL account password.  Note the password is stored in plain text, so consider the security implications.