Function "Add-SQLAlias" does not add a 32bit alias

Topics: Feature Requests, Feedback
Jul 8, 2015 at 6:13 PM
I realized today that the SQL Client Alias I have configured using AutoSPInstaller only adds a 64bit alias and not a 32bit alias. This is not a problem for straight SharePoint 2013 but it became a problem when I tried to configure Workflow Manager 1.0 on the server. It was not able to connect with the connection string I supplied because there is not 32bit alias matching the name I have in the string. WorkflowManager will not be the only thing that would need the alias, so I think having it included is pretty much a must-have.

My solution going forward for AutoSPInstaller is that I have modified the function and saved it to my "AutoSPInstallerFunctionsCustom.ps1" file. I've also pasted the modified function below if you're interested.
Function Add-SQLAlias()
{
    <#
    .Synopsis
        Add a new SQL server Alias
    .Description
        Adds a new SQL server Alias with the provided parameters.
        This custom version also adds the 32bit alias for the same.
    .Example
                Add-SQLAlias -AliasName "SharePointDB" -SQLInstance $env:COMPUTERNAME
    .Example
                Add-SQLAlias -AliasName "SharePointDB" -SQLInstance $env:COMPUTERNAME -Port '1433'
    .Parameter AliasName
        The new alias Name.
    .Parameter SQLInstance
                The SQL server Name os Instance Name
    .Parameter Port
        Port number of SQL server instance. This is an optional parameter.
    #>
    [CmdletBinding(DefaultParameterSetName="BuildPath+SetupInfo")]
    param
    (
        [Parameter(Mandatory=$false, ParameterSetName="BuildPath+SetupInfo")][ValidateNotNullOrEmpty()]
        [String]$aliasName = "SharePointDB",

        [Parameter(Mandatory=$false, ParameterSetName="BuildPath+SetupInfo")][ValidateNotNullOrEmpty()]
        [String]$SQLInstance = $env:COMPUTERNAME,

        [Parameter(Mandatory=$false, ParameterSetName="BuildPath+SetupInfo")][ValidateNotNullOrEmpty()]
        [String]$port = ""
    )

    If ((MatchComputerName $SQLInstance $env:COMPUTERNAME) -or ($SQLInstance.StartsWith($env:ComputerName +"\"))) {
        $protocol = "dbmslpcn" # Shared Memory
    }
    else {
        $protocol = "DBMSSOCN" # TCP/IP
    }

    $serverAliasConnection="$protocol,$SQLInstance"
    If ($port -ne "")
    {
         $serverAliasConnection += ",$port"
    }
    $notExist = $true
    $client = Get-Item 'HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client' -ErrorAction SilentlyContinue
    # Create the key in case it doesn't yet exist
    If (!$client) {$client = New-Item 'HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client' -Force}
    $client.GetSubKeyNames() | ForEach-Object -Process { If ( $_ -eq 'ConnectTo') { $notExist=$false }}
    If ($notExist)
    {
        $data = New-Item 'HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo'
    }
    # Add the 64bit Alias
    $data = New-ItemProperty HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo -Name $aliasName -Value $serverAliasConnection -PropertyType "String" -Force -ErrorAction SilentlyContinue
    #
    #
    ###  Below is the part that is new. It's basically the same as the lines above added in but this time it will include the 32bit SQL Client Alias
    $notExist = $true
    $client = Get-Item 'HKLM:\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo' -ErrorAction SilentlyContinue
    # Create the key in case it doesn't yet exist
    If (!$client) {$client = New-Item 'HKLM:\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client' -Force}
    $client.GetSubKeyNames() | ForEach-Object -Process { If ( $_ -eq 'ConnectTo') { $notExist=$false }}
    If ($notExist)
    {
        $data = New-Item 'HKLM:\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo'
    }
    # Add Alias
    $data = New-ItemProperty HKLM:\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo -Name $aliasName -Value $serverAliasConnection -PropertyType "String" -Force -ErrorAction SilentlyContinue
}
Coordinator
Jul 16, 2015 at 2:16 PM
Right, so although I've never run into this issue myself (even when installing Workflow Manager 1.0), it couldn't hurt so will include it as a future enhancement.

Cheers
Brian
Coordinator
Jul 16, 2015 at 2:17 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.