For this months TSQL Tuesday Matthew McGiffen asks us some broad questions around Encryption and Data Protection. His timing is great, because that’s exactly what I gave a talk about last month at SQL Saturday South Island. My timing is not so great. It’s Thursday. I could tell you about Tuesday, but I’m not sure you would believe me. So apologies for being late Matthew, thanks for the great topic which I keep telling everyone who will listen we all need to keep front of mind.

I’m going to start off in the past, where Encryption was a big scary thing for me. I didn’t get it. It was complicated and seemed like some hidden voodoo science that I just wasn’t smart enough to understand. One thing I’ve learnt though is that not understanding the intricacies of how something works is actually not that bigger deal so long as you understand what it is used for, when it’s appropriate to use and how to go about implementing it. Embracing my inability to know everything about everything is something I actually recognize as a big career turning point for me, and if it hasn’t been for you yet then I’ve got a thing called AI that we need to have a chat about.

At any rate, a little less distantly in the past I presented at SQL Saturday SOuth Island and did demo’s inside an hour on how to do the following forms of ‘encryption’.

• WITH ENCRYPTION object ‘encryption’
• Connection Encryption
• Transparent Data Encryption
• Backup Encryption
• Always Encrypted Column Encryption

5 forms of encryption in an hour. When to do it, why to do it, and how to do it – take my code. But that wasn’t the point of the talk. Hopefully what people took away from the talk was that there is no technical barrier to them going away and implementing encryption in their environment straight away. The features are available in all paid editions of SQL Server, the process is really well documented, and they had just witnessed someone implement them all in a handful of minutes.

The stuff that really matter is the bits around the protection. What is the impact of encryption on your database size…your backup size…your ability to use third party dedupe products…your developers process…your compliance…your security. Each one of those things is a blog post in itself (oh good…no more wondering what to blog about next month…) but the one I want to focus in on is a human element and it’s something I have had to deal with directly in the past week (See reference to Tuesday above).

As employees we are often tasked with figuring out how to get something done quickly. We have an incredible ability to figure out the ‘how’ to a problem, and we live in a glorious age where the ‘how’ knowledge is readily and generously shared by a multitude of online genius types. So when someone says ‘encrypt this column’ it’s quite possible that a moderately competent IT professional can figure out the how, make that column encrypted and tick whatever compliance box their boss was worried about that week.

What we are often not so good about is documentation and knowledge transfer, particularly of an ad hoc task that took us a couple of hours several years ago. The biggest problem I have with encryption is that people change roles, and change jobs, and fall out of trees. When any form of encryption is in place, we run the risk of ending up in a state where data is irreversibly encrypted because the certificate is lost – or we have a backup, but not a private key or a decryption password. I don’t want to make people afraid of encryption, but I do want people to be very aware that the technical implementation of encryption is not the hard part. The hard part is agreeing and documenting all the things that go round encryption.

So my call to action to you – person with TDE in your environment, and you – person with Always Encrypted implemented on a server near the end of it’s life – is to make sure more than just you know how to access the backups or certificates and know where your process is documented.

You won’t thank me for this, it will be one more long boring task to work your way through, but if you don’t do it – you, or whoever inherits your environment, may well curse the name of whoever introduced encryption into your workplace. And that would be a shame.

Suppose you use sys.dm_io_virtual_file_stats to get performance metrics on your server. Further suppose you then drop that into the table using the database name and the collection time as your primary key. If you do this on a managed instance, and you group by database_ID for your datacollection you are going to hit a problem.

Why?

Because this:

        select 
            database_id,
            db_name(database_id) database_name
            ,cast(sum(num_of_bytes_read + num_of_bytes_written) / 1048576 as decimal(18, 2)) io_total_mb
            ,case when convert(decimal(18,2), (sum(num_of_bytes_read ) / 1048576)) = 0 then 0.001 else convert(decimal(18,2), (sum(num_of_bytes_read ) / 1048576)) end io_read_mb
            ,case when convert(decimal(18,2), (sum(num_of_bytes_written) / 1048576)) = 0 then 0.001 else convert(decimal(18,2), (sum(num_of_bytes_written) / 1048576)) end io_write_mb
        from 
            sys.dm_io_virtual_file_stats(null, null) dm_io_stats
        group by 
            database_id

Will give you a result like this:

This stuffed me up quite a bit but once you know that you are looking at stats for invisible magic Azure Managed Instance databases messing up your results there are several options:

• Filter out the stupid high database_id’s
• Be consistent in whether you use name or id for your grouping and primary keys.

A quick one today caused by an interesting error I received. A client dropped me a message that they were running a stored procedure fine as themselves but when they dropped it into a SQL Agent Job it started to fail. The message was pretty clear:

“Executed as user: Domain\SQLAgent. Login failed for user ‘ Domain\SQLAgent ‘. [SQLSTATE 28000] (Error 18456). The step failed.”

