cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Issues with Powershell script and stored credentials when deploying on-Prem

We're using the Powershell script concept from here, to push Power BI reports up to our on-prem Power BI report server.

https://github.com/microsoft/ReportingServicesTools

 

Have some across something bizarre.  

 

This works great for  pushing PBIX files from a network drive up to our on-prem Report Server.

Unfortunately, we're having problems setting credentials securely via PowerShell (so that we can set a refresh definition)

 

Basically, in the core Powershell script, we are doing this: 

 

$dataSource.CredentialRetrieval = "Store"

$dataSource.DataModelDataSource.AuthType = "UsernamePassword"

$dataSource.DataModelDataSource.Username = "SQLUserName" 

$dataSource.DataModelDataSource.Secret = '"SQLPassword"

 Set-RsRestItemDataSource

                             -WebSession $session -RsItem "$folderPath/$PBIXName"

                                        -RsItemType PowerBIReport -DataSources $datasource

 

This "appears" to work.  When we go up to the Report Server and look at the data source screen, it seems to show what we'd otherwise type in manually.  If we do a "test connection", without changing anything, it works.  

 

However, when we defined a scheduled refresh and try to refresh immediately, we get an authentication error on the stored credentials.

 

And now - here's where it gets WEIRD!!!

 

If we go back to the data source screen, and simply click SAVE (without making any changes) and then try to do the refresh again, it works.    

 

If someone told me that, I don't know if I'd believe them.  But we can reproduce this every time.   It's like we have to go into the Power BI report server page and click SAVE.  (More on that in a minute) 

 

So at the moment, our Powershell script just pushes the PBIX up to the report server, without setting credentials.  Then we set the credentials manually.   OK, it "works" but hardly a good DevOps solution.

 

Now, this had me really curious - what was in the Report Server database (specifically in DataModelDataSource) for the User and Password....when I first tried to push it up through PowerShell, and then after I clicked SAVE in the web interface.

 

Turns out, they were different.

The varbinary values for our user ID and password (they are the same) were initially:

 

0xB237E58B0D24F50A6C870A22478EB5C4

 

However, after I go into the interface and click save  (even though it shows the same text), it saves in the DataModelDataSource table as this (and this is the one that works with the refresh):

 

0xB237E58B0D24F50A2EA3C3477345C01580996198BE863F3B2CAE6FC45159A3A3

 

So the first 18 characters are the same, but it seems that the remainder is necessary.

 

Suffice it to say, I'm not an encryption expert.   

Is there a way to "jam in" an encrypted varbinary(max) value into DataModelSource, to satisfy the credential requirements?

 

Thanks,

Kevin

 

1 REPLY 1
Highlighted
Super User III
Super User III

Re: Issues with Powershell script and stored credentials when deploying on-Prem

Looking at the examples when you do a:

Get-Help Set-RsRestItemDataSource -Full 

it appears that the DataSources parameter might be expecting an array of data source objects. Have you tried the following sort of pattern to reset the username/password?

 

$dataSources = Get-RsRestItemDataSource -RsItem '/MyPowerBIReport'
$dataSources[0].DataModelDataSource.AuthType = 'UsernamePassword' 
$dataSources[0].DataModelDataSource.Username = 'sqlSa'
$dataSources[0].DataModelDataSource.Secret = 'sqlSaPassword'
Set-RsRestItemDataSource -RsItem '/MyPowerBIReport' -RsItemType PowerBIReport -DataSources $datasources

 

I don't think there is a valid way of directly injecting a value into the tables. Directly modification of the tables is not supported and you would need access to the server encryption key to generate the correct value anyway.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors