Product Blog

2 Posts authored by: sqlrockstar Employee

As a production database administrator for many years, I was tasked with security requests. These requests ranged from “who changed what” to detection of SQL injection attacks. The role taught me how proper data security is a never-ending job, requiring the right tools and knowledge.

 

This is one reason I advocate the use of Security Event Manager (SEM) to help with database security requirements. With SEM you can use the SQL Audit Events connector to monitor for security events. The previous version of the connector required a server-side trace to capture events related to schema changes, user changes, and failures for any query activity.

 

The latest version of SQL Audit Events connector allows for using SQL Server Audit instead of a trace. SQL Server Audit is a great feature, but a bit cumbersome to work with if you haven’t before.

 

The first step is to create a Server Audit. This is the “kitchen sink” for SQL Server Audit, as it catches events and determines where to send the event output. The SQL Audit Events connector requires the SQL Server Audit output to the security or application event log on the server. One thing to note here - the Windows event log can fill up and be overwritten. Make sure you have modified the retention policy accordingly before you flood the event logs with audit events from SQL Server. It’s also worth noting that the Windows Application event log is less secure than the Windows Security event log - any authenticated user is allowed to read and write from the Windows Application event log.

 

After you have created the Server Audit, the next step is to create either a Server Audit Specification or a Database Audit Specification. The Server Audit Specification is for events affecting the instance of SQL Server, and you can only have one Server Audit Specification output to one Server Audit object. The Database Audit Specification is for events affecting a specific database, and you can have multiple Database Audit Specifications output to a Server Audit object. Here’s what it all looks like:

 

 

The full list of SQL Server Audit action groups and actions can be found here. It is difficult to list out the specific groups and actions, as each company will have different requirements. But there’s a few I would suggest you consider.

 

First, start by auditing the audit. You will want to know if the audit has been turned on or off, or if it has been altered in any way. You will use the AUDIT_CHANGE_GROUP for this task.

 

Next, you should set up a Server Audit Specification for events that affect the entire instance. I recommend the following:

 

FAILED_DATABASE_AUTHENTICATION_GROUP

LOGIN_CHANGE_PASSWORD_GROUP

SERVER_PRINCIPAL_CHANGE_GROUP

SERVER_ROLE_MEMBER_CHANGE_GROUP

USER_CHANGE_PASSWORD_GROUP

 

Be mindful that a busy server will flood your event log. Be precise with what data you want to collect. While it is possible to collect events at a server instance level for all database activity, doing so will flood the event log. That’s why I recommend using Database Audit Specifications inside of the databases you want to audit. These are the groups you should consider at a minimum:

 

DATABASE_OBJECT_CHANGE_GROUP

DATABASE_PERMISSION_CHANGE_GROUP

DATABASE_PRINCIPAL_CHANGE_GROUP

DATABASE_PRINCIPAL_IMPERSONATION_GROUP

DATABASE_ROLE_MEMBER_CHANGE_GROUP

 

You must review the groups and actions to decide if they meet your auditing requirements. The ones I have listed here are meant as a guide, a starting foundation upon which to build.

 

You will notice I didn’t include any groups or actions regarding query activity, such as a SELECT statement. I don’t like the idea of capturing that anything that has query data, especially update or insert data, and allowing that text stored in an event log or inside the SEM database.

 

SQL Server Audit is a great tool that doesn’t get enough love and attention, in my opinion. To me, the strength of this feature is how you can extend it to do things like auditing SQL Agent jobs. I’ve written an example here: https://thomaslarock.com/2017/10/audit-sql-server-jobs/

 

The downside to SQL Audit is the reporting and viewing of the audit event data. SQL Server Management Studio has a log viewer, but the user experience can be frustrating at times. By using SEM we create a better user experience. Not just for viewing event data, either. SEM allows for the creation of Correlation Rules, allowing us to automate actions to take if a specific event occurs. Here’s an example:

 

 

I can create a custom rule that would trigger an action, in this case I will have an email sent should a database object change event is found. You can’t do that out of the box with SQL Server Management Studio.

 

If you are using SQL Audit, you should give SEM a trial and discover what is possible. If you are using SEM, you should consider leveraging SQL Audit to enhance your security. Together, SQL Audit and SEM offer you the opportunity to lower your risk of loss due to a data breach.

SQL Server upgrades are a pain, I know.

 

And boring, too. It’s not very exciting to watch a progress bar.

 

Many people put off upgrading SQL Server. They wait for a business reason or an important security patch. Or, as was the case historically, they wait for the first service pack. After all, if it ain’t broke, don’t touch it.

 

