A Fine Slice Of SQL Server
There are obviously many things to check when it comes to SQL Server security. We’re talking about a lot of those elements of security this month. Today I want to talk a little more about backups as it relates to their role in securing your SQL Servers.
When it comes to your backups and security in SQL Server –>
Read on for five tips to help ensure your backup strategy is doing what you intend it to do, but is also not a real attack vector at the same time.
Comments closedDavid Seis talks about the DAC:
In SQL Server, a feature that often stands out for its unique capabilities is the Dedicated Administrator Connection (DAC). This not just another connection to your SQL Server instance, but a special diagnostic connection designed for administrators when standard connections to the server are not possible.
Read on to see how it differs from regular connections and how you can use it.
Comments closedJeff Iannucci continues a series on security:
If you have been reading along with our series of “30 SQL Server Security Checks in 30 days” posts, you’ve probably noticed a theme for a lot of these posts, where we recommend reviewing which principals have CONTROL SERVER permissions or are in the sysadmin role.
Full disclosure: I hope you aren’t tired of that yet, because there will be more of those posts.
However, today I wanted to turn your attention towards what might be considered potential shadow members of the sysadmin role. These are the members of the server’s local Windows Administrators group.
Read on for Jeff’s explanation.
Comments closedDavid Seis locks down linked servers:
SQL Server Linked Servers provide a method to directly execute distributed queries on remote databases. However, they are not an ideal tool due to performance issues. If you decide to use them, it’s crucial to ensure they are secure. This post will outline some best practices for securing SQL Server Linked Servers.
There’s not an enormous amount you can do with security and linked servers. If you are looking for a bit more granularity in that regard, PolyBase could be a viable alternative…says the guy who wrote a book on PolyBase…
Comments closedAndy Brownsword doesn’t just leave passwords in plaintext:
Configuring SSIS projects or packages can necessitate parametering information which may include sensitive values such as authentication details. Parameters are stored as plain text in the database by default. We’ll demonstrate how to protect these values using Sensitive parameters.
Read on to learn how to make an SSIS project parameter sensitive, as well as how to use them afterward.
Comments closedRod 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 closedDavid 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 closedDavid Seis puts the orphans to work:
In SQL Server, a user becomes ‘orphaned’ when it exists within a database but lacks an associated login at the server level. This typically occurs when a database is either moved or restored to a different SQL Server instance. To understand why, it’s important to note that while logins are created at the server level, users are created at the database level. Each login is linked to a unique Security Identifier (SID). Therefore, during the process of moving or restoring a database, the SIDs may not align correctly, resulting in orphaned users.
Read on for a script to find and fix orphaned users.
Comments closedSecurity 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 closedDavid 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