The series so far:

1. DBA in training: So, you want to be a DBA...
2. DBA in training: Preparing for interviews
3. DBA in training: Know your environment(s)
4. DBA in training: Security
5. DBA in training: Backups, SLAs, and restore strategies
6. DBA in training: Know your server’s limits 
7. DBA in training: SQL Server under the hood
8. DBA in training: SQL Server high availability options

By this time, we have discussed the importance of understanding your environment. You should have an idea of which databases support what applications, the SQL Server versions they run on, and the relative importance of each database and instance to the business.

Now, let’s talk about protecting the company from losing data due to security flaws.

Database security is a sub-specialty in and of itself, and you can read in-depth work by people like Denny Cherry, Robert Sheldon, Chip Andrews and numerous blogs. This will be a general discussion to help you get started. To begin the journey of a thousand miles, though, I will give you the first steps, and let you refer to the experts to address your individual needs. I’ll start with the mile-high view and work my way down, which should help you to provide the best measure of protection to your environments.

Physical security: Who can walk in the door?

Start with your physical security. Who has access to your physical servers or data centers? You need this list, and you want it to be as small as humanly possible. If there is no list, make one! Be sure to put policies in place to review this list on a semi-annual basis, and to review procedures for monitoring and auditing physical server/data center access. You should know who was in your server room, when and why. If your physical security is ever breached, your monitoring and auditing procedures will be instrumental in catching the culprit. Data is a company’s most valuable asset. This is worth the investment.

If you are completely in the cloud, you may think you are safe. You aren’t. Even if your servers are not physically accessible, it doesn’t mean they cannot be accessed. Whatever your physical setup, you should at least do an annual (unannounced!) penetration testing to see just how physically secure your company is. A good “pen tester” is worth every penny, because they will show you where the holes are in your security, giving you time to fix them before trouble really hits. You may think, “But I have a small company, where everyone knows everyone! What good will that do?” You will probably be very surprised. You can easily find stories of employees who, out of politeness, held a door open for the person who “just happened” to walk in behind them, thinking they are a new employee (or not noticing that they are unfamiliar at all). Other stories abound of employees who were careless with leaving their monitors unlocked or their badges unsecured at their desks. Perhaps you have heard the one about the person who kept the password to their computer on a Post-It on the monitor (or in their top desk drawer, etc.). Before you think it won’t happen to you, know that pen testers are very good at what they do; people are tricked all the time! So please make this an important line item on your list and do it at least yearly. People can become complacent over time with a false sense of security, so keeping awareness high is key.

Data file security: Who can get in your files?

Now that you are addressing your physical security, start thinking about the security of your data files. Your Infrastructure team may be handling this already. If so, it would be helpful to have an idea of what those measures are. At a minimum, it would be good to know who has access to your data and log files at any level – even if you have encrypted all of your data. What measures are in place to detect and alert you to unauthorized access into your data files? If you aren’t aware of any such measures, now would be the best time to work with Infrastructure to put those steps in place if possible.

What about backups files? Make sure that they are protected just as well as the data and log files. You also need to think about copies of production that might be made for development, QA, or testing. Are backups given to developers to restore on their laptops? To make sure that down level copies are production are protected, think about using a tool like Redgate’s Data Masker to sanitize sensitive data.

You also want to know what measures are in place to protect against malware, bots and the like. If your company allows pretty much anyone to download anything they like, the risk of these threats is higher to your data. The best hackers can get in and out with hardly a trace through some pretty stringent conditions, so don’t make it easier for them. Be the one to suggest some controls to keep at-risk software out of your corporate environment.

Keep your SQL Servers as current as possible

There is a very good chance that you will find a mixture of instances of SQL Server where you work. This is usually due to what vendors will support for their applications. You then ask the vendor to be able to upgrade the SQL Server instance or compatibility level for their database, only to hear a flat-out “no”, or “If you do this, you do it at your own risk – we won’t support it.” Accepting defeat, you go on with life and pray they will be ready to upgrade their product soon. The only problem is that if you run into trouble with the instance and have to open a ticket with Microsoft, the first question from the support staff is going to be, “What version of SQL Server are you running?” If you are running an instance that is in support but not current, they will likely first tell you to patch your instance and bring it current. If an instance is out of support, you are on your own.

