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.
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).
|
1 2 3 4 |
$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.
|
1 2 3 4 5 |
$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.
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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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.
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.
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.
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.
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).
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.
|
1 2 3 4 5 6 7 8 9 |
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.
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.
Table of contents
| Working with PowerShell’s Invoke-SqlCmd |
| Securely Working with Invoke-SqlCmd |
He has spent a decade working in FinTech, along with a few years in BioTech and Energy Tech.He hosts the West Texas SQL Server Users' Group, as well as teaches courses and writes articles on SQL Server, ETL, and PowerShell.
In his free time, he is a contributor to the decentralized financial industry.
View all posts by Timothy Smith
- Data Masking or Altering Behavioral Information - June 26, 2020
- Security Testing with extreme data volume ranges - June 19, 2020
- SQL Server performance tuning – RESOURCE_SEMAPHORE waits - June 16, 2020