invoke-static_安全地使用Invoke-SqlCmd

invoke-static

We have a convenient tool for working with PowerShell and SQL Server when using Invoke-SqlCmd. As we saw when running statements, we can run DDL and DML changes with the command without writing our own custom scripts. This carries advantages when we need to quickly develop with PowerShell, but it can come with drawbacks on security if we’re not careful how we use this function. We’ll look at security when using this function by starting with a few examples of what we can do when we have unlimited access along with how we can design to limit our environment to be strict with our use of this tool.

使用Invoke-SqlCmd时，我们有一个使用PowerShell和SQL Server的便捷工具。 正如我们在运行语句时看到的那样，我们可以使用命令运行DDL和DML更改，而无需编写我们自己的自定义脚本。 当我们需要快速使用PowerShell进行开发时，这具有优势，但是如果我们不注意如何使用此功能，则可能带来安全性方面的缺陷。 我们将以一些使用无限制访问权限时可以做什么的示例为例，来研究使用此功能时的安全性，以及如何设计以严格限制使用此工具的环境。

无限访问的示范 (Demonstrations of Unlimited Access)

If we use an account with sufficient access, we can run any command against SQL Server with the Invoke-SqlCmd. For an example, in the below code I run the one-line script to check the database integrity which is allowed with the Windows account running the script (sysadmin or db_owner required).

如果我们使用具有足够访问权限的帐户，则可以使用Invoke-SqlCmd针对SQL Server运行任何命令。 例如，在下面的代码中，我运行单行脚本来检查数据库完整性，而运行该脚本的Windows帐户允许使用此数据库完整性（需要sysadmin或db_owner）。

$SQLServer = "TestServerOne"
$db3 = "TestDB3"
 
Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query "DBCC CHECKDB" -Verbose 

In addition, in the below code I drop a database using Invoke-SqlCmd with enough permission to remove the database – do not run this script as a test unless you run it against a database that you are willing to drop. This demonstrates that if our user running this script has enough permissions, it will be able to drop a database.

另外，在下面的代码中，我使用具有足够权限删除数据库的Invoke-SqlCmd删除了数据库-除非对愿意删除的数据库运行脚本，否则不要将其作为测试来运行。 这表明如果我们运行此脚本的用户具有足够的权限，它将能够删除数据库。

$SQLServer = "TestServerOne"
$master = "master"
 
Invoke-Sqlcmd -ServerInstance $SQLServer -Database $master -Query "DROP DATABASE DropDBTest"
Invoke-Sqlcmd -ServerInstance $SQLServer -Database $master -Query "SELECT [name] FROM sys.databases WHERE [name] = 'DropDBTest'" 

In all cases (except demonstrations), we want to restrict permissions and we can start on the user access level, whether we use SQL Server authentication or Windows Authentication. We should apply this standard to any user account running scripts – what objects should the account be able to access and what level of access is needed? This also applies to file locations where we want to read and save files, if we’re using the common parameters of –InputFile or Out-File to read or write to files. Unrestricted access to any file location could result in the script reading or writing information it shouldn’t.

在所有情况下（演示除外），无论我们使用SQL Server身份验证还是Windows身份验证，我们都希望限制权限，并且可以从用户访问级别开始。 我们应该将此标准应用于任何运行脚本的用户帐户-该帐户应能够访问哪些对象以及需要什么级别的访问权限？ 如果我们使用– InputFile或Out-File的通用参数来读取或写入文件，这也适用于我们要读取和保存文件的文件位置。 对任何文件位置的无限制访问可能导致脚本读取或写入不应这样做的信息。

通过角色的权限限制 (Permission Limitations Through Roles)

Because it’s possible that we may have multiple users or accounts that use Invoke-SqlCmd, we’ll create a role called InvokeAccess in our database and assign permissions on the object level. If we wanted to run administrative tasks, we could assign the appropriate server level permissions. In this example, we’ll only use database-level permissions and grant access to execute CRUD operations through procedures. As we saw in part one and in the above integrity check, we can use this function for DDL and server level operations, but I recommend being strict about PowerShell use, especially if we’re running cross-server scripts with Invoke-SqlCmd. If we are going to allow the latter, we want to be aware of this in our design and be ready to audit it appropriately.

因为可能有多个用户或帐户使用Invoke-SqlCmd，所以我们将在数据库中创建一个名为InvokeAccess的角色并在对象级别分配权限。 如果我们要运行管理任务，则可以分配适当的服务器级别权限。 在此示例中，我们将仅使用数据库级权限，并授予访问权以通过过程执行CRUD操作。 正如我们在上面的完整性检查的第一部分中所看到的，我们可以将此功能用于DDL和服务器级操作，但是我建议严格使用PowerShell，尤其是当我们使用Invoke-SqlCmd运行跨服务器脚本时。 如果我们允许后者，则我们希望在设计中意识到这一点，并准备对其进行适当的审核。

In the below code, we see the T-SQL design of creating a role, testing our script of assigning 2 stored procedures to execute, and seeing this reflected in the permissions. Depending on the account that’s running our PowerShell script, we would assign this user to this role.

在下面的代码中，我们将看到T-SQL设计，用于创建角色，测试分配2个要执行的存储过程的脚本，并将其反映在权限中。 根据运行PowerShell脚本的帐户，我们可以将此用户分配给该角色。

CREATE ROLE [InvokeAccess]
 
SELECT TOP 2 [name]
  , 'GRANT EXECUTE ON ' + QUOTENAME([name]) + '  TO [InvokeAccess]' RunTestScript
FROM sys.procedures
 
SELECT 
  t.[permission_name] Permission
  , t2.[type_desc] ObjectType
  , t3.[name] RoleName
  , OBJECT_NAME(t.[major_id]) ObjectPermitted
FROM sys.database_permissions t 
  INNER JOIN sys.objects t2 ON t.[major_id] = t2.[object_id] 
  INNER JOIN sysusers t3 ON t3.[uid] = t.[grantee_principal_id]
WHERE t.state_desc = 'GRANT'
  AND t3.[name] = 'InvokeAccess'

As an alternative, we could assign objects directly on the user account. If we have one account that accesses the database, assigning directly to the user is acceptable. In addition, we may re-use the same user for all PowerShell activity (I don’t recommend this, but it may be appropriate in rare contexts). Roles can be helpful if we have multiple users that will need this access and I find this is more common with Invoke-SqlCmd. In addition, if we need to have some accounts execute administrative tasks, we can add another role, such as InvokeAccessAd, which has those permissions.

或者，我们可以直接在用户帐户上分配对象。 如果我们有一个访问数据库的帐户，则直接分配给用户是可以接受的。 此外，我们可能会在所有PowerShell活动中重复使用同一用户（我不建议您这样做，但是在少数情况下可能是合适的）。 如果我们有多个用户需要此访问权限，并且我发现Invoke-SqlCmd更常见，则角色可能会有所帮助。 另外，如果我们需要一些帐户来执行管理任务，则可以添加具有这些权限的另一个角色，例如InvokeAccessAd。

While it may add an extra step, this restricts activity for users assigned to this role and may be an appropriate route if we have multiple users that use this functionality.

尽管它可能会增加一个额外的步骤，但是这限制了分配给该角色的用户的活动，并且如果我们有多个使用此功能的用户，这可能是一条适当的路线。

Regardless of how we provide access – through roles, direct access or through a combination with objects (like procedures) – we want to start with the auditing when we design the access. Using our above example, we’ll notice that we can audit the objects that our role has access to using the above query. Likewise, we could also do loop through all of our objects in source control and find the references to the role. Assigning permissions is only part of the challenge with security when we use a function like Invoke-SqlCmd; we also need to regularly audit that these permissions match what we expect to see.

无论我们如何通过角色，直接访问或通过与对象（例如过程）的组合来提供访问权限，我们都希望在设计访问权限时从审核开始。 使用上面的示例，我们将注意到我们可以使用上面的查询来审核角色可以访问的对象。 同样，我们也可以在源代码管理中遍历所有对象，并找到对该角色的引用。 当我们使用Invoke-SqlCmd之类的功能时，分配权限只是安全性挑战的一部分。 我们还需要定期审核这些权限是否符合我们的期望。

设计多服务器访问 (Designing for Multi-Server Access)

Using Invoke-SqlCmd on one server to run commands on other SQL Servers is a common practice. This can also apply to other PowerShell scripts that we may run and we want to consider how our servers communicate with each other. In the below image, we see this type of set up where we have a server that can run PowerShell scripts against SQL Servers and this can be appropriate in the right context and with strong security, but it also gives attackers a possible weakness to exploit.

在一个服务器上使用Invoke-SqlCmd在其他SQL Server上运行命令是一种常见的做法。 这也可以应用于我们可能运行的其他PowerShell脚本，并且我们要考虑服务器之间的通信方式。 在下图中，我们看到了这种类型的设置，其中我们的服务器可以针对SQL Server运行PowerShell脚本，并且可以在正确的上下文中使用并且具有强大的安全性，但是它也使攻击者可能会被利用。

Just like we want to allow the minimum permissions to databases and objects within those databases even with using Invoke-SqlCmd, we want to make sure that communication between the servers is restricted to where appropriate. If our PowerShell scripts is required read data from a table through a stored procedure, we want to ensure that it has no further permission and no further access – the server running the script should have access to the specific port of the SQL Server and no authorization for further access along with no further access to run other commands.

