次の方法で共有


You really need to look out for msdb security

So msdb, we don't really think about it that much right? We know it holds the metadata for our Jobs and Schedules, also some good info on page corruptions, as well as backups, and much more.

But how often do we think about it’s security? You may already know that the Guest user is enabled for msdb, but you should also know that this should not be disabled, as pointed here https://support.microsoft.com/en-gb/help/2539091/you-should-not-disable-the-guest-user-in-the-msdb-database-in-sql-serv

Another configuration that is ON for msdb by default is the Trustworthy setting, and again is a setting that should not be turn off, some details on what this setting is https://support.microsoft.com/en-us/help/2183687/guidelines-for-using-the-trustworthy-database-setting-in-sql-server

but what does this mean, and could it be a problem? The short answer is YES - let's check this out.

Let's start with the fact that a lot of times our practice is to make sa the owner of all databases, a practice that although providing easier management, does not do us any favours from the security perspective.

So imagine the scenario that we have a third party app that does some automation and scheduling tasks for us, and we have been asked to add the app login to the db_owner role in msdb

So we create the login, and map it to msdb db_owner, also we make sure its not a sysadmin.

image     image

Let's just check we can now connect to SQL with the login.

image

Now, the fact that this login is a db_owner (this also apply if the impersonate permission is given to the user) on msdb allows it to exercise the impersonation privilege, and  we could then impersonate the owner of the database, which is? You guessed it, the sa account (by the way the sa account could be disabled but this just prohibits connecting with the sa credentials, but we connected to SQL with applogin, that's why the permissions are still working fine).

image

From here everything gets quite simple, getting data from another databases, or create another user, or make it part if the sysadmin server role even though we logged in to the server with the applogin credentials.

image

So what should we do then?  We cannot disable the Trustworthy setting, but we can manage the owner of the database and more importantly the permissions we give to applogin. Instead of providing db_owner we should look the necessary permissions for it, and fit it on the least privilege model, check if you can use one of the existing db roles in msdb (https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles#msdb-roles) if not, them assign the specific needed permissions to applogin.

Hope this helps you on maintaining your data secured.