search

maikvandergaag / msft-extensions

Repository for extensions mainly used for Azure DevOps Extensions
https://msftplayground.com
MIT License
125 stars 81 forks source link

Power BI Actions: Set username and password for DQ datasources #83

Closed AlexandrStadolnik closed 4 years ago

AlexandrStadolnik commented 5 years ago

Good day, Maik.

Could you introduce the actions again, as you proposed in the review? Ability to set username and password for DQ datasources is really missing in this extension.

Thank you

maikvandergaag commented 5 years ago

I marked the issue as feature request and will check when I will be able to add it again.

AlexandrStadolnik commented 5 years ago

Thank you very much. Looking forward for the updates.

Singley commented 5 years ago

@maikvandergaag @AlexandrStadolnik is this feature request encompassing of setting the SQL credentials? For example, I have confirmed that I can update my Data Source connection using the 'UpdateDataSource' operation in this extension. However, I cannot set the credentials on that same data set during the same operation. This leads to the following workflow:

  1. Deploy the Power BI report via Devops, updating the DataSource Connection at the same time. This uses a Service Account. At this time, my report cannot refresh, as the dataset credentials are invalid.
  2. Update the dataset credentials after logging on the portal with the Service Account
  3. The previous step must be done using the Service Account or I have to take ownership with my ID. If I take ownership away from the Service Account, the 'UpdateDataSource' operation fails on the next deployment. image

If the 'Update Data Source' operation allowed credentials to be set during deployment, all of these issues go away, and I finally have my true CI / CD across environments.

Thanks, and I appreciate your efforts to build and support this tool

Mark

maikvandergaag commented 5 years ago

@Singley : When updating the datasource the account you are using. Must be the owner of the dataset.

I hope to get started on the feature next week, It has been busy times with the birth of my son.

Singley commented 5 years ago

@maikvandergaag congratulations!

Thanks for considering this feature- I'll keep an eye on it moving forward.

KateHamster commented 5 years ago

@maikvandergaag Do you know when this feature will be finally available? We are looking forward to trying it!

hoffe86 commented 5 years ago

@maikvandergaag We are also interesting in this feature, because we are running our solution in four environments (Development, Integration, Test and Production) with different credentials

maikvandergaag commented 5 years ago

Hi Everyone, Sorry that it has been taking that long had some personal stuff (a new son, and moving between houses) I hope to get it in in the next few weeks.

maikvandergaag commented 5 years ago

Sorry for the late response due to some personal stuff I haven't been able to proccess this hope to be able to do this in august

larrysteele commented 5 years ago

I'm still looking forward to this feature also. I've actually been working with Microsoft on exactly how to accomplish this, mostly for Odata and passing the token to the dataset to update the credentials using PowerShell. The PS logic appears to work but invalidates the report. I'm hoping to have a resolution shortly with them. Any help you could provide would be amazing and I think most of us would be interested in Odata and SQL auth as they seem to be the most common forms of the Dataset.

maikvandergaag commented 5 years ago

Hi Larry, thank you for your message, I hope to add some things this week. The way it be working is via a connection string or are you looking for a different solution?

michaelgoconnor commented 5 years ago

Hi @maikvandergaag Just wondering if you have any update on this feature massively appreciate the effort you have put in here.

maikvandergaag commented 4 years ago

I have added the functionality in the beta version. Are you able to test?

michaelgoconnor commented 4 years ago

@maikvandergaag Sadly i ended up not using the component and i don't access to environment where I to test anymore, sorry

Singley commented 4 years ago

I'm out of the country for a week with no access to my work environments. I'll be happy to test upon my return. Thanks!

On Mon, Nov 11, 2019, 9:21 AM moconnor8West notifications@github.com wrote:

@maikvandergaag https://github.com/maikvandergaag Sadly i ended up not using the component and i don't access to environment where I to test anymore, sorry

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/maikvandergaag/msft-extensions/issues/83?email_source=notifications&email_token=AHA3TCWPFMEVWVYFPIT4ESDQTFTANA5CNFSM4HDTQ342YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEDW7DKA#issuecomment-552464808, or unsubscribe https://github.com/notifications/unsubscribe-auth/AHA3TCQC2RWZ4MHJFEECVJLQTFTANANCNFSM4HDTQ34Q .

