I have a data source using the "credentials stored securely in the report server" option. Initially it uses a windows login. Everything works fine, no problem.
Then I test this option with a SQL login. The login has a corresponding user in the msdb, report server, and AdventureWorks2008 databases in the db_datareader membership.
I have also changed the authentication mode to mixed, and tested the SQL Login working well.
However, in report manager, when I try to modify the "credentials stored securely in the report server" from the existing Windows login to the new SQL login for the data source, I always get an error: The password is not valid. Please retype the password. After I click the "Apply" button, it has no effect, the credential is back to the Windows login.
Solution (Workaround):
1. Create a new data source, mimicking the original data source except for using the SQL login
2. Make the report(s) using the new data source with the new login
3, (Optional) After all reports have been linked to the new data source, you can delete the original data source and rename the new one with the original name.
