SQL Server Agent Security and Network Access
As of SQL Server 2012, SQL and other Microsoft services run on “virtual accounts” by default instead of Network Service. This is part of a defense-in-depth strategy whereby the service running SQL is isolated from other services on the box. The virtual accounts have names like “NT SERVICE\MSSQLSERVER” and “NT SERVICE\SQLSERVERAGENT”. These are local accounts and have pretty limited access to the network. When they do access the network, they present the credentials of the Server, using the server’s name in the form of “DOMAIN\ServerName$”, eg, “Contoso\FinanceServer$”.
Details: https://msdn.microsoft.com/en-us/library/ms143504.aspx
The question I ran into is how to grant SQL Agent rights to network folders or remote database servers.
If you want to grant permissions for SQL Agent to get to folders on other servers, you can use normal Windows folder permissions, and search for this “servername$” account on the network as the user to grant permissions to. This is nice also if you would like to use xp_cmdshell to move some files around as part of a SQL job. xp_cmdshell runs under the context of the SQL Service account, and this use to be a real security concern since that account was often a very powerful domain account. If your SQL Server was hacked by a minor account but that account had xp_cmdshell access, they could roam around the network with elevated privileges, often admin level depending on how the server was configured. However that is no longer the case - xp_cmdshell is still running under SQL Service, but the SQL Service account is quite limited by default on the network and the security concern is greatly mitigated.
If your SQL Agent needs read access to a remote database, again you can create a “login from Windows” against the “servername$” account. When setting this up on the remote “target” SQL Server, the trick is to NOT use the “Search” button to find the account - it won’t find it. But if you type in “domain\servername$” and click “OK”, it will create the login, and then you can assign specific database access as needed. I first saw this described here in a post about connecting IIS to SQL Server in the era of virtual accounts:
https://blogs.msdn.microsoft.com/ericparvin/2015/04/14/how-to-add-the-applicationpoolidentity-to-a-sql-server-login/
Then in your Linked Server definition on the “home” SQL Server, you setup the security for logins to “Be made using their current security context”.
A lot of veterans would say you should just use create a real domain account specific to SQL Server. I think this had more value in the past before the default accounts were able to be so well isolated and locked down. I see the new scheme as basically accomplishing the same thing with less overhead.
Details: https://msdn.microsoft.com/en-us/library/ms143504.aspx
If you want to grant permissions for SQL Agent to get to folders on other servers, you can use normal Windows folder permissions, and search for this “servername$” account on the network as the user to grant permissions to. This is nice also if you would like to use xp_cmdshell to move some files around as part of a SQL job. xp_cmdshell runs under the context of the SQL Service account, and this use to be a real security concern since that account was often a very powerful domain account. If your SQL Server was hacked by a minor account but that account had xp_cmdshell access, they could roam around the network with elevated privileges, often admin level depending on how the server was configured. However that is no longer the case - xp_cmdshell is still running under SQL Service, but the SQL Service account is quite limited by default on the network and the security concern is greatly mitigated.
If your SQL Agent needs read access to a remote database, again you can create a “login from Windows” against the “servername$” account. When setting this up on the remote “target” SQL Server, the trick is to NOT use the “Search” button to find the account - it won’t find it. But if you type in “domain\servername$” and click “OK”, it will create the login, and then you can assign specific database access as needed. I first saw this described here in a post about connecting IIS to SQL Server in the era of virtual accounts:
https://blogs.msdn.microsoft.com/ericparvin/2015/04/14/how-to-add-the-applicationpoolidentity-to-a-sql-server-login/
Then in your Linked Server definition on the “home” SQL Server, you setup the security for logins to “Be made using their current security context”.
A lot of veterans would say you should just use create a real domain account specific to SQL Server. I think this had more value in the past before the default accounts were able to be so well isolated and locked down. I see the new scheme as basically accomplishing the same thing with less overhead.
Comments
Post a Comment