Worse, you are vulnerable.

A couple of years ago, the discovery of a couple of processor security bugs called Spectre/Meltdown set the IT world on fire. Microsoft quickly responded with patches – but the patches only went back to 2008. If you had a 2005 instance that was not running on a 2008 + Windows server, you were out of luck. This situation strongly argues for isolating databases for older applications that will not support an upgrade – or for considering the possibility of a different product.

Keeping your patching as up to date as possible is one of the best things you can do to protect your servers. By saying that, I am not advocating to throw the patch on production the day after release. Start by waiting a week or so after the patch comes out to check for reported issues. If there are going to be problems, you should begin to hear about them online within that period, or you will see that Microsoft rolls out a new patch almost immediately. Then begin with a dev environment (after taking a server snapshot for rollback, if you can). See how things go for a week or so. Then patch test (if you have a test environment), and slowly move your way up. By the time you are ready for production, you will have a well-tested patch.

You might be wondering by now how to know when new patches are available. I like https://sqlserverupdates.com/ for this. This part of the site does a great job getting you started with patching and answers some frequently asked questions. Try to build a patching schedule (at least four times a year) and ensure that you stick to it. Your servers will thank you.

If you happen to use SQL Monitor, you can also use the Estate pages to keep track of updates and the current version of all your SQL Server instances.

Server and database security

You have done what you can to keep the criminals out of your physical environment and your files. It is time to begin looking to your server and database security.

In SQL Server, you have logins and users. Logins are groups, service accounts or individuals who can get into your server. Users are groups, service accounts or individuals who can access one or more databases. One login can have more than one user name, but they can only use one user name per database. This basic query will get you the login information on your servers:

Querying sys.database_principals will show your database users:

Just as you can have logins who can access the server and users that can access one or more databases, you also have server-level roles, that apply to the whole server, and database-level roles and permissions, that apply only to their specific databases. Permissions may be given through role membership or individually. For instance, to give someone a server-level role:

To do a database-level role membership:

Alternatively, to grant a specific permission:

These operations can also be done easily through the GUI by going to the properties or a login or user, depending on your scope. Some DBAs prefer the GUI as it can help to reduce errors.

Granting permissions is easy. Keeping a tight lid on them is not so simple. You should regularly review who can do what on your servers. How often is a matter of how much time you have, but try to set a regular schedule if possible. Security monitoring and reporting (which we will discuss later on) can really help you here. Try to be one of the first to know if someone leaves the company, so that you can disable SQL Server access. This is not just a best practice, but also a practicality: disgruntled employees are responsible for some of the worst attacks, so staying on top of this could save you a very bad time later on.

Starting at the top: Do you know who has sysadmin on your servers? Whoever has sysadmin rights can do anything, including dropping databases and tables, among other disastrous things. These people could take the business down for a protracted length of time. There should be a documented list of who is in this server role and it should be very small, not to mention periodically reviewed.

Sysadmin may be the most dangerous permission, but it is not the only one. You want to know who has these, too:

• serveradmin – Members of the serveradmin role can change your server configurations and shut it down.
• securityadmin – Anyone in this role can assign most permissions and allow people on to your server in the first place.
• processadmin – These guys can end anything running in SQL Server.
• setupadmin – Members of setupadmin can spin up or remove linked servers with TSQL (but not using SSMS).
• bulkadmin – Bulkadmins can bulk insert data.
• diskadmin – Members of this role can manage your disk files.
• dbcreator – Just what it says. Anyone in this role can create (but also alter or drop!) any database.
• CONTROL SERVER – This is one level below sysadmin. There are some differences, but CONTROL SERVER encompasses all the roles listed above except for sysadmin.

Now, for the database-scoped roles:

