Saturday, April 14, 2018

Excel Services: Unable to refresh one or more data connections when using Excel 2016

Recently I used Microsoft Excel 2016 to publish a excel report to SharePoint, it gives following error when running the workbook. The workbook had a connection to BISM which is referring Analysis Services cube.

Error Message

image

ULS error log

The workbook '' attempted to access external data using the unsupported provider 'Provider=MSOLAP.8;

Following is the data connection I had

image

To resolve the issue, I had to add MSOLAP.8 to trusted data providers list.

image

image

image

Finally restart IIS to see the change

Saturday, February 17, 2018

Change the size of Azure VM in a given schedule using Azure Runbook

In our previous article, I explained how to write a simple PowerShell script to expand the size of an Azure VM.

  1. PowerShell script to expand and reduce the size of an Azure VM
  2. Change the size of Azure VM in a given schedule using Azure Runbook

In this article I’ll explain how to execute PowerShell scripts in a given schedule using Azure Automation Account, Runbooks and Schedules.

What is an Azure Automation?

Azure Automation is a Software as a Service (SaaS) that can be used to automate processes. You can automate cloud automation tasks using Azure Automation. Some common tasks are,

  • Build and deploy resources
  • Configure VMs
  • Monitor resources

What is a Runbook?

Azure Automation use Runbooks to automate processes. Runbooks are executed in automation Sandboxes, which means as a Platform as a Service (PaaS) VMs. You will get the isolation that you expect from Runbooks

Following are the steps I used to expand and reduce the size of VMs in my Azure tenant.

1. Create Azure Automation Account

image

Navigate to Variables section and create following variables

image

image

2. Create the Runbook

image

Create new Runbook as a PowerShell script to expand the size

image

Add following script

$connectionName = "AzureRunAsConnection"

try

{

# Get the connection "AzureRunAsConnection "

$servicePrincipalConnection=Get-AutomationConnection -Name $connectionName

"Logging in to Azure..."

Add-AzureRmAccount `

-ServicePrincipal `

-TenantId $servicePrincipalConnection.TenantId `

-ApplicationId $servicePrincipalConnection.ApplicationId `

-CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint

}

catch {

if (!$servicePrincipalConnection)

{

$ErrorMessage = "Connection $connectionName not found."

throw $ErrorMessage

} else{

Write-Error -Message $_.Exception

throw $_.Exception

}

}

$vmName = Get-AutomationVariable -Name 'vmName'

$resourceGroup = Get-AutomationVariable -Name 'resourceGroup'

$lowHwProfile = Get-AutomationVariable -Name 'lowHwProfile'

"Updating VM size"

Get-AzureRmVMSize -ResourceGroupName $resourceGroup -VMName $vmName

$vm = Get-AzureRmVM -ResourceGroupName $resourceGroup -Name $vmName

$vm.HardwareProfile.VmSize = $lowHwProfile

Update-AzureRmVM -VM $vm -ResourceGroupName $resourceGroup

"VM size updated"

Same way create another Runbook to reduce the VM size

3. Create Schedules and apply to Runbooks

Click on Schedules in Automation Account

image

image

Navigate to specific Runbook to apply the schedule

image

image

That’s it. We have to create another schedule to reduce the size of the VM on Monday night and apply to the particular Runbook

Friday, February 16, 2018

Presentation - Automating Business Processes with SharePoint & Flow

Following is the presentation I did at Microsoft Student Champs Monthly Meeting- February 2018

27750917_2293814987311308_7653858649129044371_n


Friday, February 2, 2018

PowerShell script to expand and reduce the size of an Azure VM

When we provision VMs from Azure, we select the best possible size template from the gallery. But sometimes our VMs can’t cater the demand for specific time periods or are underutilized in certain times. One of my SQL Server VMs is very busy on Mondays, but manageable on other days.

I had to use Azure PowerShell script to modify the size template. In this article I’ll share the script I’ve used to expand the VM size and later reduce its size.

I’ve split the article in to two

  1. PowerShell script to expand and reduce the size of an Azure VM
  2. Change the size of Azure VM in a given schedule using Azure Runbook

Expand VM size

