SQL Server Image Hardening by Packer

shafiq...@gmail.com

unread,

Mar 22, 2019, 10:57:27 AM3/22/19

to Packer

I have successfully created a SQL Image of SQL Server 2016 on Azure Marketplace using Packer.

To take this one step further,

I manually installed Java, then installed CIS-CAT, remediated according and hardened the image.

My goal is to use Packer to do all of this for me.

I'm using winrm_username and winrm_password variables to try to authenticate to the SQL Server post installation and it fails.

The steps in the process:

1. Create a SQL Server Image from the Marketplace.

2. Install Java from internal Artifactory.

3. Install CIS-CAT too from internal Artifactory.

4. Create a Test DB ( Fails, because I am unable to login to the SQL Server via Windows Authentication by passing winrm creds)

5. Run LGPO.exe (I copied the GPO settings that I acquired by manually capturing it in a previous VM)

6. Once the policies have been applied, run the CIS-CAT benchmarks to capture a report. (Problem with the Connection String, can't generate it using ENV_Variables passed through the template)

7. Copy the report to a local machine

8. Sysprep and capture the image.

If anyone has worked with SQL Server Images using Packer, do give me some feedback to correct my approach.

Open to any kind of suggestions as well.

cody.r...@gmail.com

unread,

Mar 22, 2019, 2:05:05 PM3/22/19

to Packer

What do you have so far?

shafiq...@gmail.com

unread,

Mar 22, 2019, 2:23:59 PM3/22/19

to Packer

"builders": [ 
        {
            "type": "azure-arm",
            
            "client_id": "{{user `client_id`}}",
            "client_secret": "{{user `client_secret`}}",
            "tenant_id": "{{user `tenant_id`}}",
            "subscription_id": "{{user `subscription_id`}}",

            "managed_image_resource_group_name": "GoldenImages",
            "managed_image_name": "Golden-SQL2016SP2",

            "os_type": "Windows",
            "image_publisher": "MicrosoftSQLServer",
            "image_offer": "SQL2016SP2-WS2016",
            "image_sku": "Enterprise",

            "virtual_network_name": "{{user `virtual_network_name`}}",
            "virtual_network_subnet_name": "{{user `virtual_network_subnet_name`}}",
            "virtual_network_resource_group_name": "{{user `virtual_network_resource_group_name`}}",
                        
            "communicator": "winrm",
            "winrm_use_ssl": true,
            "winrm_insecure": true,
            "winrm_timeout": "6m",
            "winrm_username": "{{user `winrm_username`}}",
            
            "azure_tags": {
                    "BAC"             : "",
                    "ResourceContact" : "",
                    "Environment"     : "{{user `environment`}}"    
            },

            "location": "{{user `location`}}",
            "vm_size": "Standard_DS3_v2"
        }
    ],
    
    "provisioners": [
        {
            "type": "powershell",
            "environment_vars": 
            [
                "ARTIFACTORYCREDS={{user `artifactory_creds`}}"
            ],
            "scripts": [
                "./scripts/sql2016/install_java.ps1"
            ]
        },
        {
            "type": "powershell",
            "environment_vars": 
            [
                "ARTIFACTORYCREDS={{user `artifactory_creds`}}"
            ],
            "scripts": 
            [
                "./scripts/sql2016/install_cis_cat.ps1"
            ]
        },
        {
            "type": "powershell",
            "scripts": 
            [
              "./scripts/sql2016/install_pscp.ps1"
            ]
        },
        {
            "type": "file",
            "source": "./scripts/sql2016",
            "destination": "c:\\Users\\packer\\",
            "direction" : "upload"
        },
        {
            "type": "windows-restart",
            "restart_check_command": "powershell -command \"& {Write-Output 'restarted.'}\""
        },
        {
            "type": "powershell",
            "environment_vars": [
              "WINRMUSER= {{user `winrm_username`}}"
            ],
            "scripts": [
             "./scripts/sql2016/harden_image.ps1"
             ]
        }
    ]
}

This is what it looks at the moment.
I need to be able to add at least one more powershell script in Provisioners that will create a test database in the VM that packer creates, as well as get the connection string that I can pass to CIS-CAT for the scan.

When run locally this is how the cis-cat command looks like, ".\CIS-CAT.BAT -b benchmarks\CIS_Microsoft_SQL_Server_2016_Benchmark_v1.0.0-xccdf.xml -p ""Level 1 - Database Engine"" -D xccdf_org.cisecurity_value_jdbc.url=jdbc:jtds:sqlserver://localhost:1433/master;domain=SQLSERVER;user=testadmin;password=password -a -r ."

How can I pass the username and password when using winrm and packer?

I'm confused as to how I can achieve this using Packer. Just need some direction.

cody.r...@gmail.com

unread,

Mar 22, 2019, 3:13:58 PM3/22/19

to Packer

This username and password?

"user=testadmin;password=password"

I would think you would have to pass the variables in and build the string for the command before launching cis-cat.bat

Rajesh Kumar Nagapuri

unread,

Oct 21, 2019, 7:11:14 AM10/21/19

to Packer

Read this post: i hope it will fix your issue https://www.phillipsj.net/posts/packer-azure-and-sql-server/

Rajesh Kumar Nagapuri

unread,

Nov 20, 2019, 10:21:24 AM11/20/19

to Packer

Hi Shafi

can you tell me how you are creating mount points with different partitions for SQL

On Friday, March 22, 2019 at 8:27:27 PM UTC+5:30, shafiq...@gmail.com wrote:

Reply all

Reply to author

Forward