6 Security Top Tips That You Can Implement Today

Backups are the mainstay of any Disaster Recovery scenario. They should be the last-stop if all else fails. Backups should be run frequently, and checked regularly.

• Run full backups at least weekly, nightly if possible
• Run incremental or differential backups nightly if you can't run full backups each night
• Run transaction log backups as often as you can during the day - hourly is not uncommon, some choose more regularly

Consider your Recovery Point Objective (RPO) when deciding how often to backup. If you can't afford to lose more than 5 minutes of data, you need to be doing transaction log backups every 5 minutes.

Consider your Recovery Time Objective (RTO) when deciding what types of backups to take and where you store them. If you have a 99.9% Service Level Agreement (SLA), that gives you 43m 12s to recover from an outage. That includes time for:

• Alerting
• Diagnostics
• Initial attempts to bring your database online
• Talking to your customers
• Making the decision to restore
• Possibly spinning up a new Windows server and installing SQL server
• Running the restore
• Running any incremental or differential restores
• Testing your data
• Releasing to production

Keeping your SQL backups on a separate hard drive on the same SQL server will give you best performance for a restore but will cause you headaches if you suffer a crypto-locker attack that encrypts all of the files on your machine. That will include all of your data and transaction log files, your backups, config files and possibly any network shares the victim account has access to.

3rd party backup software like Microsoft's DPM Server solve this issue by initiating the backup from a separate server and storing the data away from the SQL server.

If you don't have access to a 3rd party backup solution, use the built-in SQL backup option. Ola Hellengren has some amazing and free SQL Server Backup, Integrity Check, and Index and Statistics Maintenance scripts that will automate the process for you.

If that's too much for you, just backup your server manually as often as you can.

                    
EXEC sp_MSforeachdb 'EXEC sp_MSforeachdb '
    USE [?]
    BACKUP DATABASE [?]
        TO DISK = ''D:\MyBackups\TodaysDate\?.BAK''
        WITH COPY_ONLY, INIT, COMPRESSION, STATS'
                

If you can't directly backup to an off-site storage facility, backup your data to a USB3 SSD drive. Have a number of drives you can rotate so that you can store today's backup on a separate drive to yesterday's. Remove the USB disks once the backup is complete. If you get crypto-lockered overnight, you still have yesterday's backup.

Check your backups regularly. You have to assume any backup you take since the last time you checked has corrupt data or failed to run.

Check your backups as often as necessary to maintain your SLA.

• Check for database corruption with DBCC CHECKDB - Use Ola Hellengren's scripts for this - weekly or nighty
• Check your backups completed after every full, incremental and differential
• Use alerting in your SQL Agent jobs to make sure you don't miss any failures
• Check you can restore your backups by doing an actual restore and checking the data - monthly or weekly

Remember, backups are the primeval get-out-of-jail card. You need them, they need to work and keep good data. You need to double-check they are good. Failure to have good backups will lead to data loss, angry customers, an angry ICO and you being fired!

With one or two servers this one is simple. With hundreds or thousands of servers you need to think about this a lot.

Microsoft release critical patches to elements of their OS regularly. As soon as they release a patch, the contents of the patch, including the vulnerability it's fixing goes public.

It's like a to-do list for hackers and bedroom virus writers.

If your patching isn't up to date, you are vulnerable to a large number of serious security vulnerabilities.

On the other hand, Microsoft aren't perfect. They have been known to release patches that include code that breaks systems. So, what do you do?

You need a patching policy that covers:

• SQL server
• Windows OS
• Hypervisor - if your running virtualised (VMWare, Hyper-V)
• Hardware
• Networking equipment (Switches, Firewalls, Load balancers etc.)

Make sure you patch everything regularly.

Be aware of how many patches you have pending and what vulnerabilities you're not patched for.

Decide if your systems necessitate patching on release day to be secure from vulnerabilities or if you should lag a week to give others time to report issues and Microsoft a chance to rescind a faulty patch.

Running:

                    SELECT	 name			AS Name
        ,loginname		AS LoginName
        ,isntuser		AS IsNTUser
        ,isntgroup		AS IsNTGroup
