none
SQL Server service accounts Problem

    Question

  • I was attempting to setup a network domain by adding the roles DNS Server and Active Directory Domain Server roles to a windows 2008 server that I had previously setup. Note on the machine computer I had already installed SQL Server 2008 R2 which I later found out you should not do. So I uninstall the two roles. But now my SQL Server will not start\run when using an user account I setup for it to run under. It will now only run using the local system account which is a problem. But also the windows authentication logon to the database engine no longer works either. I can only logon to the database engine using SQL server login account SA. What do I have to do to get the SQL Server service to run under a user account again and how do I get SQL Server database engine to login using the windows authentication account to work again? I am trying to avoid a uninstall\reinstall of SQL Server 2008 R2 because I do not want to lose any of my SQL Server Analysis Server (SSAS) cubes that I had previously  created.

    Saturday, October 05, 2013 10:10 PM

All replies

  • You shouldn't need to reinstall.  First just switch the service account to Network Service using SQL Server Configuration manager.  This is a low-privilege account suitable for running SQL Server.  Then connect as SA (one last time) and create logins for BUILTN\Administrators and for your individual Windows account.  Add both logins to the sysadmin fixed server role.  Verify that you can connect, and then disable the SA account.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Saturday, October 05, 2013 11:48 PM
  • What error are you getting in the Application/System event viewer and SQL Server errorlogs when you are trying to start the service with the domain account ?

    Is this machine part of the domain or in workgroup ? If its in domain you should be able to add the Windows logins to SQL Server.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Sunday, October 06, 2013 6:30 AM
  • I was able to switch the log on accounts to Network Service Accounts. But I NOT was able to assign the computer Administrator account the SYSadmin fixed server role. When I tried to login to MSSQLSERVER database I go the message Login Fail for Computername\Administrator Microsoft SQL Server, Error 18456. Also I changed the authentication mode to windows authentication only so now I cannot login using the SA account. How do I change the SQL Server to Mix Mode again? 

    This computer was once part of a domain. Actually I installed AD and DNS server role on this computer but then I uninstalled those server roles. Now the computer is part of a workgroup, The domain accounts were deleted when I uninstall the domain role and user account were created for example Administrator account. I mistakenly turned on the option Windows Authentication only. The system seems to think that I am trying to login with SQL authentication. But in the logon dialog I choose windows authentication. See below the error message:

    TITLE: Connect to Server
    ------------------------------

    Cannot connect to WIN-JVF4UHQA6GS.

    ------------------------------
    ADDITIONAL INFORMATION:

    Login failed for user 'WIN-JVF4UHQA6GS\Administrator'. (Microsoft SQL Server, Error: 18456)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------


    Log Name:      Application
    Source:        MSSQLSERVER
    Date:          10/11/2013 5:00:42 PM
    Event ID:      18456
    Task Category: Logon
    Level:         Information
    Keywords:      Classic,Audit Failure
    User:          N/A
    Computer:      WIN-JVF4UHQA6GS
    Description:
    Login failed for user 'sa'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: 192.168.1.109]
    Event Xml:
    <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
      <System>
        <Provider Name="MSSQLSERVER" />
        <EventID Qualifiers="49152">18456</EventID>
        <Level>0</Level>
        <Task>4</Task>
        <Keywords>0x90000000000000</Keywords>
        <TimeCreated SystemTime="2013-10-11T21:00:42.000000000Z" />
        <EventRecordID>14073</EventRecordID>
        <Channel>Application</Channel>
        <Computer>WIN-JVF4UHQA6GS</Computer>
        <Security />
      </System>
      <EventData>
        <Data>sa</Data>
        <Data> Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.</Data>
        <Data> [CLIENT: 192.168.1.109]</Data>
        <Binary>184800000E00000010000000570049004E002D004A0056004600340055004800510041003600470053000000070000006D00610073007400650072000000</Binary>
      </EventData>
    </Event>

    • Edited by k1vinsrtech Friday, October 11, 2013 10:17 PM
    Friday, October 11, 2013 2:22 AM
  • Verify the Sa account not disabled, then try like below-

    To change security authentication mode

    1. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.

    2. On the Security page, under Server authentication, select the new server authentication mode, and then click OK.

    3. In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.

    ref-http://technet.microsoft.com/en-us/library/ms188670%28v=sql.105%29.aspx

    Also you need to consider while selecting the domain account as follows-

    Setting Up Windows Service Accounts
    http://msdn.microsoft.com/en-US/library/ms143504%28v=sql.105%29.aspx

    Also you verify whether the computer name or domain which you have entered is correct WIN-JVF4UHQA6GS\Administrator & you are part of windows administrator &that exists in the sql server as well with Sysadmin role


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Sunday, October 13, 2013 2:42 AM
  • The last response was good informatiom but now I cannot login into the SQL Server at all. I changed the SQL Server to use windows authentication only option and the windows authentication access to my SQL Server has been broken since I uninstalled the Active Directory and DNS server roles a while back on the computer. Is the component/function of SQL Server authentication mode contained/controlled by the SQL Server master database? Is there a way to turn Mix Mode Authentication back on and fix the problem with the windows authentication problem as well?


    • Edited by k1vinsrtech Tuesday, October 15, 2013 11:40 PM
    Tuesday, October 15, 2013 5:48 PM
  • I turned on Mix mode by location the LoginMode entry in the registry. I looked up the error for the windows authentication login process and it indicated to look at the state value for further explaination. However I did not see a state value. Where would the state value be located in the Windows event viewer? 
    • Edited by k1vinsrtech Thursday, October 17, 2013 5:32 PM
    Thursday, October 17, 2013 5:31 PM
  • Hi if its really issue with domain account which you are trying to use for sql then you need to fix that from the AD level & ensure that added to the windows administrator group as well & in the sql server added account with sysadmin role.

    Is the component/function of SQL Server authentication mode contained/controlled by the SQL Server master database? >>SQL server authentication mode means-

    SQL Server Authentication

    When a user connects with a specified login name and password from a nontrusted connection, SQL Server performs the authentication itself by checking to see if a SQL Server login account has been set up and if the specified password matches the one previously recorded. If SQL Server does not have a login account set, authentication fails and the user receives an error message.

    ref-http://technet.microsoft.com/en-us/library/aa905171%28v=sql.80%29.aspx

    so you should have the SQL account like if you know SA account password using that you can connect to the sql server & you can work(but the account shouldn't be disabled if disabled you can't login)

    or if any sql account exists then you canlogin to thesqlserver

    coming to other ->Is there a way to turn Mix Mode Authentication back on and fix the problem with the windows authentication problem as well?

    Authentication mode is what how you can allow users to connect to the sql server only for ex- say

    if its set to windows authentication mode then only domain account users can login to the sql server if they have proper permissionon sql server.

    incase of  mixed mode ->windows authentication + SQL server Authentication mode allows both the domain users & the SQL account.

    inorder to turnbackanything either you have to do using http://technet.microsoft.com/en-us/library/ms188670%28v=sql.105%29.aspx

    or registry, in either of case you need to restart sql server services.

    but now what is the issue arised & problem with sql connection or authenticationmode


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Thursday, October 17, 2013 6:21 PM
  • I turned mix mode authentication on again by going to the LoginMode registry entry and settings it a value of 2. So now SQL Server authentication works find and I am able to login to SQL Server using SQL Server authentication via the SA account. A while back I added AD and DNS server roles to this same computer then I changed my mine about that strategy so I uninstall the server roles AD and DNS. So I am no longer using a domain configuration. The computer is now using a workgroup configuration with just user accounts. But when I try to login to the SQL Server using windows authentication computername/Administrator account I get the error Microsoft SQL Server, Error 18456 with a state 11 which is 11 and 12 indicate that a valid login was used but server access failure has occurred. I got the error message from SQL Server event viewer log entry and the SQL SQL ERROLOG file. It appears to me when I uninstall the server roles AD and DNS something happen to the SQL SERVER 2008 R2 software and now it no longer accepts logins using windows authentication. I noticed when I installed AD and DNS server roles it deleted my workgroup user accounts and replaced them with domain accounts. And when I uninstalled AD and DNS server roles it deleted the domain accounts and created workgroup user accounts again for Administrator etc. I can use this Administrator user account to login to the host windows server 2008 but SQL Server 2008 R2 will not accept it as a valid windows authentication account. It seems like the SQL Server 2008 R2 windows authentication mode is broken.


    • Edited by k1vinsrtech Sunday, October 20, 2013 2:00 AM
    Friday, October 18, 2013 1:01 AM
  • I tried to run a repair on the SQL Server instance and received the following failure.

    TITLE: Microsoft SQL Server 2008 R2 Setup
    ------------------------------

    The following error has occurred:

    No mapping between account names and security IDs was done.


    Click 'Retry' to retry the failed action, or click 'Cancel' to cancel this action and continue setup.

    For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=10.50.1600.1&EvtType=0x0F90E2F6%25400xDC80C325

    ------------------------------
    BUTTONS:

    &Retry
    Cancel
    ------------------------------

    Friday, October 25, 2013 2:26 AM