Version 2

    Like many others (see: http://thwack.solarwinds.com/ideas/1246) we have long been awaiting a means of granting granular node management rights to a select group of users.  For example, we have SQL admins who should have the ability to freely reboot their testing environments at will without necessarily scheduling it with IT, but if that same server goes down unexpectedly IT should treat it as a node down event just as they do for any other server.  Unfortunately, under the current security model Orion does not allow for this level of granularity, and you cannot grant the ability to manage / unmanage a node without also granting a slew of other heavy permissions in the process.

     

    Thus, I've came up with a tentative means of doing so.  It has some pros and cons, and could likely be improved (all collaborations to this end are welcome!), but I wanted to share what I have as I've seen a notable increase in the number of people looking for a means of achieving this goal.

     

    This makes use of a pair of PowerShell scripts (see the attached .zip), a "front end" and a "back end".

    • The front end does some security checks, and is meant to be ran from a user's workstation.  Ultimately it puts in a request to unmanage a node into a pending requests table, itself residing in a new database.
    • The back end should be ran from a protected area every couple minutes to process pending requests.  It does additional security checks to make sure that they are actually valid, and then leverages the Orion SDK to actually put in the request.

     

    Some caveats and points of note:

    • This does require a minor addition/change to the Orion production SQL database.  I've done my best to minimize such changes, but I did have to settle on the addition of two new stored procedures.  Thus...
    • I ***highly recommend*** testing this out in some form of testing environment first.  Orion servers seem to P2V very well in our test environment, if you have something like Hyper-V or VMware.
    • In our case, we have only done scaled testing with these scripts.  We plan on rolling them out soon and have found no issues, but your mileage may vary.  I've tried to write the scripts in a readable format, so figuring out what they do at a line-by-line basis shouldn't be too difficult.
    • The method of actually controlling the unmanagement access on a per node level doesn't scale all that well.  Currently you have to add security pairings of user to node on a one by one basis, and groups are not supported.  Thus, if you have ten people that need to unmanage ten servers, it amounts to a hundred entries.

     

    Updates:

    • 2013/02/04 - Minor edits to the text for clarity.  No query or script changes.

     

     

    How It Works

    Users run the front end script, and supply a search string.  The script queries the database via a stored procedure and returns the list of nodes that match the name (a wildcard on each end is assumed).  Once users have selected an individual node they can select how many hours they wish to unmanage the node and submit it as a request for unmanagement, assuming they have the rights to do so (as granted via the security pairings table).


    A back end script runs every few minutes (or every minute, it's up to you) to check this requests table, and process any pending requests.  This script should be ran as a scheduled task, either on the Orion server or in any other protected location.  If the user passes a second security check against the security pairings table (which it should unless the request was bogus) the script will then invoke the Orion SDK to actually unmanage the node for the amount of time specified.  Finally, the script then makes a minor alteration to the auditing events table to change the username who is associated with that particular unmanage request to the user who actually put in the request, rather than the username that the script used to access the Orion SDK.



    How It Could Be Made Better

    • I don't like the idea of using a PowerShell script as a front end, even with the menu system in place.  Some other, more user friendly, front end would probably be better.  However, I've designed the system in such a way that just about anything could be the front end as long as it follows the same logic.
    • The ability to schedule a unmanagement window for a future date rather than essentially immediately starting would be great, but it fell outside the scope of our usage.  It'd likely require a significant overhaul of both ends.
    • The ability to use some form of groups (Active Directory? Orion?) in the security pairings table.  One-to-one pairings scales horribly.

     

     

    1 - PowerShell Scripts Configuration

    Documentation for each of the two scripts can be found within the attached .zip file.  It goes over the operation of the scripts in much greater detail than I want to go into here, as this will already be a big document.  The rule of thumb is to just make sure that the variable block near the top of each script matches your environment.

     

     

    2 - User Account Configuration

    You will need to make the following users and groups, in the specified places:

    • Active Directory
      • Create a group named "OrionUnmgmt".  Add any users to this group that should have the ability to use this process.  This essentially just lets them run the front end script and gives them the ability to make read-only queries to specific stored procedures; the security for actually unmanaging a node is handled by the security pairing table.
    • Orion SQL Instance
      • Create a SQL user account named "UnmanageScriptBackend" on the instance of SQL that hosts the Orion database.  This user account will be used by the back end script for read-only access to specific stored procedures.
    • Orion Web Interface
      • Create a user account named "UnmanageScript", and give it Node Mgmt rights.  This user account will be used to access the Orion SDK by the backend script.

     

     

    3 - SQL Instance Configuration

    This section details how the production Orion SQL instance needs to be set up in order for the system to function properly.


    Create the SQL Logins

    The following user accounts / groups must be created and set up with their corresponding permissions. Note that none of them require any additional Server Roles above the "public" default. These accounts will be edited later in this guide to add the appropriate database mapping.

    • UnmanageScriptBackend
      • Type: SQL account
      • Purpose: Used by the back end script to access the instance.
    • DOMAIN\OrionUnmgmt
      • Type: Active Directory group
      • Purpose: Used by the front end script to facilitate access the various stored procedures it uses. All users that are intended to run the front end script need to be a member of this group.


    Create the Unmanagement database

    Default Name: SolarWindsOrion_UnmgmtSec

    Note that this name can be altered. However, if you do so please keep that in mind for any portion of this guide that references it by its original name.


    Modify the SQL logins

    The SQL logins should now be altered to contain the appropriate database mapping.

    • UnmanageScriptBackend
      • User Mapping:
        • SolarWindsOrion: Only should be mapped (check the box); no role membership should be defined.
        • SolarWindsOrion_UnmgmtSec: Only should be mapped (check the box); no role membership should be defined.
    • DOMAIN\OrionUnmgmt
      • User Mapping:
        • SolarWindsOrion: Only should be mapped (check the box); no role membership should be defined.
        • SolarWindsOrion_UnmgmtSec: Only should be mapped (check the box); no role membership should be defined.


    Modify the Unmanagement database

    Security Pairs Table

    This table contains the security pairs which are used by both scripts, and has two columns. Each pair can only accommodate one user account and one node ID, so a username will need one record entered into the table for each node that they should have the rights to manage. This table needs to be populated manually, and the system will not function without it.

     

    Default Name: NodeUnmgmtPairs

    Note that this name can be altered. However, if you do so please keep that in mind for any portion of this guide that references it by its original name.

     

    The following query can be used to create this table:

    USE [SolarWindsOrion_UnmgmtSec] GO CREATE TABLE [dbo].[NodeUnmgmtPairs]( [Username] [nvarchar](50) NOT NULL, [NodeID] [INT] NOT NULL ) ON [PRIMARY] GO

    Columns
    • Username
      • Data Type: nvarchar(50)
      • Allow Nulls: No
      • Purpose: Contains the username of this particular security pair. This must be in the full DOMAIN\USERNAME format. This field is not case sensitive.
    • NodeID
      • Data Type: int
      • Allow Nulls: No
      • Purpose: Contains the node ID of this particular security pair. This information can be most easily found by clicking on any node in the web interface and seeing what shows in the address immediately following N:.


    Requests Table

    This table contains the requests submitted by the front end script to be processed by the back end script, and has six columns. This table is populated automatically, and no manual intervention should ever be needed.

    Default Name: NodeUnmgmtRequests

     

    Note that this name can be altered. However, if you do so please keep that in mind for any portion of this guide that references it by its original name.

     

    The following query can be used to create this table:

    USE [SolarWindsOrion_UnmgmtSec] GO CREATE TABLE [dbo].[NodeUnmgmtRequests]( [UnManageFrom] [datetime] NOT NULL, [UnManageUntil] [datetime] NOT NULL, [UnManaged] [bit] NOT NULL, [NodeID] [INT] NOT NULL, [Username] [nvarchar](255) NOT NULL, [STATUS] [tinyint] NOT NULL ) ON [PRIMARY] GO

    Columns
    • UnManageFrom
      • Data Type: datetime
      • Allow Nulls: No
      • Purpose: Contains the UTC date and time of when the node should be unmanaged/remanaged from. This also serves as the time that the request was submitted.
    • UnManageUntil
      • Data Type: datetime
      • Allow Nulls: No
      • Purpose: Contains the UTC date and time of when the node should be unmanaged/remanaged to. In the case of requests to remanage a node this value will be the same as the one in UnManageFrom.
    • UnManaged
      • Data Type: bit
      • Allow Nulls: No
      • Purpose: Determines if a request is to unmanage a node or to remanage it:
        • 0: Remanage. Request is to remanage the node (make Orion begin to monitor it once again).
        • 1: Unmanage. Request is to unmanage the node (make Orion stop monitoring the node).
    • NodeID
      • Data Type: int
      • Allow Nulls: No
      • Purpose: Contains the node ID of this particular request.
    • Username
      • Data Type: nvarchar(255)
      • Allow Nulls: No
      • Purpose: Contains the username of the user that originally submitted the request.
    • Status
      • Data Type: tinyint
      • Allow Nulls: No
      • Purpose: Contains the status of the request:
        • 0: Pending. Request was submitted by the front end script but has not yet been processed by the back end script.
        • 1: Completed. Request was processed by the back end script and was passed on to the Orion SDK.
        • 2: Failure. Request was processed by the back end script, but failed to match a security pair. This should not happen unless either the front end script logic was subverted or the request was manually (and incorrectly) input into the Requests table.


    Stored Procedures

    uspSubmitRequest

    This stored procedure submits a request to the Requests table. The supplied parameters are for the UnManageFrom, UnManageUntil, UnManaged, and NodeID columns. The Username column automatically gets set to the username of the user running the stored procedure (and thus submitting the query) and the Status column automatically gets set to a status of "0" (Pending).

     

    The following query can be used to create this stored procedure:

    USE [SolarWindsOrion_UnmgmtSec] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[uspSubmitRequest] @UnManageFrom datetime, @UnManageUntil datetime, @UnManaged bit, @NodeID INT AS INSERT INTO NodeUnmgmtRequests VALUES (@UnManageFrom,@UnManageUntil,@UnManaged,@NodeID,LOWER(SYSTEM_USER),'0') GO

     

    Permissions Needed

    • DOMAIN\OrionUnmgmt
      • Execute: Grant

    uspValidatePair

    This stored procedure validates a security pair, and is used by both scripts. In the front end script this is used to validate a request before submitting it to the Requests table, whereas in the back end script it is used to validate existing requests before acting on them. Accepts Username and NodeID as parameters for a full check, but can also be ran with just the NodeID parameter to make the stored procedure validate that node versus the username of the user running the stored procedure.

     

    The following query can be used to create this stored procedure:

    USE [SolarWindsOrion_UnmgmtSec] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[uspValidatePair] @Username nvarchar(255) = NULL, @NodeID INT AS SELECT * FROM [dbo].[NodeUnmgmtPairs] WHERE Username = ISNULL(@Username,LOWER(SYSTEM_USER)) AND NodeID = @NodeID   GO

     

    Permissions Needed

    • DOMAIN\OrionUnmgmt
      • Execute: Grant
    • UnmanageScriptBackend
      • Execute: Grant

     

    uspGetRequests

    This stored procedure performs a select operation to get all requests that have a status of "0" (Pending). No parameters are used.

     

    The following query can be used to create this stored procedure:

    USE [SolarWindsOrion_UnmgmtSec] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[uspGetRequests] AS SELECT * FROM [dbo].[NodeUnmgmtRequests] WHERE STATUS = '0' GO

     

    Permissions Needed

    • UnmanageScriptBackend
      • Execute: Grant

    uspSetRequestStatus

    This stored procedure sets a particular Requests in the table to have a new status. The supplied parameters of UnManageFrom and Username are used to find the particular request, and the Status parameter is used to set the requests status accordingly.

     

    The following query can be used to create this stored procedure:

    USE [SolarWindsOrion_UnmgmtSec] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[uspSetRequestStatus] @UnManageFrom datetime, @Username nvarchar(255), @STATUS tinyint AS UPDATE NodeUnmgmtRequests SET STATUS = @STATUS WHERE UnManageFrom = @UnManageFrom AND Username = @Username GO

     

    Permissions Needed

    • UnmanageScriptBackend
      • Execute: Grant


    Modify the production Orion database

    While care was taken to minimize the amount of changes that needed to be made to the production Orion database, a pair of stored procedures need to be added to it. Note that these changes may be overwritten or removed if an Orion upgrade or maintenance window is conducted, making these the most vulnerable as such.


    Stored Procedures

    uspNodeSearch

    This stored procedure searches the production Orion database for details of a node, based on the Caption or a portion of it. A "Search" parameter is supplied as the search string.

     

    The following query can be used to create this stored procedure:

    USE [SolarWindsOrion] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[uspNodeSearch] @SEARCH nvarchar(255) AS SELECT [Caption],[NodeID],[UnManaged],[UnManageFrom],[UnManageUntil] FROM [SolarWindsOrion].[dbo].[Nodes] WHERE Caption LIKE '%' + @SEARCH + '%' GO

     

    Permissions Needed

    • DOMAIN\OrionUnmgmt
      • Execute: Grant

     

    uspSetAuditUsername

    This stored procedure is invoked to change the username in all cases where an audit entry contains the script's username. This stored procedure is ran after every operation, so it only ends up changing just the most recently added audit entries.

     

    The following query can be used to create this stored procedure:

    USE [SolarWindsOrion] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[uspSetAuditUsername] @Username nvarchar(255) AS UPDATE [SolarWindsOrion].[dbo].[AuditingEvents] SET AccountID = @Username WHERE AccountID = 'unmanagescript' GO

     

    Permissions Needed

    • UnmanageScriptBackend
      • Execute: Grant

     

     

    4 - Security Pairings Table Configuration

    To add a security pairing, you must have the user's full username (DOMAIN\USERNAME) in the left column and the NodeID they should have the ability to unmanage in the right column.  To view a list of the current contents of the table that uses a join to show the actual node name, run the following query:


    SELECT SolarWindsOrion_UnmgmtSec.dbo.NodeUnmgmtPairs.Username, SolarWindsOrion_UnmgmtSec.dbo.NodeUnmgmtPairs.NodeID, SolarWindsOrion.dbo.Nodes.Caption FROM SolarWindsOrion_UnmgmtSec.dbo.NodeUnmgmtPairs INNER JOIN SolarWindsOrion.dbo.Nodes ON SolarWindsOrion_UnmgmtSec.dbo.NodeUnmgmtPairs.NodeID=SolarWindsOrion.dbo.Nodes.NodeID