Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Using MS SQL Server 2012 in Microsoft Windows Server 2008.

I am somewhat confused by the New Account Types Available with Windows 7 and Windows Server 2008 R2 virtual Windows accounts [NT SERVICE]\<SERVICENAME> like NT SERVICE\MSSQLSERVER, NT SERVICE\SQLSERVERAGENT, etc.

Is it viable of daring to give permissions to, for example, [NT Service\SQLSERVERAGENT]' to access a shared resource (or local file or directory)?
And, how to do this?
For example, to attribute permissions to a Windows file share to run psexec ?

While browsing (or pressing button Find< or adding group and users to a file share) the available in domain and/or local/remote computer, there is no such accounts available and entering it manually give an error:

"An object (User or Built-in security principal) with the following cannot be found..."

enter image description here

Related (though different) question that provoked this one: How to copy bak files to remote share (without AD/domain accounts involvement)?

UPDATE, answering @Jon-Siegel's comment to my deleted answer: there is no error in my screenshot, sometimes virtual accounts detection happens, sometimes fails. Really the last 2 days I cannot reproduce it.

Can it be explained that I do not have proper permissions?
When I try to open SQL Sever Configuration Manager, I am always getting:

--------------------------- 
SQL Server Configuration Manager
 --------------------------- 
Cannot connect to WMI provider.   
You do not have permission or the server is unreachable.  
Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.  
Invalid class [0x80041010] 
 --------------------------- 
OK   
---------------------------

SQL Server Configuration Manager error

Also, in eventvwr.msc I observe a lot of permission-related errors (under System):

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID   
{FDC3723D-1588-4BA3-92D4-42C430735D7D}  
 and APPID   
{83B33982-693D-4824-B42E-7196AE61BB05}  
 to the user NT SERVICE\SQLSERVERAGENT SID (S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430) from address LocalHost (Using LRPC).  
This security permission can be modified using the Component Services administrative tool

.

enter image description here

I am not sure whether I should open another question or stick with this one... The follow-up questions are:
1) what are permissions for reading virtual accounts?

I cannot list anything except local computer in "Select Users, Computers, Service Accounts, Cgoups or Built-in security principals" - the entries are visible but on opening them there are no a single sub-entries visible to me:

enter image description here

2)
How can I enter the NT SERVICE\SQLSERVERAGENT (by typing in it manually) on another computer?

Currently I can choose it only on local computer:

  • by choosing the name of local computer through pressing "Locations..." button, then choosing top entry in the tree list - see the scrrenshot above;
  • then typing "NT Service\SQLSERVERAGENT" in "Enter the object names to select" listbox

    Check names - before

    which is after pressing "Check names" button:

    enter image description here

That is, there is no hint how to type it.
Pressing "Advanced..." button, then "Find Now") shows a list where virtual accounts are absent:

enter image description here

share|improve this question
    
Is the instance of SQL Server a default instance, or a named instance? –  Jon Seigel Oct 14 '13 at 18:59
    
@JonSiegel, default instance. NVOSIB-1C, in this case, is the name of host (machine running MS Windows Server 2008) –  Gennady Vanin Геннадий Ванин Oct 15 '13 at 1:18
    
Windows Server 2008, or 2008 R2? If memory serves, service SIDs were not introduced until 2008 R2. As far as I can tell, you're following the correct procedure, so if you're on 2008, that's the only thing I can think of that would explain it. –  Jon Seigel Oct 15 '13 at 1:33
    
Windows Server 2008 R2 Standard, 64-bit Operating System –  Gennady Vanin Геннадий Ванин Oct 15 '13 at 5:39
    
Ideally you should never attribute permissions to accounts. Always create a group, grant relevant permissions to this group, then add account(s) to the group. Applies to virtual accounts as well. –  Remus Rusanu Oct 15 '13 at 9:21

2 Answers 2

they are service accounts and can not be used as user accounts. Instead of explaining it all i would like you to read nice blog by Rick. see the below link.

Service/Database Accounts

share|improve this answer

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.

share|improve this answer
    
this is rather conceptual question, it doesn't mean I would use it practically. "As they live only on the machine that you installed SQL server on (NVOSIB-1C in your case)" - I have SQL Server 2012 installed on another machine too. I thought it was obvious from the question. "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" - so, I can do it for NT Service\MSSQLSERVER then I do not see how and why it is different for SQLSERVERAGENT –  Gennady Vanin Геннадий Ванин Oct 16 '13 at 22:24
    
Citing RTFM: "Virtual accounts cannot be authenticated to a remote location. All virtual accounts use the permission of machine account. Provision the machine account in the format <domain_name>\<computer_name>$' –  Gennady Vanin Геннадий Ванин Oct 16 '13 at 22:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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