Support more credential sources in dlt CLI

### Feature description

Currently, the CLI only works when specifying credentials in 3 out of 6 possible ways:

- [x] secrets.toml
- [x] config.toml
- [x] environment variables
- [ ] [custom config providers](https://dlthub.com/docs/general-usage/credentials/setup/#custom-providers)
- [ ] parameters specified in the pipeline
For example,
```python
pipeline = dlt.pipeline(..., destination=dlt.destinations.clickhouse(credentials=my_credentials))
```
- [ ] dynamically provided credentials
For example, `dlt.secrets["my-secret"] = "my_dynamic_secret_value"`

In practical terms, this makes eg. using a secret manager for storing credentials troublesome, because users need to have one of the 3 static credential sources available locally, which basically defeats the purpose of using an external secret manager.

### Are you a dlt user?

None

### Use case

_No response_

### Proposed solution

Probably creds passed via the `credentials` param should be prioritized, as some of the tutorials use this method of providing credentials (eg. https://dlthub.com/docs/pipelines/sql_database_mssql/load-data-with-python-from-sql_database_mssql-to-mssql#5-next-steps-to-get-your-pipeline-running-in-production), which means if you followed the tutorial, you would end up unable to use `dlt` CLI commands.

### Related issues

_No response_