Appendix H - Configuring Custom Snare MSSQL Service Account

Owned by Steve Challans
Last updated: Oct 09, 2023 by rthornton
4 min read

Overview

The least privileged principle is a critical cybersecurity mindset that improves your cybersecurity posture by scrutinizing authentication and authorization of accounts in your environment. The following walkthrough will guide you through the necessary steps to provision a service account with minimal access to the local system and each SQL instance that will be monitored. The SnareMSSQL service will utilize this service account to access each SQL instance and interact with the local system.

Walk-through 

Step 1. Create a service account with access to each SQL instance you want to monitor and the local system where the Snare MSSQL agent is deployed.

Step 2. Provision the service account with "full control" to the following on the local system.

  1. Snare Certificate in the cert store  

    1. Right-click> All Tasks > Manage Private Keys.

b. Security > Add the service account with full control

  1. HKEY_LOCAL_MACHINE\SOFTWARE\InterSect Alliance\SnareMSSQL   

    1. Right-click > Permissions > Add the service account with full control

  1. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SystemCertificates\My  

    1. Right-click > Permissions > Add the service account with full control

  1. HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion

    1. Right-click > Permissions > Add the service account with full control

  1. C:\program files\SnareMSSQL

    1. Right-click > Properties > Security> Add the service account with full control

 

Step 3. Create the following Account Role. User Role can be any name you choose. The table below contains a script to create a role with only the necessary permissions. The Role will allow the SnareMSSQL agent to connect to the SQL Instance, start logging, and offload database logs to the local system.

Script to create a SQL Role (Change the role name to whatever you want)

Script to create a SQL Role (Change the role name to whatever you want)

USE master;

-- Creating SnareMSSQLagent role for Extended and trace event logging

CREATE SERVER ROLE SnareMSSQLagent;

-- Grant permissions to the new role

GRANT CONNECT ANY DATABASE TO SnareMSSQLagent;

GRANT VIEW SERVER STATE TO SnareMSSQLagent;

GRANT ALTER TRACE TO SnareMSSQLagent;

GRANT ALTER ANY EVENT SESSION TO SnareMSSQLagent;

GRANT VIEW ANY DEFINITION TO SnareMSSQLagent;

GO

  1. Run the Query to create the Snare MSSQL Agent Role.

a)    Open SQL Server Management Studio and connect to your SQL Server instance.

b)    Open a new query window (Ctrl + N or click on "New Query").

c)    Paste the script into the window.

d)    Execute the script by clicking the "Execute" button (F5).

  1. Assign the Snare Service account the new role.

 

Step 4. Go to the "SnareMSSQL" service and set it to run as the service account.

  1. Go to Services > Right-click “SnareMSSQL” > Log on > Browse > choose the Service account

Step 5. Restart the "SnareMSSQL" service.

  1. Right-click “SnareMSSQL” > Restart

  1. Verify that the service came back up successfully and is running.

Step 6. Connect to the Snare MSSQL Agent Web Console by going to https://localhost:6163

Step 7. Create an audit policy and set the policy to use the service account.

  1. Go to Agent Web Console > Audit Policy Configuration.

 

 

Troubleshooting

Verify the proper configuration by doing the following.

Step 1. Check the path to write trace files.

  1. Go to Agent Web Console > General Configuration > Path to write Trace Files

    1. change the path to something else other than C:\ProgramFiles\SnareMSSQL. Refer to Appendix D - Troubleshooting

Step 2. Check the audit configuration authentication method.

  1. Go to Agent Web Console > Access Configuration > Database Authentication Method

    1. Ensure the method selected is “Use Service Credentials,” as shown below.

Step 3. Check that databases have been enumerated.

  1. Go to Agent Web Console > Enumerate Databases. You should be able to see the database names for each Instance to which you gave the service account permissions, too.

Step 4. Check successful connection to Snare Central.

  1. Go to Agent Web Console > Latest Events. You should see a successful connection and logs showing in the latest events table.