Friday, September 5, 2014

SQL Server Virtual Service Accounts

SQL Server Virtual Service Accounts
From SQL Server 2012 onwards if you observe the SQL Server configuration manager you will notice SQL Server Engine and SQL Server Agent will be assigned with two different accounts like NT Service\MSSQLServer or NT Service\MSSQL$InstanceName and NT Service\SQLServerAgent or NT Service\SQLAgent$Instance, these two are known as virtual service accounts and were introduced in SQL Server 2012. For earlier versions like SQL Server 2005, 2008 if you observe you will see NT Authority\System or Local System but there is no NT Service\MSSQLServer.

Virtual Accounts was introduced in windows 2008 R2 which is managed local account with auto-password. These virtual accounts are unique instances of NT Service account and we cant find this accounts in Local Users and Groups and they cannot be deleted and you cannot change password or you cannot get password of the virtual account that will be auto password maintained by OS. When you install SQL Server 2012 on top of windows 2008 R2 you will see these virtual accounts in the SQL Server configuration manager, DBA’s generally runs SQL Service with Local system account or Domain Account with limited privileges, tough it is tough periodically they will change the password of domain accounts for security reasons. If we use virtual accounts then we can avoid this situations like changing passwords periodically.

For some reason if you change the virtual account to Local System for SQL Server Engine. Later you want to assign virtual account back to SQL Service, now you cant browse this account because it is not found in local users and groups, So you need to type the account as NT Service\MSSQLServer, For password you don’t know the password if you give any password system will say it’s an incorrect password, So you need to leave the password box blank and then click apply to get the auto generated password automatically. Like this you can change the service accounts to assign virtual accounts.

No comments: