SqlSetup: Analysis Service Will Not Start - Access Denied

[aleksbor]

Details of the scenario you tried and the problem that is occurring

Deploying SQL using DSC push and MSA and analysis services will not start. The MSA account does get added to the correct OLAP local group; however, that group is only added to P:\data\MSOLAP ACL with full access, and apparently, for AS to start, it needs to be in P:\data. Adding to the P:\data ACL manually lets AS start with no issues.
P:\data is a mounted volume, btw.
Additionally, tried deploying with built-in accounts (NT SERVICE\MSSQLSERVEROLAPSERVICE) and ran into the same problem.

Verbose logs showing the problem

VERBOSE: [TESTSERVER01]: [[SqlSetup]InstallDefaultInstance::[OPSQLBase]SQLBase] Connected to SQL instance 'TESTSERVER01'. (SQLCOMMON0018)
VERBOSE: [TESTSERVER01]: [[SqlSetup]InstallDefaultInstance::[OPSQLBase]SQLBase] Clustered instance not detected.
VERBOSE: [TESTSERVER01]: [[SqlSetup]InstallDefaultInstance::[OPSQLBase]SQLBase] Detecting Full-text feature.
VERBOSE: [TESTSERVER01]: [[SqlSetup]InstallDefaultInstance::[OPSQLBase]SQLBase] Full-text feature not detected.
VERBOSE: [TESTSERVER01]: [[SqlSetup]InstallDefaultInstance::[OPSQLBase]SQLBase] Detecting Reporting Services feature.
VERBOSE: [TESTSERVER01]: [[SqlSetup]InstallDefaultInstance::[OPSQLBase]SQLBase] Reporting Services feature not detected.
VERBOSE: [TESTSERVER01]: [[SqlSetup]InstallDefaultInstance::[OPSQLBase]SQLBase] Detecting Analysis Services feature.
VERBOSE: [TESTSERVER01]: [[SqlSetup]InstallDefaultInstance::[OPSQLBase]SQLBase] Analysis Services feature detected.
VERBOSE: [TESTSERVER01]: [[SqlSetup]InstallDefaultInstance::[OPSQLBase]SQLBase] Perform operation 'Query CimInstances' with following
parameters, ''queryExpression' = SELECT * FROM Win32_Service WHERE Name = 'MSSQLServerOLAPService','queryDialect' = WQL,'namespaceName' = root\cimv2'.
VERBOSE: [TESTSERVER01]: [[SqlSetup]InstallDefaultInstance::[OPSQLBase]SQLBase] Operation 'Query CimInstances' complete.
VERBOSE: [TESTSERVER01]: LCM: [ End Test ] [[SqlSetup]InstallDefaultInstance::[OPSQLBase]SQLBase] in 4.6250 seconds.
PowerShell DSC resource MSFT_SqlSetup failed to execute Test-TargetResource functionality with error message: System.InvalidOperationException: Failed to connected to
Analysis Services instance 'TESTSERVER01'. (SQLCOMMON0021) ---> System.Management.Automation.MethodInvocationException: Exception calling "Connect" with "1"
argument(s): "A connection cannot be made. Ensure that the server is running." ---> Microsoft.AnalysisServices.ConnectionException: A connection cannot be made. Ensure
that the server is running. ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it 127.0.0.1:2383
at System.Net.Sockets.TcpClient..ctor(String hostname, Int32 port)
at Microsoft.AnalysisServices.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo)
--- End of inner exception stack trace ---
at Microsoft.AnalysisServices.XmlaClient.OpenConnection(ConnectionInfo connectionInfo, Boolean& isSessionTokenNeeded)
at Microsoft.AnalysisServices.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
at Microsoft.AnalysisServices.Core.Server.Connect(String connectionString, String sessionId, ObjectExpansion expansionType)
at Microsoft.AnalysisServices.Core.Server.Connect(String connectionString)
at CallSite.Target(Closure , CallSite , Object , Object )
--- End of inner exception stack trace ---
at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
--- End of inner exception stack trace ---
+ CategoryInfo : InvalidOperation: (:) [], CimException
+ FullyQualifiedErrorId : ProviderOperationExecutionFailure
+ PSComputerName : TESTSERVER01

Suggested solution to the issue

n/a

