Scheduled export of the security log

If you have trouble with the log being overwritten before you can read it and do not want to increase the size of the log further, you can use a scheduled PowerShell script to create regular exports. The script below creates csv files that can easily be imported to a database for further analysis.

The account running the scheduled task needs to be a local admin on the computer.

#######################################################################################################################
#   _____     __     ______     ______     __  __     ______     ______     _____     ______     ______     ______    #
#  /\  __-.  /\ \   /\___  \   /\___  \   /\ \_\ \   /\  == \   /\  __ \   /\  __-.  /\  ___\   /\  ___\   /\  == \   #
#  \ \ \/\ \ \ \ \  \/_/  /__  \/_/  /__  \ \____ \  \ \  __<   \ \  __ \  \ \ \/\ \ \ \ \__ \  \ \  __\   \ \  __<   #
#   \ \____-  \ \_\   /\_____\   /\_____\  \/\_____\  \ \_____\  \ \_\ \_\  \ \____-  \ \_____\  \ \_____\  \ \_\ \_\ #
#    \/____/   \/_/   \/_____/   \/_____/   \/_____/   \/_____/   \/_/\/_/   \/____/   \/_____/   \/_____/   \/_/ /_/ #
#                                                                                                                     #
#                                                   http://lokna.no                                                   #
#---------------------------------------------------------------------------------------------------------------------#
#                                          -----=== Elevation required ===----                                        #
#---------------------------------------------------------------------------------------------------------------------#
# Purpose:Export and store the security event log as csv.                                                             #
#                                                                                                                     #
#=====================================================================================================================#
# Notes: Schedule execution of tihis script every capturehrs hours - script execution time.                           #
# Test the script to determine the execution time, add 2 minutes for good measure.                                    #
#                                                                                                                     #
# Scheduled task: powershell.exe -ExecutionPolicy ByPass -File ExportSecurityEvents.ps1                               #
#######################################################################################################################

#Config
$path = "C:\log\security\" # Add Path, end with a backslash
$captureHrs = 20 #Capture n hours of data

#Execute
$now=Get-Date
$CaptureTime = (Get-Date -Format "yyyyMMddHHmmss")
$CaptureFrom = $now.AddHours(-$captureHrs)
$Filename = $path + $CaptureTime + 'Security_log.csv' 
$log = Get-EventLog -LogName Security -After $CaptureFrom
$log|Export-Csv $Filename -NoTypeInformation -Delimiter ";"

SQL Server 2016 sysadmin escalation using PowerUpSQL

Scenario

For some reason you need to gain sysadmin access to a SQL Server instance. Maybe you have inherited it from a DBA that was eaten by a sleepy tiger, or more likely, the SQL Server instance was installed by a consultant and the sysadmin password was forgotten a long time ago. Oh, and you want to do it while the SQL Server is running. No matter why, here is one possible solution, provided that you have local admin access. There are probably ways to escalate access without local admin as well. And of course there are tons of ways to escalate a normal user to an admin user, but that is not the focus of this post.

This procedure has been tested on SQL Server 2014 and 2016. On previous versions, simply running SQL Server Management Studio as admin locally on the server was enough, but on never versions that path has been blocked by default.

To make the process easier, we will utilize the PowerUpSQL PowerShell module from NetSPI.

Procedure

Note: One of the SQL Server instances used in the example is the default instance of the server IM01. As such, it has no instance specification on the form Server\instance. If your server has on or more named instances, you will have to specify the instance name.

You need to obtain local or remote console access to the server. The user has to be a local admin on the server, and should be a user on the SQL Server. Application service accounts are nice starting point if no one has any access to the SQL Server at all. That is, you may not have access to the server, but you have an application using it, and that application accesses the SQL Server using a service account in Active Directory. You can give that account temporary local admin access on the SQL Server. Then you can escalate it to sysadmin access, and then use that login to grant yourself access. You should of course remove sysadmin access from the service account when you are done.

As a sidenote, you will often find that the service account already has sysadmin access, or that the application has a hard-coded SQL Server login account (not in AD but a SQL Server specific account) that is called sa (sysadmin) or is a member of the sysadmin group.

  • Download the module from https://github.com/NetSPI/PowerUpSQL. There are ways to launch the module remotely, but in this example we are copying them to the server.
  • Copy the files to a local folder, we use C:\Temp in our example.
  • Get a copy of psexec.exe from sysinternals.
  • Open an administrative PowerShell session running as the local system.
  • PsExec.exe -i -s powershell.exe

image

  • Run the following commands in the black PowerShell console.
  • Verify that you are running as the nt authority\system account by running whoami.
  • Import-Module c:\Temp\PowerUpSQL.psd1
  • Enumerate local SQL Server instances
  • Get-SQLInstanceLocal
  • Check the current access level
  • Get-SQLServerInfo -Verbose -Instance Server\instance

