SqlRSServiceAccount: New resource proposal

[johlju]

Resource proposal

Manages the Windows service account for SQL Server Reporting Services. This resource handles the complete service account change workflow, which is complex and requires multiple steps to maintain site accessibility. Having a dedicated resource with encryption key backup/restore support makes this operation safer.

Parent Issue: #2415

Features

• Changes the service account for Reporting Services
• Handles complete service account change workflow automatically
• Supports encryption key backup/restore to preserve encrypted data
• Supports encryption key regeneration for simpler deployments
• Creates SQL Server login and grants database rights for the new account
• Recreates URL reservations after account change
• Re-initializes Reporting Services after change
• Supports both SQL Server Reporting Services (SSRS) and Power BI Report Server (PBIRS)
• SQL Server 2019+ only

Service Account Change Workflow

When the service account needs to be changed, the resource performs these steps in order:

1. Backup encryption key (if EncryptionKeyManagement = 'BackupRestore')
   • Uses Backup-SqlDscRSEncryptionKey
2. Change the service account
   • Uses Set-SqlDscRSServiceAccount
3. Create SQL Server login for the new service account
   • Uses New-SqlDscLogin (via Connect-SqlDscDatabaseEngine)
4. Grant database rights to the new service account
   • Uses Request-SqlDscRSDatabaseRightsScript and Invoke-SqlDscQuery
5. Restart the service
   • Uses Restart-SqlDscRSService
6. Handle encryption key
   • If BackupRestore: Uses Restore-SqlDscRSEncryptionKey
   • If Regenerate: Uses Remove-SqlDscRSEncryptionKey then New-SqlDscRSEncryptionKey
7. Recreate URL reservations
   • Uses Set-SqlDscRSUrlReservation -RecreateExisting
8. Re-initialize Reporting Services
   • Uses Initialize-SqlDscRS
9. Final service restart
   • Uses Restart-SqlDscRSService

Commands Used

Method	Commands
Get	Get-SqlDscRSServiceAccount, Get-SqlDscRSConfiguration (WindowsServiceIdentityActual)
Test	Compare current service account with desired
Set	Backup-SqlDscRSEncryptionKey, Set-SqlDscRSServiceAccount, Connect-SqlDscDatabaseEngine, New-SqlDscLogin, Request-SqlDscRSDatabaseRightsScript, Invoke-SqlDscQuery, Restart-SqlDscRSService, Restore-SqlDscRSEncryptionKey, Remove-SqlDscRSEncryptionKey, New-SqlDscRSEncryptionKey, Set-SqlDscRSUrlReservation, Initialize-SqlDscRS

Proposed properties

Property	Type qualifier	Data type	Description	Default value	Allowed values
InstanceName	Key	String	The RS instance name (SSRS or PBIRS)	None	None
Credential	Required	PSCredential	The service account credentials	None	None
UseBuiltInAccount	Write	Boolean	Indicates the account is a built-in account (e.g., NetworkService)	$false	None
DatabaseServerName	Required	String	SQL Server hosting the RS database (needed for granting rights)	None	None
DatabaseInstanceName	Write	String	SQL instance name for the RS database	MSSQLSERVER	None
EncryptionKeyManagement	Write	String	How to handle encryption keys	Regenerate	BackupRestore, Regenerate
BackupEncryptionKeyPath	Write	String	Path for encryption key backup (required when BackupRestore)	None	None
BackupEncryptionKeyPassword	Write	PSCredential	Password for encryption key backup (required when BackupRestore)	None	None
RestartTimeout	Write	UInt32	Seconds to wait after service restart	None	None

Encryption Key Management Options

Option	Description	Use Case
Regenerate (default)	Removes encryption key and creates a new one. This destroys all encrypted data (stored credentials, connection strings).	Development/test environments or when encrypted data loss is acceptable.
BackupRestore	Backs up the encryption key before the change and restores it after. Preserves encrypted data.	Production environments where encrypted data must be preserved.

Special considerations or limitations

1. Proposed File: source/Classes/020.SqlRSServiceAccount.ps1

2. Inheritance: Inherits from ResourceBase (from DscResource.Base module), not SqlResourceBase.

3. Complex workflow: Changing the service account is a complex operation that requires multiple steps. The resource handles all steps automatically to ensure site accessibility is maintained.

4. Encryption key management: Users must choose between Regenerate (destroys encrypted data) and BackupRestore (preserves encrypted data). Production environments should use BackupRestore.

5. Database server access: The resource needs to connect to the SQL Server hosting the RS database to create the login and grant rights. The DatabaseServerName and DatabaseInstanceName properties are required for this.

6. URL reservation recreation: After changing the service account, URL reservations must be recreated. The resource handles this automatically using Set-SqlDscRSUrlReservation -RecreateExisting.

7. Recommended usage order: This resource should be used after SqlRSDatabase has initialized the report server.

8. Integration tests: Should follow the patterns in Integration_Test_Commands_BIReportServer_Secure stage (specifically the Pre.ServiceAccountChange.Secure.RS, Mid.ServiceAccountChange.Secure.RS, and Post.ServiceAccountChange.Secure.RS tests).