I have a working solution that will deploy rdl report definitions to a report server using a POST request. These reports utilize embedded XML data sources that will include a request token for the target endpoint(s) (via https) and thusly don't need any credentials. Subsquently, the rdl files are defined to require no credentials--this required setting up An Unattended Execution Account is required to allow a report to be configured using no credentials. The deployment of these reports is successful, however, I've run into an initial problem and a subsequent problem trying to fix the first:
1) Upon deployment, the SSRS Server is ignoring the report definition data source credential setting "Log into the data source without any credentials", and setting the data source to prompt for credentials.
2) As a workaround to the issue described in #1, I attempted to make additional API requests (PUT) against the /Reports(path='/folder/reportname')/DataSources endpoint, however, I'm unable to form an appropriate json object to complete the request.
EDIT:
On closer inspection of the GET responses (including a slightly doctored example below), the Id's associated with the embedded data sources appear to be meaningless and change on every request, however, trying to address the data sources (omitting the Id) doesn't seem to work either (I've updated the previously included sample payloads to omit the ID and include the Name of the data source, but these requests are still returning 400 Bad Request Status Codes.
EDIT #2: My familiarity with deploying reports via Visual Studio (SSDT) made me forget that credentials are not actually defined as part of the report, this would be undesirable as there would be, in many cases, credentials stored in source control or the report server where they could be compromised. This means that I should be pursuing the secondary API call, but I have yet to successfully craft an object to pass to that endpoint as a payload, as of yet.
Example GET Request Response against /Reports(path='/folder/reportname')/DataSources
{
"@odata.context": "https://my-report-server.com/reports/api/v2.0/$metadata#DataSources",
"value": [
{
"Id": "00000747-020f-019c-e8be-4f8ff834bee5",
"Name": "DebugSource",
"Description": null,
"Path": null,
"Type": "DataSource",
"Hidden": false,
"Size": 0,
"ModifiedBy": null,
"ModifiedDate": "0001-01-01T00:00:00Z",
"CreatedBy": null,
"CreatedDate": "0001-01-01T00:00:00Z",
"ParentFolderId": null,
"ContentType": null,
"Content": "",
"IsFavorite": false,
"Roles": [],
"IsEnabled": true,
"ConnectionString": "https://localhost:8082/some-api-endpoint",
"DataSourceType": "XML",
"IsOriginalConnectionStringExpressionBased": false,
"IsConnectionStringOverridden": false,
"CredentialRetrieval": "prompt",
"CredentialsByUser": {
"DisplayText": null,
"UseAsWindowsCredentials": false
},
"CredentialsInServer": null,
"IsReference": false
},
{
"Id": "000009b9-0093-0257-52cc-e93a261f0862",
"Name": "ActualAPIEndpointForNonDevUse",
"Description": null,
"Path": null,
"Type": "DataSource",
"Hidden": false,
"Size": 0,
"ModifiedBy": null,
"ModifiedDate": "0001-01-01T00:00:00Z",
"CreatedBy": null,
"CreatedDate": "0001-01-01T00:00:00Z",
"ParentFolderId": null,
"ContentType": null,
"Content": "",
"IsFavorite": false,
"Roles": [],
"IsEnabled": true,
"ConnectionString": null,
"DataSourceType": "XML",
"IsOriginalConnectionStringExpressionBased": true,
"IsConnectionStringOverridden": false,
"CredentialRetrieval": "prompt",
"CredentialsByUser": {
"DisplayText": null,
"UseAsWindowsCredentials": false
},
"CredentialsInServer": null,
"IsReference": false
}
]
}
JSON payload example: 1
(created by modifying a json excerpt from a GET Request against /Reports(path='/folder/reportname')/DataSources)
{
"Name": "DebugSource",
"Description": null,
"Path": null,
"Type": "DataSource",
"Hidden": false,
"Size": 0,
"ModifiedBy": null,
"ModifiedDate": "0001-01-01T00:00:00Z",
"CreatedBy": null,
"CreatedDate": "0001-01-01T00:00:00Z",
"ParentFolderId": null,
"ContentType": null,
"Content": "",
"IsFavorite": false,
"Roles": [],
"IsEnabled": true,
"ConnectionString": "https://localhost/some-endpoint",
"DataSourceType": "XML",
"IsOriginalConnectionStringExpressionBased": false,
"IsConnectionStringOverridden": false,
"CredentialRetrieval": "none",
"CredentialsByUser": {
"DisplayText": null,
"UseAsWindowsCredentials": false
},
"CredentialsInServer": null,
"IsReference": false
}
JSON payload example: 2 (minimal)
this would be the ideal form and should be valid based on api documentation
{
"Name": "DebugSource",
"CredentialRetrieval": "none"
}
The ideal solution here would be a setting that would force SSRS to honor the embedded data source settings, but additional REST API calls would be fine as well if I can figure out the correct syntax to use.
I forgot to follow up on this question:
I ended up resolving this issue with Microsoft support. Hitting this endpoint:
http://reportserverurl/reports/api/v2.0/Reports(19489e3b-5653-466a-b242-43e4e198be49)/DataSources
With a payload like so:
[{
"Name":"DataSourceUpdated",
"Description":null,
"Hidden":false,
"Path":"",
"IsEnabled":true,
"DataSourceSubType":null,
"DataModelDataSource":null,
"IsReference":false,
"DataSourceType":"SQLAZURE",
"ConnectionString":"Data Source=someting;Initial Catalog=rsadventureworks;Encrypt=True;TrustServerCertificate=False",
"IsConnectionStringOverridden":true,
"CredentialRetrieval":"store",
"CredentialsInServer":
{
"UserName":"something",
"Password":"something",
"UseAsWindowsCredentials":false,
"ImpersonateAuthenticatedUser":false
},
"CredentialsByUser":null
}
]
worked for me in my testing at the time, though I haven't yet implemented this in my deployment solution (I originally used the legacy soap api because this issue dragged on for some time).