LOGbinder Blog

Updates, Tips and News   RSS Feed  

Least Privilege Workaround for SQL DB Access

Sat, 08 Dec 2018 10:21:56 GMT
In the past we have explained how LOGbinder for SharePoint uses SQL privileges. We also informed you about the unfortunate workaround of giving dbo access to certain DB's in SQL in the sporadic cases when the SharePoint API interferes with access to the databases. 

This was never a "workaround" that we were really happy with.  Giving dbo access is not only like giving the bank the title to your home as collateral for the mortgage but also giving them a letter that says "Stop by anytime you want and while you're here feel free to repaint the walls and help yourself to the scotch in the pantry."

Thankfully, we have found a proper workaround that does not require dbo access.  There is a role on the SharePoint SQL DB's named "SPDataAccess".  We have found that giving the service account this role grants enough access for LOGbinder for SharePoint to function properly.  Again we would like to specify that this is not the standard configuration needed with LOGbinder for SharePoint.  This is only used in the rare situations when the SharePoint API is giving issues with DB access.  For most of our customers the permissions set within SharePoint itself for the service account is all that is needed. 

There are two ways to give the service account this role.  One is using the SharePoint Management Shell and the other is directly in SQL (in our example below using SSMS). 

Our preferred method is making the changes directly in SQL.  We noticed that when using the SP Management Shell an extra role is given.  We also noticed that this is not always the case as well.  Sometimes the extra role is given and sometimes it is not.  Why?  We don't know.  Maybe it's a hidden Microsoft feature.

Here is how to make the changes using SSMS.

1. In SSMS add your service account as a login.


2. Open the logins properties and locate the three databases that your SharePoint farm is using for the Admin Content, Configuartion and WSS Content databases..  In this instance we have SharePoint_AdminContent(GUID), SharePoint_Config2019 and WSS_Content(GUID). 


3. For each database map the SPDataAccess role to the login.  You will notice that for the WSS_Content db, after saving the role change SSMS also grants the PSDataAccess and the PSReportingSchemaAdmin role.  If you have more than one content db, then you will have to perform these steps on all applicable db's with the WSS_Content prefix.  For more information on how to set SPDataAccess on a large number of content databases, click here.


You can also perform the steps above with a simple cmdlet using the SharePoint Management Shell.  Run the following cmdlet:

Get-SPContentDatabase | Add-SPShellAdmin -UserName domain\ServiceAccount

So in our example below we ran "Get-SPContentDatabase | Add-SPShellAdmin -UserName lab\sp2019srvacct".  Notice that doing this grants an additional role on all three databases; the SharePoint_Shell_Access role. As security experts our recommendation is obviously whichever process results in the least privilege needed to get the job done which, in this case, is making the changes via SSMS.


What does the SPDataAccess role allow?  According to TechNet, the SP_DATA_ACCESS role will have the following permissions:

  • Grant EXECUTE or SELECT on all SharePoint stored procedures and functions
  • Grant SELECT on all SharePoint tables
  • Grant EXECUTE on User-defined type where schema is dbo
  • Grant INSERT on AllUserDataJunctions table
  • Grant UPDATE on Sites view
  • Grant UPDATE on UserData view
  • Grant UPDATE on AllUserData table
  • Grant INSERT and DELETE on NameValuePair tables
  • Grant create table permission
Reference:  TechNet

Support for Exchange 2016 Auditing; New Features in LOGbinder for SQL Server

Wed, 15 Aug 2018 11:38:50 GMT

Exchange 2016 support

We are happy to announce support for Exchange 2016. Now you may be thinking 2016; wasn't that years ago?  It's true, Exchange 2016 was released in 2015 but because of a bug that seemed to have been introduced with the 2016 version, LOGbinder was not able to support it.  At the time we discovered it almost two years ago, we worked with Microsoft to confirm this behavior. This is what Microsoft said at that time:

  • The issue is caused due to limit of 100 search folders in particular mailbox. Before any new search can start, the old search folder has to age out and needs to be cleared. If this does not happen then it would fail.
  • We cannot modify these search folder limits, as it is by design.
  • We also found that it would take approx. 12hrs to reset the search folders count. So that we can run new query.

The above limitations posed such restrictions on the auditing capabilities of Exchange, that LOGbinder was not able to support Exchange 2016 at that time.

Our latest tests reveal that this has since been resolved and the above limitations have been removed in the latest cumulative updates. We have confirmed this to be true starting with CU6.

Therefore, LOGbinder now fully supports Exchange 2016 CU6+.

You can download LOGbinder for Exchange from our website and start auditing your Exchange environment.

SQL Server 2017

Microsoft released SQL Server 2017 and along with it they introduced new audit events. We have included these events in the latest LOGbinder for SQL Server version, adding events 24338-24348 and 24350-24375. These events are related to permissions on database scoped credentials and external libraries, and creating and dropping external libraries and database scoped resource governors, among some other events.

Additional new features in version 4:

  • Adding inputs in bulk from a CSV file. 
    • This is useful for users who have dozens or more inputs.  These inputs can now be added all at once instead of one by one.
  • As a counterpart to adding inputs in bulk, selecting and deleting multiple inputs is now also enabled.
  • Improve resilience by not stopping the service if one of the inputs is temporarily unavailable
    • This means that if there are many inputs monitored by LOGbinder for SQL Server and one or more of them is temporarily down or inaccessible, auditing will continue uninterrupted for the rest of the inputs.  For the unavailable inputs a warning will be generated and sent to the output.

Please download LOGbinder for SQL Server version 4.0 from our website to start auditing your SQL Server 2017.

