Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.


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"


                             -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:




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):




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?





Super User
Super User

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

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.