Script to create data sources

[daive]

Hi all,

I am trying to script the creation of data sources as we are doing a tenant migration.

I have some questions about the following MS scripts but first some background into my environment...

My on-premises gateway was setup using a recovery key (as per https://github.com/microsoft/PowerBI-Developer-Samples/blob/master/PowerShell%20Scripts/EncryptGatew...) 

My connections are all SQL with a service principal as the auth

Script 1:

https://github.com/microsoft/PowerBI-Developer-Samples/blob/master/PowerShell%20Scripts/EncryptGatew...

1. I should remove the sections basic credentialks, windows credentials, anonymous credentials & Oauth credentials and just leave key credentials?
2. under the param section, there is a variable for $Key - does that need to be specified somewhere?
3. Is the key value the same as the recovery key?

I added in a variable

$Key = "xxxxxxxxxxxxxxxxx"

And when running the script i get no errors

Script 2:

https://github.com/microsoft/PowerBI-Developer-Samples/blob/master/PowerShell%20Scripts/CreateDataso...

1. I am fine to enter the details datasource name, datasource type, gateway id, secret, app id, tenant id but for username and password what do i enter as mentioned i am using a service principal?
2. Under $encryptedCredentials = EncryptKeyCredentials what would this string look like in my case?

When running the script I am getting errors like

The term 'EncryptWindowsCredentials' is not recognized as a name of a cmdlet, function, script

I changed it to $encryptedCredentials = EncryptKeyCredentials but then all sorts of errors

Is someone able to point me in the right direction?

Thanks,
David

[lbendlin]

great questions. Make sure to be precise when you say "connections" - you seem to be working on gateway connections, not semantic model connections.

The recovery key has nothing to do with connections, it is only needed when you add or modify gateway cluster members.

You are saying all your SQL sources use Service Principals for authentication>  Where are you migrating to?

[daive]

@lbendlin ,

My goal is to create managed connections. We are busy with a tenant migration (consolidation of multiple tenants into one).

According to script 2 i need to run script 1 first so that is why I am referencing data gateways. But my goal.is to create the managed connections which use the gateway to connect to data. In my case all connections are sql based. The MS documentation on creating datasources seems light on info so these scripts looked great but I am unable to get a result, likely either me misunderstanding the requirements or formatting the body of the REST call.

So looking for some help forming the script to achieve my goal

[lbendlin]

Personal, biased, subjective opinion:  Don't waste your time.  Set up the connections from scratch in the new environment.  The entire connections API is half baked and very temperamental. A true time sink.

[daive]

thanks @lbendlin ,

I am leaning towards the same conclusion. Thanks for having a look. Hopefully the API is improved eventually.

[Anonymous]

Hi, @daive 
 

I'm sorry to hear that you've lost hope, and I can see that you've made numerous attempts to resolve the issue.

However, if you feel that performance improvements are needed, you can

submit an idea on https://ideas.fabric.microsoft.com/. This allows other users with the same needs to vote for your idea, helping to expedite its implementation. Many features of our existing products are designed and upgraded based on customer feedback. As demand increases, this issue is likely to be addressed in the future.

Additionally, if you find lbendlin 's response to be an effective alternative, you might consider accepting it as the solution. This not only acknowledges lbendlin's effort in assisting you but also helps other community members with similar issues find solutions more quickly.

Thank you in advance for your efforts and understanding. I hope these suggestions are helpful to you! 

Best Regards,

Leroy Lu