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.

Screenshot from the iOS app.

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?