8

A few months ago we installed SQL Server 2012 in Windows 2008 R2 under the virtual account "NT Service\MSSQLSERVER", all good.

A few days ago, one of the admins of the IT dept installed Full Text Search component to the SQL Server 2012 (the problem is he could not recall what settings exactly he chose during the setup), and after that, quite a few problems come:

A. We checked the Windows Logs, in Application, we find out that MSSQLServer has quite a lot of abnormal logs, like:

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/FooComputer.FooDomain.com:1433 ] for the SQL Server service. Windows return code: 0xffffffff, state: 63. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

It seems to be the cause but no idea why and how to solve it.

B. SQL jobs with owners who are domain users like ("MyDomain\FooUser") will fail with following message:

The job failed. Unable to determine if the owner (MyDomain\FooUser) of job JOBNAME has server access (reason: Could not obtain information about Windows NT group/user 'MyDomain\FooUser', error code 0x6e. [SQLSTATE 42000] (Error 15404)).

We did intensive search, and finally replace the owner with "sa" and solved the problem, although it's not that decent. Still, we would like to find out why.

C. Cannot access network resources like a folder in other computers, for example, the following sql will return "access is denied":

DECLARE @CopyCommand nvarchar(1000)
set @CopyCommand = 'dir ' + Char(34) + '\\FooComputer\FooFolder\' + Char(34)
EXEC master..xp_cmdshell @CopyCommand

For problem C, according to MSDN (http://technet.microsoft.com/en-us/library/ms143504.aspx) we tried to grant full control access for the account "MyDomain\SQLServerComputerName$" to the folder, still same result.

3 Answers 3

9

Those three problems are all a result of the account running the SQL Service not being a domain account, and they will all be corrected by changing SQL to run under a domain account. Specifically:

A - an SPN is a Kerberos security feature that requires a domain account, and doesn't work with local accounts

B - In order to read from active directory, the service needs a domain account's credentials

C - Local accounts aren't recognized by remote computers, so they deny the connection attempt.

Here's a walk-through on how to change the service account:

http://technet.microsoft.com/en-us/library/ms345578.aspx

3
  • But what confused me is that it has been 2 months all working good from day 1 under the virtual account "NT Service\MSSQLSERVER". Why suddenly not working? And we did the same thing for SQL Server 2008 in Windows 2008 for years still no problem.
    – unruledboy
    May 14, 2013 at 3:58
  • 1
    You're saying it was set to use the service account previously, and it worked without issue? As I understand it, that's not possible - the service account is local to the machine, and won't authenticate on any other servers/workstations (unless maybe as the guest account, which is usually disabled). Switching to a proxy domain account is the way to go in any case, and the recommended method when your sql service needs to communicate with other machines on the domain.
    – SqlRyan
    May 14, 2013 at 14:33
  • Perfect. This helped me get the job done in a jiffy. Oct 27, 2014 at 9:03
3

Old question but doesn't seem to have a proper answer. NT Service\MSSQLSERVER being a local virtual account, it accesses the network as the computer account. And as long as the computer account has access to shares and filesystems you should be able to for example backup to UNC paths on the network. See Configure Windows Service Accounts and Permissions.

1

When we ran up to this issue, our resolution was to locate the folder and add the permissions needed for the virtual account needing this. Once we had added the account, simply by typing it in, we monitored the log files and found we no longer had the issue with respect to this folder/file.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .