(secrets): How do we force Secret resolution in a particular scope?

### Describe the bug

Ref: https://cdk-dev.slack.com/archives/C018XT6REKT/p1706125503162629

I want to resolve a secret (`{{resolve:secretsmanager...}}` within the context of a parent `Stack`, and pass the parameter through to a `NestedStack`. This will allow the nested stack to use the parameter in `Custom::xxx` resources. This is a pattern we have in many of our legacy YAML based CloudFOrmation stacks and it works well.

When I try to do this, CDK seems to do the resolution in the nested stack itself.. this resolution then fails, and the custom resource will not work. It seems odd to me that a `Secret` would be resolved (turned into `{{resolve:...}}` in ANY context other than the scope passed to it.

### Expected Behavior

I expect the resolution to occur in the parent stack where I have passed the scope in.

### Current Behavior

It is resolved in the nested stack.

### Reproduction Steps

```typescript
# stack.ts
/** @format */

import { CustomResource, NestedStack, NestedStackProps, Stack } from 'aws-cdk-lib';
import { Construct } from 'constructs';
import { Secret } from 'aws-cdk-lib/aws-secretsmanager';

export interface AppStackProps extends NestedStackProps {
readonly apikey: string;
}

export class ExampleStack extends Stack {
constructor(scope: Construct, id: string) {
super(scope, id);
new AppStack(this, 'appStack', {
apikey: Secret.fromSecretNameV2(this, 'ApiKey', '/secret').secretValue.toString(),
});
}
}

export class AppStack extends NestedStack {
constructor(scope: Construct, id: string, props: AppStackProps) {
super(scope, id);

new CustomResource(this, 'Custom::thingy', {
serviceToken: 'arn:to:lambda',
properties: {
token: props.apikey,
},
});
}
}
```

## Generate it...
```
yarn cdk synth
```

## Check out the output:

```json
# template.json
{
"Resources": {
"appStackNestedStackappStackNestedStackResourceA44C89FF": {
"Type": "AWS::CloudFormation::Stack",
"Properties": {
"TemplateURL": {
"Fn::Join": [
"",
[
"https://s3.us-west-2.",
{
"Ref": "AWS::URLSuffix"
},
"/cdk-hnb659fds-assets-XXXXX-us-west-2/aa703b1fd367d9aecc9683745cac0b97d90123b2d2a96a4014499c95009bf223.json"
]
]
}
},
"UpdateReplacePolicy": "Delete",
"DeletionPolicy": "Delete",
"Metadata": {
...
}
},
"CDKMetadata": {
"Type": "AWS::CDK::Metadata",
"Properties": { },
"Metadata": {
"aws:cdk:path": "Stage/ExampleStack/CDKMetadata/Default"
}
}
},
"Parameters": {
"BootstrapVersion": {
"Type": "AWS::SSM::Parameter::Value",
"Default": "/cdk-bootstrap/hnb659fds/version",
"Description": "Version of the CDK Bootstrap resources in this environment, automatically retrieved from SSM Parameter Store. [cdk:skip]"
}
},
"Rules": {
"CheckBootstrapVersion": {
"Assertions": [
{
"Assert": {
"Fn::Not": [
{
"Fn::Contains": [
[
"1",
"2",
"3",
"4",
"5"
],
{
"Ref": "BootstrapVersion"
}
]
}
]
},
"AssertDescription": "CDK bootstrap stack version 6 required. Please run 'cdk bootstrap' with a recent version of the CDK CLI."
}
]
}
}
}
```

```json
# nested.template.json
{
"Resources": {
"Customthingy": {
"Type": "AWS::CloudFormation::CustomResource",
"Properties": {
"ServiceToken": "arn:to:lambda",
"token": {
"Fn::Join": [
"",
[
"{{resolve:secretsmanager:arn:",
{
"Ref": "AWS::Partition"
},
":secretsmanager:us-west-2:XXXXX:secret:/secret:SecretString:::}}"
]
]
}
},
"UpdateReplacePolicy": "Delete",
"DeletionPolicy": "Delete",
"Metadata": {
"aws:cdk:path": "Stage/ExampleStack/appStack/Custom::thingy/Default"
}
},
"CDKMetadata": {
"Type": "AWS::CDK::Metadata",
"Properties": { },
"Metadata": {
"aws:cdk:path": "Stage/ExampleStack/appStack/CDKMetadata/Default"
}
}
}
```

### Possible Solution

_No response_

### Additional Information/Context

_No response_

### CDK CLI Version

2.115.0

### Framework Version

_No response_

### Node.js Version

18

### OS

osx

### Language

TypeScript

### Language Version

_No response_

### Other information

_No response_

Start with the TypeScript reproduction in stack.ts and run yarn cdk synth to compare template.json with nested.template.json. Trace how the Secret value is assigned across ExampleStack and AppStack, then use the generated templates to verify that resolution occurs in the parent stack as requested.

Written by the indexing model from the issue text.