How Best To Secure Your SQL Server

A checklist of the best ways to keep your SQL secure from hackers and cyber criminals.

24th October 2019Blog

Are you ready to get in touch?

  • 0207 448 8500
  • info@northdoor.co.uk
Request a Call back

Design your server from the ground up with security in mind

24th October 2019

One of the most crucial challenges facing many businesses in today’s cyber world is keeping it’s data safe.  The concept of security, data security and data protection has moved further into speciality areas of responsibility to mainstream corporate responsibility and corporate governance.

One of the essential jobs for a DBA is keeping your data safe, but this role is not limited just to a DBA. Developers, system administrators and IT users all have a role to play too. The best way to ensure that a system is secure is to design it from the ground up with security in mind, so it’s essential to consider every person and role involved in interacting with a system.

Why all the fuss? To a cyber criminal or hacker, data is money. Personal information such as names, address, and banks details are worth a lot. Hackers continue to develop more sophisticated means to steal data every second of every day, so precaution must be made to ensure your data is protected at all times.

With the introduction of GDPR, there are considerable repercussions, including enormous fines for those who fail to collect, manage and dispose of data inappropriate ways. So, ensuring that your data is secure has become more critical than ever to everyone at all levels in the business.  With this in mind, what can we do to ensure our SQL server is as secure as possible?

Before looking at the specifics in SQL Server let’s take a step back and understand the database platform.

Remember: is only a link more than an extensive chain. A chain is only as strong as its weakest link, so we must check the underlying systems that support the database to ensure they are also as secure as possible.

Things you should consider when safeguarding your data:

  • Is your network secure?
  • Is the firewall maintained and monitored?
  • Are the Windows servers and desktops patched and secure?
  • Is the Active Directory protected?
  • Do all the desktops have right and up to date anti-virus and anti-spam software?
  • Have your staff had training on how to avoid social engineering attacks and phishing emails?
  • Do you have appropriate password policies in place?
  • Do you have documented and understood security policy?
  • Do you have a security policy in place for privileged access?

If the above are not in place or not secure, then your entire IT infrastructure will be vulnerable. If a cyber criminal can hack into your system with elevated privileges, then they can circumvent your SQL server security to get to the data files regardless of the level of protection surrounding your SQL server.

Assuming your IT systems are as secure as possible, what can we do to SQL Server to protect it even more?

This is not a fully exhaustive list but undoubtedly a good starting point:

  • Only install the SQL Database Components you need. Don’t just select everything in the SQL installation GUI. Don’t give a hacker a potential path into your database.
  • Service packs and cumulative updates.  Keep the server updated, apply all service packs as soon as you can test them properly. Apply services packs to the entire technology stack, not just SQL, but the operating system as well. The lack of services packs and security updates can be one of the critical drives to a SQL Server modernisation project. Microsoft no longer supports legacy SQL Servers such as SQL Server 2008 R2 and earlier. As such, this software no longer has service packs, cumulative updates nor security patches. Your old database servers can become vulnerable when not updated regularly.
  • Disable the SQL Server Browser Service unless you need it.
  • Service accounts – use an account with limited permissions to run the SQL server and the SQL agent. The best approach is a Managed Service account or local Virtual Service account.
  • Limit the open ports on the server firewall to just those that are required.
  • Change the default SQL Server port number.
  • Separate the Database Server onto a separate VNET and only allow application servers into the VNET. Ensure all support staff to go through a jump server as by isolating the server you reduce the number of paths that a hacker can break through. A configuration such as this comes with a trade-off as using this approach would make support slightly more convoluted for support staff.
  • Use the Principle of Least Privilege when assigning permissions to the server. Only assign administrators the server role they need and only give sysadmin permissions to those that genuinely need it. Check if the other server roles meet the requirements or even create your server roles where necessary. You can take this stage further and only give privileged access to your DBAs when they need it and revoke it when it’s not required.
  • The same goes for database access. Always create roles with minimum permissions required then assign Active Directory groups to those roles. Avoid assigning individual users’ access to a role. A security model such as this allows central management of a database and server access, allowing the information security team to ensure the people in that AD group are the correct people.
  • Wherever possible use a Windows Authentication mode.  By turning off SQL server authentication, you stop an easy attack vector.
  • Disable the SA account.
  • If you have to use SQL server authentication, then turn on the enforce password policy and enforce password expiry options.
  • Ensure you disable or delete SQL Server Logins when they are no longer required.
  • Audit your logins- Audit failed and successful logins and examine the logs regularly for anomalies.
  • Audit changes to the system.  Who has created, deleted or changed a login, user or role?  Also, monitor who has made changes to a database for you may spot malicious changes to your system.

SQL Server and Business Intelligence solutions

Over time and through various versions and releases of SQL Server, Microsoft has tried to enhance the product.

One area they have introduced several new features is around encryption. There are several encryption techniques available in SQL server now, that can help you keep your data safe:

  • Transparent Data Encryption (TDE). TDE encrypts the data at rest. The data files are encrypted so if somebody steals the data files, they cannot access the data stored in them.
  • Dynamic Data Masking. This allows you to obfuscate data in real-time.
  • Always Encrypted.  A technique that helps protect data both at rest and in motion with a master key.
  • Column Level Encryption.

There is also the concept of row-level security that gives you control over who can see which rows in a table, based on the user’s rights.

There are still lots of systems on the internet where a simple SQL injection attack can easily extract the data.

This list isn’t exhaustive, but the most important thing is that you have thought through and made your system as secure as possible.

For more information contact our SQL experts

SQL-Server-2019-logo