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 Samples\WindowsPowerShell\DelegatedAdmin 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

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:\Windows\system32\WindowsPowerShell\v1.0\Modules\SqlProxy 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:\Windows\system32\WindowsPowerShell\v1.0\Modules\SqlProxy 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.