Delegated SQL Server Administration with Powershell

Providing delegated administration to groups that need to perform various security functions has always been a difficult task, but thanks to Powershell V3 (currently in CTP 1 as of this blog post) and PowerGUI we have new tools to provide a solution.

The Problem

You have groups outside of database administration that need to have the ability to manage security of SQL Server logins, users, and roles. Using the out-of-the-box SQL Server roles or permissions doesn’t quite handle all of these use cases and you want to avoid  putting these groups into the sysadmin server role on SQL Server for obvious reasons. One thing you may consider is placing these groups into the  securityadmin server role, but the problem with the securityadmin role is the lack of certain rights. The securityadmin role can only add logins to the instance and not users to databases or in turn users to database roles.  You could add your delegated security  group to every database as db_securityadmin, but this would be difficult to maintain in a highly dynamic environment with thousands of databases. There’s also a larger problem of auditing the actions your security administrators perform. Ideally you want to show every security change is related to specific documented change order. 

You could use the various server and database level permissions over the fixed role approach and in fact that is what Books Online recommends, however you’ll have the same issues of per database permissions and auditable actions to overcome. If this problem statement sounds unfamiliar to you then you probably haven’t had deal with segregation of duties and reducing administration access that has become prevalent in our post-SOX IT world.

A Solution

Looking at web-based applications which run under a service account , you’ll notice  normal users and administrators users as part of almost any applications. Neither group has direct access to the database systems they touch instead a service account is used. A simple idea which can easily be applied to administration functions all we need to do is create a web-based application, assign a service account the necessary rights to perform administration tasks and restrict access to the web-based application. What’s that? You’re not web developer? Well, neither am I. Here’s where Powershell V3 and PowerGUI can help.

Powershell V3 Delegated Administration

One of the simple, but really useful changes made to the Powershell V3 is the ability to delegate administration by setting up  runas credentials for a remoting configuration. In the CTP 1 download there’s an example script called runas.ps1 located under SamplesWindowsPowerShellDelegatedAdmin which demonstrates the functionality. This presents an interesting an idea, instead of having your security users connect to each SQL Server they could connect to a single “Proxy” server. The proxy server would then connect to various SQL Servers on their behalf.  Of course the account used for the delegated administration will need to have the necessary rights perhaps even sysadmin access. As an added bonus by using Powershell remoting the security administrator’s machine doesn’t need SQL Server tools or SMO installed. The only thing they needed is Powershell.

If you go this route you’ll want to create a distinct Session Configuration (endpoint) and ACL the configuration to the appropriate groups. You’ll l also need to create the various Powershell functions for the administration tasks on the remote server, here’s where I created a module called SqlProxy.

SQLProxy Module

SQL Server lacks Powershell coverage for security administration, so I created SqlProxy module which provides various functions for managing SQL Server logins, users and roles. In addition, because auditability is a key requirement I’ve added logging to a custom Windows Eventlog called “SqlProxy” for every function which change security settings. The module can be used in Powershell V2 or V3 with or without remoting. When used within a delegated administration setting the module will be loaded into the remote session and anyone granted access to the remote endpoint will be able to execute the SqlProxy functions. We could say our problem of providing delegated administration is solved, however unless your security admins are very adapt at Powershell you’ll probably want to provide GUI.

PowerGUI SQLProxy PowerPack

Because PowerGUI provides an easy way to create MMC-style UI’s over Powershell, I applied PowerGUI to the SQLProxy module and created the SqlProxy PowerPack. The security administrators can now use GUI-based tool without having to know Powershell. The complete solution does requires some additional setup and configuration as documented below.

Putting it Together

sqlproxydiag

Diagram courtesy of yuml.me

A security administer will use PowerGUI with the SqlProxy PowerPack installed to connect to a proxy server via Poewershell remoting. The proxy server has a session configuration with delegated credentials. The proxy server also has a SqlProxy module which is then used to connect to the various SQL Servers through SMO. Note Powershell remoting is not used between the proxy server and SQL Server. The only area where Powershell remoting is used is between the user’s machine and the proxy server.

