How Best To Secure Your SQL Server

Design your server from the ground up with security in mind

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 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 the 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, and 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. 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 or 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 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 go through a jump server, as by isolating the server, you reduce the number of paths 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 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, 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.

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 encryption. There are several encryption techniques available in SQL servers 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 many 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 End of Support

SQL Server 2012 reached the end of support on 12th July 2022, and Windows Server 2012/2012 R2 will reach the end of support on 10th October 2023.  After these dates, these products will no longer receive security updates, non-security updates, bug fixes, or technical support.  Learn what this means for you and how to safeguard your data estate. Contact us for more information.

Introducing SQL Server 2022

SQL Server 2022 builds on previous releases to grow SQL Server as a platform that gives you choices of development languages, data types, on-premises or cloud environments, and operating systems.

Find out what is new.