maikvandergaag commented 4 years ago

If you send me your organization is I will give you access to the beta.

Groetjes,

Maik van der Gaag

From: Singley notifications@github.com Sent: Monday, November 11, 2019 9:31:18 PM To: maikvandergaag/msft-extensions msft-extensions@noreply.github.com Cc: Maik van der Gaag maik@familie-vandergaag.nl; Mention mention@noreply.github.com Subject: Re: [maikvandergaag/msft-extensions] Power BI Actions: Set username and password for DQ datasources (#83)

I'm out of the country for a week with no access to my work environments. I'll be happy to test upon my return. Thanks!

On Mon, Nov 11, 2019, 9:21 AM moconnor8West notifications@github.com wrote:

@maikvandergaag https://github.com/maikvandergaag Sadly i ended up not using the component and i don't access to environment where I to test anymore, sorry

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/maikvandergaag/msft-extensions/issues/83?email_source=notifications&email_token=AHA3TCWPFMEVWVYFPIT4ESDQTFTANA5CNFSM4HDTQ342YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEDW7DKA#issuecomment-552464808, or unsubscribe https://github.com/notifications/unsubscribe-auth/AHA3TCQC2RWZ4MHJFEECVJLQTFTANANCNFSM4HDTQ34Q .

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/maikvandergaag/msft-extensions/issues/83?email_source=notifications&email_token=ACIXAE7PEQSBAGXZFNWPSLLQTG6JNA5CNFSM4HDTQ342YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEDYAY4A#issuecomment-552602736, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ACIXAE2QXCLUEBEP4LYIXJLQTG6JNANCNFSM4HDTQ34Q.

MartinWa commented 4 years ago

I am looking for this feature and I could help test it.

maikvandergaag commented 4 years ago

If you send your organization id of Azure DevOps I can add you to the beta

Groetjes,

Maik van der Gaag

From: Martin Wåger notifications@github.com Sent: Monday, November 11, 2019 11:33:00 PM To: maikvandergaag/msft-extensions msft-extensions@noreply.github.com Cc: Maik van der Gaag maik@familie-vandergaag.nl; Mention mention@noreply.github.com Subject: Re: [maikvandergaag/msft-extensions] Power BI Actions: Set username and password for DQ datasources (#83)

I am looking for this feature and I could help test it.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/maikvandergaag/msft-extensions/issues/83?email_source=notifications&email_token=ACIXAE3ZB5NPUM3SP4N6TRDQTHMRZA5CNFSM4HDTQ342YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEDYLVKA#issuecomment-552647336, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ACIXAE7MAIW3EH6RJTWNJDLQTHMRZANCNFSM4HDTQ34Q.

MartinWa commented 4 years ago

I have done some testing with the beta. I do the following three steps:

steps:
- task: Development-vanderGaag.maikvandergaag-power-bi-actionsDev.PowerBIActions.PowerBIActions@4
  displayName: 'Power BI Action: Publish'
  inputs:
    serviceConnection: PowerBi
    WorkspaceName: 'Embedded-Dev'
    PowerBIPath: '$(System.DefaultWorkingDirectory)/Statistics-CD/PowerBiReports/basic.pbix'
steps:
- task: Development-vanderGaag.maikvandergaag-power-bi-actionsDev.PowerBIActions.PowerBIActions@4
  displayName: 'Power BI Action: UpdateDatasource'
  inputs:
    serviceConnection: PowerBi
    Action: UpdateDatasource
    WorkspaceName: 'Embedded-Dev'
    DatasetName: basic
    OldServer: 'xxx.database.windows.net'
    NewServer: 'yyy.database.windows.net'
    OldDatabase: 'zzz'
    NewDatabase: 'ttt'
steps:
- task: Development-vanderGaag.maikvandergaag-power-bi-actionsDev.PowerBIActions.PowerBIActions@4
  displayName: 'Power BI Action: SQLDirect'
  inputs:
    serviceConnection: PowerBi
    Action: SQLDirect
    WorkspaceName: 'Embedded-Dev'
    DatasetName: basic
    ConnectionString: 'Data Source=yyy.database.windows.net;Initial Catalog=ttt;User ID=sss;Password=uuu'

When I run the first time (no report in Power BI) I get the following error: Dataset doesn't contain a datasource that matches selector #1 If I re-run without deleting the report it works.

The above works for Service Principal. I have also tested with User Principal but then I get: Exception calling "GetResult" with "0" argument(s): "AADSTS7000218: The request body must contain the following parameter: 'client_assertion' or 'client_secret'. Perhaps I have configured the User Principal wrong?

In any case the SQLDirect step seems to work fine (verified by calling the Get Datasources API).

maikvandergaag commented 4 years ago

Hi Martin,

Thank you for testing, regarding your issue are you using a native app?

From: Martin Wåger notifications@github.com Sent: woensdag 13 november 2019 10:11 To: maikvandergaag/msft-extensions msft-extensions@noreply.github.com Cc: Maik van der Gaag maik@familie-vandergaag.nl; Mention mention@noreply.github.com Subject: Re: [maikvandergaag/msft-extensions] Power BI Actions: Set username and password for DQ datasources (#83)

I have done some testing with the beta. I do the following three steps:

steps:

steps:

steps:

When I run the first time (no report in Power BI) I get the following error: Dataset doesn't contain a datasource that matches selector #1 If I re-run without deleting the report it works.

The above works for Service Principal. I have also tested with User Principal but then I get: Exception calling "GetResult" with "0" argument(s): "AADSTS7000218: The request body must contain the following parameter: 'client_assertion' or 'client_secret'. Perhaps I have configured the User Principal wrong?

In any case the SQLDirect step seems to work fine (verified by calling the Get Datasources API).

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/maikvandergaag/msft-extensions/issues/83?email_source=notifications&email_token=ACIXAEYVO2A2WXCKR22RJ5DQTPABHA5CNFSM4HDTQ342YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOED5NIKQ#issuecomment-553309226, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ACIXAE4CU2PSD6NKCQSJJILQTPABHANCNFSM4HDTQ34Q.

MartinWa commented 4 years ago

I am using a Web type of application

maikvandergaag commented 4 years ago

When using the User option it should be a native application that’s why youi are receiving the error.

From: Martin Wåger notifications@github.com Sent: woensdag 13 november 2019 10:24 To: maikvandergaag/msft-extensions msft-extensions@noreply.github.com Cc: Maik van der Gaag maik@familie-vandergaag.nl; Mention mention@noreply.github.com Subject: Re: [maikvandergaag/msft-extensions] Power BI Actions: Set username and password for DQ datasources (#83)

I am using a Web type of application

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/maikvandergaag/msft-extensions/issues/83?email_source=notifications&email_token=ACIXAE6E4RWLXPHVX4XU5FLQTPBUVA5CNFSM4HDTQ342YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOED5OSUA#issuecomment-553314640, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ACIXAE7QPWW4WW3ZM43R3ALQTPBUVANCNFSM4HDTQ34Q.

MartinWa commented 4 years ago

That worked. It took some time to figure out how to do it thoug so for anyone else with the same problem: Setting Treat application as a public client to true will allow the app registration to work for user principal (resource owner password): image

MartinWa commented 4 years ago

After further testing I still had problems with UpdateDatasource. I did however have the same problems with the non-beta version of the extension so I think that is unrelated. I also found out that I do not need to call UdpdateDatasource as it will be done for me when I call SQLDirect. For me the beta version works.

Note I have only one datasource in my report. Have not tested with multiple sources.

maikvandergaag commented 4 years ago

Thanks Martin,

Could you give me some more information on the UpdateDatabsource problems?

From: Martin Wåger notifications@github.com Sent: woensdag 13 november 2019 13:20 To: maikvandergaag/msft-extensions msft-extensions@noreply.github.com Cc: Maik van der Gaag maik@familie-vandergaag.nl; Mention mention@noreply.github.com Subject: Re: [maikvandergaag/msft-extensions] Power BI Actions: Set username and password for DQ datasources (#83)

After further testing I still had problems with UpdateDatasource. I did however have the same problems with the non-beta version of the extension so I think that is unrelated. I also found out that I do not need to call UdpdateDatasource as it will be done for me when I call SQLDirect. For me the beta version works.

Note I have only one datasource in my report. Have not tested with multiple sources.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/maikvandergaag/msft-extensions/issues/83?email_source=notifications&email_token=ACIXAE7ZJWYDIY56MB3UCWLQTPWH3A5CNFSM4HDTQ342YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOED56TTQ#issuecomment-553380302, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ACIXAEYX66JTSCYSQOTCIY3QTPWH3ANCNFSM4HDTQ34Q.

MartinWa commented 4 years ago

This is the output, i removed the text in brackets :

2019-11-13T12:11:14.3710996Z ##[section]Starting: Power BI Action: UpdateDatasource
2019-11-13T12:11:14.3817349Z ==============================================================================
2019-11-13T12:11:14.3817431Z Task         : Power BI Actions
2019-11-13T12:11:14.3817499Z Description  : Release Task Power BI Actions
2019-11-13T12:11:14.3817549Z Version      : 4.0.9
2019-11-13T12:11:14.3817618Z Author       : Maik van der Gaag
2019-11-13T12:11:14.3817664Z Help         : 
2019-11-13T12:11:14.3817732Z ==============================================================================
2019-11-13T12:11:15.2454059Z ******************************
2019-11-13T12:11:15.2461310Z ** Service Connection: <guid>
2019-11-13T12:11:15.2488679Z ** TenantId: 
2019-11-13T12:11:15.2495689Z ** ClientId: <guid>
2019-11-13T12:11:15.2503686Z ******************************
2019-11-13T12:11:15.3375838Z FilePattern           : d:\a\r1\a
2019-11-13T12:11:15.3379981Z ClientID               : <guid>
2019-11-13T12:11:15.3412900Z PassWord               : ***
2019-11-13T12:11:15.3416542Z Username               : ***
2019-11-13T12:11:15.3419508Z WorkspaceName         : <workspace>
2019-11-13T12:11:15.3423002Z Overwrite             : True
2019-11-13T12:11:15.3426723Z Create                : True
2019-11-13T12:11:15.3431254Z Action                : UpdateDatasource
2019-11-13T12:11:15.3435284Z Dataset               : <dataset>
2019-11-13T12:11:15.3439409Z Users                 : 
2019-11-13T12:11:15.3443442Z AccessRight           : 
2019-11-13T12:11:15.3446866Z OldServer             : <xxx>.database.windows.net
2019-11-13T12:11:15.3449659Z NewServer             : <yyy>.database.windows.net
2019-11-13T12:11:15.3453158Z OldDatabase           : <sss>
2019-11-13T12:11:15.3456988Z NewDatabase           : <ttt>
2019-11-13T12:11:15.3459910Z OldUrl                : 
2019-11-13T12:11:15.3463130Z NewUrl                : 
2019-11-13T12:11:15.3467777Z DatasourceType        : Sql
2019-11-13T12:11:15.3470657Z UpdateAll             : False
2019-11-13T12:11:15.3475284Z ClientSecret          : 
2019-11-13T12:11:15.3479119Z TenantId              : 
2019-11-13T12:11:15.3483926Z Service Principals    : 
2019-11-13T12:11:15.3503107Z Logging in with a User Principal
2019-11-13T12:11:15.7224124Z Trying to update the datasource
2019-11-13T12:11:15.7376783Z Getting Power BI Workspace properties; <workspace>
2019-11-13T12:11:16.4559461Z ##[error]Dataset doesn't contain a datasource that matches selector #1
2019-11-13T12:11:16.4840165Z ##[section]Finishing: Power BI Action: UpdateDatasource
maikvandergaag commented 4 years ago

Closing this issue if you need further assistant please let me know