• db_owner – Can create and drop objects and change configuration and maintenance on the database.
• db_securityadmin – Can modify custom role membership and manage permissions – including their own.
• db_accessadmin – Can add/remove access for Windows and SQL Server logins/groups.
• db_backupoperator – Can backup the database (possibly breaking your backup chains).
• db_ddladmin – Can create objects (or remove them) in the database.
• db_datawriter – Can INSERT, UPDATE or DELETE data in all user tables.
• db_datareader – Can read anything in any user table.
• db_denydatawriter – Cannot INSERT, UPDATE or DELETE data in any user table.
• CONTROL DATABASE: Encompasses everything in the database roles above.

Note: Server and database roles are fixed by Microsoft and cannot be changed.

These roles apply to every database, but there are some roles that you will only find in the msdb database. The msdb is part of the system databases, which we will discuss in more depth in another article. The msdb controls all agent jobs and stores information on your backups, SQL Server Integration Services (SSIS), data collector, policy-based management and database mail information. The msdb is a unique database for this reason. Because of all of the things it stores and controls, msdb needs some special roles of its own:

• db_ssisadmin – Can do anything to any package. Could possibly elevate their own permissions to sysadmin.
• db_ssisltduser – Can enumerate all packages, but only view, execute, export, own or delete their own packages. May import packages.
• db_ssisoperator – Can see, execute, export and enumerate all packages. Can execute any package in SQL Server Agent.
• SQLAgentUserRole – Can create/modify/delete their own local jobs and schedules. They cannot control ownership, and their role does not extend beyond the server where their role membership exists. They can enable or disable their own jobs and schedules, and edit their properties, as well as execute, stop and start them. They cannot delete job history on their own jobs unless they are granted that permission.
• SQLAgentReaderRole – Members of the SQLAgentUserRole, the SQLAgentReaderRole can create/modify/delete their own jobs and schedules. They can enable or disable their own jobs and schedules, and edit their properties, as well as execute, stop and start them. They cannot delete job history on their own jobs unless they are granted that permission.
• SQLAgentOperatorRole – Also a member of SQLAgentUserRole, the SQLAgentOperator has all of the permissions of the User and Reader roles, and can also view properties for operators and proxies, and see available proxies and alerts. They can start, stop or execute any local job, and they can delete the job history. They have do not have access to the Error Logs.

Finding permissions

I may have frightened you enough by now that you do not even want to give db_datareader permissions. Good! There is a concept called the Principle of Least Privilege which (put succinctly) says not to give any more permissions than what is absolutely needed. So role membership should be sparing!

Now, you may be wondering: how do I find out what permissions everyone has? After all, permissions don’t (and shouldn’t) just come through role memberships, and you may be scaring yourself imagining what your users are doing in your databases – or worse, what they could do, without realizing the danger. For server-level roles, you can use this query from Books Online:

For the database permissions and roles, I have a script that can help. It’s based on one that I found on Stack Overflow some time ago, but I modified it to look throughout an entire instance and to allow me to query it by database(s) or user(s). That way, you can get all the permissions on an instance (or database(s)) for a user or group. It will take you all the way down to the column level.

Now, here is the script. It is not fast, especially if you run it with the WHERE clause commented out. You’ve been warned.

If you want to get an overall picture of where things stand with all your databases, you might consider running this with the WHERE clause commented out, so you get everything. Between these two queries, you should begin to build a picture of who can do what.

The first thing you want to look for is sysadmin permissions. Whenever a user or an application insists that it needs sysadmin permissions, question it. Often, that is not the case. For instance, the account running the Agent jobs will need sysadmin. You as a DBA will sometimes need sysadmin (i.e., when working with replication, viewing error logs, reading audit logs in SSMS, etc.). Even then, you will want to restrict when you use those rights, so consider having an alternate account for yourself that has sysadmin for only those occasions, and give your regular account reduced permissions to allow you to do your everyday tasks. If nothing else, it will make you think about what you are doing when you use that sysadmin account and lessen the risk of you making a mistake. Try to narrow that sysadmin membership as low as you possibly can. I have spoken to DBAs who work at companies where everyone has sysadmin. That means every one of those people could, by either mistake or design, drop every table in the enterprise or mess the data up badly enough to bring the business to a halt. If that does not scare upper management, I don’t know what will.

