Power BI Backup Automation Suite

A comprehensive PowerShell-based automation suite for backing up Power BI Report Server reports, extracting connection strings, managing permissions, and scheduling automated backups.

📋 Table of Contents

• Overview
• Features
• Prerequisites
• Installation
• Scripts Overview
• Usage Guide
• Configuration
• Security
• Scheduling
• Troubleshooting
• Contributing
• License

🎯 Overview

This suite provides enterprise-grade automation for Power BI Report Server management, including:

• Automated Backups: Scheduled backups of all Power BI reports (PBIX) and paginated reports (RDL)
• Connection String Extraction: Extract and import data source connection strings to SQL Server
• Permission Management: GUI-based tool for managing report permissions
• Email Notifications: Automated email summaries of backup operations
• Task Scheduling: Easy Windows Task Scheduler integration
• Multi-Server Support: Manage multiple Power BI servers from a single interface

✨ Features

Core Capabilities

• ✅ Multi-server Power BI backup automation
• ✅ Secure credential management using Windows Credential Manager
• ✅ Connection string extraction and SQL Server import
• ✅ Report permissions management GUI
• ✅ Email notification system with GUI configuration
• ✅ Windows Task Scheduler integration
• ✅ Background mode for scheduled tasks
• ✅ Comprehensive logging
• ✅ LDAP/Active Directory configuration helper

Security Features

• 🔒 Credentials stored in Windows Credential Manager (encrypted)
• 🔒 No hardcoded passwords or secrets in code
• 🔒 XML fallback for credential storage (less secure, but functional)
• 🔒 Separate storage of server URIs and credentials
• 🔒 Credential validation and automatic refresh

📦 Prerequisites

System Requirements

• Windows: Windows 10/11 or Windows Server 2016+
• PowerShell: PowerShell 5.1 or later
• Modules:
  • ActiveDirectory (optional, for LDAP helper)
  • CredentialManager (optional, for enhanced credential storage)

Network Requirements

• Access to Power BI Report Server(s)
• Network access to backup storage location
• SMTP server access (for email notifications)
• SQL Server access (for connection string import)

🚀 Installation

1. Clone or Download

# Clone the repository
git clone <repository-url>
cd PowerBI-Automation

# Or download and extract to a folder (e.g., C:\PowerBI-Automation)

2. Configure Environment Variables

REQUIRED: Create a .env file in the project root directory:

# Copy the example file
Copy-Item .env.example .env

# Edit .env and set your configuration path
# Example:
DEFAULT_CONFIG_PATH=\\server\share\PBI_Backup_Config\servers.xml

Important Notes:

