May PowerShell: Remove PowerShell V.2

PowerShell - @SeniorDBA

Note: For the month of May 2019, I’m focusing on PowerShell information that could help you better utilize this powerful scripting tool in your environment.

Microsoft recommends you no longer use PowerShell V.2 for security reasons, but it is probably installed on your computers.

Microsoft has done a great job of recently adding powerful new security features in PowerShell. It is also obvious that the security features integrated in the latest versions of PowerShell do not apply to the older versions of PowerShell, which makes its use by malicious attackers to target PowerShell v.2 a risk to your computers. The older version of PowerShell does not have native logging capabilities, it remains undetected, and offers stealth in malicious operations so it is often used for lateral movement and persistence techniques.

For these reasons Microsoft decided that PowerShell v.2 is deprecated from the more recent versions of Windows, so it is also highly recommend to check and remove PowerShell v.2 from your environment.

You can check whether Windows PowerShell 2.0 is installed by running the following (as an administrator). Continue reading “May PowerShell: Remove PowerShell V.2”

May PowerShell: Auditing Office 365 using PowerShell and Hawk

PowerShell - @SeniorDBA

Note: For the month of May 2019, I’m focusing on PowerShell information that could help you better utilize this powerful scripting tool in your environment.

Hawk is a Powershell based tool for gathering information related to O365 intrusions and potential Breaches. You can simply use the Hawk Powershell Script that makes use of Exchange Online and Azure powershell scripts to generate the auditing reports you may need when investigating a suspected breach.

Reports Include:

  • CAS Mailbox Info
  • Azure Audit Logs (writes AzureActiveDirectoryAccountLogon: User login events with IP addresses)
  • Mailbox Audit Report (Mailbox login report with delegate and admin actions)
  • User Mailbox Forwarding Information
  • User Inbox Rules Information
  • Mailbox Info
  • Mailbox Statistics
  • Azure Authentication logs report (All authentication activity for the user in RAW + Readable form)

Azure AD reports rely on AAD P1 and P2 licenses, make sure you have the required licenses assigned.

GitHub Repository: https://github.com/Canthv0/hawk

A good starting place is the “Start-HawkTenantInvestigation”, this will run all the tenant based cmdlets and provide a collection of data to start with. Once this data has been reviewed if there are specific user(s) that more information should be gathered on “Start-HawkUserInvestigation”, which will gather all the User specific information for a specific user.

May PowerShell: Scripts for listing all SQL Server Databases and Objects using PowerShell

PowerShell and SQL Server - SeniorDBA

Note: For the month of May 2019, I’m focusing on PowerShell information that could help you better utilize this powerful scripting tool in your environment.

This powerful script lists all objects in an instance and scripts them into a network folder, by date and instance, so you can keep a record of the objects.

Installing PowerShell the SqlServer module:

Install-Module -Name SqlServer

If there are previous versions of the SqlServer module on the computer, you may be able to use Update-Module, or provide the -AllowClobber parameter:

Install-Module -Name SqlServer -AllowClobber

This article by Angel Gomez gives you the script and some information on how to use it.

Continue reading “May PowerShell: Scripts for listing all SQL Server Databases and Objects using PowerShell”

12 Cybersecurity Tips to Stay Secure on the Internet

Hacker - @SeniorDBA

The internet is a wonderful place full of free information, endless entertainment, and useful ways to communicate with you family and friends. There are also people that want to use that wonderous virtual environment to attack the cyber-weak and take what they have for their own profit. You see the news stories almost weekly, where another company has been breached and their customer data has been stolen, stores where companies have been attacked with ransomware and all their files are encrypted until they meet their attackers demands, or just average users bombarded with phishing emails or robocalls.

People don’t always know what they can do protect themselves, so I have collected 10 simple tips that will help guide the average user to a safer cybersecurity profile that will help protect their valuable systems and data from cybercriminals.

