Voice of the DBA

Tag: Redgate

Monday Monitor Tips: CIS Compliant

A new feature added to Redgate Monitor Enterprise automatically. CIS compliance is something many enterprises think about as their auditors use this as a benchmark.

If you’ve never looked at the Center for Internet Security, you ought to glance at them, and check out the benchmarks they have for many systems.

This is part of a series of posts on Redgate Monitor. Click to see the other posts

The CIS SQL Server Benchmark

You might get asked by an auditor how you know your SQL Server estate is secure. There are lots of things you can do, but an easy one is being CIS compliant. There are benchmarks from CIS for many SQL Server versions. You can download the benchmark from CIS as a PDF, go through it, and then start to compare that to your SQL Server instances.

That’s not complex, but it is complicated. Lots of moving parts, where do you keep the benchmark data, how do you compare it to your instances, how do you ensure it’s up to date or get notified if it’s not?

This is a simple job, but labor intensive, boring, and tedious. There’s a better way.

Redgate Monitor Compliance

We’ve added a compliance section to Redgate Monitor, which I’ve written about in terms of looking for older versions. However, we also have added to this section with a CIS Benchmark template.

At the top of Redgate Monitor, there is a Security section and Compliance is under this.

When I get to the compliance screen, on the right side, I have a drop down for the templates. We’ve pre-loaded the CIS Benchmark in here. I can select that to see how compliant I am.

In this case, I’ve filtered to the SSC servers and when I do that, I see that I’m mostly compliant, but just barely. I say me, but this is our IT group that manages the config.

There is a disclaimed at the top, which you should note. It links here, where the docs note that this is a template that cannot be deleted or changed. It can be duplicated. Note, this is only for SQL Server 2022.

If I click a server, I see the details of where and where not I am compliant. In tis case, things like database mail ought to be disabled.

I can’t change things from here, but I can export this as a report and work on remediation. If I want to set a template that is like CIS, but I have a good reason for an exception, such as the Cost threshold for parallelism set to something different, I can duplicate this template and alter it.

Summary

Auditing and compliance are becoming more important at many organizations, especially in light of the main data breaches and other issues that many organizations have experienced. This might even be required by insurance companies who want to ensure that you have not left open configurations that might become attack vectors.

If you haven’t tried the compliance templates in Redgate Monitor, give it a try, or have a play at monitor.red-gate.com..

Redgate Monitor is a world class monitoring solution for your database estate. Download a trial today and see how it can help you manage your estate more efficiently.

December 23, 2024
Monday Monitor Tips: Am I Patched?

One of the things that I think is neat is that Redgate Monitor helps you track patching on your systems. This is something that has been challenging in every position I’ve had, with some systems being forgotten or remaining unpatched for too long.

This post looks at how you track patches and versions.

This is part of a series of posts on Redgate Monitor. Click to see the other posts

The Challenges of An Estate

There are two aspects to tracking your systems: the version and the patch level. Microsoft releases versions periodically and unless you’re in the cloud with a PaaS service, you may or may not have just one version of your database platform. Azure SQL Database is evergreen and updates every quarter or so.

If you install SQL Server 2016, unless you upgrade, it stays at 2016.

The second part is the patch level. SQL Server 2016 has had 3 service packs, multiple CUs in between those, and a few post-SP3 security patches this year (2024). Are you up to date?

It’s a good question since you might be vulnerable to issues, and you certainly can be out of compliance with auditors if you aren’t patched.

What Systems Are Behind?

The estate tab has a versions page, in which we list the installed versions of SQL Server. I hope PostgreSQL is coming soon as well (and others). Here is the overview, where you can see this estate spans SQL Server 2008 R2 to 2022, and includes the cloud.

This helps me with upgrades, as I can see which systems might be old and in need of an upgrade plan. I can filter at the top for different groups, tags, etc., but I can see what I have, and I get quick links to the current patch.

Below this, I have details. Here is where I can dive down to individual groups and systems to see if they are patched, how long ago the last patch was released, and the end of support

This is a good way for me to see at a glance how patched I am. The yellow up arrows mean I need to patch. The green check mark means I am patched.

This is a good view so you can tell how out of date you are. It’s one thing if there are patches released within the last month and not applied. It’s another thing when you have systems that are months or years out of date.

Using This Data

This isn’t something I’d check every day or week, but I would set reminders to have this monitored monthly and have plans in place to get patched. While lots of patches might not affect security, they often to affect support if you need it, and certainly these affect compliance and auditing.

Even if no one audits you, if you have an issue and you aren’t patched, someone will use that as an excuse to blame you in some way. Get patched, at least within 60 days if not 30.

BTW, this data is maintained by Redgate and updated as patches are released. Redgate Monitor downloads a file that populates the latest patches for each version. If your Redgate Monitor Base Monitor cannot reach the Internet, you can update this yourself by downloading this file and copying to your system.

Summary

