Category: Security

David Seis looks at three roles:

Understanding SQL Server roles is crucial for managing permissions and ensuring SQL Server security. In this post, we will delve into three specific roles: db_accessadmin, db_securityadmin, and db_ddladmin, discussing when each should be used and considerations for least privilege and security. We’ll also include a script you can use to audit your database roles.

Read on to see what each of those three do. I’m not sure I’ve ever worked in an environment that required use of any of these three roles. Typically, the person or set of people responsible for doing the activities associated with one of those three roles needed to do all three (and more).

Comments closed

Rod Edwards runs into legal troubles:

This is a finger pointing situation that i’ve witnessed in the past regarding native SQL Auditing, and the potential for edge case false positives. Something really not helpful when it comes to any security related topic.

This post is just to highlight a potential gotcha with the native SQL Auditing functionality, dependent on it’s configuration. It’s certainly not a best practice on setting up Auditing, or access controls, or even the intent someone may have in falling foul of any Audit. There are many awesome guides out there on how to do exactly that.

Despite this post not being any of those things, it is still quite useful in pointing out an edge case in auditing, one to which I don’t have a good answer.

Comments closed

Sean Gallardy is not amused:

Security must be making the rounds again as I’ve recently had quite a few inquiries about a “vulnerability” in SQL Server connections. I am in the camp of, if it isn’t easy to exploit or is completely theoretical and the exploit is barely an inconvenience such as, “could allow a local admin user to rearrange the startup order”, I don’t get very excited. Enter in the old-fad-turned-new “vulnerability” of version information. Yes, you didn’t misread that. Version. Information. At the very worst it’s potentially information disclosure and even that is a stretch.

Read on for an eminently sensible take.

Comments closed

David Seis explains and warns:

In SQL Server, an ownership chain is created when one object (like a stored procedure) accesses another object (like a table) in the same database. If both objects have the same owner, SQL Server only checks permissions for the first object, not the second. This is known as ownership chaining.

Cross-database ownership chaining extends this concept across databases. If a stored procedure in Database A accesses a table in Database B, and both objects have the same owner, SQL Server will not check permissions for the table in Database B.

For what I consider the definitive explanation of why you shouldn’t use cross-database ownership chaining (or other techniques like setting TRUSTWORTHY on), Solomon Rutzky’s article is it. Module signing is the answer and the rest are traps.

Comments closed

Jeff Iannucci continues a series on security in SQL Server:

SQL Server logins are often necessary, like the sa login, but be aware they present a particular set of vulnerability issues. Their lack of multifactor authentication, their susceptibility to brute force attacks, and the dumb passwords folks give SQL Server logins make them a prime target for hackers.

Read on to learn more about these three issues. Jeff also includes a note about a built-in function that lets you compare the hashed passwords in your systems versus a specific password, with the idea that you can at least find if people are using some of the most common passwords.

Comments closed

David Seis has some advice:

Roles play a crucial part in managing permissions and access control. One such role that often gets overlooked is the public role. This role, by default, has minimal permissions and every database user is a member. Sometimes, however, there are permissions granted to the public role which puts your data at risk.

David’s focus is not adding new permissions to the public role. I also recommend not taking permissions away. Some security tools will gripe that the public role has CONNECT access. I was foolish enough to follow those tools’ recommendations once and removed CONNECT from public. Then I got a bunch of calls about people not being able to log in… I suppose you can spin a yarn about how there are logins that a person should not be able to use for connections, but how often is that really the case?

Comments closed

Pieter Vanhove explains what secure enclaves are and why they’re useful:

Encryption is a vital technique for protecting sensitive data from unauthorized access or modification. SQL Server and Azure SQL Database offer two encryption technologies that allow you to encrypt data in use: Always Encrypted and Always Encrypted with secure enclaves. In this blog post, we will compare these two technologies and highlight their benefits and limitations.

Read on to learn more.

Comments closed

Jeff Iannucci talks about a role that might as well be sysadmin:

Based on the name, you probably can guess that members of the securityadmin role can make dangerous changes to the permissions of other server principals. What many folks don’t realize is that this role is simultaneously less dangerous and more dangerous than you might think.

Allow me to explain, or better yet show you what that means.

Click through for the explanation.

Comments closed

Jeff Iannucci has a series on security in SQL Server:

The CONTROL SERVER permission has been around since SQL Server 2005, and is the most powerful permission granted as part of membership in the sysadmin role. What many folks don’t realize is that this permission can be granted to a login or group without including them in the sysadmin role. And that can become problematic if, as an administrator, you aren’t aware of logins or groups that don’t have this permission.

Jeff points out how CONTROL SERVER isn’t quite the same as sysadmin, but why you should still treat it that way.

Comments closed