<

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

SSAS: Automating SQL Server Agent service logon as administrator

Published on
3,347 Points
347 Views
Last Modified:
Dorababu M
7+ years of experience in IT. Working as senior developer in Wallero, Hyderabad from past one year.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that with a PowerShell script.

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. 

Figure 1 - Before


After executing my PowerShell script (below), the agent has been added.


Figure 2 - After script


The following PowerShell function will get the SQL service logon name associated with the specified computer and will add that user to the Analysis Server administrator group.


Function Add-SqlServiceLogonAccount
{
    [CmdletBinding()]
    param
    (
    [parameter(Mandatory=$true)]
    [string] $SqlServerInstance = $env:computername,
    [parameter(Mandatory=$true)]
    [string]  $AnalysisServerInstance,
    $ComputerName = $env:computername
    )
    
    try
    {
        $ValidAnalysis = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")         | Out-String
        if([string]::IsNullOrWhitespace($ValidAnalysis))
        {
            throw "Unable to find either Microsoft.AnalysisServices.AdomdClient or Microsoft.AnalysisServices in GAC"
        }

        $SqlInstance =  $SqlServerInstance.Split("\")
#Getting the required SQL services running for both default localhost and as well for new Instances
        if($SqlServerInstance -match "\\")
        {
            $SqlInstance = "SQLAgent`$$($SqlInstance[1])"
        }
        else
        {
            $SqlInstance = "SQLSERVERAGENT"
            $SqlServiceDetails = Get-WmiObject -Class Win32_Service -ComputerName $ComputerName |
            select name,DisplayName, StartName, State |
            Where {$_.name -eq "$SqlInstance"  -and $_.State -eq "Running" }
        }
        [string]$loginName = [string]::Empty
        if($SqlServiceDetails -ne $null)
        {
            if($SqlServiceDetails.Name -eq $SqlInstance)
            {
                $loginName = $SqlServiceDetails.StartName
            }
        }
        if(![string]::IsNullOrWhitespace($loginName))
        {
            $Targetserver = new-Object Microsoft.AnalysisServices.Server
            $Targetserver.Connect($AnalysisServerInstance)
            #Getting members under the role Administrators
            $administrators = $Targetserver.Roles["Administrators"]
        #checking for the existence of loginname, if not exists adding member to Administrators group
            if ($administrators.Members.Name -notcontains $loginName) 
            {
                Write-Host "Adding the agent logon account $loginName to the Administrators group"
                $administrators.Members.Add($loginName) | Out-Null
                $administrators.Update()
                Write-Host "Adding the agent logon account $loginName to the Administrators group"
            }
            else
            {
                Write-Verbose "$loginName was already added to the Administrators group"
            }
            $Targetserver.Disconnect()
       }
    }
    catch
    {
    throw $_.Message
    }
}


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

Add-SqlServiceLogonAccount -SqlServerInstance "localhost" -AnalysisServerInstance "localhost"


Example 2 - Named SQL and Analysis instance

Add-SqlServiceLogonAccount -SqlServerInstance "pc-name\local" -AnalysisServerInstance "pc-name\local"


0
Comment
Author:Dorababu M
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
0 Comments

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Join & Write a Comment

Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month