I like problems that are easy to solve, just check the Domain\SQLAgent account on the SQL Instance and find it’s….enabled…and has all the permissions required to run this stored procedure? Hmmm.

Then I noted that despite getting a login failure message there was no corresponding login failure message in the SQL Error Logs. Time to dig into the code and I was able to identify that the stored procedure references a linked server. That linked server was (Hooray) set up to log in to the remote server in the context of the executing account. So the login wasn’t failing locally, it was failing on the remote server. Sure enough I could see login failure messages on the remote server that matched the times the job had tried to run.

Resolving the remote login failure allowed the job to run successfully. Hopefully this post will point you to your potential error and save some time banging your head against a wall!

SQL Server Authentication Modes: Windows vs. Mixed Mode Security

When configuring SQL Server, one of the first decisions you’ll face is choosing the authentication mode. SQL Server offers two primary modes of authentication: Windows Authentication Mode and Mixed Mode Authentication. In general you are going to want to leave that in Windows Authentication Mode unless you have a compelling reason not to. In reality, most shared SQL instance will need Mixed Mode at some point.

---

Windows Authentication Mode

Windows Authentication is the default and recommended option for securing SQL Server. It leverages Active Directory (AD) to authenticate users, allowing for centralized management of credentials and permissions.

Advantages of Windows Authentication:

1. Centralized Management: Administrators can manage user accounts, passwords, and permissions directly through AD.
2. Kerberos Support: Windows Authentication uses the Kerberos protocol for secure authentication, providing enhanced security and performance.
3. Password Management: SQL Server doesn’t store or manage passwords in this mode, reducing the risk of credential theft. An often forgotten benefit of this is the ease of migrating logins and databases between instances. There is no chance of a mismatched SID when using Windows Authentication
4. Group Management: The ability to have a group and create a single login for all members of that group is often forgotten. This takes the centralized Management component in point one to another level allowing you to bring real role based management capabilities to your SQL environment.

Limitations of Windows Authentication:

1. Dependency on Active Directory: This mode requires a robust AD setup, making it less ideal for standalone servers or environments without AD integration.
2. Limited Flexibility: Non-Windows users or external applications may face challenges accessing the server. Scratch that – even SQL Server itself may have problems using Windows logins. For example using Windows authentication between linked servers will very quickly run you into a double-hop credential error unless you plan for it in advance.

---

Mixed Mode Authentication

Mixed Mode Authentication supports both Windows Authentication and SQL Server Authentication. In addition to using AD accounts, it allows SQL Server to manage its own user accounts, often referred to as SQL logins.

Advantages of Mixed Mode Authentication:

1. Supports Non-Windows Clients: This mode is ideal for applications or users outside the AD ecosystem.
2. Greater Flexibility: Mixed Mode provides more options for authenticating users and systems.

Limitations of Mixed Mode Authentication:

1. Password Management Burden: SQL Server accounts require manual password policies and management, increasing administrative overhead. However from about SQL 2005 this is less of an issue. When creating a login the “Enforce Password Policy” box allows you to enforce those policies, while leaving the “Enforce Password Expiry” box unchecked removes the necessity of continually updating the passwords used in connection strings.
2. Security Risks: SQL logins can be more vulnerable to brute-force attacks if strong password policies aren’t enforced. Additionally, it’s not uncommon to find that SQL logins are exposed in plain text in configuration files or connection strings.
3. Shared Credentials: People are people…and inevitably someone is going to use that SQL login they found in a connection string to login to the database directly. Or an authorised user may share that login with another user while they are “waiting for their access to be authorised”. There’s a mountain of scenarios where people will begin using a SQL login, and the truth is that it is very hard to track even if you are looking for it.

---

Key Considerations for Choosing the Right Mode

1. Environment Type:
   • For corporate environments with AD infrastructure, Windows Authentication is typically the better choice.
   • For standalone servers or systems requiring third-party integrations, Mixed Mode might be necessary.
2. Security Requirements:
   • Windows Authentication reduces the attack surface by avoiding local SQL Server accounts.
   • Mixed Mode requires careful configuration to minimize risks, such as enforcing strong password policies and auditing login attempts.
3. Application Needs:
   • Mixed mode authentication often ends up becoming a practical necessity if your applications or services don’t support Windows Authentication.

---

Securing Your Chosen Mode

Regardless of the chosen mode, here are a number of best practices to follow:

• Enable Auditing: Monitor login attempts to detect and respond to suspicious activity.
• Use Encryption: Protect data in transit by enabling SSL/TLS for client connections.
• Enforce Password Policies: Implement strong, complex passwords for SQL logins in Mixed Mode. When these are stored in password vaults they should include the intended purpose and preferably the servers\ip addresses they will be used on. If you do this you can monitor for activity outside these locations to determine when the password is being shared\abused.
• Limit Privileged Accounts: Restrict sysadmin access to only those who truly need it. And revisit that group on a regular basis to ensure they still need it.

---