image

  • Look for IsSysadmin in the output
  • Escalate access
    • Invoke-SQLEscalatePriv –Verbose –Instance “SQLServer1\Instance1”
    • Invoke-SQLImpersonateService -Verbose -Instance SQLServer1\Instance1

Note: Sadly, some of the screenshots were lost. I will add some new ones later if I remember.

  • If you are successful, you may execute TSQL code to grant yourself access.
  • Get-SQLServerInfo -Verbose -Instance Server\instance
  • Look for IsSysadmin in the output
  • Import SQLPS
  • Import-Module -Name Sqlps
  • Test that you are able to execute arbitrary TSQL
  • Invoke-Sqlcmd -Query “Select @@version” -serverinstance SQLServer1\Instance1
  • Run the TSQL necessary to grant yourself access. Sample:
  • Invoke-Sqlcmd -Query “ALTER SERVER ROLE [sysadmin] ADD MEMBER [Domain\user]” -serverinstance
  • De-escalate access
  • Invoke-SQLImpersonateService -Verbose -Rev2Self
  • Connect to the server using your chosen account.

If that did not work

There is another method, based on shutting the SQL Server down and starting it in single user mode.

  • Log in to the SQL Server as a local admin.
  • Shut down SQL Server and the SQL Server Agent.
  • image
  • Start an administrative command prompt.
  • Navigate to the SQL Server instance directory, usually something like this: “C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn”
  • If you are using a default instance, execute sqlservr.exe –m –c
  • For a named instance, use sqlservr.exe –m –c –s Instancename
  • If someone else keep nabbing your connection, use -m”SQLCMD” to only allow sqlcmd connections.
  • Open a new command prompt and navigate to the instance directory.
  • Execute sqlcmd.exe
  • You should get a 1> prompt.
  • Input “@@SERVERVERSION” on line 1 without the quotes and press enter.
  • Input “GO” at line 2 and again press enter. The server should respond with the current version.

image

  • Add yourself to the sysadmin role by entering the following lines:
USE [master]
GO
CREATE LOGIN [DOMAIN\user] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [DOMAIN\user]
GO

image

  • There will be no response, but you can verify by executing the following lines.
EXEC sp_helpsrvrolemember 'sysadmin'
GO
  • It will return a list of sysadmin group members.
  • When you are done, return to the first cmd window and press [Ctrl]+C to shut down the SQL Server.
  • Restart the SQL Server and SQL Server Agent services in the normal mode.

Securing Windows Active Directory

This is a list of measures you can implement to increase your Windows AD Security. The list is in no way exhaustive, and some of the items overlap. Be aware that security recommendations change over time. This article was originally created 2018.01.22. If that is several years in the past when you read this, I cannot promise that all recommendations are up to date.

LAPS – Local administrator password management

Implementing LAPS ensures that all your domain-joined computers have a unique password that is changed periodically for the local administrator account. It operates as a GPO Client Side Extension, and thus requires you to install and register a DLL on each target computer. You can do this via GPO, in your VM image, or through any other software deployment solution you may use.

On the management computers and/or the DC itself, you have to add management tools and GPO Editor templates. There is a graphical user interface and a PowerShell module. The PowerShell module also includes the commands necessary to extend the AD Schema for storing the passwords and their associated expiry date.

See https://technet.microsoft.com/en-us/mt227395.aspx for details.

Securing the built-in Administrator account

 

The built in Administrator account in the domain should be secured. The ObjectSID of the domain admin account always ends in -500, and is thus easy to identify even if the name has been changed. The guidance used to be “Disable the Administrator account”, but it has been changed due to some recovery scenarios requiring an active Administrator-account. Specifically, the Administrator account is the only account able to log on when no global catalogs are online.

See https://docs.microsoft.com/en-us/windows-server/identity/ad-ds/plan/security-best-practices/appendix-d–securing-built-in-administrator-accounts-in-active-directory for details and an implementation guide. Some highlights are shown below.

Set the DOMAIN\Administrator account as sensitive and require smart card

 

clip_image001

Create a GPO to prevent Domain Admins from logging on to member servers or workstations

I have gone a bit further than the guide here, adding Domain Admins and Guests for good measure. The “Local account and member of Administrators group” is related to denying local administrator accounts access to the computer from the network. More about this below.

Make sure that this GPO does not apply to domain controllers, that is, do not link it at the domain level.

clip_image002

 

Block remote access for local accounts

Add Guests, Local account and member of Administrators group, Domain Admins, Enterprise Admins and Schema Admins to the policy Computer Configuration\Windows Settings\Local Policies\User Rights Assignment\Deny Access to this computer from the network.

clip_image003

For details, see https://blogs.technet.microsoft.com/secguide/2014/09/02/blocking-remote-use-of-local-accounts/

Disable weak ciphers for Windows Secure Channel

You can build a GPO to limit the cipher suites used by the Windows Secure Channel API, and by extension IIS. Be aware that this does not in any way limit other usage of weak ciphers. For instance, a TomCat server running on the same computer may very well use RC4 even if you have removed it from the list of Windows secure channel ciphers.

The GPO is located at Computer Configuration\Administrative Templates\Network\SSL Configuration Settings\Cipher Suites.

When you enable this setting, you get a list of all the default ciphers as a long comma separated string. Which ciphers you get is dependent of the Windows version. The easiest way to edit this list is to copy the string into a text editor. You can change the order to change the priority and remove weak ciphers.

clip_image004

clip_image005

 

Do not allow local users to run remote elevated sessions

Do not apply this fix: https://support.microsoft.com/en-us/help/951016/description-of-user-account-control-and-remote-restrictions-in-windows

That is, do not create the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System \LocalAccountTokenFilter Policy value, and if it exists, make sure it is set to 0. We could of course create a GPO to enforce this setting.

clip_image006

For details, see https://www.harmj0y.net/blog/redteaming/pass-the-hash-is-dead-long-live-localaccounttokenfilterpolicy/

Set a password policy and a lockout policy

 

  • Password length: 8 characters. Encourage users to create passwords with a random length between 8 and 20 characters. You want your users to have passwords that vary in length. If you set this limit to 14, chances are all passwords are exactly 14 characters long. This makes it a lot easier to crack them.
  • Complexity not required. If you require complexity, users tend to add numbers and capitals at the start and end of the password.
  • Password history: 10.
  • Maximum password age: 0, that is password never expires. To frequent password changes may lead to bad password diversity and predictable passwords. Leaked passwords are almost always exploited immediately, so there is no point in forcing a monthly password change. If you must, set the maximum age to one year. Urge users to choose new passwords that are completely different from the previous passwords. That is, do not use MypassWord1, MypasswOrd2 and so on.
  • Do not enable the reversible encryption option. Ever. Just don’t.
  • Lockout policy: Locked for 24 hours after five unsuccessful attempts.

clip_image007

clip_image008

For background information, see:

Enforce SMB Signing and disable SMB1

 

Enforce signing

You can enforce signing on both the server and client side. The server side is shown below. Be aware that some services require this setting to be disabled. If you have such services, create an overriding GPO for those servers only, leaving SMB signing on in the rest of the domain.

 

clip_image009

See https://technet.microsoft.com/en-us/library/cc731957(v=ws.11).aspx

Disable SMB1

You have to create some registry-GPO settings. Details are at the link below. Be aware that legacy clients like Windows XP will be dependent on SMBv1 on Domain Controllers to access the Sysvol share. The recommendation is still to disable SMBv1 everywhere.

 

clip_image010

 

See https://blogs.technet.microsoft.com/staysafe/2017/05/17/disable-smb-v1-in-managed-environments-with-ad-group-policy/ for details.

Create new computer objects in a separate OU, not in the Computers container

 

Thus you can delegate permissions to manage them, and you can apply GPOs to newly added computers. You do this with the rdircmp console application.

  • Log on to a domain controller.
  • Start an administrative CMD-shell
  • Execute rdircmp [FQDN of OU]

clip_image011

You can verify or check this setting using PowerShell:

Get-ADDomain |Select-Object ComputersContainer.

Limit the number of domain admins

Domain admin accounts should only be used for domain administration tasks, and you should not have many of them. Do not use service accounts with domain admin access.

A recommended number of domain admins is 5.

Avoid explicit permissions, prefer group permissions

 

All permissions in AD should preferably be given to groups, not individual users. This makes it a lot easier to manage permissions, and it is also easier to see what permissions a user has based on which groups he is a member of. That is, if you follow this principle. There will always be exceptions, but they should be few and far between.

Limit the number of people with delegated access to AD

 

AD administration task can be delegated. For instance, your service desk could be able to reset passwords and create users without full domain admin access. It is important to limit these delegations and keep tabs on them.

Use dedicated domain controllers

 

  • Make sure that your domain has at least two domain controllers.
  • If they are virtual, they should not be on the same cluster. Preferably you should have at least one dedicated physical domain controller.
  • Do not install anything on you domain controllers, with the exception of backup agents, antivirus software, monitoring agents and software deployment agents.
  • Do not enable the Hypervisor role on your physical domain controllers to run other software in a VM.
  • Make sure you have a system state backup of your domain controllers.

No trusts between domains

 

Avoid using forests and trusts between domains. Trusted domains should be handled as a single security context (e.g. dev, test, production, management etc), and thus you only really need one domain for each security context unless you want to divide it based on departments or divisions.