Setup

Setup Proxy Server

Requirements:
  • Windows 7 with Sp1, Windows 2008 R2 with SP1 or Windows 8.
  • Powershell V3 CTP1 or higher
Installation

1. Install SMO or SQL Server clients tools (Express edition will work)

2, Copy the SqlProxy.psm1 module from http://poshcode.org/3040 to C:Windowssystem32WindowsPowerShellv1.0ModulesSqlProxy folder

3. Create the SqlProxy Eventlog and Source by running following command

New-EventLog -LogName SqlProxy -Source SqlProxy

4. Create the SqlProxy Session Configuration with delegated credentials

$cred = Get-Credential
Register-PSSessionConfiguration -Name "SqlProxy" -RunAsCredential $cred

5. ACL the SqlProxy session configuration to the appropriate AD groups (by default only administrators on the proxy server will have access to the remote session):

Set-PSSessionConfiguration -Name SqlProxy –ShowSecurityDescriptorUI

6. Optional: As noted on the Powershell team blog the default settings  of Powershell remoting are way too low. The low default settings are particularly problematic in a fan-in scenario like SqlProxy. You should make Powershell remoting more robust by changing the default concurrent and memory settings:

cd WSMan:\localhost\Shell
Set-Item MaxShellsPerUser 25
Set-Item MaxConcurrentUsers 25
Set-Item MaxMemoryPerShellMB 1024
 
#Do the same for the SqlProxy Session Configuration
 
cd WSMan:\localhost\Plugin\SqlProxy\Quotas
Set-Item MaxShellsPerUser 25
Set-Item MaxConcurrentUsers 25
Set-Item MaxMemoryPerShellMB 1024

Setup Client

Requirements:
  • Powershell V2 or higher (that’s right you can remote from V2 client to V3 server!)
  • PowerGUI version 3.0 or higher
Installation
  1. Download and install the SqlProxy PowerPack
  2. Right click "Connect to Server" Node
  3. Select "Shared Scripts" and change $global:SqlProxy variable to your proxy server
  4. Uncomment #-ConfigurationName "sqlproxy" in the Get-ValidSession function of Shared Scripts
  5. Save script and exit script editor
  6. Optional: Re-export the PowerPack with your customizations for reuse in your environment:
    1. Select File, PowerPack Management
    2. Highlight the SQL Security Administration PowerPack
    3. Select edit and change the PowerPack file link to a UNC share in your environment all of your users will have access to.
    4. Export the PowerPack to the UNC share.
    5. Have your colleagues install the customized PowerPack. PowerGUI has nice feature to automatically update PowerPack where you specify a file link whenever you update your customized version all of your users will get update notifications

Using SqlProxy

The SqlProxy PowerPack was written to duplicate functionality available in SQL Server Management Studio. If you’re familiar with logins, user mappings, database users and roles the interface should look familiar. I’ve posted some screen shots as an additional download on the PowerGUI SqlProxy PowerPack site.

Testing On Single Machine

The solution described in this blog post will work on a single machine running Powershell V2 with minimal configuration. The only caveat is that the delegated credentials requires v3. In the course of developing this solution I used Windows 7 with a local SQL Server instance and then would test using a 3-tier solution. A minimal setup for testing on a Windows 7 machine looks like this:

Requirements

  • Powershell V2
  • Local SQL Server instance
  • PowerGUI 3.o or higher

1. Copy the SqlProxy.psm1 module from http://poshcode.org/3040 to C:Windowssystem32WindowsPowerShellv1.0ModulesSqlProxy folder

2. Create the SqlProxy Eventlog and Source by running following command

New-EventLog -LogName SqlProxy -Source SqlProxy

