Sql server installation is failing.

[venkateswaris]

Hi,

I am trying to install sql server using packer. It is failing saying that access denied. When i print sql error log, i am getting following message.

Exception type: Microsoft.SqlServer.Chainer.Infrastructure.ChainerInfrastructureException Message: There was an error generating the XML document. HResult : 0x84b10001 FacilityCode : 1201 (4b1) ErrorCode : 1 (0001) Data: DisableWatson = true Stack: at Microsoft.SqlServer.Chainer.Infrastructure.DataStoreService.SerializeObject(String rootPath, Object objectToSerialize, Boolean saveToCache) at Microsoft.SqlServer.Chainer.Infrastructure.DataStoreService.SerializeObject(Object objectToSerialize) at Microsoft.SqlServer.Chainer.Infrastructure.PublicConfigurationBridge.Calculate() at Microsoft.SqlServer.Chainer.Infrastructure.InputSettingService.CalculateSettings(IEnumerable1 settingIds)
at Microsoft.SqlServer.Chainer.Infrastructure.InputSettingService.CalculateAllSettings(Boolean chainerSettingOnly)
at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.<>c__DisplayClasse.b__b()
at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(ActionWorker workerDelegate)
Inner exception type: System.InvalidOperationException
Message:
There was an error generating the XML document.
HResult : 0x80131509
Stack:
at System.Xml.Serialization.XmlSerializer.Serialize(XmlWriter xmlWriter, Object o, XmlSerializerNamespaces namespaces, String encodingStyle, String
at System.Xml.Serialization.XmlSerializer.Serialize(TextWriter textWriter, Object o, XmlSerializerNamespaces namespaces)
at Microsoft.SqlServer.Chainer.Infrastructure.DataStoreService.SerializeObject(String rootPath, Object objectToSerialize, Boolean saveToCache)
Inner exception type: System.Security.Cryptography.CryptographicException
Message:
Access is denied.

        HResult : 0x80070005
        Stack:
                    at System.Security.Cryptography.ProtectedData.Protect(Byte[] userData, Byte[] optionalEntropy, DataProtectionScope scope)
                    at Microsoft.SqlServer.Common.SqlSecureString.WriteXml(XmlWriter writer)
                    at System.Xml.Serialization.XmlSerializationWriter.WriteSerializable(IXmlSerializable serializable, String name, String ns, Boolean isNullab
                    at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationWriterAgentConfigurationPublic.Write6_AgentConfigurationPublic(String n, St

olean isNullable, Boolean needType)
at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationWriterAgentConfigurationPublic.Write7_AgentConfigurationPublic(Object

o)`

• Packer version is 1.1.1
• Host platform is windows

I am not sure whether it is a issue with packer winrm connection or sql server.

[jborean93]

You are probably coming across a WinRM limitation, the SQL Server installer needs to access the DPAPI which under most WinRM situations is restricted. You should be able to get past this by;

• Using a scheduled task (probably avoid this as it is quite finnicky)
• Download PsExec and run it as the system account to bypass the WinRM restriction
• See if the WinRM communicator supports CredSSP auth which solves the DPAPI issue

I would doubt the 3rd option can be done as I only know of CredSSP being available with Python and the native. The 2nd is probably the best and most straight forward and can easily be tested.

[lfarnell]

@venkateswaris How are you installing Sql Server? Which provisioner are you using?

[venkateswaris]

@lfarnell I am using powershell provisioner and i am executing setup.exe in sql installer.

C:\temp\sqlserver\SETUP.exe /Action=Install /IACCEPTSQLSERVERLICENSETERMS

[lfarnell]

@venkateswaris I use Puppet to install Sql Server and I had to run it as an elevated user. Try that and see if it works.

[venkateswaris]

@lfarnell As per this issue #2511, we don't need elevated user when using aws ami.

[SwampDragons]

@venkateswaris Can you share a gist containing the simplest possible packer config and scripts necessary to reproduce the error?

[venkateswaris]

This is my packer script

{
  "variables": {
    "aws_access_key": "",
    "aws_secret_key": ""
  },
  "builders": [
    {
      "type": "amazon-ebs",
      "access_key": "{{user `aws_access_key`}}",
      "secret_key": "{{user `aws_secret_key`}}",
      "region": "eu-west-1",
      "source_ami_filter": {
        "filters": {
          "virtualization-type": "hvm",
          "name": "6.1-SNAPSHOT-windows-x86_64-ebs-hvm-*",
          "root-device-type": "ebs",
          "architecture": "x86_64"
        },
        "owners": [
          "200193376361"
        ],
        "most_recent": true
      },
      "instance_type": "m3.large",
      "launch_block_device_mappings": [
        {
          "device_name": "/dev/sda1",
          "volume_size": 50,
          "volume_type": "gp2",
          "delete_on_termination": true
        }
      ],
      "ami_name": "win2012r2-dotnet4.x-sql-{{isotime \"2006-01-02-150403\"}}",
      "ami_description": "dotnet4.x-sql",
      "vpc_id": "vpc-id",
      "subnet_id": "subnet-id",
      "security_group_ids": "sg-id",
      "encrypt_boot": "true",
      "communicator": "winrm",
      "winrm_username": "Administrator",
      "associate_public_ip_address": "true",
      "iam_instance_profile": "ami-instance-role",
    }
  ],
  "provisioners": [
    {
      "type": "powershell",
      "scripts": [
        "..\\installers\\sql-express-16.ps1"
      ]
    }
  ]
}

in side sql-express-16.ps1

Start-Process ./sqlserverexpress.exe -Wait -ArgumentList '/u', '/x:"C:\temp\sqlserver"'
& C:\temp\sqlserver\SETUP.exe /Action=Install /IACCEPTSQLSERVERLICENSETERMS /ENU /UpdateEnabled=false /FEATURES=SQLEngine /SQLCOLLATION="Latin1_General_CI_AS" /INSTANCENAME=SQLEXPRESS /Q /SAPWD="somepasss" /SECURITYMODE=SQL /SQLSVCSTARTUPTYPE=Automatic /SQLSYSADMINACCOUNTS="$env:USERDOMAIN\$env:USERNAME"

[jborean93]

@venkateswaris the issue you referenced #2511 is about UAC being turned off on AMI instances and is unrelated to your issue. Your issue is around the restrictions when running a command over WinRM and because the SQL Server installer interacts with DPAPI it fails as this is not allowed under most circumstances in WinRM. You will have to run the command under a scheduled task or through another tools like PsExec. It seems like elevated_user/elevated_password runs it as a scheduled task and does all the legwork for you so you can try that.

[venkateswaris]

@jborean93
Yes.. executing with PSExec is working fine.