The DSC configuration that is used to reproduce the issue (as detailed as possible)

                SqlSetup 'InstallDefaultInstance'
                {

                    InstanceName         = 'MSSQLSERVER' 
                   
                    Features             = $Node.sqlfeatures 
                    SQLCollation         = $Node.collation
                    SQLSvcAccount        = $Node.SqlServiceCredential 
                    #SQLSvcStartupType    = $Node.SqlSvcStartupType
                    AgtSvcAccount        = $Node.SqlAgentServiceCredential 
                    RSSvcAccount         = $Node.SqlReportingServiceCredential 
                    ISSvcAccount         = $Node.SqlIntegrationServiceCredential 
                    ASSvcAccount         = $Node.SqlAnalysisServiceCredential 
                    FTSvcAccount         = $Node.SqlFTSearchServiceCredential 
                    SQLSysAdminAccounts  = 'DOMAIN\Group'
                    ASSysAdminAccounts   = 'DOMAIN\Group'
                    InstallSharedDir     = 'C:\Program Files\Microsoft SQL Server'
                    InstallSharedWOWDir  = 'C:\Program Files (x86)\Microsoft SQL Server'
                    InstanceDir          = 'C:\Program Files\Microsoft SQL Server'
                    InstallSQLDataDir    = $Node.DataVolume
                    SQLUserDBDir         = $Node.DataVolume + 'MSSQL'
                    SQLUserDBLogDir      = $Node.LogVolume + 'MSSQL'
                    SQLTempDBDir         = $Node.TempdbVolume + 'MSSQL'
                    SQLTempDBLogDir      = $Node.TempdbVolume + 'MSSQL'
                    SQLBackupDir         = $Node.DataVolume + 'Backup'
                    ASServerMode         = 'TABULAR'
                    ASConfigDir          = $Node.DataVolume + 'MSOLAP\Config'
                    ASDataDir            = $Node.DataVolume + 'MSOLAP'
                    ASLogDir             = $Node.LogVolume + 'MSOLAP'
                    ASBackupDir          = $Node.DataVolume + 'MSOLAP\Backup'
                    ASTempDir            = $Node.TempdbVolume + 'MSOLAP'
                    SourcePath           = Join-Path $Node.DestTempPath -childpath $Node.SQLVersion
                    UpdateEnabled        = 'True'    
                    UpdateSource         = Join-Path $Node.DestTempPath -childpath $Node.UpdatesFolder
                    ForceReboot          = $true
                    PsDscRunAsCredential = $Node.SqlInstallCredential

                }

SQL Server edition and version the target node is running

Microsoft SQL Server 2017 (RTM-CU14) (KB4484710) - 14.0.3076.1 (X64) Mar 12 2019 19:29:19 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)

SQL Server PowerShell modules present on the target node

Name Version Path

---

SQLPS 14.0 C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

The operating system the target node is running

OsName : Microsoft Windows Server 2016 Standard
OsOperatingSystemSKU : StandardServerEdition
OsArchitecture : 64-bit
WindowsBuildLabEx : 14393.3204.amd64fre.rs1_release.190830-1500
OsLanguage : en-US
OsMuiLanguages : {en-US}

Version and build of PowerShell the target node is running

Name Value

---

PSVersion 5.1.14393.3053
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.14393.3053
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

Version of the DSC module that was used ('dev' if using current dev branch)

Name Version Path

---

SqlServerDsc 13.2.0.0 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\13.2.0.0\SqlServerDsc.psd1

[aleksbor]

EventID 0 is logged:
The service cannot be started: The following error occurred during a file operation: Access is denied. . (\?\P:\data\MSOLAP).

[johlju]

Interesting. The resource does not set any permission. 🤔 This must be an issue with the SQL Server setup. If you can replicate this when running sqlsetup.exe from command line (same command as outputted in the verbose log) then I suggest you report this as a support case to Microsoft, or add it as a uservoice https://feedback.azure.com/forums/908035-sql-server

[johlju]

As a workaround we should have a resource (in another module) that can create the folder structure and set the correct permission. 🤔 For now we can document this.

[aleksbor]

It does show the same behavior when executed from the command line and it is not limited to MSA. I'll check with MS rep on that.
What kind of documentation help are you looking for?

[johlju]

Great that you report this, hopefully it can be resolved in a future update.

In regards to documentation, I think just mention it under the requirements section that the folder structure(s) used must be created with the correct permissions prior to installing the Analysis Services, and reference this issue.