Power BI - Consume on-premises data sources for external users with on-premises data gateway

In my previous post I illustrated how to use on-premises data gateway to expose on-premises data to Power BI.

I will show how external users can access those information.

Step 1: Ad external user to Azure Active Directory tenant

Step 2: Create a data source using the on-premises data gateway

Step 3: Create the data source in reports workspace

Step 4: Create test report

Step 5: Add permissions for external user

Let's try it out

By this way I was able to provide external user access to reports.

Configure on-premises data gateway to analyze on-premises data with Power BI

Recently I configured Power BI on-premises data gateway to connect on-premises SQL Server Analysis Services to Power BI. 

My objective was to make my on-premises data source to be available as a data source in Power BI. Then I will create my reports without a hassle. 

Following are the steps I followed

Step 01: Download on-premises data gateway installer

Step 02: Install on-premises data gateway installer

Step 03: Configure on-premises data gateway installer from on-premises server

Step 04: Configure Gateway from Power BI side

Let's try this out

That's all we need to do.

How to periodically send IIS Logs with custom fields to SQL Server database

Recently I wanted to push IIS logs to SQL database for analytics purpose.

Following are the steps I used

Step 1: Configurations at IIS side

Add custom fields as shown below

Step 2: Create a table in SQL Server database with following columns

Step 3: Construct following PowerShell scripts

a. RemoveLogs.ps1 - This will remove existing logs in working directory and clean up for new logs

$ErrorActionPreference = "Stop"
 
Import-Module Pscx -EA 0
 
function RemoveLogFiles
{
    Write-Host "Removing log files..."
    Remove-Item ($httpLogPath1)
}
 
function Main
{   
 
    [string] $httpLogPath1 = "E:\TempLogs\*.log"
     
    RemoveLogFiles 
 
    Write-Host -Fore Green "Successfully removed log files."
}

b. CopyLogs.ps1 - This will copy Logs from IIS log file location to our logs directory. I'm copying everything created during last two days

$Date = Get-Date
$Date = $Date.adddays(-2)
$Date2Str = $Date.ToString("yyyMMdd")
 
$Files1 = gci "\\IIS-01\LogFiles\W3SVC4"
ForEach ($File in $Files1){
     $FileDate = $File.creationtime
     $CTDate2Str = $FileDate.ToString("yyyyMMdd")
     if ($CTDate2Str -eq $Date2Str) {Copy-Item $File.Fullname "E:\TempLogs"}
}

c. CopyLogsToDB.ps1 - This will import logs to our SQL Server database table

$ErrorActionPreference = "Stop"
 
Import-Module Pscx -EA 0
 
function ImportLogFilesServer(
    [string] $httpLogPath)
{
    If ([string]::IsNullOrEmpty($httpLogPath) -eq $true)
    {
        Throw "The log path must be specified."
    }
 
    [string] $logParser = "${env:ProgramFiles(x86)}" `
        + "\Log Parser 2.2\LogParser.exe "
 
    [string] $query = `
        [string] $query = `
        "SELECT" `
            + " LogFilename" `
            + ", RowNumber" `
            + ", TO_TIMESTAMP(date, time) AS EntryTime" `
            + ", s-ip AS ServerIpAddress" `
            + ", cs-method AS Method" `
            + ", cs-uri-stem AS UriStem" `
            + ", cs-uri-query AS UriQuery" `
            + ", s-port AS Port" `
            + ", username  AS Username" `
            + ", c-ip AS ClientIpAddress" `
            + ", cs(User-Agent) AS UserAgent" `
            + ", pageurl AS Referrer" `
            + ", sc-status AS HttpStatus" `
            + ", sc-substatus AS HttpSubstatus" `
            + ", sc-win32-status AS Win32Status" `
            + ", time-taken AS TimeTaken" `
            + ", storename  AS StoreName" `
        + " INTO IISLog" `
        + " FROM $httpLogPath"

 
    [string] $connectionString = "Driver={SQL Server Native Client 11.0};" `
        + "Server=DB-01;Database=IISLogs;Trusted_Connection=yes;"
 
    [string[]] $parameters = @()
 
    $parameters += $query
    $parameters += "-i:W3C"
    $parameters += "-e:-1"
    $parameters += "-o:SQL"
    $parameters += "-createTable:ON"
    $parameters += "-oConnString:$connectionString"
 
    Write-Debug "Parameters: $parameters"
 
    Write-Host "Importing log files to database..."
    $logParser 
    $parameters
    & $logParser $parameters

}
 
 
function Main
{
    [string] $httpLogPath = "E:\TempLogs\*.log"
 

    ImportLogFilesServer $httpLogPath
 
#Repeat above for all other server
 
    Write-Host -Fore Green "Successfully imported log files."
}
 
Main

Step 4: Apply a schedule using Task Scheduler

This will upload latest logs including custom fields to SQL Server database.

Keynote : Building SPFx solutions for SharePoint Online and MS Teams at Global Microsoft 365 Developer Bootcamp Sri Lanka

It was my privilege to speak at Global Microsoft 365 Developer Bootcamp Sri Lanka.

I presented on building SPFx solutions for SharePoint Online and MS Teams.

Resolving “Could not retrieve a valid windows identity for username with UPN.” error for a user from external domain

In one of my SharePoint/BI farms I got following error when opening a PowerPivot report with data refresh.

     

In this scenario I had another domain with Forest trust and I faced the error when logging in to SharePoint site with a user account from that domain.
Following is the error message I got from ULS

SPSecurityContext: Could not retrieve a valid windows identity for username '' with UPN ''. UPN is required when Kerberos constrained delegation is used. Exception: System.ServiceModel.Security.SecurityAccessDeniedException: Access is denied

Following are the checkpoints

• Relevant SPN entries are in place : Done
• Service account is enabled for delegation : Done
• Name suffix is enabled for routing : Gotcha!!

In my scenario relevant name suffix was disabled. Following is the way to enable it

• Log in to Domain Controller
• Navigate to Active Directory Domains and Trusts. Go to Properties & Trusts tab

      

        

• Select the relevant domain name & click on Properties.Then navigate to Name Suffix Routing Tab

    

       

• Enable it

     

   














It resolved the error for me!

FIT Code Rush 2018 - Keynote

I was privileged to address at CodeRush hackathon organized by Faculty of IT - University of Moratuwa recently. Myself and my team at LB Finance PLC submitted few challenges for the hackathon from FinTech and cloud spaces.

We spent the same night at University premises and evaluated the competition. I was amazed and thrilled by the competence and dedication from young students

During my speech I presented opportunities available in FinTech and Cloud segments and stressed the importance of not forgetting basic concepts

    

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

ULS error log

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

Following is the data connection I had

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

Finally restart IIS to see the change