• The .env file is REQUIRED - scripts will fail without it
• Never commit the .env file to version control (it's in .gitignore)
• Update DEFAULT_CONFIG_PATH to match your environment
• Use network paths (\\server\share) or local paths (C:\Path) as needed

### 2. Set Execution Policy

```powershell
# Run PowerShell as Administrator
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser

3. Install Optional Modules

# Install CredentialManager module (recommended)
Install-Module -Name CredentialManager -Scope CurrentUser

# Install ActiveDirectory module (if needed for LDAP helper)
Install-Module -Name ActiveDirectory -Scope CurrentUser

📚 Scripts Overview

All scripts are located in the PowerBI-Automation directory.

1. PowerBI-Launcher.ps1

Main Dashboard - Central GUI launcher for all tools

Features:

• Centralized access to all tools
• Configuration status monitoring
• Credential management
• Quick launch buttons for all scripts
• Intelligent button enable/disable based on configuration status

Usage:

.\PowerBI-Launcher.ps1
.\PowerBI-Launcher.ps1 -Show

Button Enable/Disable Logic:

The launcher includes intelligent button state management to ensure proper setup order:

Button	Enabled When	Visual Indicator
Manage Credentials	Always	Orange (always enabled)
Power BI Backup Configuration	Credentials configured	Green (enabled) / Gray (disabled)
Connection Strings Extractor	Credentials + Configuration complete	Purple (enabled) / Gray (disabled)
Task Scheduler	Always	Blue (always enabled)
Report Permissions Manager	Credentials + Configuration complete	Dark Magenta (enabled) / Gray (disabled)

Configuration Requirements:

The following buttons require both credentials AND Power BI configuration to be enabled:

• Connection Strings Extractor - Requires:
  • ✅ Credentials configured
  • ✅ At least one Power BI server added
  • ✅ Backup root path set
• Report Permissions Manager - Requires:
  • ✅ Credentials configured
  • ✅ At least one Power BI server added
  • ✅ Backup root path set

Why This Matters:

Connection Strings and Report Permissions tools require Power BI servers to be configured first. The disable mechanism:

• ✅ Prevents errors from running tools without proper configuration
• ✅ Guides users through the correct setup sequence
• ✅ Provides clear visual feedback (gray = disabled, colored = enabled)
• ✅ Automatically enables buttons once configuration is complete
• ✅ Shows warning message if user tries to click disabled buttons

Setup Sequence:

1. Click "Manage Credentials" → Enter shared credentials for Power BI servers
2. Click "Power BI Backup Configuration" → Add at least one server and set backup root path
3. Connection Strings and Report Permissions buttons automatically become enabled (change from gray to colored)

2. PowerBI-Backup.ps1

Main Backup Script - Downloads and backs up all Power BI reports

Parameters:

• -ConfigPath <path> - Path to configuration file
• -ListServers - List all configured servers
• -ServerName <name> - Backup specific server only
• -Configuration - Open server configuration GUI
• -Background - Run in background mode (for scheduled tasks)
• -LoadFunctionsOnly - Load functions without executing

Usage Examples:

# List configured servers
.\PowerBI-Backup.ps1 -ListServers

# Add a new server (opens GUI)
.\PowerBI-Backup.ps1 -Configuration

# Backup all servers
.\PowerBI-Backup.ps1

# Backup specific server
.\PowerBI-Backup.ps1 -ServerName "Production"

# Run in background mode (for scheduled tasks)
.\PowerBI-Backup.ps1 -Background

Functions Available:

• Add-PBIServer - Add a new Power BI server
• Remove-PBIServer - Remove a server configuration
• Show-ConfiguredServers - Display all configured servers
• Get-ServerCredential - Get credentials for a server
• Test-ServerCredentials - Test server credentials

3. PowerBI-ConnectionStrings.ps1

Connection String Extractor - Extracts connection strings from reports and imports to SQL Server

Parameters:

• -ConfigPath <path> - Path to configuration file
• -ServerName <name> - Extract from specific server only
• -SqlServerName <name> - SQL Server name
• -SqlDatabaseName <name> - SQL Database name
• -SqlSchemaName <name> - SQL Schema name (required)
• -SqlTableName <name> - SQL Table name (required)
• -UseSqlServerAuth - Use SQL Server authentication
• -SqlUsername <user> - SQL Server username
• -SqlPassword <pass> - SQL Server password
• -Show - Show GUI dialog
• -Background - Run in background mode
• -TrackAccessStatus - Track report access status

Usage Examples:

# Extract connection strings (opens GUI for SQL Server config)
.\PowerBI-ConnectionStrings.ps1 -Show

# Extract from specific server
.\PowerBI-ConnectionStrings.ps1 -ServerName "Production" -Show

# Extract and import to SQL Server (command line)
.\PowerBI-ConnectionStrings.ps1 -SqlServerName "SQLServer01" -SqlDatabaseName "BI_Data" -SqlTableName "ConnectionStrings"

# Run in background mode
.\PowerBI-ConnectionStrings.ps1 -Background

4. PowerBI-EmailNotification.ps1

Email Notification Sender - Sends backup summary emails

Parameters:

• -SummaryFilePath <path> - Path to summary file
• -ShowDialog - Show GUI configuration dialog

Usage Examples:

# Open email configuration GUI
.\PowerBI-EmailNotification.ps1 -ShowDialog

# Send email with specific summary file
.\PowerBI-EmailNotification.ps1 -SummaryFilePath "C:\Backups\PowerBI_Reports_20240101\_Overall_Summary.txt"

Features:

• GUI-based recipient management
• Email preview
• Automatic credential storage
• Support for Office 365 SMTP
• Find latest summary file automatically

5. PowerBI-ReportPermissions.ps1

Report Permissions Manager - GUI tool for managing report permissions

Parameters:

• -ConfigPath <path> - Path to configuration file
• -Show - Show GUI (default)

Usage:

.\PowerBI-ReportPermissions.ps1
.\PowerBI-ReportPermissions.ps1 -Show

Features:

• View all reports and their permissions
• Add/remove users and groups
• Active Directory integration
• Permission inheritance management
• Bulk permission operations

6. PowerBI-TaskScheduler.ps1

Task Scheduler GUI - Create and manage Windows scheduled tasks

Parameters:

• -ScriptPath <path> - Path to script to schedule
• -TaskName <name> - Name for the scheduled task
• -Arguments <args> - Arguments to pass to script
• -Show - Show GUI (default)

Usage Examples:

# Open task scheduler GUI
.\PowerBI-TaskScheduler.ps1

# Schedule PBI backup script
.\PowerBI-TaskScheduler.ps1 -ScriptPath "C:\PowerBI-Automation\PowerBI-Backup.ps1" -TaskName "PBI Report Server Backup"

# Schedule connection strings extraction
.\PowerBI-TaskScheduler.ps1 -ScriptPath "C:\PowerBI-Automation\PowerBI-ConnectionStrings.ps1" -TaskName "PBI Connection Strings Extraction"

Features:

• Daily, weekly, or hourly scheduling
• Run as SYSTEM or current user
• Passwordless task creation
• View existing tasks
• Run tasks immediately
• Delete tasks

7. PowerBI-LDAPHelper.ps1

LDAP Configuration Helper - Discovers Active Directory settings for LDAP configuration

Usage:

.\PowerBI-LDAPHelper.ps1

Features:

• Auto-discovers Domain Controller
• Generates LDAP configuration values
• Tests LDAP connectivity
• Provides .env file format output

Note: This script must be run on a domain-joined Windows computer.

⚙️ Configuration

Initial Setup

1. Launch the Main Dashboard:

   .\PowerBI-Launcher.ps1

2. Configure Credentials:

   • Click "Manage Credentials"
   • Enter shared credentials for all Power BI servers
   • Credentials are stored securely in Windows Credential Manager
   • Note: This enables the "Power BI Backup Configuration" button

3. Add Power BI Servers:

   • Click "Power BI Backup Configuration" (enabled after credentials are set)
   • Add your Power BI Report Server URLs
   • Set backup root directory
   • Note: This enables the "Connection Strings Extractor" and "Report Permissions Manager" buttons

4. Configure Email (Optional):

   • Run .\PowerBI-EmailNotification.ps1 -ShowDialog
   • Add email recipients
   • Configure "From" email address
   • Set email password

Configuration File

The configuration is stored in an XML file. The path is set in your .env file via the DEFAULT_CONFIG_PATH variable.

Structure:

<Configuration>
  <GlobalSettings>
    <BackupRoot>C:\Backups\PowerBI</BackupRoot>
    <SharedCredentials>
      <UserName>domain\username</UserName>
      <IsConfigured>true</IsConfigured>
      <LastUpdated>2024-01-01 12:00:00</LastUpdated>
    </SharedCredentials>
  </GlobalSettings>
  <Servers>
    <Server>
      <Name>Production</Name>
      <ReportServerUrl>https://prod.company.com/power_bi</ReportServerUrl>
      <BackupRoot>C:\Backups\PowerBI\Production</BackupRoot>
    </Server>
  </Servers>
</Configuration>

Custom Configuration Path

You can use a custom configuration path:

.\PowerBI-Backup.ps1 -ConfigPath "C:\MyConfig\servers.xml"

The last used path is automatically saved and reused.

🔒 Security

Credential Storage

Primary Method (Recommended):

• Credentials stored in Windows Credential Manager
• Encrypted by Windows
• Accessible only to the user or system (depending on persistence level)

Fallback Method:

• XML files in %LOCALAPPDATA%\PBI_Backup_Config\
• Less secure but functional if Credential Manager is unavailable
• Files are not included in Git (see .gitignore)

Best Practices

1. Never commit sensitive data:

   • Configuration files with server URLs
   • Credential files (.cred.xml)
   • Environment files (.env)

2. Use Windows Credential Manager:

   • Prefer LocalMachine persistence for system-wide access
   • Use Enterprise persistence for domain-wide access

3. Regular Credential Rotation:

   • Update credentials periodically
   • Remove old credentials when no longer needed

4. Network Security:

   • Use HTTPS for Power BI Report Server URLs
   • Restrict access to backup directories
   • Use encrypted connections for SQL Server

Security Checklist

• ✅ No hardcoded passwords in code
• ✅ Credentials stored securely
• ✅ Configuration files excluded from Git
• ✅ Sensitive data in .gitignore
• ✅ LDAP helper uses placeholders for credentials

📅 Scheduling

Using Task Scheduler GUI

1. Launch Task Scheduler:

   .\PowerBI-TaskScheduler.ps1

2. Configure Task:

   • Select script path (e.g., PowerBI-Backup.ps1)
   • Set arguments (e.g., -Background)
   • Choose schedule (Daily, Weekly, or Hourly)
   • Set start time
   • Choose run mode (SYSTEM or current user)

3. Create Task:

   • Click "Create / Update Task"
   • Task is registered in Windows Task Scheduler

Using Main Launcher

1. Open PowerBI-Launcher.ps1
2. Click "Task Scheduler"
3. Configure and create the task

Recommended Schedules

• Daily Backup: Run at 2:00 AM daily
• Connection Strings: Run weekly on Sunday at 3:00 AM
• Email Summary: Run after backup completes

Background Mode

Always use -Background flag for scheduled tasks:

.\PowerBI-Backup.ps1 -Background
.\PowerBI-ConnectionStrings.ps1 -Background

This ensures:

• No GUI dialogs appear
• All output logged to files
• Runs silently in Task Scheduler

🐛 Troubleshooting

Common Issues

1. "Credentials not found"

Solution:

• Run PowerBI-Launcher.ps1
• Click "Manage Credentials"
• Enter and save credentials

2. "Configuration file not found"

Solution:

• Run PowerBI-Backup.ps1 -Configuration
• This creates the default configuration file
• Or specify custom path with -ConfigPath

3. "Access denied" when creating scheduled task

Solution:

• Run PowerShell as Administrator
• Or use "Run only when user is logged on" option
• Or provide Windows password when prompted

4. "Cannot connect to Power BI Server"

Check:

• Server URL is correct and accessible
• Credentials have access to the server
• Network connectivity to server
• Firewall rules allow connection

5. "Email sending failed"

Check:

• SMTP server settings (must be configured in .env file)
• Email credentials are correct
• Office 365 app password if MFA is enabled
• Firewall allows SMTP traffic

Log Files

Log files are stored in:

%LOCALAPPDATA%\PBI_Backup_Config\Logs\

Log File Names:

• PowerBI-Backup_YYYYMMDD_HHMMSS.log - Backup logs
• ConnectionStrings_YYYYMMDD_HHMMSS.log - Connection string extraction logs

Debug Mode

For detailed debugging, check log files or run scripts without -Background to see console output.

📝 Usage Examples

Complete Workflow

# 1. Initial Setup
.\PowerBI-Launcher.ps1

# 2. Configure credentials (click "Manage Credentials")
# 3. Add servers (click "Power BI Backup Configuration")

# 4. Test backup
.\PowerBI-Backup.ps1 -ServerName "Production"

# 5. Schedule daily backup
.\PowerBI-TaskScheduler.ps1 -ScriptPath "C:\PowerBI-Automation\PowerBI-Backup.ps1" -TaskName "PBI Backup Daily"

# 6. Configure email notifications
.\PowerBI-EmailNotification.ps1 -ShowDialog

# 7. Schedule email after backup
.\PowerBI-TaskScheduler.ps1 -ScriptPath "C:\PowerBI-Automation\PowerBI-EmailNotification.ps1" -TaskName "PBI Backup Email"

Advanced Usage

# Load functions for scripting
. .\PowerBI-Backup.ps1 -LoadFunctionsOnly

# Add server programmatically
Add-PBIServer -ServerName "Test" -ReportServerUrl "https://test.company.com/power_bi" -BackupRoot "C:\Backups\Test"

# List all servers
.\PowerBI-Backup.ps1 -ListServers

# Extract connection strings to SQL Server
.\PowerBI-ConnectionStrings.ps1 -SqlServerName "SQL01" -SqlDatabaseName "BI" -SqlSchemaName "MySchema" -SqlTableName "ConnStrings"

🤝 Contributing

Code Standards

• Use PowerShell best practices
• Include error handling
• Add comments for complex logic
• Follow existing code style
• Test all changes thoroughly

Security Guidelines

• Never commit credentials or sensitive data
• Use placeholders in examples
• Document security considerations
• Follow principle of least privilege

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

📞 Support

For issues, questions, or contributions:

• Open an issue in the repository
• Check the troubleshooting section
• Review log files for errors

🔄 Version History

Current Version

• Multi-server backup support
• Secure credential management
• Connection string extraction
• Email notifications
• Task scheduler integration
• Report permissions management
• LDAP configuration helper