none
SQL Server 2012 Installation: Service Accounts

    Question

  • Hi

    I am new to SQL Server 2012. I need help with Service Accounts on Server Configuration page. I created another account with a username and password and used it in Service Accounts during SQL server 2012 installation. But SQL server 2012 works only when I log on with that account and not with mine. If I leave everything default, SQL Server still does not work. What I mean is SSMS works fine, I just can't log on to SQL Server through my ASP.NET application. I have seen many tutorials but none deals with this page in detail so that I can understand it.

    I really do not understand a bit of the following and also do not know how to tackle these:

    NT AUTHORITY\System

    NT AUTHORITY\NetworkService

    NT AUTHORITY\LOCAL SERVICE(default)

    Can anybody guide me in a plain and simple way how to set these account and what is a managed service account?

    I'll really appreciate your help

    Thanks.

    Sunday, November 11, 2012 3:07 PM

Answers

  • Hi nice_newbee,

    Local System Account: Local System is a very high-privileged built-in account. It has extensive privileges on the local system and acts as the computer on the network. The actual name of the account is NT AUTHORITY\SYSTEM.

    Network Service Account: The Network Service account is a built-in account that has more access to resources and objects than members of the Users group. Services that run as the Network Service account access network resources by using the credentials of the computer account in the format <domain_name>\<computer_name>$. The actual name of the account is NT AUTHORITY\NETWORK SERVICE.

    Local Service Account: The Local Service account is a built-in account that has the same level of access to resources and objects as members of the Users group. This limited access helps safeguard the system if individual services or processes are compromised. Services that run as the Local Service account access network resources as a null session without credentials. Be aware that the Local Service account is not supported for the SQL Server or SQL Server Agent services. Local Service is not supported as the account running those services because it is a shared service and any other services running under local service would have system administrator access to SQL Server. The actual name of the account is NT AUTHORITY\LOCAL SERVICE.

    Configure Windows Service Account and Permissions: http://msdn.microsoft.com/en-us/library/ms143504(v=sql.110).aspx.
     
    You installed SQL Server 2012 with another account. Please use that account to login the SQL Server and add your own account to SQL Server as sysadmin. Then you could use your own account to login the SQL Server.
    Create a Login: http://msdn.microsoft.com/en-us/library/aa337562.aspx.

    If you have any problem, please feel free to let me know.

    TechNet Subscriber Support
    If you are
    TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

    Thanks.

     


    Maggie Luo

    TechNet Community Support

    Monday, November 12, 2012 2:47 PM

All replies

  • If you can tell us the error message, it would be better to point you in certain direction.
    Mostly the error which you are getting in application is "login failed for user". If yes then you need to add that account in SQL Server which is used to access SQL Server.


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Sunday, November 11, 2012 3:17 PM
  • Hi Balmukand, thanks for the reply.

    Mostly the login fails for NT AUTHORITY\System.

    But I wanted to know how to configure Service Accounts?

    And what do you mean by this? "you need to add that account in SQL Server which is used to access SQL Server. "

    Thanks

    Sunday, November 11, 2012 3:33 PM
  • Okay. If you are getting login failed for use "NT AUTHORITY\System" then it means that IIS/ASP.net is sending this account to connect to SQL. If you want SQL login to connect to SQL Server then you need to modify web.config file (I am not an IIS expert so don't know if you can have other file) and use SQL Account. OR you need to run the application pool with domain account rather than system account.
    If you don't know more about IIS (like me) then add "NT AUTHORITY\System" account in SQL Server as login using below command. If you want you can make this as sysadmin as well (NOT recommended)

    USE [master]
    GO
    CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
    GO
    ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT AUTHORITY\SYSTEM]
    GO
    


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Sunday, November 11, 2012 4:10 PM
  • Hi Balmukand, thanks for the reply and I really appreciate your help.

    But this is not what I am looking for. I already have this solution at hand. What I wanted to know was how to configure Service Accounts for SQL Server?

    And I wonder does anybody know about it (not you)? Is it really so complicated? Or cannot be done?

    Thanks anyway.

    Monday, November 12, 2012 11:45 AM
  • Generally you have to assign a domain account not being a member of Admin groups but must have appropriate permissions, for example if you need backup the database to remote machine, that account has to be granted read/write to the share folder on this machine.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Monday, November 12, 2012 11:51 AM
  • Use SQL Server Configuration Manager to modify the Service accounts for SQL Server.

    SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )

    Monday, November 12, 2012 11:58 AM
  • Hi Uri Dimant, thanks for the reply.

    By the way I am doing all this on my local machine, Windows 8 Pro and SQL Server 2012 Web Edition (No problems on my production server). And I am not administering SQL Server on a large scale. So will you please explain (if you can) a little more. What is a domain account and appropriate permissions?

    As I said earlier (in my replies above) when I create another windows account and add that username and password to SQL Server installation, I have to log on to windows with that account to use SQL Server and this is not what I want. So how to set up an account to be used with SQL Server Service Accounts? Does it have to be a windows account?

    If I leave everything default on Service Accounts page, it defaults to NT AUTHORITY\System and Services. What the hell is that? And it does not work.

    It is all really confusing. I am not at all getting the hang of it. Can you guide me somewhere? Any good simple solution or tutorial etc.

    Thanks again for your help.


    • Edited by nice_newbee Monday, November 12, 2012 12:11 PM
    Monday, November 12, 2012 12:09 PM
  • Hi nice_newbee,

    Local System Account: Local System is a very high-privileged built-in account. It has extensive privileges on the local system and acts as the computer on the network. The actual name of the account is NT AUTHORITY\SYSTEM.

    Network Service Account: The Network Service account is a built-in account that has more access to resources and objects than members of the Users group. Services that run as the Network Service account access network resources by using the credentials of the computer account in the format <domain_name>\<computer_name>$. The actual name of the account is NT AUTHORITY\NETWORK SERVICE.

    Local Service Account: The Local Service account is a built-in account that has the same level of access to resources and objects as members of the Users group. This limited access helps safeguard the system if individual services or processes are compromised. Services that run as the Local Service account access network resources as a null session without credentials. Be aware that the Local Service account is not supported for the SQL Server or SQL Server Agent services. Local Service is not supported as the account running those services because it is a shared service and any other services running under local service would have system administrator access to SQL Server. The actual name of the account is NT AUTHORITY\LOCAL SERVICE.

    Configure Windows Service Account and Permissions: http://msdn.microsoft.com/en-us/library/ms143504(v=sql.110).aspx.
     
    You installed SQL Server 2012 with another account. Please use that account to login the SQL Server and add your own account to SQL Server as sysadmin. Then you could use your own account to login the SQL Server.
    Create a Login: http://msdn.microsoft.com/en-us/library/aa337562.aspx.

    If you have any problem, please feel free to let me know.

    TechNet Subscriber Support
    If you are
    TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

    Thanks.

     


    Maggie Luo

    TechNet Community Support

    Monday, November 12, 2012 2:47 PM
  • Hi Maggie, thanks a lot for the reply. It was more like a reply.

    I am already there:Configure Windows Service Accounts and Permissions.

    Thanks for the login link.

    I shall let you know once it is sorted out and also how (if possible).

    Monday, November 12, 2012 3:48 PM
  • Hi Maggie and all of you for your help

    Here's what I have done:

    1. Created a new user in Users of "Users and Groups" section of Computer Management.
    2. Added this account to all SQL Server Service accounts (instead of creating separate account for each service as recommended by Microsoft)
    3. Completed SQL Server 2012 installation.
    4. Deployed my databases.
    5. After playing around for a while with my ASP.NET application and IIS8( and failures, of course) I configured my IIS Application Pools and set the DefaultAppPool's identity to custom and added the account I created in step 1 above.
    6. And Voila! That's all folks.
    7. Did not have to change anything in web.config nor any other configurations.

    So all I wanted to know is:

    Did I do it correctly?

    In other words:

    Was it the right way of doing this?

    Thanks again for all of your help and support. I appreciate your efforts.

    Tuesday, November 13, 2012 4:01 PM
  • Hi nice_newbee,

    I think it is OK. You created a new user and added this account to SQL Server Account. Then you could use this user to log on the SQL Server instance.

    Thanks.


    Maggie Luo

    TechNet Community Support

    Wednesday, November 21, 2012 9:28 AM