Quoting of CCM DB SQL User Password Fails If Complex

[ryanrichter94]

What You Are Seeing?

If during Step 3: CCM Setup of the guide you enter a password containing ', ;, or " characters. the password is not being properly quoted in the connection string when installing the CCM service package. Causing the package install to fail.

What is Expected?

This should be accounted for within the scripting.

How Did You Get This To Happen? (Steps to Reproduce)

1. Run through the QSG stopping at step 3: CCM Setup
2. Run the Step 3: CCM Setup part of the guide
3. Provide the following password when prompted by the step C4B;123'"mn0pX
4. You should see the script fail when adding the ChocoUser to the ChocolateyManagement DB

System Details

• OS Build: 10.0.20348.0
• Windows PowerShell version: 5.1.20348.558
• Chocolatey version: 1.1.0

Output Log

Relevant part from Start-C4bCcmSetup Transcript Log Ouput

Adding ChocoUser to ChocolateyManagement with the following permissions: db_datareader, db_datawriter
PS>TerminatingError(Add-DatabaseUserAndRoles): "Exception calling "ExecuteNonQuery" with "0" argument(s): "The identifier that starts with 'mn0pX', DEFAULT_DATABASE=[ChocolateyManagement], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

USE [ChocolateyManagement]
IF EXISTS(SE' is too long. Maximum length is 128.
Unclosed quotation mark after the character string 'mn0pX', DEFAULT_DATABASE=[ChocolateyManagement], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

USE [ChocolateyManagement]
IF EXISTS(SEL'.""
>> TerminatingError(Add-DatabaseUserAndRoles): "Exception calling "ExecuteNonQuery" with "0" argument(s): "The identifier that starts with 'mn0pX', DEFAULT_DATABASE=[ChocolateyManagement], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

USE [ChocolateyManagement]
IF EXISTS(SE' is too long. Maximum length is 128.
Unclosed quotation mark after the character string 'mn0pX', DEFAULT_DATABASE=[ChocolateyManagement], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

USE [ChocolateyManagement]
IF EXISTS(SEL'.""
>> TerminatingError(Add-DatabaseUserAndRoles): "Exception calling "ExecuteNonQuery" with "0" argument(s): "The identifier that starts with 'mn0pX', DEFAULT_DATABASE=[ChocolateyManagement], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

USE [ChocolateyManagement]
IF EXISTS(SE' is too long. Maximum length is 128.
Unclosed quotation mark after the character string 'mn0pX', DEFAULT_DATABASE=[ChocolateyManagement], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

USE [ChocolateyManagement]
IF EXISTS(SEL'.""
Exception calling "ExecuteNonQuery" with "0" argument(s): "The identifier that starts with 'mn0pX', DEFAULT_DATABASE=[ChocolateyManagement], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

USE [ChocolateyManagement]
IF EXISTS(SE' is too long. Maximum length is 128.
Unclosed quotation mark after the character string 'mn0pX', DEFAULT_DATABASE=[ChocolateyManagement], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

USE [ChocolateyManagement]
IF EXISTS(SEL'."
Add-DatabaseUserAndRoles : Exception calling "ExecuteNonQuery" with "0" argument(s): "The identifier that starts with
'mn0pX', DEFAULT_DATABASE=[ChocolateyManagement], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

USE [ChocolateyManagement]
IF EXISTS(SE' is too long. Maximum length is 128.
Unclosed quotation mark after the character string 'mn0pX', DEFAULT_DATABASE=[ChocolateyManagement],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

USE [ChocolateyManagement]
IF EXISTS(SEL'."
At C:\choco-setup\files\Start-C4bCcmSetup.ps1:122 char:5
+     Add-DatabaseUserAndRoles -DatabaseName 'ChocolateyManagement' -Us ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Add-DatabaseUserAndRoles], MethodInvocationException
    + FullyQualifiedErrorId : SqlException,Add-DatabaseUserAndRoles

References

• ZenDesk Ticket