dsccommunity / dsccommunity/SqlServerDsc

SqlSetup: When installing a Analysis Services in a cluster fails with 'Failed to connect to Analysis Services'

Open
#435 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

enhancement help wanted
Dominant language
PowerShell
Stars
385
Forks
224
PR merge metrics
No merged PRs in 30d

Description

Details of the scenario you try and problem that is occurring:
When installing SQL Server Analysis Services (SSAS) it fails in the Test-TargetResource.

PowerShell DSC resource MSFT_xSQLServerSetup  failed to execute Test-TargetResource functionality with error message: Failed to connect to Analysis Services TESTCLU01A\SQL2014 
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure

Trying to connect to the instance from SSMS you get this message.

image

This issue occurs because by default Analysis Services is configured to use both IPv4 and IPv6. If the cluster only listens to IPv4 you get this error.

image

To solve this, IPv6 has to be removed. In the file 'C:\Program Files (x86)\Microsoft SQL Server\90\Shared\ASConfig\msmdredir.ini' search for 'PortIPv6'.

	<Instances>
		<Instance>
			<Name>SQL2014</Name>
			<Port>2383</Port>
			<PortIPv6>51269</PortIPv6>
		</Instance>
	</Instances>

Remove the line containing 'PortIPv6' and save the file. No restart of Analysis Services is needed.

	<Instances>
		<Instance>
			<Name>SQL2014</Name>
			<Port>2383</Port>
		</Instance>
	</Instances>

Read more in article Error when you connect to a named instance of SQL Server 2012 Analysis Services by using IPv6: "No connection could be made because the target machine actively refused it".

The DSC configuration that is using the resource (as detailed as possible):

xSQLServerSetup $resourceConfigName
{
    Action = 'InstallFailoverCluster'
    ForceReboot = $false

    SourcePath = $Node."SourcePath$($currentSqlInstance)"
    UpdateEnabled = 'False'

    SetupCredential = $SqlInstallCredential
    SourceCredential = $SqlInstallCredential

    InstanceName = $Node."$($currentSqlInstance)InstanceName"
    Features = 'SQLENGINE,AS,SSMS,ADV_SSMS'

    InstallSharedDir = 'C:\Program Files\Microsoft SQL Server'
    InstallSharedWOWDir = 'C:\Program Files (x86)\Microsoft SQL Server'
    InstanceDir = 'C:\Program Files\Microsoft SQL Server'

    SQLCollation = 'Finnish_Swedish_CI_AS'
    SQLSvcAccount = $SqlServiceCredential
    AgtSvcAccount = $SqlAgentServiceCredential
    SQLSysAdminAccounts = 'COMPANY\SQL Administrators', $SqlAdministratorCredential.UserName

    InstallSQLDataDir = 'G:\MSSQL\Data'
    SQLUserDBDir = 'E:\MSSQL\Data'
    SQLUserDBLogDir = 'F:\MSSQL\Log'
    SQLTempDBDir = 'H:\MSSQL\Temp'
    SQLTempDBLogDir = 'H:\MSSQL\Temp'
    SQLBackupDir = 'I:\MSSQL\Backup'

    ASSvcAccount = $SqlServiceCredential
    ASSysAdminAccounts = 'COMPANY\SQL Administrators', $SqlAdministratorCredential.UserName

    ASConfigDir = 'G:\AS\Config'
    ASDataDir = 'E:\AS\Data'
    ASLogDir = 'F:\AS\Log'
    ASBackupDir = 'I:\AS\Backup'
    ASTempDir = 'H:\AS\Temp'

    FailoverClusterNetworkName = 'TESTCLU01A'
    FailoverClusterIPAddress = '192.168.10.46'
    FailoverClusterGroupName = 'TESTCLU01A'

    DependsOn = '[WindowsFeature]NetFramework35','[WindowsFeature]NetFramework45'
}

Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running:
Windows Server 2016, SQL Server 2014, WMF5.0

Version of the DSC module you're using, or 'dev' if you're using current dev branch:
Dev

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the MSFT_xSQLServerSetup Test-TargetResource path for InstallFailoverCluster and the Analysis Services connection reported for TESTCLU01A\SQL2014. Reproduce the failure with the Windows Server 2016, SQL Server 2014 configuration, then verify completion by confirming the resource can connect successfully when the cluster listens only on IPv4.

Written by the indexing model from the issue text.

Assessment

Tech stack
powershell, sql
Domain
databases, infrastructure
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.