$vmName = "Test-VM"
$resourceGroup = "Test-Resource"
$highHwProfile = "Standard_DS13_v2_Promo"
$subscriptionId = "<subscription_id>"

Login-AzureRmAccount
Select-AzureRMSubscription -SubscriptionId $subscriptionId

Get-AzureRmVMSize -ResourceGroupName $resourceGroup -VMName $vmName
$vm = Get-AzureRmVM -ResourceGroupName $resourceGroup -Name $vmName
$vm.HardwareProfile.VmSize = $highHwProfile
Update-AzureRmVM -VM $vm -ResourceGroupName $resourceGroup

Reduce VM size

$vmName = "Test-VM"
$resourceGroup = "Test-Resource"
$highHwProfile = "Standard_DS12_v2_Promo"
$subscriptionId = "<subscription_id>"

Login-AzureRmAccount
Select-AzureRMSubscription -SubscriptionId $subscriptionId

Get-AzureRmVMSize -ResourceGroupName $resourceGroup -VMName $vmName
$vm = Get-AzureRmVM -ResourceGroupName $resourceGroup -Name $vmName
$vm.HardwareProfile.VmSize = $highHwProfile
Update-AzureRmVM -VM $vm -ResourceGroupName $resourceGroup

Saturday, January 27, 2018

Microsoft Azure : Resolve - You don’t have access to this data. Please contact your global administrator to get access

I have been using my Azure subscription for a long time. But I was surprised when I got following error message when I tried to play around with Azure Active Directory

image

I can’t even add ore remove users!

Then I realized that I have associated my subscription to someone else's directory when creating the tenant.

Following are the steps I used to bring my tenant back to normal, where I have access to all features.

1. Create a new Azure Active Directory

image

image

image

2. Change directory in Azure Subscriptions

image

image

image

image

image

3. Verify

image

image

As you can see all my assets (including VMs) are unharmed.

Thursday, January 18, 2018

Presentation - Continuous Integration with SharePoint

I presented regarding ALM with SharePoint in SharePoint Sri Lanka Forum on 10th January 2018.

26239066_10156123357012716_4941186543697729196_n

26229564_10214594227070477_8761087956115667399_n

Following is the presentation I did

Monday, January 15, 2018

Automate build and release for SharePoint Framework solution

This is the third part of the article series on how to manage SharePoint Framework solution in a team environment

  1. Configure SharePoint Framework solution with Git
  2. Integrate SharePoint Framework solution in to TFS project
  3. Automate build and release for SharePoint Framework solution

In this blog post I’ll explain how to automate build and release process for SharePoint Framework solutions. This uses ALM API which was released recently.

Configure Solution

Add following script to the root of the solution. This script will retract if previous version of the solution already exists. Then it will add the solution to App Catalog site and then to the SharePoint site that you wish your customization to be published.

Param(

[string]$username,

[string]$password

)

$packagePath = ".\release\sharepoint\solution\sp-fx-test.sppkg"

$encpassword = convertto-securestring -String $password -AsPlainText -Force

$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $encpassword

Connect-PnPOnline -Url "https://dinushaonline.sharepoint.com/sites/dev" -Credentials $cred

$appExist = Get-PnPApp | ? { $_.Title -eq 'SPFxTest' }

if ($appExist -ne $null) { 

Uninstall-PnPApp -Identity $appExist.Id

Unpublish-PnPApp -Identity $appExist.Id

Remove-PnPApp -Identity $appExist.Id

}

Add-PnPApp -Path $packagePath -Publish -Overwrite

$appAfterAdd = Get-PnPApp | ? { $_.Title -eq 'SPFxTest' }

Publish-PnPApp -Identity $appAfterAdd.id

Install-PnPApp -Identity $appAfterAdd.id

image

Configure Build

Create a new build definition in your TFS Online environment to perform continuous integration

image

image

We have to perform npm install, gulp bundle, ship and archive steps in following steps

image

image

image

image

image

image

Configure Release

Create variables for SharePoint online username and password which will be used in publishapp.ps1 script

image

image

image

image

image

image

Enable Continuous Deployment

This step will be used to automatically trigger a release as soon as a build is succeeded

image