Sql-server – SQL Server Service Accounts Max Character Length

Confusion comes from the fact that user account objects have two names in Active Directory: User Principal Name (UPN) and Security Accounts Manager ID (SAMID). Both of these must be unique in the forest.

The UPN is composed of the user logon name concatenated with an at symbol concatenated with one of the UPN suffixes that are set up in the AD forest. It is the "User logon name" entry in the dialog above. For example administrator@adatum.com.

The SAMID is composed of the NetBIOS name of the domain concatenated with a backslash concatenated with the NetBIOS name of the user account. It is the "User logon name (Pre-Windows 2000)" entry in the dialog above. For example, ADATUM\Administrator.

Many places in Windows accept either name; many do not. For example, logging on to a non-AD machine such as Windows NT4 requires the use of the SAMID. For example, logging on across a forest trust may require the use of the UPN.

The user account name part of the SAMID is also used to generate user folders (local profile, home folder, roaming profile, etc).

As for your problem, the service account should, as far as I know, accept either SAMID or UPN. You do, however, have to specify the full UPN. In other words sqlservice@adatum.com, not just sqlservice. I'll do some testing here once my VMs are started.

OK, after some testing (SQL Server 2008 R2 and SQL Server 2012), it seems that SQL Server Configuration Manager ust doesn't like the UPN. I kept getting a "specified network password is not correct" error. Google found me this StackOverflow issue. As that page states, changing the service account in the Services MMC can use both UPN and SAMID.

Historically, changing service accounts using a tool other than SQL Server Configuration Manager was bad as it didn't put the new service accounts in the correct groups. Newer versions of SQL Server don't use groups for registry and folder permissions but use virtual accounts instead. Despite that, I think my advice is still to always use Configuration Manager and to use specify service accounts using their SAMID.

As an aside, you may be better off using Managed Service Accounts (if your AD supports it). They remove the need for you to deal with password expiry and SPN registration.

So, firstly have a quick look through this which explains virtual accounts:

TechNet: Service Accounts Step-by-Step Guide

To answer your questions:

1/ You can't list these virtual accounts in that dialog box, I'm not 100% sure of the reason, but you always need to type them in manually, in your screenshot you also have the active directory domain listed, you would never see them in here as they are local accounts.

2/ You can't add permissions on files/shares on another machine by finding these accounts, as they live only on the machine that you installed SQL server on (NVOSIB-1C in your case). These accounts though can access resources on the network, but they do so in the context of the machine account of the server.

So, an example would be you have NVOSIB-1C with SQL Server installed and NVOSIB-Filer with a share on it that you want the SQL Server account to be able to access.

What you would do in this situation is to add the machine account of the SQL server to the permissions on the file share, you can do this just by assigning the account DOMAINNAME\NVOSIB-1C$ the permissions you want and then as the virtual account accesses the network in the context of the machine it lives on, it would have permission to do so.

Obviously this limits your ability somewhat to assign permissions, as anything running on NVOSIB-1C that authenticated in the context of the server would now have access to the share, if this is a concern, you can do one of 2 things:

• Use standard domain service accounts for the SQL Server services, you then need to maintain passwords etc for these, but it makes assigning permissions somewhat easier.

• Set up a managed service account in AD, this has the advantage that the local accounts have in that you don't need to worry about passwords, and also the advantage that you can see and manage them centrally in AD like a normal account, the main disadvantage is that you need to have the managed service account created before you install SQL server, so in your case you would have to reinstall to take advantage of this.