Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
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.

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

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.