3. Enable Powershell remoting. This  enables remoting but also will allow you to remote into the local machine from the local machine for testing purposes:

Enable-PSRemoting

If you’re on a non-domain machine you will need to add your localhost to the trustedhosteds as described here.

Additional Configurations

If you use this solution you may want to consider additional steps to secure the environment. You can configure a Powershell session configuration to only allow specific commands to be executed. If this is something you’d like to do I encourage you read about constraining a Powershell session. In addition PowerGUI supports a lockdown script to enable administrators to customize PowerGUI configurations for their environment. You read about PowerGUI lockdown features here.

Final Thoughts

I deployed this solution in my environment with around three dozen users. Was it successful? Well, it did allow me to remove various groups from syadmin role. My only gripe is the touch point on each workstation. Because of this you end up dealing with some inconsistencies in configurations. The touch point issue becomes more of a problem as you deal with various operating systems, PowerGUI versions and even antivirus and firewall settings (I found several instances where a client firewall would block Powershell remoting traffic through PowerGUI, but allow it from Powershell). One of the things I’m looking at is moving PowerGUI into a Citrix environment to ensure a single working configuration, but for now I’m happy with the solution. That said, looking at the general problem of delegated administration I wish there was an easier way to create web-based solutions which leverage Powershell.

{ 2 comments… add one }

  • Bob Beauchemin November 9, 2011, 12:04 pm

    Hi Chad,

    I’m curious about your statement: “…manage security of SQL Server logins, users, and roles. Using the out-of-the-box SQL Server roles or permissions doesn’t quite handle all of these use cases”

    When is this true? There are nice granular permissions around users, logins, and roles, you don’t need to use sysadmin. And with the advent in SQL Server 2005 of signed procs and execute as, there’s no permission (even DBCC and bulk copy, which still don’t use the granular permissions) that you can’t hide behind a stored procedure, and give user permission only to the procedure.

    Just curious…

    BTW, nice posting.

    Reply
    • Chad Miller November 9, 2011, 1:54 pm

      Thanks Bob. Let me walk through a scenario. My security user needs to be able to add/remove logins, add/remove users to databases, add/remove database users from database roles, and add/remove logins from server roles. Focusing on post SQL 2005 (incidentally I still have some 2000 to deal with) I can grant the user ALTER ANY LOGIN , but this has zero permissions to actually add users to databases. In order to allow my security user to also handle database security I would need to apply the following permissions to every database ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, CREATE SCHEMA ON ANY DATABASE, VIEW DEFINITION. In an environment with thousands of databases many of which are installed by applications maintaining these permissions at the database level is very difficult. What I’d like and what’s missing is the ability to say TO ANY DATABASE at the server level for the by-database permissions. This would be kind of like some of the ANY SCHEMA permissions in Oracle (SQL database ~= Oracle schema). The granularity I’m looking for is less granular than sysadmin, but not granular as by-database–is this medium grain :) ?

      A security admin that can only add logins to servers is useless and maintaining by database permissions is an administration nightmare.

      You could work around the issue in all kinds of creative ways. Maybe add the database permissions to model or setup a SQL Job or setup a PBM policy, etc. There are pros/cons to each approach.
      If we take your proposal as an example:
      Pros: Solve the problem, use least privilege
      Cons: No GUI, need to deploy procedure on every server, no auditing of actions (you could include logging in our customer procedure)

      The big draw back I see with a custom stored procedure is that you can no longer use SSMS and my security administrators will not have a non-GUI approach. The way my solution is designed there is little typing which equals fewer errors. So, you’ll still need to layer a GUI on top of the custom procedure. You could use Powershell and PowerGUI to do that, but then the solution would start to look similar to what I’ve presented.

      I’m certainly open to other ideas on how on to solve this problem, but the solution needs to provide a GUI, be easily integrated into a large dynamic environment, provide for full auditing and support SQL 2000.

      Reply

Leave a Comment


+ seven = 14

%d bloggers like this: