安装了Chocolate Chocolatey（通过Chef）时，SQL Server无法对用户进行身份验证 [英] SQL Server can't auth user when installed witch chocolatey (via chef)

问题描述

我正在尝试自动安装SQL Server 2016 Express。

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

我有一本厨师食谱，可使用Chocolatey软件包安装SQL Server Express。

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

与厨师资源

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

等效PowerShell命令

Equivalent PowerShell command

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

如果使用安装向导正常安装SQL Server Express，则可以进行身份​​验证和创建/修改数据库。

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

如果我以Chocolatey / Chef身份安装SQL Server Express，则无法创建或修改数据库。

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

创建数据库权限被拒绝在数据库'master'

CREATE DATABASE permission denied in database 'master'

尝试修改现有数据库时的错误

The error when trying to modify an existing database

服务器主体 Foo\Bar无法访问当前安全上下文下的数据库 foobar

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

我尝试以 sa用户身份登录。混合身份验证未启用，我无法启用。

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

如何使SQL Server Express用户的巧克力安装与普通安装相同？

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

推荐答案

很多天后我发现了问题所在。 以本地管理员用户的身份运行Chocolatey，而不是 SYSTEM 。

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

如果由于WinRM协议的限制在WinRM上执行命令，则不会安装Chocolatey软件包。

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.

不幸的是，计划任务运行作为 SYSTEM 用户。结果是MSSQL将只允许SYSTEM用户运行管理命令。

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

解决方案是在计划任务中以本地系统帐户（而非SYSTEM）的身份运行Chocolatey。或者使用RDP登录到系统并直接运行Chocolatey命令（而不是通过Winrm）。

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

这篇关于安装了Chocolate Chocolatey（通过Chef）时，SQL Server无法对用户进行身份验证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！