Category: Administration

Mike Lynn talks Transparent Data Encryption:

Transparent Data Encryption uses certificates in its architecture for protecting your data while at rest. One attribute of a certificate is they have an expiration date. Certificates expire for a couple reasons, but the main reason is to enforce security. When a website certificate expires it forces the website owners to get a new certificate by proving they are who they say they are with a trusted third party. 

SQL Server certificates that are used for TDE also have an expiration date, but these dates are only checked when you are creating a self-signed certificate using the “CREATE CERTIFICATE” T-SQL command. If you don’t supply an expiration date when creating your certificate SQL Server will assign one that is 1 year into the future.

Read on to learn more about how it works with TDE. I will say that with encrypting backups, SQL Server does care about the expiration date when it comes to creating a new encrypted backup, but not when it comes to restoring a backup.

Jeff Iannucci talks xp_cmdshell:

That most likely means the hacker in the incident gained access to SQL Server, and then used xp_cmdshell to open a Windows command shell. With that shell opened, they could then pass DOS and/or PowerShell commands to collect information and download malware onto the server.

It sounds dangerous. It is. But whether or not xp_cmdshell is enabled is irrelevant.

Exactly. This is something Sean McCown has harped upon for years, and I’ve done my best to follow. You can do bad things with xp_cmdshell, but disabling it doesn’t actually help for reasons Jeff mentions. Instead, assuming you need xp_cmdshell for some work, don’t go beyond the pre-set limitations (where a user needs sysadmin or CONTROL SERVER) and it’s fine.

Jeff Iannucci talks about OLE Automation:

It’s rare to see OLE Automation procedures enabled on a SQL Server instance. Most folks aren’t using these specially system procedure because they didn’t have a need to use them, have a compliance requirement that prohibits using them, or they tried using them and had adverse results. As the Microsoft documentation on OLE Automation stored procedures notes:

“Don’t directly or indirectly call Automation procedures from any SQL Server common language runtime (CLR) objects. Doing so can cause SQL Server to crash unexpectedly.”

But if this setting is enabled in any of your SQL Server instances, you need to consider it similarly to the xp_cmdshell setting. By that I mean: this is probably not a problem, but you should try to figure out why the setting is enabled, and how its associated system stored procedures are being used.

I find this perfectly reasonable. There’s a lot of fear around xp_cmdshell, when in practice, it doesn’t affect security at all unless you completely mess things up and start granting rights to non-sysadmins.

Jeff Iannucci shares good advice:

If you have ever used our free tool to check SQL Server security, you may have seen the check for the “TRUSTWORTHY database owned by sysadmin” show up as one of the highest of priority items, requiring action. When we started reviewing the security permissions and configurations for our clients’ instances, we didn’t expect to find it very often since TRUSTWORTHY database setting is off by default.

Unfortunately, this has been discovered with some frequency, and when combined with a few other common practices, it presents a tremendous vulnerability to escalate privileges for both authorized users and hackers.

Read on to learn more about this. And to supplement, I will once again link Solomon Rutzky’s outstanding guide on the topic.