0

SQL Server 2008 R2 on Windows Server 2012 R2, none of the built in security principals are appearing when trying to view user accounts and assigning permissions.

They are available on the service to assign the LOGON as value. Specifically I am looking for NETWORK SERVICE.

FOR CLARIFICATION: I am referring to the local accounts IUSER, NETWORK SERVICE, LOCAL SYSTEM, etc. enter image description here

I just completed a parallel installation on a demo server with no issues both 2012 R2 boxes. The one with issue is a VM.

And just to confirm, the accounts DO IN FACT exist on the server. So they were created during the OS install.

I uninstalled an reinstalled both SQL Server and SSMS, both back up and running, same issue.

Any ideas on how to get those back?

htm11h
  • 1,739
  • 8
  • 47
  • 104
  • What do you mean? The server logins, server roles, database users, or database roles? – Bacon Bits Apr 08 '15 at 15:31
  • I am referring to the local accounts, IUSER, NETWORK SERVICE, LOCAL SYSTEM, etc. any account that is normally listed as part of the local system and is usually available within SSMS to apply to a database should you need to, is not listed. – htm11h Apr 08 '15 at 15:32
  • Can't you just add logins through wizard? – Giorgi Nakeuri Apr 08 '15 at 15:41
  • No that is my point they are not available. Not sure why. Install of OS and SQL was uneventful. Other than this issue. Just updated OP with image. – htm11h Apr 08 '15 at 15:43
  • First add Login, then add user. – Giorgi Nakeuri Apr 08 '15 at 15:49
  • @Giorgi Nakeuri, not sure I under stand the issue. First I would like to learn why they do not appear. But secondly, where exactly do I add Login? – htm11h Apr 08 '15 at 15:51
  • 1
    Check if you have appropriate logins under Server->Security->Logins. If yes then enter the beginning of name in Select Login window and press Check Names – Giorgi Nakeuri Apr 08 '15 at 15:54
  • Yes they all exist. I have added logon rights for one account. – htm11h Apr 08 '15 at 15:55
  • 1
    On your picture left top corner window: `enter the beginning of name in Select Login window and press Check Names` – Giorgi Nakeuri Apr 08 '15 at 15:58
  • Yes it does find it. Just not sure why it was not listed. I am still getting an error when trying to connect to the DB. CREATE DATABASE permission denied in database 'master'. An attempt to attach an auto-named database for file C:\inetpub\techTrans\App_Data\myDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. – htm11h Apr 08 '15 at 16:01
  • Are you attaching database from asp application? This is quite a different story/question don't you think? – Giorgi Nakeuri Apr 08 '15 at 16:07
  • Try removing `User Instance=true` in your connection string – Giorgi Nakeuri Apr 08 '15 at 16:10
  • Yes it is, but it was the initial error that got me to the missing principals. I will post a new question. Thanks for your help. Yes I am trying to connect from an asp app on the same server. – htm11h Apr 08 '15 at 16:11

1 Answers1

2

IUSR isn't a default principal. That's an IIS user account in IIS 7 for anonymous access. SQL Server doesn't assume IIS needs access to the instance, let alone that IIS anonymous users need access. Beginning with IIS 7.5, that was considered a legacy account, IIRC, and isn't even created by default. If you have an account actually named "IUSER", then I imagine that's an account specific to your environment.

You may not see NETWORK SERVICE or LOCAL SERVICE since those accounts were virtualized in Windows Server 2008 R2 and later. SQL Server 2012 doc makes this more clear (scroll down to the bit about "New Account Types Available with Windows 7 and Windows Server 2008 R2"). SQL Server 2008 R2 doc likely wouldn't have been written with changes to Windows Server 2008 R2 wholly in mind. I can't seem to find clear documentation about what SQL Server 2008 R2 does on these systems, but on my installations at least (SQL 2008 R2 SP1 media installed on 2008 R2 SP1 installations, updated later) these older accounts aren't present at all while the virtual accounts are.

You should see NT AUTHORITY\SYSTEM, NT SERVICE\MSSQLSERVER (or NT SERVICE\MSSQL$InstanceName) group, and NT SERVICE\SQLSERVERAGENT (or NT SERVICE\SQLAgent$InstanceName) group. Those last two are the virtual accounts that are references to the service accounts used to start those services configured in SQL Server Configuration Manager. You should also see ##MS_PolicyTsqlExecutionLogin## and ##MS_PolicyEventProcessingLogin##, but they're probably disabled.

If you included SQL authentication, you should see the sa account, too. You should also see whatever accounts you added explicitly during the install. Beginning in SQL Server 2008, I think that's it for defaults. If you upgraded an existing instance, you may see different things, otherwise I would expect those accounts were explicitly added during or after the install on the other server.

Bottom line, you need to create missing server logins and associated database users yourself, but you may not be able to create NETWORK SERVICE or LOCAL SERVICE since they may not even exist.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
  • What is really bizarre here is that I just finished installing a clean load of the same OS and SQL config for a development platform and all of the expected accounts are present. I went to perform the same set up in the VM for production and they are not there. – htm11h Apr 08 '15 at 16:42
  • @htm11h Same install media and everything? Truly, I think your physical box is the one that's unusual. It sounds more like a Windows Server 2008 install than Windows Server 2008 R2. – Bacon Bits Apr 08 '15 at 17:11
  • Yes same media. Except for the OS which has its own install CD. I have the exact same set up on a prod and test 2003 systems. Trying to migrate before EOL of 2003. Prod is 2003 on VM. – htm11h Apr 08 '15 at 17:32
  • could you take a look at this post: http://stackoverflow.com/questions/29520286/sql-server-2008-r2-on-server-2012-r2-web-site-connection-issue it is the original issue, before I tracked down the missing principles problem. – htm11h Apr 08 '15 at 17:35