I’m here today to tell you those days are over.

 

No longer can you sit back and allow systems and applications to lag behind with regards to patches and upgrades. You must stay current. Allowing applications to be more than one major version behind puts you, and your systems, at greater risk for security threats than ever before.

 

Microsoft has made it easier to upgrade and patch SQL Server. They’ve removed service packs, opting instead for cumulative updates. By shifting to a model that is similar to continuous deployment, Microsoft is able to deliver features, performance improvements, and security enhancements at a faster rate than ever before.

 

So, if you are waiting for SQL Server 2017 SP1, you’ll be waiting forever.

 

Don’t wait. Get started on upgrading SQL Server to the latest version today.

 

Let me help you understand just a few of the reasons why upgrading SQL Server is right for you.

 

Reasons for Upgrading SQL Server

As I mentioned before, it’s just common sense to stay current with the latest version of SQL Server. Microsoft has built tools like the Database Migration Assistant to help make upgrades easy. Applying cumulative updates has also been simplified. And because Microsoft hosts millions of database workloads inside of Azure SQL Database, you can be assured that these updates have been tested thoroughly.

 

Here’s a handful of the features available, out of the box, when you upgrade to the latest version of SQL Server.

 

Automatic database tuning – The ability for the database engine to identify and fix performance problems.

 

Adaptive query processing – While processing the execution plan, SQL Server will adapt query plans as necessary, essentially tuning itself instead of reusing the same plan.

 

Data security and privacy featuresAlways Encrypted, Dynamic Data Masking, Row Level Security, Data Discovery and Classification, and Vulnerability Assessment are all new, and all awesome.

 

Those are just a handful of the improvements. You will also find things like faster DBCC CHECKDB, improved backup security, and a new cardinality estimator. All those are great features worth your time for upgrading.

 

But there’s one more thing: the Orion® Platform.

 

See, we’ve been busy refactoring the Orion Platform to take advantage of newer SQL Server features.

 

Reasons for Upgrading Your Orion Installation

When I’m at an event performing demos, I am surprised how many customers haven’t upgraded to the latest version of the Orion Platform. Of course, I understand the many reasons why upgrades are put on the back burner.

 

I’m here today to help you understand that there’s more to the latest Orion version than a few fancy screens.

 

By using columnstore indexes, we have reduced the size of the Orion database (up to 33% less space), the amount of time it takes to perform maintenance (up to 6x faster on average), and the amount of time to retrieve data (up to 10x faster). That’s a lot of performance gains.

 

Table partitioning allows Log Manager for Orion to scale, accommodating multiple log sources, and the ability to quickly display all logs in time sequential order. As anyone that has had to analyze logs will tell you, it’s important to be able to quickly see all events in the exact order they occurred.

 

Also, in-memory OLTP helps products that leverage the Orion Platform achieve a high rate of concurrency, accelerating performance and scalability.

 

Those features sound great, but don’t just take my word for it. You should read about the SQL Server features being used by NetFlow Traffic Analyzer (NTA) over at this FAQ page.

 

Now, at the bottom of that page, I want to call out something else that you will find interesting…

 

“You can install your NTA Flow Storage database and your Orion database in the same instance of MS SQL, provided that instance is an MS SQL 2016 SP1 or later version.”

 

That’s right, upgrading to the latest version of NTA allows you to consolidate your SolarWinds footprint. For customers paying by the core for SQL Server licensing, this alone should motivate you to upgrade.

 

I’ll make it easy for you: here’s a link to help you get started. Also, here’s the official upgrade guide located on our Customer Success Center.

 

I’ve also written some other in-depth posts about tips and tricks on upgrading SQL Server. Have a look—I believe you’ll find the information useful.

 

Summary

At the end of the day, we want the same thing that any company would want: happy customers.

 

By upgrading to the latest version of SQL Server, and then the Orion Platform, our customers can see benefits immediately. Not just in performance, but in your wallet.

 

Continuous improvement is the world in which we live now. Stop thinking of upgrades as a chore or a task to get past. Upgrade because you want to, not because you have to.

 

The SolarWinds trademarks, service marks, and logos are the exclusive property of SolarWinds Worldwide, LLC or its affiliates. All other trademarks are the property of their respective owners.

Filter Blog

By date: By tag:

SolarWinds uses cookies on its websites to make your online experience easier and better. By using our website, you consent to our use of cookies. For more information on cookies, see our cookie policy.