SQL Server can't auth user when installed witch chocolatey (via chef)

Question

I am attempting to automate the installation of SQL Server 2016 Express.

I have a chef cookbook to install SQL Server Express using the chocolatey package.

The same command as a chef resource

chocolatey_package 'sql-server-express' do
  action :install
  options '--cachelocation c:\temp\choco'
end

Equivalent PowerShell command

choco install sql-server-express --cachelocation c:\temp\choco

If I install SQL Server Express normally with the install wizard, I can authenticate and create / modify databases no problem.

If I install SQL Server Express with chocolatey / chef, I am not able to create or modify databases.

The error when trying to create a new database

CREATE DATABASE permission denied in database 'master'

The error when trying to modify an existing database

The server principal "Foo\Bar" is not able to access the database "foobar" under the current security context

I've tried logging in as the 'sa' user. mixed authentication is not enabled, and I can't enable it.

How can I get chocolatey installations of SQL Server Express users the same as a normal installation?

Answer 1

After many days I discovered the problem. Run chocolatey as a local administrative user, not SYSTEM.

Why:

The chocolatey package will not install if the command is executed over WinRM due to limitations of the WinRM protocol.

The common work around is to run chef/chocolatey in a scheduled task.

Unfortunately, scheduled tasks run as the SYSTEM user. The result is that MSSQL will only allow the SYSTEM user to run administrative commands.

The solution is to run chocolatey inside a scheduled task as a local system account (not SYSTEM). Or use RDP to log into the system and run the chocolatey command directly (not over winrm).

An example of how to run chef test kitchen as a (non SYSTEM) elevated user

transport:
  name: winrm
  elevated: true
  elevated_username: vagrant
  elevated_password: vagrant