就像我们想允许对数据库和这些数据库中的对象的最低权限，即使使用Invoke-SqlCmd一样，我们也要确保将服务器之间的通信限制在适当的位置。 如果需要我们的PowerShell脚本通过存储过程从表中读取数据，我们要确保它没有进一步的权限并且没有进一步的访问–运行脚本的服务器应该有权访问SQL Server的特定端口并且没有授权用于进一步访问，而没有其他访问权限来运行其他命令。

In addition, if we design this with scheduled intent, we want to ensure that we have an audit in place that confirms what we expect to see – if Invoke-SqlCmd runs daily for reports on each of our SQL Servers, we should not see multiple times of access if no retries were attempted (or required).

此外，如果我们按计划的目的进行设计，我们希望确保已进行审核，以确认我们期望看到的内容–如果Invoke-SqlCmd每天为每个SQL Server上的报告运行，则不应看到多个如果没有尝试重试（或要求重试）的次数。

包装Invoke-SqlCmd (Wrapping Invoke-SqlCmd)

We can write a custom script that doesn’t accept some parameters or has some settings fixed and is sourced appropriately for auditing purposes. We can take the same approach by wrapping Invoke-SqlCmd inside a function like we would do with a custom function, especially if we don’t need to pass in parameters that are configured outside of its defaults (such as a command timeout that exceeds its limit). In the below script, we use a wrapper around this built-in function and we’ll notice that only the parameter -InputFile is allowed to change.

我们可以编写一个自定义脚本，该脚本不接受某些参数或已修复某些设置，并且其来源适当以用于审核。 我们可以通过将Invoke-SqlCmd包装到函数中来采用相同的方法，就像使用自定义函数一样，尤其是如果我们不需要传入在其默认值之外配置的参数（例如超过其默认值的命令超时）时限制）。 在下面的脚本中，我们在此内置函数周围使用了包装器，我们将注意到仅参数-InputFile被允许更改。

Function Copy-Report {
    Param(
        [Parameter(Mandatory=$true)][string]$rptfile
    )
    Process
    {
        Invoke-Sqlcmd -ServerInstance " TestServerOne" -Database " TestDB3" -Username "User" -Password "Pass" -InputFile $rptfile
    }
}

The above function is only for demonstration and the values for the credentials are only intended as placeholders. What we see is that the parameter for rptfile (which is passed to the -Inputfile) can differ when it’s called, but nothing else can. This means that a user with appropriate permission to call the function, but not edit it, can pass in the appropriate file, but not change where the function will submit the script – the server, database and credentials are present and cannot be changed when this function is called. As demonstrated in this example, the advantage of function-wrapping can be to restrict the options that are passed into Invoke-SqlCmd and this demonstration doesn’t mean we would always hard code the values that we see. In some cases, this may be an option that we consider when we may only want one or two parameters to change.

上面的功能仅用于演示，凭据的值仅用作占位符。 我们看到的是， rptfile的参数（传递给-Inputfile ）在调用时可以有所不同，但没有其他可以。 这意味着具有适当权限但不能编辑该功能的用户可以传递适当的文件，但不能更改该功能将提交脚本的位置–服务器，数据库和凭据存在，并且在此情况下不能更改函数被调用。 如本示例所示，函数包装的优点是可以限制传递给Invoke-SqlCmd的选项，并且此演示并不意味着我们将始终对所看到的值进行硬编码。 在某些情况下，当我们可能只希望更改一个或两个参数时，可以考虑使用此选项。

结论 (Conclusion)

As we see, a user with sufficient access can run any number of commands with Invoke-SqlCmd – we saw both an integrity check and a drop database run because the underlying user had access. While this function allows for convenient development, we should follow best practices with its use from considering the appropriate permissions to restricting some accounts (or uses) with procedures to creating custom functions with it. As we’ve also seen, this extends to all the access points where our script communicates – the file system, other servers, etc.

如我们所见，具有足够访问权限的用户可以使用Invoke-SqlCmd运行任意数量的命令-我们看到完整性检查和删除数据库都在运行，因为基础用户具有访问权限。 虽然此功能允许方便的开发，但我们应遵循最佳实践使用它，从考虑适当的权限到限制某些帐户（或使用情况）的过程以使用它创建自定义功能。 正如我们已经看到的那样，这扩展到脚本进行通信的所有访问点–文件系统，其他服务器等。

目录 (Table of contents)

Working with PowerShell’s Invoke-SqlCmd

Securely Working with Invoke-SqlCmd

使用PowerShell的Invoke-SqlCmd

安全地使用Invoke-SqlCmd

翻译自: https://www.sqlshack.com/securely-working-with-invoke-sqlcmd/

invoke-static