Post
Ask Question

SSAS - Automating SQL Server Agent Service Logon As Administrator

Connect to your specific SQL analysis instance and go through properties to security. You should see that there is no Service Agent in the list of Server Administrators.

 
After executing my PowerShell script (below), the agent has been added.
 
 
 
The following PowerShell function will get the SQL service login name associated with the specified computer and will add that user to the Analysis Server administrator group.
  1. Function Add-SqlServiceLogonAccount  
  2. {  
  3.     [CmdletBinding()]  
  4.     param  
  5.     (  
  6.     [parameter(Mandatory=$true)]  
  7.     [string] $SqlServerInstance = $env:computername,  
  8.     [parameter(Mandatory=$true)]  
  9.     [string]  $AnalysisServerInstance,  
  10.     $ComputerName = $env:computername  
  11.     )  
  12.       
  13.     try  
  14.     {  
  15.         $ValidAnalysis = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")         | Out-String  
  16.         if([string]::IsNullOrWhitespace($ValidAnalysis))  
  17.         {  
  18.             throw "Unable to find either Microsoft.AnalysisServices.AdomdClient or Microsoft.AnalysisServices in GAC"  
  19.         }  
  20.   
  21.         $SqlInstance =  $SqlServerInstance.Split("\")  
  22. #Getting the required SQL services running for both default localhost and as well for new Instances  
  23.         if($SqlServerInstance -match "\\")  
  24.         {  
  25.             $SqlInstance = "SQLAgent`$$($SqlInstance[1])"  
  26.         }  
  27.         else  
  28.         {  
  29.             $SqlInstance = "SQLSERVERAGENT"  
  30.             $SqlServiceDetails = Get-WmiObject -Class Win32_Service -ComputerName $ComputerName |  
  31.             select name,DisplayName, StartName, State |  
  32.             Where {$_.name -eq "$SqlInstance"  -and $_.State -eq "Running" }  
  33.         }  
  34.         [string]$loginName = [string]::Empty  
  35.         if($SqlServiceDetails -ne $null)  
  36.         {  
  37.             if($SqlServiceDetails.Name -eq $SqlInstance)  
  38.             {  
  39.                 $loginName = $SqlServiceDetails.StartName  
  40.             }  
  41.         }  
  42.         if(![string]::IsNullOrWhitespace($loginName))  
  43.         {  
  44.             $Targetserver = new-Object Microsoft.AnalysisServices.Server  
  45.             $Targetserver.Connect($AnalysisServerInstance)  
  46.             #Getting members under the role Administrators  
  47.             $administrators = $Targetserver.Roles["Administrators"]  
  48.         #checking for the existence of loginname, if not exists adding member to Administrators group  
  49.             if ($administrators.Members.Name -notcontains $loginName)   
  50.             {  
  51.                 Write-Host "Adding the agent logon account $loginName to the Administrators group"  
  52.                 $administrators.Members.Add($loginName) | Out-Null  
  53.                 $administrators.Update()  
  54.                 Write-Host "Adding the agent logon account $loginName to the Administrators group"  
  55.             }  
  56.             else  
  57.             {  
  58.                 Write-Verbose "$loginName was already added to the Administrators group"  
  59.             }  
  60.             $Targetserver.Disconnect()  
  61.        }  
  62.     }  
  63.     catch  
  64.     {  
  65.     throw $_.Message  
  66.     }  
  67. }  

Here are some examples of the above script in use. Check the instance after running to ensure the user has been added correctly (as indicated in Figure 2 above)

Example 1 - Default SQL and Analysis instance
  1. Add-SqlServiceLogonAccount -SqlServerInstance "localhost" -AnalysisServerInstance "localhost"  
Example 2 - Named SQL and Analysis instance
  1. Add-SqlServiceLogonAccount -SqlServerInstance "pc-name\local" -AnalysisServerInstance "pc-name\local"