Report Server API pach datasource failing

[Anonymous]

Hi.

We have a server with possibly hundreds of user created reports the need to have their connectionstrings updated.

I've tried to get this to work using the API and a python script but without luck.

Link to swaggerhub´s patch method.

https://app.swaggerhub.com/apis/microsoft-rs/PBIRS/2.0#/PowerBIReports/UpdatePowerBIReportDataSource

The 'get' method works fine but the 'patch' returns 400 Bad request.

Has anybody been successful with this?

Using this RS Version 1.5.7074.36177 (May 2019)

code snippet 

import json
import requests

from requests_negotiate_sspi import HttpNegotiateAuth 

uri = 'http://localhost/Reports/api/v2.0/PowerBiReports(17872a26-8066-4a96-b149-c4f327f91daf)/DataSources'

payload = {'ConnectionString': 'data source=GRAL-PROD-DB01,51491;initial catalog=IFD_Journal_AL;persist security info="false"'}

headers = {'Content-type': 'application/json'}

r = requests.patch(uri, auth=HttpNegotiateAuth(), json=payload, headers=headers)
print(r.status_code)

r = requests.get(uri, auth=HttpNegotiateAuth(), headers=headers)
print(r.status_code)
print(r.content)

Here´s the response when run in sublime on windows 10.

CMD.EXE was started with the above path as the current directory.
UNC paths are not supported.  Defaulting to Windows directory.
400
200
b'{\r\n  "@odata.context":"http://localhost/Reports/api/v2.0/$metadata#DataSources","value":[\r\n    {\r\n      "Id":"1424c60b-0acb-e911-8346-648099cc70c1","Name":null,"Description":null,"Path":null,"Type":"DataSource","Hidden":false,"Size":0,"ModifiedBy":"VGREGION\\\\marst160","ModifiedDate":"2019-08-30T11:40:44.153+02:00","CreatedBy":"VGREGION\\\\marst160","CreatedDate":"2019-08-30T11:39:22.913+02:00","ParentFolderId":null,"IsFavorite":false,"Roles":[\r\n        \r\n      ],"ContentType":null,"Content":"","IsEnabled":true,"ConnectionString":"data source=xxxxxxxxxxxx;initial catalog=xxxxxxxxx;persist security info=False","DataSourceType":null,"IsOriginalConnectionStringExpressionBased":false,"IsConnectionStringOverridden":false,"CredentialRetrieval":"prompt","CredentialsByUser":null,"CredentialsInServer":null,"IsReference":false,"DataSourceSubType":"DataModel","DataModelDataSource":{\r\n        "Type":"DirectQuery","Kind":"SQL","AuthType":"Windows","SupportedAuthTypes":[\r\n          "Integrated","Windows","UsernamePassword"\r\n        ],"Username":"marst160","Secret":"","ModelConnectionName":"de823fb1-b39a-4acd-9046-2e5f818e4794"\r\n      }\r\n    }\r\n  ]\r\n}'

Any help would be appreciated.

[d_gosbell]

A pbix report can have multiple data sources. I have not tried using this API, but I think at the very least you will need to include the "Id" attribute in your request json so that the server knows which data source you are updating.

If you use something like fiddler or the chrome dev tools you can see the body of the request that the PBIRS portal sends when you update a connection string through the UI. You could copy one of those requests and then start seeing if there are properties that the UI sends which you could strip out.

[Anonymous]

Hi.

The report only have one datasource and the Id for it is provided in the uri above.

Good point using fiddler.

I tried this but realised I cannot change the connectionstring at all i the GUI.

Both fields "Type" and "Connection string" are greyed out.

Is this a setting I can change on the server?

[d_gosbell]

---

@Anonymous wrote:

The report only have one datasource and the Id for it is provided in the uri above.

---

So the Id in the uri should be the report Id. The data source Id would then be in the body of the report

I tried this but realised I cannot change the connectionstring at all i the GUI.

Both fields "Type" and "Connection string" are greyed out.

---

You definitely can't change the type of the data source. It would break your query if you changed the type. And from what I've seen they only support changing certain types of connections. We primarily have pbix reports in live connect mode and you can change the server/database for those. But I don't know if you can alter data sources for import mode models. These seem to be disabled in the UI, so I suspect the API won't let you change these either