This short posted highlighted what data you get about versions and patches, and my recommendation, which is to review this monthly.

Having Redgate Monitor keep all this for you is nice and helps you keep a healthy, up-to-date estate.

Redgate Monitor is a world class monitoring solution for your database estate. Download a trial today and see how it can help you manage your estate more efficiently.

December 9, 2024
Adding Test data to msdb.sysjobhistory
One of our internal people was looking to test some process in (I assume) Redgate Monitor and needed more job history than they had in msdb.sysjobhistory. They wanted to use SQL Data Generator to help, but couldn’t make it work.

This gives the solution I sketched for them. It can work with some other system tables, but not all. Many system tables do not allow user data to be inserted. Some do.

The Main Problem

The main problem here is that SQL Data Generator doesn’t see system tables. If I open a project in msdb, I see this:

That matches what I see in SSMS. Only user tables.

However, I know I can do this and it works.
```
INSERT INTO dbo.sysjobhistory
  (job_id, step_id, step_name, sql_message_id, sql_severity, message, run_status, run_date, run_time, run_duration, operator_id_emailed, operator_id_netsent, operator_id_paged, retries_attempted, server)
VALUES
  ('EA6B3BC3-D358-4B0C-A793-8C3C558098AB', 0, 'mystep', 0, 0, 'Executed as user: NT Service\SQLAgent$SQL2022. The job script encountered the following errors.'
  , 0, 20241111, 175438, 0, 0, 0, 0, 0, 'ARISTOTLE\SQL2022')
```
Now, how to get SQL Data Generator to help?

A Little System Table ETL

Since I know I can insert data into the table, how can I generate data? Apparently SQL Data Generator cannot read these tables, but I can use a trick that I’ve used in the past.

First, I’ll run this in msdb:
```
SELECT *
  INTO mysysjobhistory
  FROM dbo.sysjobhistory AS s
  WHERE 1 = 0
```
This code will make a copy of dbo.sysjobhistory with no data. However, this is a user table. Once I do this, now I can refresh SQL Data Generator and I can see my table.

Now I can use the settings to get the type of data I want. Here’s a preview of some data I set, using the data in my existing table.

Now, I can click generate data and I have data added to my user table. If I query this table, I see data:

The last step is to move this data. I’ll use this query:
```
INSERT dbo.sysjobhistory
   (job_id, step_id, step_name, sql_message_id, sql_severity, message, run_status, run_date, run_time, run_duration, operator_id_emailed, operator_id_netsent, operator_id_paged, retries_attempted, server)

SELECT job_id, step_id, step_name, sql_message_id, sql_severity, message, run_status, run_date, run_time, run_duration, operator_id_emailed, operator_id_netsent, operator_id_paged, retries_attempted, server
  FROM dbo.mysysjobhistory AS m
```
Once this runs, I can see the data in msdb.

Of course, I’d also have to populate sysjobs if I wanted this linked to a job and shown in the Agent Job History Viewer.

Summary

This post showed how I’ve sometimes worked in situations where I couldn’t directly access a table from an application. In this case, I want to get data into sysjobhistory, but SQL Data Generator doesn’t support that directly. My solution:
1. make a copy of the table
2. insert data into the copy
3. move the data into the original
This has worked in a few situations as well where I might need to move/stage data before it gets into an application table. In this case, we wanted to generate some random history for Redgate Monitor to read.

This can work for other tables as well, as long as you can insert..select into them.

SQL Data Generator is a neat tool to generate data quickly for a variety of purposes in SQL Server. Give it a try, especially if you already have the Toolbelt Essentials.
November 27, 2024
Using SQL Compare with Read-only Access

Recently a customer asked if SQL Compare and SQL Data Compare can be used with a read-only database as a source. It’s a good questions as I’ve seen some tools that create temp tables or do some other work in a source database, which might cause problems. Certainly someone running SQL Compare against production would want to ensure it works as a read-only application.

This post will look at SQL Compare with a read-write database but a user account with read-only access. My previous post looked at a read-only database.

This is part of a series of posts on SQL Compare.

Setup

I’ve got a couple of databases that I use for Compare demos. In this case, compare5_prod and compare1. The compare5_prod is set to read write, which is normal.

I created a new login, which will default to the Compare5_prod database.

This login maps to a user, which has read only rights in the database.

In my compare setup, I’ll use this login to connect to the database.

As you can see below, Compare works fine, even reading the various system metadata tables:

This works because SQL Compare is not writing anything to the database. We read metadata and then process that in-memory on the client before returning the results.

You can see this also works in SQL Data Compare. Here’s the connection:

And here are the results

Summary

This was a very simple example, but I find that clients always would prefer to see examples already completed and proof that something works when they are evaluating software. Hopefully this helps answer this question.

SQL Compare is an amazing tool that millions of users have enjoyed for 25 years. If you’ve never tried it, give it an eval today and see what you think.

November 20, 2024