<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

SSAS: Automating SQL Server Agent service logon as administrator

Published on
3,818 Points
818 Views
Last Modified:
Dorababu M
8  years of experience in IT. Working as senior software engineer in OTSI, Hyderabad.
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
0 Comments

Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Join & Write a Comment

This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month