Basically speaking, when you want to secure a user, a family, or an entire company you have to first secure the perimeter, then secure the data that enters and exits through that perimeter. Just a few years ago that perimeter was much smaller and easily defined, but with todays services relying on the internet for almost all information like news, weather, movies, emails, file storage, gaming, etc. that perimeter is larger than ever before.

You need to think about how you use the services and systems that you have access to each day and determine what data you share has value, what processes are at a high risk, and how a malicious user might monetize your activity. One basic example is you may use your personal computer to access your bank to transfer money from checking to savings.  The risk is your computer may be compromised and that might allow a hacker to gain access to your bank account to transfer your money to their bank account. A hacker might just gain access to your password and is then able to use your email address and stolen password to log into your bank account from anywhere in the world to open new accounts to borrow massive amounts of money in your name. Continue reading “12 Cybersecurity Tips to Stay Secure on the Internet”

History of SQL Server

Have you seen the video on the history of SQL Server?

History of SQL Server Video

Microsoft released its first version of SQL Server in 1988. It was designed for the OS/2 platform and was jointly developed by Microsoft and Sybase. During the early 1990s, Microsoft began to develop a new versions of SQL Server for the NT platform.

This post has really useful information on the subject of SQL Server history, written by Euan Garden.

The SAF (SQL Admin Facility) interface from SQL Server 1.1:

SAF Interface from SQL Server 1.1

This article lists some early notes about the development:

“While it was under development, Microsoft decided that SQL Server should be tightly coupled with the NT operating system. In 1992, Microsoft assumed core responsibility for the future of SQL Server for NT. In 1993, Windows NT 3.1 and SQL Server 4.2 for NT were released. Microsoft’s philosophy of combining a high-performance database with an easy-to-use interface proved to be very successful. Microsoft quickly became the second most popular vendor of high-end relational database software. In 1994, Microsoft and Sybase formally ended their partnership. In 1995, Microsoft released version 6.0 of SQL Server. This release was a major rewrite of SQL Server’s core technology. Version 6.0 substantially improved performance, provided built-in replication, and delivered centralized administration. In 1996, Microsoft released version 6.5 of SQL Server. This version brought significant enhancements to the existing technology and provided several new features. In 1997, Microsoft released version 6.5 Enterprise Edition. In 1998, Microsoft released version 7.0 of SQL Server, which was a complete rewrite of the database engine. In 2000, Microsoft released SQL Server 2000. SQL Server version 2000 is Microsoft’s most significant release of SQL Server to date. This version further builds upon the SQL Server 7.0 framework. According to the SQL Server development team, the changes to the database engine are designed to provide an architecture that will last for the next 10 years.”

