I've just set up SQL Server 2012 with domain service accounts. I found that the account name would not validate unless I used the shorter user name.

enter image description here

Is there any way of using the full names?

  • What do you mean by 'shorter user name' ? The NT valid name formats are described at msdn.microsoft.com/en-us/library/windows/desktop/…. SQL supports the NameSamCompatible format. – Remus Rusanu May 2 '13 at 8:25
  • @RemusRusanu image attached to Question. Is the *Reportin user name the NameSamCompatible one? – mizzle May 2 '13 at 10:03
  • That image is not from any SQL Server Management tool. Only use SQL Server tools to manage SQL Server services, like the SQL Server Configuration Manager. – Remus Rusanu May 2 '13 at 12:49
  • @RemusRusanu how do you create the AD user from there? – mizzle May 2 '13 at 13:08
  • 1
    The screenshot is from AD. He means to only use the SQL Server Configuration Manager application to make changes to the SQL Server service account usage (don't use the Services applet in Windows). In any event, I haven't found a way to use the long name either. But at the same time, I don't see why this is a problem? – Jon Seigel May 2 '13 at 14:07

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.

| improve this answer | |
  • 1
    The reason why SQL Server Configuration Manager should be used to change the service accounts is because doing so automatically regenerates the service master key; the applet in Control Panel does not. See the Remarks section here. Good answer, though. +1 – Jon Seigel May 6 '13 at 2:40
  • Excellent point, Jon. I'd forgotten all about the keys. Thanks. – Greenstone Walker May 6 '13 at 20:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.