FROM		master..syslogins
WHERE		sysadmin = 1
AND			hasaccess = 1
AND			denylogin = 0
ORDER BY	name
                    
                

effectively gives you a list of the people who can get you fired. The shorter that list, the more secure your job. If you're using NT Groups as sysadmin users, it's very difficult to keep track of who can get you fired.

Only give the sysadmin privilege to those who actually need it.

There are many ways in SQL to give permissions to users and service accounts without giving a blanket sysadmin permission.

Challenge anyone who asks for sysadmin permissions! If they can't think of an alternative way of doing what they need to, should you be giving them the access? Laziness is not a good reason.

Keep a record of who is a member of the sysadmin group, when it was granted, an expiry time or event if possible and a reason why they were added for each SQL server.

Anyone with sysadmin permissions can read any data on the database server. Anyone with sysadmin permissions can have their laptop hacked and give access to all of the data to hackers.

Anyone with sysadmin permissions can read the passwords of Linked Server objects in plain text and potentially gain access to other SQL servers. Ditto for hackers.

Where you do need sysadmins, give each user a unique login. NT User objects are perfect for this. (Domain user preferably) You can see who is doing or who did what on your server. You know which account to disable if one has been compromised whilst leaving other sysadmins active to mop up.

Lock your sa account away for a rainy day. Store the password securely where trusted people can access it in an emergency but don't use it as an active account.

SQL Server stores the login credentials for Linked Server objects in reversable encryption.

Antti Rantasaari from NetSPI wrote a great blog post on how to retrieve the Linked Server Passwords. This can be really useful if you are migrating servers or taking ownership of a server someone else built. It's also useful to hackers.

If you need to use linked servers, that's ok. They're very useful. Please please use a dedicated login for the Linked server. You can change the password safely when you need to and also keep an eye on what each login is accessing.

If you no longer use a Linked Server, remove it from your SQL server configuration. It reduces the blast radius of an attack and limits the servers a hacker or a virus can get access to.

Keep an eye out for test Linked Server objects. These are often created with an Engineer's credentials to test something or see if permissions are a problem when troubleshooting an issue. Clean up as soon as you can.

Make it a habit and preferably a policy to connect to your SQL servers with SQL Server Management Studio (SSMS) remotely.

Aside from the additional load your SQL server will suffer with your windows profile loading, running SSMS locally and rendering your screen, having your session active on the SQL server poses a security threat.

Millennia Cloud Services posted a great article on how RDP Hijacking works. If you leave your RDP session disconnected, it's extremely easy for amateurs to silently hijack that session and traverse the network.

With a little more creativity and effort, it is possible to extract passwords from memory for users who have active sessions on the server. The more sessions, the larger the risk.

A third threat comes from the user themselves. It's quite possible a user will access services from outside your network whilst logged on to your SQL server. If that includes a website with a malicious payload your SQL server can become the victim of a virus or crypto-locker attack.

Queue those backups!

• Don't RDP to your SQL server for day-to-day work
• Don't connect to your SQL server's network shares
• Don't share any file data from your SQL server
• Don't install anything other than SQL on your SQL server
• That includes SQL Server Reporting services (SSRS) and SQL Server Analysis Services (SSAS)

Transparent Data Encryption (TDE) has been available since SQL 2008 but only in Enterprise, Developer, Evaluation and Datacenter editions. TDE is now available in Standard edition from SQL 2019. It's one of the strongest reasons to upgrade to SQL 2019.

TDE encrypts your data as it is written to disk. SQL Server decrypts the data as it is read, leaving the SQL Engine and your applications to process data as they always have. It's transparent.

Why bother with TDE if SQL server processes the data unencrypted? I hear you ask.

Enabling TDE on your database also encrypts the data in the Transaction Logs, TempDB and in your backups.

Backups are the key one here! Once you've backed up your database, that .bak file can be transported anywhere. That USB drive you have in your laptop bag... That you just left on the train. Oh dear. It's a call to the ICO.

If that backup file is encrypted it's a whole different story you will need to tell your boss, your customers, the ICO, the media and your SO.