What are SQL Server msdb databases, agent proxies and the public roles
SQL Agent proxies are a form of built-in service that allows the schedule and running of automated tasks within SQL Server. It is used by SQL Server Agent services for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail.
The msdb database is a crucial system database in Microsoft SQL Server which primarily serves SQL server agents. These databases store information related to SQL Agent jobs, including their configuration, execution history, vital system tables and data. They also maintain a comprehensive backup-and-restore history of the msdb database.
Unlike normal proxies which use specific user credentials and can therefore be given limited permissions to do a specific task, such as restricting access to specific databases, SQL server agents do not have such individual restrictions.
The public role of an SQL server is assigned to all users by default. The public role typically has very limited permissions or no permissions at all. This ensures users need to be explicitly assigned to a specific role with appropriate permissions to do anything meaningful within a database.
Physical Properties of msdb
The table below presents the initial configuration values for the msdb data and log files. Note that the sizes of these files can vary slightly depending on the edition of the SQL Server Database Engine.
| File | Logical name | Physical name | File growth |
|---|---|---|---|
| Primary data | MSDBData | MSDBData.mdf | Autogrow by 10 percent until the disk is full. |
| Log | MSDBLog | MSDBLog.ldf | Autogrow by 10 percent to a maximum of 2 terabytes. |
Why it’s important to not give an SQL agent proxy permissions in the public role
Ensuring SQL agent proxies are not granted access in the public role provides a level of assurance that only authorized users can control the automated tasks and tools within an SQL Server. Granting access to SQL Agent proxies for the public role would enable all users to utilize the proxy, even those with lower permissions, enabling them to potentially gain access to information they aren’t supposed to.
To help protect the information that is stored in msdb, it’s recommended to place the msdb transaction log on fault tolerant storage.
Not granting public role permissions prevents potential security risks and maintains the smooth operation of your automated processes.
Impact of revoking the public role permissions from a proxy
It is important to consider the needs of the proxy when revoking the public role as, if no alternative user defined database roles are granted, the proxy could lose access to data needed to properly function.
How to determine if access to proxies has been granted to the msdb’s public role
Use the following syntax to determine if access to any proxies have been granted to the msdb database’s public role.
| USE [msdb]
GO SELECT sp.name AS proxyname FROM dbo.sysproxylogin spl JOIN sys.database_principals dp ON dp.sid = spl.sid JOIN sysproxies sp ON sp.proxy_id = spl.proxy_id WHERE principal_id = USER_ID(‘public’); GO |
This query should not return any rows.
How to Ensure the public role in the msdb database is not granted access to SQL Agent proxies
- Ensure the required security principals are explicitly granted access to the proxy
(use sp_grant_login_to_proxy).
- Revoke access to the <proxyname> from the public role.
| USE [msdb]
GO EXEC dbo.sp_revoke_login_from_proxy @name = N’public’, @proxy_name = N'<proxyname>’; GO |
Default value
By default, the msdb public database role does not have access to any proxy.
Best practices
This setting is just one of many that must be properly configured for the smooth running and operations of a server. Each setting must balance with all the other settings, creating a delicate ecosystem that must be cared for and maintained.
Server hardening helps make this process easier, acting like a multi-layered shield against potential security threats. Server hardening provides a comprehensive security approach that goes beyond just configuring SQL Server permissions. It fortifies the entire server environment, making it a more robust and secure platform for hosting your valuable SQL Server data. Find out more HERE!