Once you have reviewed the sysadmin membership list, look at the other server role permissions and narrow those down. Then go on to the database permissions. The ultimate goal is to classify your end users into logical groups and give the collective groups only the permissions they need rather than to do individual permissions. In that way, if a person leaves or switches job positions in the company, they can be removed or added to the groups they need without having to do individual permissions auditing. Keep records of your SQL Server users (including any linked server users and the distributor admin for replication) and their passwords securely stored and access to them tightly controlled.

Depending on the state of your instances and databases, this step can take a good deal of time. Be patient – and persistent. Your data’s safety depends on you.

Be your authentic(ated) self

In a perfect world, you would only use Windows authentication on SQL Server. Using Windows authentication means that Windows verifies the user’s credentials in order to log into SQL Server. It is the safer way to go, and what you should do whenever possible.

If you are not there yet, then try to minimize the number of SQL Server accounts you have. Enforce your password policies when you make the accounts and make them change their passwords at least yearly if you can. Disable the sa user if at all possible.

Security monitoring

Now that you know who should be able to do what, think about how you will know if someone is trying to do something on your servers that they shouldn’t. What if a pen tester tried to get into one of your SQL Servers – would you know? Write jobs that look for failed logins and alert you regularly. I am a huge fan of Brent Ozar’s First Responder Kit. Among many other issues, it will catch elevated permissions and other security-related concerns and put them in a table for you to review. You can then report or alert on that data to help keep yourself on top of things.

If you are not ready or able to buy security monitoring tools, you might check out the EPM Framework. It is a free and easy way to set policies in SQL Server and ensure compliance. There is a good course in Pluralsight that will walk you through everything you need to know, should you have questions.

If you do have SQL Monitor in place, you can monitor when an account is added to sysadmin, failed login attempts, or anything you can think of with a custom metric.

Dangerous coding practices

Some practices can undermine all the careful work you have done so far without proper precautions. While I do not recommend saying no to all of these out of hand, you should know the risks they can present, and how to use them properly. This list is by no means comprehensive but should get you started.

Dynamic SQL

As a DBA, I have written a metric ton of dynamic SQL over the years. What is dynamic SQL? It is when you use a mixture of parameters and strings to construct and run a query. Sometimes, it is the best way to get things done. I happen to like it for optimizing some problem queries as well. You saw me use it in the query above to get all the permissions on an instance.

The risk here lies not the use of dynamic SQL itself, but in using it wrong. What is the wrong way to write dynamic SQL? This can go off the rails in a few ways:

• Using EXEC (string). This is only there for backward compatibility. Use EXECUTE sp_executesql instead. It can be much safer.
• Using unparameterized statements.
• Using dynamic SQL in ad-hoc statements.
• Not reviewing code to find vulnerabilities

Dynamic SQL needs to be tightly controlled, preferably executed by a login-less user that has very tightly restricted permissions and parameters that are sanitized with checks for things like semicolons, double dashes, asterisks, pound signs and keywords like DROP, DELETE, ALTER, TRUNCATE, GRANT, FROM, UPDATE, CREATE, etc.

Why? Two words. SQL Injection.

Unfortunately, if your company is hacked because of SQL injection, the hackers will not be the first to be blamed. You will. Spare yourself a bad day at the office. Erland Sommarskog and Bert Wagner have some great explanations and demos to share if you want to learn more.

xp_cmdshell

Think of granting access to xp_cmdshell as granting access to SQL Server’s OS. Unless there is a good reason to use it (and your security around it is good), this should be disabled in the configuration settings. However, this still requires monitoring (even after disabling). Steve Stedman has a good demo explaining how xp_cmdshell can still be used after disabling it and some preventative measures you can take to reduce your risks.

Remote queries

Any time you allow one SQL Server instance to query another one, you have opened a potential vulnerability for a hacker to jump servers. You may or may not be able to say no to this. If you cannot, ensure that your linked server security is as good and controlled as it can be, and that your network security is as solid as possible, making it as close to impossible for the black hats and the script kiddies as you can. Certainly, try to avoid allowing anyone to do anything but read across a linked server.

CLR

CLR stands for Common Language Runtime. CLR is a way to host .NET code in SQL Server. The possible danger comes as an added vector into your instance and not necessarily knowing what code is executed. You will want to monitor for these (again, sp_Blitz will catch them) and check them to ensure they are expected (and performant).

User-owned schemas, jobs, databases (or anything)

SQL Server users shouldn’t own anything. They are there to work with data, not to own it. When the end-user inevitably leaves the company, at the very least, you won’t be able to drop their accounts until you fix this, but at worst, it gives them a level of access that they just do not need – and you don’t want. Make a low-privileged user without a login or a (disabled!) sa the owner of databases and database objects whenever you can. It’s also possible to rename the sa, disabled or not.

Auditing

SQL Server auditing has been available in all editions at the server level since 2012, and fine-grained auditing became available in all editions with the release of SQL Server 2016 SP1. Take advantage of this feature in SQL Server and keep those records so that you know who did what, when and why. They will be invaluable if something goes wrong, if you are asked who dropped that table, or if other mischief occurs. This article will walk you through it!

Were you born in a barn? Shut the door!

How many times did you hear your mom or dad say that to you when you were a kid? They didn’t want flies in the house (and they certainly didn’t want the electric bill). Do not give the criminals more ways of getting in your servers than you must. Disable SQL Server browser (unless you have more than one instance on a server, in which case it must be on). Additionally, do not install every available feature when you install SQL Server – only put on what you need. You can go back and easily install other features such as Analysis Services or SSIS later, but there is no reason to have them on and to maintain them if no one is using them.

From a different perspective, if you have any individual permissions or users in your databases, you need to know when that person leaves the company, and have their access removed as soon as possible. This includes either disabling or – preferably – dropping the login and user. You have to do both. Dropping the login doesn’t drop the user from the databases; you are better off writing a script to drop the user where it exists in all the databases, then finally dropping the login. This is where you will be glad you didn’t allow users to own objects, because you won’t run into the “Cannot drop schema because it is being referenced by object”, “The database principal owns a schema in the database and cannot be dropped” and the “Login granted one or more permissions” messages that can be painful to unwind after the fact.

What do I do if my company has a data breach?

What if, despite your best efforts, you find that there has been a data breach? As DBAs, we plan for backups to go bad, and we layer our backups to cover it. We plan for disaster recovery, and we have availability options to cover that scenario. Have you ever thought about what to do if you are hacked?

First, if there is not one already, collaborate with other teams to come up with a run book, just as you would for disaster recovery and start writing your steps down. If possible, a response team consisting of people from infrastructure or network services, the DBA team, and business management should spring into action if a suspected breach occurs. This same team should meet periodically to review security standards and practices, to identify holes and how best to fix them, and to ensure that your security is in line with best practices. The Federal Trade Commission has a good set of guidelines to help you get started with your run book. Have the names of some forensic investigators handy so that you can quickly contact them. You want to quickly stop the breach and prevent anything else from happening. Then you are going to be very thankful for all the auditing and monitoring you have set up because it will be vital information to help determine what happened, how it happened, and how to prevent it from happening again.

Conclusion

Believe it or not, this will only get you started. Your server security will never be perfectly where you want it to be. You will find it is an ongoing process – you will no sooner wind up one round of server hardening than new applications, or features come out, or new vulnerabilities are discovered, and you are starting all over again. This is one of the most important parts of the job, though – if the data is hacked or ransomed, it will either not be available, or it will be useless. This makes security worth every bit of time you devote to it.