After downloading LOGbinder for SQL Server version 4, if you have a current active support and maintenance license, you will have to request a new license key by opening a ticket at the https://support.logbinder.com site. If you do not currently own a license, please contact sales at LOGbinder for a quote.


Using Site-linked GPOs for Targeting Windows Event Collectors Prevents Forwarder Health Analysis

Wed, 22 Nov 2017 10:12:47 GMT

For the most part WEC allows you to control event forwarding from the collector but there is one setting in group policy: Group Policy Management Editor\Default Domain Policy\Computer Configuration\Policies\Administrative Templates\Windows Components\Event Forwarding\"Configure target Subscription Manager setting Enabled

This setting is like the bootstrapper for Windows Event Collection.  When computers out in the domain apply group policy and see this setting, they now know which collector(s) to start checking in with regularly to find new/updated subscriptions.

In larger environments with regional data centers we often setup local collectors so that we keep event forwarding traffic local – computer in a given region send events to the local collector.  That makes total sense but the possible problem arises with how you do this with group policy.

You may have Sites already defined in Active Directory that correspond to each region of computers and their local collector.  Active Directory allows you to assign GPOs to Sites and then each computer physically in that site will apply the policies in that GPO – a seemingly convenient way to configure computers based on their location. 

The problem arises when you try to assess the health of WEC subscriptions.  Before you can determine if all computers are actively forwarding events for a given subscription you need to know which computer should be sending events.  Supercharger’s Deterministic forwarder health analysis automates this process for you.  By default, when you specify Deterministic mode on a subscription, Supercharger looks at the groups assigned to that subscription, enumerate the computer accounts there-in and uses that as the baseline for the computers that should be sending events.  Then Supercharger compares that list to the list of computers WEC says are currently sending events.  (This is a simplified description, click here for more information). 

But sometimes we encounter environments where the customer has specified an all-encompassing group like Domain Computers which includes, well, all computers in the domain – probably many more times the number of computers in the region of that collector.  WEC doesn’t care: only those computers in that group which also apply the GPO pointing to a given collector will subscribe and start sending events.  But we obviously can’t use the group’s membership to perform health analysis.

For this reason, we added an alternative method to Deterministic health analysis that allows you to specify a custom LDAP filter.  This allows you to use most any attribute available on computer accounts to determine the baseline list of computers that should be sending events.  For instance, if your computer naming convention prefixes computers with region you could use that.  Or maybe department or description on the computer account provides the appropriate criteria – or a combination of fields.  The bottom line is you specify an ldap filter that produces the right set of computers for that collector and assign it to each subscription on the collector.

Here’s the gotcha – look at the attributes available on computer accounts in AD – there’s nothing for Site because it's not static.  Whenever a computer boots up it compares it's IP address to Site definitions in AD to determine which Site it's in.  So, you can't specify an LDAP filter where "site=Detroit" for example. 

If you want to use deterministic health analysis for accurate diagnosis of which computers are sending events and which ones where forwarding is broken for some reason you need an OU or some other criteria in AD that can be specified in an LDAP filter. 

If there’s no such data on your computer accounts you might consider creating a Startup script in the same GPO that causes each computer to update a selected attribute on its computer account in AD with its current site.  You’d need to make sure to delegate Write access that attribute on computer accounts.  The script would need to query Windows for its current site and then update the appropriate field on its computer account. 

Failing that, if you have a good idea of the number of computers that should be sending events you can use Arbitrary instead of Deterministic.  Or simply use Empirical instead of Deterministic.

 

In-depth How To's for Windows Event Collection

Mon, 28 Aug 2017 13:36:35 GMT

Here at LOGbinder we have been deep in the weeds with Windows Event Forwarding/Collection (WEC or WEF) for quite some time now.  In the past month since we’ve released Supercharger for WEC and opened our new forums for WEC and also Supercharger.  This has resulted in many of you asking questions and finding new challenges with WEC. 

For example, a few Supercharger users have recently asked about workstation status outside of work hours like weekends and holidays.  As you know, one of the benefits of a Supercharged WEC environment is being able to see the health status of all your forwarders.  During weekends or holidays, a forwarder may be shutdown or sleeping until the user gets the machine online again.  During this workstation’s down time you don’t want a healthy workstation reporting as unhealthy in Supercharger.  This KB article explains a how to change a simple setting to keep this from happening.

Another situation that some users are dealing with is when they need to define expected forwarders by some AD criteria other than an AD group.  For example, you create a subscription targeting “Domain Computers” but you only want a subset of the computers running Windows 10 in this group to forward events.  We have had users scratching their heads trying to figure out if this is possible without creating new AD groups which can take time, especially if you are working with thousands of forwarders like some of our users.  This KB article explains how to do this using LDAP filters in Superchargers Deterministic Subscription Policies.

We will do our best to keep you updated with tips and tricks to get your WEC Supercharged.  In the meantime, feel free to browse the “How To” section in our Support Portal to see if you are missing out on any of our latest articles and tips.


Randy releases two new "How-To" Videos

Wed, 21 Jun 2017 13:55:45 GMT
Randy Franklin Smith, guru at UltimateWindowsSecurity.com, just released two new "How-To" video's on monitoring two important areas with Windows Event Collection.

Video 1 - In this 4 minute video, Randy shows you step-by-step how you can use Supercharger to create a WEC susbscription that pulls PowerShell security events from all of your endpoints to a central collector.

Video 2 - In this 8 minute video, Randy shows you how to monitor security event ID 4688 from all of your endpoints. Obviously this would normally create a plethora of data but using Supercharger's Common System Process noise filter you will see how you can leave 60% of the noise at the source.

You can watch the video's by clicking on the links above or visiting the resources page for Supercharger by clicking here.

previous | next

powered by Bloget™