Security is often something people think about only after they have had a problem. Given that the average cost of a data breach is $3.92 million (SecurityIntelligence 2019) and ransomware attacks have increased 97% over the past 2 years (PhishMe 2019), the "if it's not broke, don't fix it" approach can clearly be catastrophic. Here is a list of 6 things you can do to make your Microsoft SQL Server database more secure. This is hardly an exhaustive list, but it is a great place to start to protect against the most common threats.

Use Appropriate Permissions

Permissions can be complicated and annoying, so many people give up and just make everyone a sysadmin. That certainly makes them stop complaining. It also means that hacking by any random user at your company grants complete access to the system. Over my 22 years in IT, the number of clients using SA for everything is truly terrifying. It only takes one employee clicking on an email with malware to cause a breach.

Reserve SA only for emergencies and for no other reason. It is a built-in account you can't remove. The account has rights to do anything, including overriding other administrators. Overusing is one of the most dangerous things you can do. Give administrators the sysadmin role or other more specific admin rights instead.

Analyze your applications carefully to determine which permissions they need. This can often be complicated and poorly documented, so giving administrator privileges is common. That is extra dangerous with applications which have holes in their own security. I commonly see applications configured with a specific person's credentials, but this usually gives an inappropriate level of privileges. The application can also crash an application when an employee quits, which often results in administrators never deactivating that employee's account. Give applications their own login with permissions only for what they need.

Individual users rarely need direct access to SQL Server, because giving them application access only is generally preferable. If you must give a login to an individual, give them only the abilities they need on only the objects they need. When an individual leaves the company, it should be part of your checklist to deactivate their login.

Document your company's permissions strategy. Explain which people and applications have permissions and why. Review your systems regularly to ensure your organization continues to comply with these guidelines.

Encryption

Encryption in motion and at rest is what keeps data access limited to only the people you give database permissions and not just anyone with network access. Without encryption, accessing sensitive data can be as easy as copying a file.

Transparent Data Encryption (TDE) was introduced in SQL Server 2008, and it is available in Enterprise edition. TDE encrypts the entire database, end-to-end, while using minimum CPU for the operation. Remember to encrypt your backups too, a commonly forgotten step. Your backups are a really convenient way to steal your entire database.

Use Transport Layer Security (TLS) to protect your data while in motion. Use this to prevent network-based attacks.

The final part of encryption is the hardest, because it involves employee training. What good is encrypting your entire database, if a user in HR exports all your Social Security Numbers to an Excel file? You need to have training with all your employees about the dangers of storing information outside the database and establish policies for encrypting any files they do create.

SQL injection

SQL Injection is one of the oldest and still most common exploits of SQL Server. It is the largest security risk for web applications in general according to OWASP (OWASP 2017). It is where you pass code to a control which wasn't expecting any in order to bypass security. This is when you are expecting someone to type in their first name, and instead they type in Bob'; DROP DATABASE master;

The first line of defense against SQL injection is appropriate permissions. By limiting the permissions that the application is working with at any moment, you reduce the surface area of attack. One technique I often use is to give the application permissions to execute stored procedures, and nothing else. The stored procedures will then impersonate users with whatever permissions they need at the moment to perform their task. By denying permissions to anything other than stored procedures, you greatly reduce the ability to SQL injection to work, because you force any malicious code through a procedure parameter before it has a chance to operate.

The second line of defense is to inspect any input submitted by a user. Anytime you allow the application to accept text whether it is a textbox on a website or a post where a would-be hacker can type their text directly into the URL, this is an opportunity for injection. You are at a higher level of risk anytime you use dynamic SQL. Pay special attention to these scenarios. Inspect any string submitted to the database for special characters and prevent their usage. Record any detections and report them to an administrator, so you can shut down injection attempts and possibly improve your security in case the hacker attempted something you didn't think of.

Windows and SQL Updates

One of the easiest things you can do to improve security is stay up to date with versions and updates of Windows and SQL Server. Most successful breaches of servers involve exploits which have been known for some time. Being current with your updates can prevent most of these. You need automatic updates of both Windows and SQL as well as a policy to regularly check that all updates are applied.

A commonly forgotten part of this is the version of software itself. If you are running SQL Server 2008, which is no longer supported by Microsoft, and you are at significant risk. Even if your version is still supported, it is reasonable to assume that most of Microsoft's time is spent on their latest and greatest. Because of that, I always recommend upgrading to the newest version of SQL as soon as it comes out.

Audit logins

Without a proper log of security events, it is difficult to even know when someone is trying to breach you or whether they have. There are many types of logs you can create, but at a minimum, everyone should have a log of connections.

Create a log of all successful and failed login attempts along with the date and time. This can help you identify unusual activity to prevent a breach in progress. At the very least, it can help you identify a breach after the fact, so you can attempt to remedy it. You can also tie this into your security policy to ensure compliance. Is SA logging in outside emergency situations?  How often do administrator logins get used?  A log is an important part about knowing the reality about how people actually interact with your database from a security standpoint.

Data masking

SQL Server introduced Dynamic Data Masking in 2016. This gives you more control over security of sensitive data compared to column level security. An important part of any security audit is to identify your most sensitive information, such as social security numbers, credit card numbers, identifiable health information, etc., and take extra care with these columns.

Data masking is applied directly to columns, and anyone with rights to SELECT from that table will see the masked data unless you grant them the UNMASK permission. This can be an easy way to exclude by default permissions when they are not needed. It also gives you the ability to define custom masks to allow a partial view of the data. For example, if your company decides that seeing the last 4 digits of a social security number is safe, you can mask everything but that.