This is the first article in a series that examines the output of the Brent Ozar Unlimited sp_Blitz™ script run against the SQLServerCentral database servers.
Managing a SQL Server is an ongoing job. While the SQL Server platform will run itself for a long time, as your workload evolves, your applications are patched, and your staff experiences changes, it's easy for configuration and security issues to creep into your environment. The best way to prevent issues and problems is with regular monitoring and auditing of your instances.
The SQLServerCentral servers are regularly monitored, using SQL Monitor from Red Gate Software, and you can see the data at monitor.red-gate.com. We expose that as a demonstration of what the monitoring software can do. This covers alerts and problems that occur in real time, but it doesn't catch some changes which may occur over time.
This is where some type of auditing becomes very important. One of the ways in which you might think about auditing your instances is with a standard script that examines various potential places where your configuration isn't optimal.
sp_Blitz™
Brent Ozar Unlimited has released a fantastic script for examining the configuration, health, and performance of your SQL Server instances. This script is known as sp_Blitz™ and is currently at v16. You can download it and it builds a stored procedure that you can run on any of your instances.
There is a tremendous amount of information returned, and if you are interested, you can read about the script on the Brent Ozar Unlimited site.
We used this script to perform a quick audit of our database server instances and learned a few things. This series of articles examines the results and mitigation strategies we've taken. The other articles are:
- Security
- Reliability
- Performance - Part 1
- Performance - Part 2
- Query Plans
- Informational and other lower priority items
This article looks at the security section.
Security
One of the sections of the result set concerns security. These are the the items listed under the "Findings Group" column as security. We had five sets of results in this area.
- SQL Agent Job Runs at Startup
- Sysadmins
- Elevated Permissions on a Database
- Database Owner <> sa
- Jobs Owner by Users
We will talk about the results and what they mean for us in terms of administering these databases.
SQL Agent Job Runs at Startup
You can configure SQL Agent jobs to run at startup. This is a setting for the Job Schedule in SQL Agent. You can see this below in a properties of a job on my local instance.
These can be a security risk if someone has enabled a job that performs some malicious task on the instance startup, like ensuring that a hacker's sysadmin account exists, and if not, create it.
We have two jobs that run when our instances start up. These are jobs that we've set up and they are appropriate for the operation of our server. However I wasn't aware of these jobs until I ran this script, mostly because we have over 20 jobs running on this Agent. By running this audit, I learned about these jobs and checked with our administrators to be sure these jobs are necessary for our workload.
You can read more about SQL Agent Startup Jobs at Brent Ozar Unimited.
Sysadmins
Sysadmin privileges should be limited to as few people as possible. The more people that have rights to your instance, the more likely that there will be conflicting changes from different administrators. It's also more likely that a hacker can create a new account and it would not be noticed if there are already a dozen other accounts.
For our database instances, we host them with an ISP and do not have this domain linked with our corporate domain. As a result, we have people connecting to these machines with separate domain accounts. Over time, we've added new domain admins, and each has been done ad hoc, rather than with a group. Not a best practice for security, as I would prefer we use roles, but this isn't a big problem for now.
I won't show the result set, but in our list, we did find that there were 11 accounts with sysadmin privileges. That seems like a lot to me, so I checked to see if all the accounts were appropriate for our systems.
In our list there were 5 service accounts, including a system one. Two of these run the two instances, and 3 are for third party pieces of software. These are all acceptable since the software is built to manage or access privileged parts of the SQL Server instance.
Two accounts are for our IT administrators, one is for me, and another is for a former administrator.
Red Alert!
This is a problem. We had an employee leave and while I am sure their corporate account was disabled, and I hope their VPN account was disabled, the account on this server was not. This is a hole in our process, and a hole in our security. After completing this audit, I notified our IT group to remove the account. I could do it, but that's not a good practice. I want the people responsible for this instance to be aware of a change that needs to be made.
I've seen old accounts on many instances in the past at various companies. When someone is not using Windows Authentication and using Windows AD Groups for access, it's easy to forget to remove an account from all instances of SQL Server. Regular auditing will help you catch these security holes.
Update: The old account was removed by our IS department.
You can read more about Sysadmins at Brent Ozar Unimited.
Elevated Permissions on a Database
Security best practices say that the principle of least privilege should be applied everywhere. However I have often found that many software applications don't adhere to this. Developers are quick to build with sysadmin or db_owner privileges, even when they aren't required.
In examining our server, I did find that there are nine instances of accounts with db_owner in various databases. These are a total of 5 accounts here, one of which looks like it was a migration account used to move data from our old databases to these databases. I've sent a note off to determine if this account is still being used.
There are three other accounts that are used for our software and I suspect db_owner isn't needed, but getting this changed, tested, and verified is not something I can do in the short term. I've noted that this is a problem and notified people that future development should use a less privileged account. We'll see if that makes a difference.
One account is an administrator that also is a sysadmin, so there is no issue here, but I've asked this privilege be removed since it's not needed.
Update: The account was removed by our IS department.
You can read more about Elevated Permissions on a Database at Brent Ozar Unimited.
Database Owner <> sa
The database owner is a bit of a throwback. In most cases there isn't an issue with the database owner (different than the db_owner role) being some login account. However since there are potential problems and it could be a security issue, I've always set these to sa.
On our instance, the SQL Server service account for the database engine owns a number of databases. I've made a request to change these back to sa.
You can read more about the Database Owner <> sa at Brent Ozar Unimited.
Jobs Owner by Users
When a job is owned by a user during setup, things appear to be fine. There's no issue and the jobs run. The problems often come a year or two later when the user account is changed, removed, disabled, etc., and the job starts to fail. If you have monitoring with notifications in SQL Agent or software like SQL Monitor, you'll be alerted when the job fails, but if your luck is like mine, you'll be notified at 2am.
The best practice for me has always been to be sure that a service account, or a built in account like sa owns all of the jobs on the server. We had three jobs, all owner by me, on the server instance. These were business jobs that performed data changes for various business purposes at SQLServerCentral. I set them up, and that means these were my fault.
I change the ownership of the jobs to sa to prevent future issues.
You can read more about Job Owner by Users at Brent Ozar Unimited
Conclusion
This article looked at the security output from the sp_Blitz™ script and it's effect on the administration of our instances. In future articles, we'll examine the other sections of output from the script.
This is a great script to run on your instances, but you need to run it periodically to catch changes, some of which might potentially be causing you problems, or will cause you problems in the future. Saving your output from previous runs and comparing the results and looking for changes is a good idea to save during future analysis.
The other articles in this series are: