Tips for Deploying Secure Instances of Microsoft SQL Server

Your organisation’s data is a critical asset that needs the upmost care and attention to securely store & manage. For organisations with a Microsoft technology stack – in terms of infrastructure, applications and internal skills – Microsoft SQL Server is a RDBMS of choice designed with security in mind to meet such requirements.

When you deploy an SQL Server instance it’s important to work with your internal IT teams to make your installation secure right from the point of installation. In this blog post I’m going to discuss 5 points I recommend to ensure a secure basis for any SQL Server installation.

Tip 1: Deploy on Windows Server Core. If you can.

Most of us are familiar with the Windows GUI but for Windows Server it’s possible to deploy a reduced install of the OS known as Windows Server Core. This feature was first introduced with Windows Server 2008. This is achieved via a selection of the edition when the OS is first installed and is actually the default choice.

Installing Windows Server as Server Core affords a number of advantages. The principle benefit is that as the install is reduced in terms of components and features compared to a GUI install the attack surface is much reduced. There is also a slight performance advantage in terms of CPU and memory saved by the reduced overhead of the install.

Note that a drawback of a Server Core install is that some features such as SQL Server Reporting Services need the GUI install. If you choose to deploy an application on the same server (typically to save costs if the application only serves a few users) the vendor may also require a full GUI install. You also need to be completely proficient in using CLI based tools or remote management as the usual GUI tools aren’t present.

Tip 2: Apply the Latest Cumulative Update or Security Update

SQL Server generally has a good record when it comes to security vulnerabilities. That being said there has been a number of security issues identified and fixed in SQL Server. Patching just like any other program in use is a necessity.

In SQL Server you have two options regarding your update track. Highly recommended is the apply the latest Cumulative Update (CU). Not only will the instance receive the most up-to-date security fixes but also fixes to other areas of the database server.

Alternatively if your SQL Server instance hasn’t been patched using a CU then it would still be considered “GDR” or General Distribution Release”. These releases will still receive updates without applying a CU typically via Windows Update.

You should also check that on both clients and server that the various SQL Server connectivity drivers are patched up to date if they are mandated by any client applications.

Tip 2: Enable TLS by Applying a Certificate

TLS or Transport Layer Security is an encryption protocol that protects data sent and received between SQL Server and the client. Under principles of Zero Trust you should still take steps to encrypt data in transit even if you own the network SQL Server is running on.

A certificate should be procured either via an internal Certificate Authority (CA) server or via a recognised broker. For testing purposes a self-signed certificate can be used by is not recommended. This then secures application traffic against eavesdropping.

Note that you should never allow connectivity to SQL Server over the internet.

Tip 4: Use Windows Authentication Where Possible

SQL Server can be run in either Windows or Mixed authentication modes. There are a number of drawbacks to using SQL Server’s built-in authentication method. Chiefly is that login passwords get transmitted unencrypted and can be seen using packet sniffing.

For best results leave use Windows Authentication mode only and make sure your applications authenticate via Windows. Certain applications do not support Windows authentication so check carefully with the vendor in case any patches or reconfiguration are required.

Tip 4a: If You Must Use Mixed Authentication Disable the ‘sa’ Account.

The sa account in SQL Server is a very special one. It has the SID of 0x01 and is considered the most powerful account in SQL Server. It’s also the most vulnerable one being so well known. For best results leave sa disabled and rename it. That way it can’t be used to exploit the whole instance.

Tip 5: Consider Firewall Rules

The Windows or Linux firewall should be enabled on each operating system for SQL Server. Connectivity to SQL Server is via the default port 1433/tcp. A rule needs to be in place to allow traffic to and from the server.

Locking down firewall rules to only allow access via known application servers, authorised management PCs and restricted virtual desktops is highly recommended. This effectively stops any malicious login attempts from unauthorised hosts. This can help keep your SQL Server instance safe.

Conclusion

In this post we’ve discussed the importance of protecting your organisation’s data as well as the theory over a number of basic steps for securely deploying SQL Server instances including: secure configuration, updates, firewall controls as well as encryption in transit.

Once the SQL Server instance is promoted to production there are also other features of SQL Server that should be considered such Transparent Data Encryption (TDE) for encrypting SQL Server databases, logs and backups.

If your organisation is facing challenges over securing your SQL Server instances then help is at hand. Digital Incite and Matter Ltd can help further with this challenging subject. We also have experience helping clients with annual security audits. Get in touch with us today for a free initial consultation.

Multi-Factor Matters

Did you know that 25% of users in a World Password Day survey admitted to reusing their passwords across multiple sites? This kind of behaviour poses a challenge for organisations. Should a user’s password be guessed or compromised then an attacker could access multiple systems via that one combination.

Multi-Factor Authentication or MFA is a system by which an additional factor of authentication beyond a password is added to an account. This is done to enhance the security of the account by preventing takeovers if the password is lost, guessed or brute-forced. You may also hear this referred to as Two-Factor Authentication or 2FA which is a term often used interchangeably.

By using MFA for both administrators and users of a system you can prevent account takeovers that result from passwords that have been guessed, reused or compromised. Enabling MFA for any cloud services your organisation subscribes to is also required for schemes like Cyber Essentials in the UK.

Types of MFA could include but are definitely not limited to:

A one-time code sent via: SMS to a mobile phone number, voice to a telephone number or an email account
Response to a notification via an authentication app on a smartphone.
a Time-based One Time Password (TOTP) generated by a smartphone app or physical device.
A hardware authentication device such as a Yubikey. This may support the FIDO2 scheme and/or the older U2F standard.

Yubikey atatched to a keyring — An example of MFA: a Yubikey.

It should be noted that whilst MFA offers additional security from unauthorised access it does not completely guarantee secure systems. Breaches may still result via other means. Principally you should always be mindful of attackers trying to gain access to an account by social engineering techniques. A common way by which this is happening is attackers calling users to ask them for the one-time codes.

Additional ways of adding security to accounts should also be considered. A password manager can help users avoid password reuse, help them to generate secure & unique passwords per login and also allow an administrator to monitor password use. Securing sites with Single Sign-On (SSO) is also another option to explore. This allows a user to access multiple systems seamlessly via one login. This can help users by avoiding situations where they struggle to remember different passwords and be tempted to reuse the same password. Instead their “primary” account acts as the login which can be secured via MFA and continuously monitored (such as Microsoft’s Conditional Access for Entra ID).

Ultimately the password is indeed quite dead. It’s not uncommon that in our day to day lives across personal and professional accounts an individual person might have to remember 100s of individual accounts. Passwordless schemes along with SSO are the way to go.

In summary MFA is a great way of adding additional security to a system for both administrators and users. It should be an automatic requirement in setting up new accounts – both cloud and privately hosted – especially in Cyber Essentials certified organisations.

Get in touch with us today for further assistance with Active Directory, Entra ID or SQL Server based authentication challenges.

Discussing Authentication Modes in SQL Server

I have been on holiday for the past two weeks but just before I left the subject came up with a client regarding authentication methods in SQL Server. Whether you are logging in using SQL Server Management Studio (SSMS) to make queries or configuring an application to work with SQL Server the authentication method is important to consider.

By authentication we mean a process a user must complete to gain access to a system. SQL Server roles and rights fall under authorisation – determining what access a user has in a system – which I’ll discuss another time.

When you install a fresh SQL Server instance on Windows you’ll be prompted regarding the authentication mode to have it in:

Windows – Default and enables Active Directory authentication only.
Mixed – Enables both Active Directory and SQL Server’s built in authentication method

You also get to specify what AD users and groups to add to the sysadmin role. When I do this for clients I make sure to identify the users or DBA groups to add. Adding the domain admins group I would advise avoiding unless as a last resort because given that sysadmin has read permissions on data in all databases there should still be a separation of roles between an IT admin and someone in an organisation who has a both requirement & trust to access that information.

If you change your mind later on after the install you can switch between modes in the instance properties in SSMS or using the registry.

I recommend if possible that an SQL Server instance uses Windows Authentication. This is because it’s simply put the most secure form of authentication of the two.

Windows Authentication makes use of an organisation’s existing Active Directory user directory. This mode is always active and is often referred to as “Trusted”. I’ll not go into the exact workings of Active Directory authentication in this post but the critical point is that SQL Server is not performing the authentication itself. Instead the authentication is done against Active Directory by passing back authentication tokens. A slight downside would be that this creates a dependency on a working and redundant Active Directory Domain Services setup (read: ideally two servers with AD replication working and healthy).

When using an SQL Server authenticated login this will use SQL Server’s own authentication scheme which is much weaker than Active Directory. An example of how much weaker this is would be that in the communication between the client and SQL Server instance you’ll find the password sent in plain text.

Special Note On The sa Account

There’s a default account in SQL Server which by default and on installation is named ‘sa’. This is the most powerful account in any SQL Server instance and has the SID 0x01.

I often get asked the question either in handover or in a support query about the sa account and my answer is always that I don’t use it. Applications, MSPs and consultants that ask for this account lose a few points for doing so. Using the sa account to configure an application or for general use is an absolute no.

I highly recommend leaving the sa account disabled. It’s also recommended to rename this account as an attacker can usually assume there is an account named sa on the instance to try for.

As a safer alternative giving users named logins is highly recommended for accountability purposes instead of a shared account.

Note that if you install SQL Server in Windows Authentication mode the sa account is still created but with a random password and left disabled.

ProtonPass

This week the Proton team announced that their take on a password manager ProtonPass is now on general release. I’ve had a test of the product in the beta phase and now that a stable release has been announced I’ve started testing in view of adoption that I’m intending to last until the end of July.

ProtonPass has launched with extensions for all the popular browsers such as Chrome, Edge, Firefox, Safari and more as well as Android & iOS apps. Within the vault there is support for storing data in the forms of logins for which you can also store 2FA codes as well as notes. Proton stress that all data is end to end encrypted and protected under Swiss privacy laws.

I’m currently using BitWarden but decided ProtonPass was worth a go. Here’s what I’ve found so far.

Loading from my current password manager BitWarden was straightforward as ProtonPass can read the output json. It for some reason missed at least one record which was a note on an encryption key. ProtonPass found the record but did not put the key with it. ProtonPass also doesn’t support filling in your adresses or credit/debit cards which is a useful feature to have. For these records there was at least a note in the import wizard recording that these records were skipped. It should be noted that there is support for many other providers too.

As noted above there is support for a wide range of web browsers and also both major smartphone OSes. Being a Firefox user on the desktop and an iOS user in my hand I found no major issue installing either extension or app respectively. Both extension and app I’ve found to have a modern, clean and easy to use design which matches the design language of the existing Proton applications. All in all no complaints here. It does struggle for some reason to autofill logins to this website which seems to have something to do with the fact this blog URL matches several account emails in the vault.

There are some things missing in the product that you’ll find in other password managers such as the ability to organise items into folders. ProtonPass has a “vaults” concept but ProtonPass did not create vaults based on folders in my BitWarden import so I am not 100% sure folders and vaults are analogous concepts. Password breach monitoring and reports are also missing which I would like to see on the roadmap. These can alert you to finding if an account has appeared in a breach and are also useful for

Possibly most missed however is a web vault. At the moment you need to use a browser extension or smartphone app to access your vault. Whilst this is OK and arguably you’re using the password manager most from the apps & extesnsions it does mean if you need to see a larger UI to look through and oganise your vault then you might miss such an interface.

ProtonPass is free for the basic tier which includes unlimited devices & logins, entries such as passwords and notes in the vault and upto 10 hide my email aliases via SimpleLogin. The “PassPlus” option at €4.99 per month or €1 a month for a 12 month plan offers unlimited email aliases, 2FA and vaults to organise items is more feature complete but questionable value at €4.99 if you only want to commit month by month for some reason. If anything it would be best to either use it as the free tier or as part of an unlimited or family subscription.

At this point if you don’t have a password manager then ProtonPass is worth a try. It is barebones in some regards such as reporting and a completely missing web interface but with strong privacy credentials from the Proton team it’s worth a try as part of the

Patch Time

If you’re in the SQL Server world you should be aware of this by now however for those who’ve been busy getting stuff done this week Microsoft have released a security update for SQL Server described in full in KB4583459.

Data can be sent over a network to an affected Microsoft SQL Server instance that might cause code to run against the SQL Server process if a certain extended event is enabled. To learn more about the vulnerability, see CVE-2021-1636.

Patches are available for SQL Server 2012 and above with currently supported service packs. As it’s most people’s best interest to maintain a SQL Server that doesn’t allow this to happen it’s a great idea to get this patch installed now. If you are intending on ruining someone’s day with this exploit I wholeheartedly apologise for spoiling your fun.

I anticipate quite the majority of my clients will “accidentally” end up with this patch through Windows Update which is probably for the better (exceptions given if it somehow kills the SQL Server).

I’ve Been an IdIoT

From time to time we have to be the family’s IT support. It comes with the profession. Today I had work to do for Mum and Dad. The CCTV just wasn’t working. They were going on holiday, hadn’t checked the cameras in a while but they weren’t accessible from their phones. They really wanted them back before they went away.

The tell tale signs quickly came apparent after I logged in to the NVR (Network Video Recorder) which is a Dahua model. The camera list read “HACKED”, “CHANGE”, “FIRMWARE”. The issue with the device falling off the network turned out to be that a deliberately faulty IPv6 address had been set which also knocked out IPv4 communication.

Best I can tell they had fallen victim to an attack that appeared back in 2017. The default super user account for this NVR is ‘88888888’ and the default password is ‘888888’. Last time I’d checked out the CCTV I noticed this was in place and I really should have changed this. On Dahua NVRs this super user account should not be accessible from remote. The system should check if the access request had been made from local or remote and – in case of the latter – deny access accordingly. The flaw apparently is that this check isn’t properly done. The article linked also references a Facebook post which suggests that using Dahua’s DDNS also made it easy for the attackers to identify and target vulnerable devices. My parents were indeed using Dahua’s DDNS.

In other words the system was a sitting duck on the internet.

I can only assume someone out there has written a script to look for vulnerable Dahua devices exposed to the internet, log in via the admin account using default or easy to guess passwords and then apply setting changes to forcibly remove these devices off the internet. Malicious but also a public service. The device runs an embedded version of Linux and could have easily ended up part of a botnet if hacked firmware was uploaded.

To resolve the attack I reset the unit to factory defaults, uploaded new firmware to both NVR and cameras then set accounts with strong passwords using the XKCD method. I do not have a high trust of IoT devices but not being particularly happy about going up a ladder to replace the cameras this was the best I could do.

It should be noted that Dahua’s website is diabolical for finding firmware updates. You cannot just search a model number to find the device and associated firmware. I needed to check a load of firmware releases to see which ones were pertinent. I had a few attempts with what I thought was correct firmware but apparently wasn’t. I am thankful that the devices can do checks of the firmware to be installed because the risk of bricking the devices in this scenario is real.

In the industry we all know the lessons and we all recognise when a chain of events start happening and not get stopped then the results can be catastrophic. In the case of my parents I assume someone’s shut off network access deliberately but the reality is that someone could’ve used the CCTV access to determine that nobody was in the house. The cameras point to the drive where their cars are parked after all.

Let’s conclude it by reiterating the standard points:

Default passwords (and arguably account names) aren’t acceptable for an internet facing device. I had an opportunity to change it and I didn’t. Whilst the admin account should not have been accessible remotely it was thanks to a firmware flaw. Changing that password could’ve stopped this attack in progress.
Firmware updates need to be done as often as they become available. Security issues happen. Pobody’s nerfect right? As soon as those vulnerabilities and fixes become apparent they need to be tested and deployed.
Trust is a weakness and no trust is owed to the average IoT device. The industry is notorious for bad security practices and abandoning devices within a short lifecycle. Ideally these devices shouldn’t be left open on the internet but placed behind a VPN where the access can be defended slightly better. That’s not a practical solution for most home networks so there must be an accepted risk.
I will reserve judgement on using a DDNS. The CCTV was registered with a *.dahuaddns.com address which I suppose makes it pretty easy to focus an attack if you can narrow it down to specific devices or a single manufacturer. I would suggest using a static IP instead but my parent’s ISP does not offer this. Comment below if you have anything to offer on this as I don’t know how much safer you are with a custom DNS.

Ransomware

Last week I was at a customer site when NotPetya hit. I was working in the company’s IT Ops room when news broke. All of a sudden people went from worrying about an AD user surname change to contemplating moving their patch schedule forward.

Toward the weekend I tried and failed to help another customer who had been hit by the ransomware. Somehow the application server had become infected. In the end there was no other option except to reinstall the application. Thankfully the databases were safe and the customer is back to running.

NotPetya targets vulnerabilities in the ancient SMB1 protocol. I recently disabled SMB1 on my desktop PC at home. Save for not seeing my NAS and Router appear as objects in Windows File Explorer there were no adverse affects. Microsoft have recently announced that future Windows 10 builds will not have SMB1 installed by default. The IT community should really be working on consigning SMB1 to the bin alongside SSL 3.0.

Speaking of which years ago when the Heartbleed vulnerability broke out I ran a test in production: I disabled SSL 3.0 without telling anyone that I’d done it. The only known site that broke was – very shockingly – a major UK bank partly owned by UK.gov. Why is there such inertia behind retiring old and broken protocols?