If you are just interested in the sequence of events the following timeline by Raksh Mishra summarizes the development history of SQL Server:

  • 1987 Sybase releases SQL Server for UNIX.
  • 1988 Microsoft, Sybase, and Aston-Tate port SQL Server to OS/2.
  • 1989 Microsoft, Sybase, and Aston-Tate release SQL Server 1.0 for OS/2.
  • 1990 SQL Server 1.1 is released with support for Windows 3.0 clients. Aston-Tate drops out of SQL Server development.
  • 1991 Microsoft and IBM end joint development of OS/2.
  • 1992 Microsoft SQL Server 4.2 for 16-bit OS/2 1.3 is released.
  • 1992 Microsoft and Sybase port SQL Server to Windows NT.
  • 1993 Windows NT 3.1 is released.
  • 1993 Microsoft and Sybase release version 4.2 of SQL Server for Windows NT.
  • 1994 Microsoft and Sybase co-development of SQL Server officially ends.
  • Microsoft continues to develop the Windows version of SQL Server
  • Sybase continues to develop the UNIX version of SQL Server.
  • 1995 Microsoft releases version 6.0 of SQL Server.
  • 1996 Microsoft releases version 6.5 of SQL Server.
  • 1998 Microsoft releases version 7.0 of SQL Server.
  • 2000 Microsoft releases SQL Server 2000.
  • SQL Server 2000 Service Pack 1 – Release date: June 12, 2001
  • SQL Server 2000 Service Pack 2 – Release date: November 30, 2001
  • SQL Server 2000 Service Pack 3 – Release date: January 17, 2003
  • SQL Server 2000 Service Pack 3a – Release date: May 19, 2003
  • SQL Server 2000 Service Pack 4 – Release date: May 6, 2005
  • 2005 Microsoft releases SQL Server 2005 on November 7th, 2005.
  • SQL Server 2005 Service Pack 1 – Release date: March 18, 2006
  • SQL Server 2005 Service Pack 2  – Release date: March 5, 2007
  • SQL Server 2005 Service Pack 3 – Release date: December 15, 2008
  • 2008 Microsoft releases SQL Server 2008 RTM on August 2008.
  • SQL Server 2008 Service Pack 1 – Release date: August 27  2009
  • SQL Azure
  • Microsoft releases SQL Server 2008 R2 RTM on April 21, 2010.
  • SQL Server 2008 Service Pack 2 – Release date: September 29  2010
  • SQL Server 2011, Code name Denali CTP1 Release date: November 8, 2010
  • SQL Server 2005 Service Pack 4 – Release date: December 17, 2010

These is also some (humorous) details from Kevin Kline at this site.

Faster Backups with SQL Server Backup Compression

Compression - @SeniorDBA

Database compression is a feature that Microsoft introduced in SQL Server 2008, but many people still don’t understand or regularly use the feature. The power of this feature is to both speed up the backup process, and to save disk space. The speed benefit is a result of reduced disk activity as you stream the compressed backup file directly to disk. You can use all your available CPU cycles to perform the backup straight to disk, and since the smaller file is saved to disk you will probably reduce any potential delay as you write the backup file to disk. The other obvious benefit is the resulting backup file can also be much smaller. In my experience, I’ve seen compression between 20-50 percent, but you will need to test your backup to determine your real space savings based on the contents of the database and how well your data can be compressed.

Perform a test backup without compression and see how long it takes and how large the resulting BAK file is for your sample database. Then backup the same database using compression to see if it is faster and how much smaller the BAK file is when it is complete.

To create compressed database backups, all you need to do is add the COMPRESSION option to the BACKUP command as shown below:

BACKUP DATABASE MyDatabase
 TO DISK = 'H:\BACKUPS\MyDatabase.BAK'
 WITH FORMAT, COMPRESSION;

Compression has been a feature available with SQL Server 2017 in both the Standard and Enterprise editions:

Feature Enterprise Standard Web Express with Advanced Services Express
Server core support 1 Yes Yes Yes Yes Yes
Log shipping Yes Yes Yes No No
Database mirroring Yes Yes

Full safety only

Witness only Witness only Witness only
Backup compression Yes Yes No No No
Database snapshot Yes Yes Yes Yes Yes
Always On failover cluster instances2 Yes Yes No No No
Always On availability groups3 Yes No No No No
Basic availability groups 4 No Yes No No No
Online page and file restore Yes No No No No
Online indexing Yes No No No No
Resumable online index rebuilds Yes No No No No
Online schema change Yes No No No No
Fast recovery Yes No No No No
Mirrored backups Yes No No No No
Hot add memory and CPU Yes No No No No
Database recovery advisor Yes Yes Yes Yes Yes
Encrypted backup Yes Yes No No No
Hybrid backup to Windows Azure (backup to URL) Yes Yes No No No

You can read more about compression here.

Finding Last Password Changed for an Active Directory User Account

You can check the Last Password Changed information for a user account in Active Directory. The information for last password changed is stored in an attribute called “PwdLastSet”. You can check the value of “PwdLastSet” using the Microsoft “ADSI Edit” tool.

  Continue reading “Finding Last Password Changed for an Active Directory User Account”