Enforce the Windows firewall

 

Make sure that Windows Firewall is turned on. There are many ways to do this, e.g. SCCM or GPO.

Install antivirus software on all servers and workstations

 

And make sure that it is activated and up to date. SCCM enables you to monitor and manage the default Windows Defender antivirus. Most commercial AntiVirus software comes with some kind of centralized management and monitoring tool.

Log out RDP sessions after 24 hours

 

Remote desktop server sessions that are still active (idle or disconnected) after 24 hours should be logged out automatically. Really active sessions are left for 5 days.

The GPO settings are located at:

Computer Configuration\Administrative Templates\Windows Components\Remote Desktop Services\Remote Desktop Session Host\Session Time limits

  • Set time limit for disconnected sessions: 1 day.
  • Set time limit for active but idle RDS sessions: 1 day.
  • Set time limit for active RDS sessions: 5 days.
  • End session when time limits are reached: Enabled
  • Set time limit for logoff of RemoteApp sessions: 1 day.

Group Managed Service accounts and Managed Service Accounts

Enable the domain for group managed service accounts, and encourage its use on supported services.

https://docs.microsoft.com/en-us/windows-server/security/group-managed-service-accounts/group-managed-service-accounts-overview

https://blogs.msdn.microsoft.com/markweberblog/2016/05/25/group-managed-service-accounts-gmsa-and-sql-server-2016/

More about password managers and security

In response to comments on my post about 1Password and cloud security, this is an update about other password managers and my way out. It is highly recommended that you read the previous post first to understand where I am going with this. I was looking for a password manager for a specific purpose, that is cloud sync ability with something that at least looks like true encryption without back doors, and my comments are written from that mindset.

Why I would not use Dropbox for my passwords

Or Google drive. Or Onedrive for that matter.

And note the “for my passwords” part of the sentence. It is not that I do not use such services, but I consider all of them as an insecure location. They are just to juicy a target for wrongdoers, both intelligence agencies and the other kind of cyber criminals.

About “Free” cloud services

Such as Facebook, or maybe Lastpass is a better example in this case. There is no free lunch. You are either a customer or a product. You are either the farmer or the pig. Pigs have no rights to privacy. And before you consider using any google-based service, read this: Street View cars slurping wi-fi. This case complex was for me the first warning that something was rotten in the house of google. And it has not become any better since. I guess the “Don’t be evil” company motto should have been a warning as well…

As such I would never install a password manager on an Android or IOS-based phone that contains passwords for other systems. They are way to easy to hack.

And just as a note, LastPass and LogMeOnce was not considered due to their lack of a desktop client.

KeePass

I have used KeePass in the past, but it is mostly a local solution only, and is therefore out of scope. At least initially. I am also slightly worried about the fact that it is free, as in free of charge. I have nothing against open source software, but for some needs I prefer to have access to someone to complain to if it all goes wrong. Someone who is paid to listen to my ails and complaints. That being said, KeePass is a nice product for local password management.

Keeper

Had a brief look at it, discovered it was dependent on java, uninstalled immediately. Also, at revisiting it seems to be “moving to the cloud”.

DashLane

A solid security policy. I ran the demo for some time, but the “Modern” UI was horrible, if not as horrible as the 1Password 6 beta. I wanted to like it, but after continuously having to click buttons two or three times for them to do anything, I gave up. I may re-test it in the future. This is sadly a complaint I have about most “Modern” UI applications, they do not respond to mouse clicks consistently. I also could not get the browser plugin to work properly in Vivaldi.

StickyPassword

This has been the best contender so far. I got as far as testing the synchronization, and I used it for the full trial period without a rage-uninstall. What actually stopped me from going for it in the end was it’s login dialog constantly popping up when I wasn’t trying to use it. It became a nuisance. I also did not like that it wants to run all the time, instead of when I actually want to use it. The chance of someone snagging access to it while walking by if I forgot to lock my screen is of course a danger, but primarily I stopped using it because it became irksome.

What I ended up doing

I stuck with what I had, a simple local-only password manager not to be named. Because the password manager itself is not important, as long as you have control of the data locally.  It is how you control the synchronization of data that is important. And as I do not really trust any of the “public cloud” alternatives, I decided to make my own. I installed Resilio Sync, a file synchronization application based on the BitTorrent protocol, and used it to keep my encrypted password store in sync across my computers.

This allows me to keep the data in sync and, to a certain degree, actually know where my data is physically located. It could still be hacked or intercepted of course, but that had to be a much more directed attack than the usual “lets  archive everything that was ever stored on Dropbox in case we need it some time” behavior we have come to expect from the people who are supposedly working to keep the world “safe”. I may come across as rather paranoid in this post, but such are the times.