In some scenarios, when SQL Server needs to access network resource such as backing up a database to a network file share, we need to configure SQL Server service account appropriately.
Currently, for SQL Server 2005, 2008 and 2008 R2, the following accounts are supported to run SQL Server service:
- Domain User Account
- Local User Account
- Local System Account (built-in)
- Network Service Account (built-in)
In above four types of accounts, only Local User Account does not support to access network resources as it is recognized only on the local computer. For domain user account, it uses its our domain user name and password to access network resource. However, for built-in Local System Account and Network Service Account, they are acts as the computer account (DomainName\ComputerName$) on the network.
Here are some scenarios when we using built-in system accounts:
Suppose we have a SQL Server instance on server, SrvSQL01 in domain, Domain01 which is running under an Local System built-in account, and we need to backup a database to a remote file share server, SrvFileShare01, under \\SrvFileShare01\SQLServer\Backups share. To make sure we can backup the database properly, we need to perform the following security configurations.
- Make sure the current SQL Server login account has backup permission. For more information about backup permissions, see SQL Server Books Online.
- Since SQL Server runs under built-in Local System account, it will act as computer account which has SQL Server runs on. Therefore, we need to grant Write permission to Domain01\SrvSQL01$ on the remote share folder \\SrvFileShare01\SQLServer\Backups.
- Perform the backup database statement in SQL Server, and we should be able to backup the database correctly.
We have two SQL Server 2008 instances on different server boxes, SrvSQL01 and SrvSQL02, the both server instances are running under Network Service built-in system accounts. In this case, we need to configure a database mirroring session between these two server instances. For SQL Server Database Mirroring, we can configure a database mirroring session using Windows Authentication for network access or using certificates for inbound and outbound connections. In this case, we use Windows Authentication and to do this we need to performing the following steps:
- Setup a database mirroring session according to steps in the following BOL documentation: How to: Establish a Database Mirroring Session Using Windows Authentication (Transact-SQL).
- In database mirroring, the two servers (or three servers if using the third witness server) are communicating each other via Endpoints using SQL Server service account so that we need to make sure each SQL Server service account to have CONNECT permission on the other server’s endpoint. As we mentioned, Network Service system account acts as computer account on the network therefore we need to add each other’s computer account as a login and grant this account CONNECT permission on the corresponding database mirroring endpoint. You can refer to the sample T-SQL script to do this:
— Run the following T-SQL script to add mirroring partner’s service account
— as a SQL Server login and grant CONNECT permission on the database mirroring
— endpoint. This script is running on SrvSQL02.
CREATE LOGIN [Domain01\SrvSQL01$] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];
GRANT CONNECT ON ENDPOINT::[MirroringEndpoint] TO [Domain01\SrvSQL01$];
We need to run the similar T-SQL script on this server to add witness service account if any witness server configured; and also perform the same steps on other database mirroring partner as well as witness server if any.
Setting Up Windows Service Accounts
Database Mirroring Deployment