locked
SQL Server login failure after domain change RRS feed

  • Question

  • Our SBS 2003 server had a catastrophic hardware failure. Rebuild wasn't possible, hence we moved to SBS 2011.

    My development machine contains an installation of SQL Server 2008 R2 and now I'm unable to login to the database engine using Windows Authentication or SQL Server Authentication. Any other type of login using Win authentication (i.e., Analysis Services, etc.) works fine.

    Steps I've tried to correct this issue:

    1. In SQL Config Manager/SQL Server Services/SQL Service (MSSQLSERVICE) - change the default logon from 'Network Service' to the domain administrator user account.

    2. Logoff domain account and login to the machine as local admin.

    3. Login using the 'sa' account while logged in as local machine admin.

    4. Changed startup parameters to start the db engine in single-user mode and login from cmd prompt.

    5. Added the domain user account to the local machine user group SQLServerMSSQLUser$[MACHINENAME]$MSSQLSERVER

    6. Downloaded setspn.exe and atttempted to add the SPN (not sure if I completed this correctly or not...the returned message is below)

    C:\Users\syarbrough>setspn -A MSSQLSvc/xw6400dev.sycomputing.local syarbrough
    Registering ServicePrincipalNames for CN=SYARBROUGH,OU=SBSComputers,OU=Computers
    ,OU=MyBusiness,DC=sycomputing,DC=local
            MSSQLSvc/xw6400dev.sycomputing.local
    Updated object

    Nothing has worked, obviously.

    Before I reinstall the application is there anything else I can do?

    I'm happy to retry all of the above if necessary.

    Thank you for your help!


    Sy Computing

    Monday, May 14, 2012 11:16 PM

Answers

  • Hi SyComputing,

    I guess you issue is in SPN setting, this online article may help you.

    http://technet.microsoft.com/en-us/library/cc772897(v=ws.10).aspx


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by sycomputing Wednesday, May 16, 2012 7:24 PM
    Wednesday, May 16, 2012 2:01 AM
  • 1. Are you able to login using sa? If no,share the errorlog which has sa faliure to login.
    Use below steps to add yourself as SysAdmin and then recreated windows login. Mostly the SID for account is not matching and that is one of the reason you are seeing "State 11"
    http://sqlserver-help.com/2012/02/08/help-i-lost-sa-password-and-no-one-has-system-administrator-sysadmin-permission-what-should-i-do/

    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

    • Marked as answer by sycomputing Wednesday, May 16, 2012 7:24 PM
    Wednesday, May 16, 2012 2:12 AM

All replies

  • Hi Sy Computing,

    If you change server’s domain and the server is running SQL Server. You need to take care of the below items:

    1.SA Access

    If you only have SQL Server sysadmin via domain accounts/groups, you should temporarily enable the sa account and ensure you know the password, until you get the new domain sysadmin accounts/groups working.

    2. Service Account

    The new domain service account(s) will need the same privileges as the old domain service accounts.

    3. SQL Server Domain-based Logins

    Logins that are based on domain accounts or domain groups will need to be recreated.

    4. Changing IP Addresses

    If required by the domain change and it's clustered, then the virtual server IP address must change in addition to the individual nodes.

    5. Service Principal Names (SPNs).

     If the IP addresses are changing, the old SPNs need to be dropped and new ones created. If the SQL Server service account is used to manage its own SPNs (not recommended) then the new domain service account will need to be granted the "Write servicePrincipalName" privilege.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Tuesday, May 15, 2012 8:06 AM
  • Have you looked in the SQL Server errorlog? There should be error messages for the login failures. Specifically, these errors includes a state, which can give you a clue where the problem is.

    These states are detailed in these blog posts:
    http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
    http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, May 15, 2012 10:02 PM
  • Hi Peja:

    I appreciate your assistance.

    SQL Server 2008 R2 is installed on my domain workstation running Win 7 Pro 64bit. It's not clustered nor used for anything other than testing/debugging web applications before their deployed to production. No other domain machines connect to the instance. It's just for me.

    I installed the application on this workstation initially. Even though I'm certain I'm using the password I used for the sa account at the initial install of SQL Server, it fails to login.

    I used the setspn tool incorrectly and registered a new SPN using my domain user account name instead of the machine name. In other words, I ran setspn.exe -A syarbrough.sycomputing.local syarbrough where the machine name is actually XW6400DEV.sycomputing.local

    The following are the results of running the setspn tool to check for duplicates (the command was run on the workstation where SQL Server 2008 R2 is installed):

    C:\Users\syarbrough>setspn -x
    Checking domain DC=sycomputing,DC=local
    Processing entry 0
    MSSQLSvc/XW6400DEV.sycomputing.local is registered on these accounts:
            CN=XW6400DEV,OU=SBSComputers,OU=Computers,OU=MyBusiness,DC=sycomputing,DC=local
            CN=SYARBROUGH,OU=SBSComputers,OU=Computers,OU=MyBusiness,DC=sycomputing,DC=local

    found 1 group of duplicate SPNs.

    This is the result of attempting to delete the duplicate SPN using the setspn tool:

    C:\Users\syarbrough>setspn -D MSSQLSvc/syarbrough.sycomputing.local syarbrough
    Unregistering ServicePrincipalNames for CN=SYARBROUGH,OU=SBSComputers,OU=Compute
    rs,OU=MyBusiness,DC=sycomputing,DC=local
            MSSQLSvc/syarbrough.sycomputing.local
    Updated object

    This is the result of listing the SPN's after I attempted the delete:

    C:\Users\syarbrough>setspn -L xw6400dev
    Registered ServicePrincipalNames for CN=XW6400DEV,OU=SBSComputers,OU=Computers,O
    U=MyBusiness,DC=sycomputing,DC=local:
            MSSQLSvc/XW6400DEV.sycomputing.local:1433
            MSSQLSvc/XW6400DEV.sycomputing.local
            TERMSRV/XW6400DEV
            TERMSRV/XW6400DEV.sycomputing.local
            MSSQLSvc/XW6400DEV.sycomputing.local:SQLEXPRESS
            RestrictedKrbHost/XW6400DEV
            HOST/XW6400DEV
            RestrictedKrbHost/XW6400DEV.sycomputing.local
            HOST/XW6400DEV.sycomputing.local

    This is the result of listing the duplicate SPN's again:

    C:\Users\syarbrough>setspn -x
    Checking domain DC=sycomputing,DC=local
    Processing entry 0
    MSSQLSvc/XW6400DEV.sycomputing.local is registered on these accounts:
            CN=XW6400DEV,OU=SBSComputers,OU=Computers,OU=MyBusiness,DC=sycomputing,DC=local
            CN=SYARBROUGH,OU=SBSComputers,OU=Computers,OU=MyBusiness,DC=sycomputing,DC=local

    found 1 group of duplicate SPNs.

    Any advice?


    Sy Computing

    Tuesday, May 15, 2012 10:33 PM
  • Hello Erland:

    Thank you for your reply. The log is below:

    2012-05-12 23:17:26.44 Server      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
     Jun 17 2011 00:54:03
     Copyright (c) Microsoft Corporation
     Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

    2012-05-12 23:17:26.44 Server      (c) Microsoft Corporation.
    2012-05-12 23:17:26.44 Server      All rights reserved.
    2012-05-12 23:17:26.44 Server      Server process ID is 3520.
    2012-05-12 23:17:26.44 Server      System Manufacturer: 'Hewlett-Packard', System Model: 'HP xw6400 Workstation'.
    2012-05-12 23:17:26.44 Server      Authentication mode is WINDOWS-ONLY.
    2012-05-12 23:17:26.44 Server      Logging SQL Server messages in file 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
    2012-05-12 23:17:26.44 Server      This instance of SQL Server last reported using a process ID of 2248 at 5/12/2012 11:17:21 PM (local) 5/13/2012 4:17:21 AM (UTC). This is an informational message only; no user action is required.
    2012-05-12 23:17:26.44 Server      Registry startup parameters:
      -d D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
      -e D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG
      -l D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
    2012-05-12 23:17:26.46 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
    2012-05-12 23:17:26.46 Server      Detected 4 CPUs. This is an informational message; no user action is required.
    2012-05-12 23:17:26.55 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
    2012-05-12 23:17:26.57 Server      Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
    2012-05-12 23:17:26.61 spid7s      Starting up database 'master'.
    2012-05-12 23:17:26.71 spid7s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
    2012-05-12 23:17:26.82 spid7s      Resource governor reconfiguration succeeded.
    2012-05-12 23:17:26.82 spid7s      SQL Server Audit is starting the audits. This is an informational message. No user action is required.
    2012-05-12 23:17:26.82 spid7s      SQL Server Audit has started the audits. This is an informational message. No user action is required.
    2012-05-12 23:17:26.82 spid7s      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
    2012-05-12 23:17:26.87 spid7s      SQL Trace ID 1 was started by login "sa".
    2012-05-12 23:17:26.87 spid7s      Starting up database 'mssqlsystemresource'.
    2012-05-12 23:17:26.88 spid7s      The resource database build version is 10.50.2500. This is an informational message only. No user action is required.
    2012-05-12 23:17:27.07 spid10s     Starting up database 'model'.
    2012-05-12 23:17:27.07 spid7s      Server name is 'XW6400DEV'. This is an informational message only. No user action is required.
    2012-05-12 23:17:27.20 Server      A self-generated certificate was successfully loaded for encryption.
    2012-05-12 23:17:27.21 Server      Server is listening on [ 'any' <ipv6> 1433].
    2012-05-12 23:17:27.21 Server      Server is listening on [ 'any' <ipv4> 1433].
    2012-05-12 23:17:27.21 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
    2012-05-12 23:17:27.21 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
    2012-05-12 23:17:27.21 Server      Server is listening on [ ::1 <ipv6> 1434].
    2012-05-12 23:17:27.21 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
    2012-05-12 23:17:27.21 Server      Dedicated admin connection support was established for listening locally on port 1434.
    2012-05-12 23:17:27.23 spid13s     A new instance of the full-text filter daemon host process has been successfully started.
    2012-05-12 23:17:27.25 spid10s     Clearing tempdb database.
    2012-05-12 23:17:27.25 spid14s     Starting up database 'ReportServer'.
    2012-05-12 23:17:27.25 spid13s     Starting up database 'msdb'.
    2012-05-12 23:17:27.25 spid15s     Starting up database 'ReportServerTempDB'.
    2012-05-12 23:17:27.25 spid16s     Starting up database 'xewebgeneral'.
    2012-05-12 23:17:27.25 spid17s     Starting up database 'xewebstore'.
    2012-05-12 23:17:27.28 Server      The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/XW6400DEV.sycomputing.local ] for the SQL Server service.
    2012-05-12 23:17:27.28 Server      The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/XW6400DEV.sycomputing.local:1433 ] for the SQL Server service.
    2012-05-12 23:17:27.28 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.
    2012-05-12 23:17:29.30 spid10s     Starting up database 'tempdb'.
    2012-05-12 23:17:29.56 spid13s     The Service Broker protocol transport is disabled or not configured.
    2012-05-12 23:17:29.56 spid13s     The Database Mirroring protocol transport is disabled or not configured.
    2012-05-12 23:17:29.57 spid13s     Service Broker manager has started.
    2012-05-12 23:17:29.57 spid7s      Recovery is complete. This is an informational message only. No user action is required.
    2012-05-12 23:17:39.88 spid52      Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
    2012-05-12 23:17:39.88 spid52      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
    2012-05-12 23:17:39.91 spid52      Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
    2012-05-12 23:17:39.91 spid52      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
    2012-05-12 23:17:39.95 spid52      Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
    2012-05-12 23:17:39.95 spid52      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
    2012-05-12 23:17:40.22 spid52      Attempting to load library 'xpsqlbot.dll' into memory. This is an informational message only. No user action is required.
    2012-05-12 23:17:40.23 spid52      Using 'xpsqlbot.dll' version '2009.100.1600' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
    2012-05-12 23:17:40.34 spid52      Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
    2012-05-12 23:17:40.34 spid52      Using 'xpstar.dll' version '2009.100.1600' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
    2012-05-12 23:17:40.36 spid52      Attempting to load library 'xplog70.dll' into memory. This is an informational message only. No user action is required.
    2012-05-12 23:17:40.37 spid52      Using 'xplog70.dll' version '2009.100.1600' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
    2012-05-12 23:17:56.23 Logon       Error: 18456, Severity: 14, State: 11.
    2012-05-12 23:17:56.23 Logon       Login failed for user 'SYCOMPUTING\syarbrough'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]
    2012-05-13 00:00:33.54 spid19s     This instance of SQL Server has been using a process ID of 3520 since 5/12/2012 11:17:29 PM (local) 5/13/2012 4:17:29 AM (UTC). This is an informational message only; no user action is required.
    2012-05-13 12:19:13.03 Logon       Error: 18456, Severity: 14, State: 11.
    2012-05-13 12:19:13.03 Logon       Login failed for user 'SYCOMPUTING\syarbrough'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]
    2012-05-14 00:00:44.69 spid17s     This instance of SQL Server has been using a process ID of 3520 since 5/12/2012 11:17:29 PM (local) 5/13/2012 4:17:29 AM (UTC). This is an informational message only; no user action is required.
    2012-05-14 12:05:43.89 Logon       Error: 18456, Severity: 14, State: 11.
    2012-05-14 12:05:43.89 Logon       Login failed for user 'SYCOMPUTING\syarbrough'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]
    2012-05-14 17:01:23.14 Logon       Error: 18456, Severity: 14, State: 11.
    2012-05-14 17:01:23.14 Logon       Login failed for user 'SYCOMPUTING\syarbrough'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]
    2012-05-15 00:00:57.10 spid16s     This instance of SQL Server has been using a process ID of 3520 since 5/12/2012 11:17:29 PM (local) 5/13/2012 4:17:29 AM (UTC). This is an informational message only; no user action is required.
    2012-05-15 13:08:58.21 Logon       Error: 18456, Severity: 14, State: 11.
    2012-05-15 13:08:58.21 Logon       Login failed for user 'SYCOMPUTING\syarbrough'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]


    Sy Computing

    Tuesday, May 15, 2012 10:37 PM
  • Hi SyComputing,

    I guess you issue is in SPN setting, this online article may help you.

    http://technet.microsoft.com/en-us/library/cc772897(v=ws.10).aspx


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by sycomputing Wednesday, May 16, 2012 7:24 PM
    Wednesday, May 16, 2012 2:01 AM
  • 1. Are you able to login using sa? If no,share the errorlog which has sa faliure to login.
    Use below steps to add yourself as SysAdmin and then recreated windows login. Mostly the SID for account is not matching and that is one of the reason you are seeing "State 11"
    http://sqlserver-help.com/2012/02/08/help-i-lost-sa-password-and-no-one-has-system-administrator-sysadmin-permission-what-should-i-do/

    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

    • Marked as answer by sycomputing Wednesday, May 16, 2012 7:24 PM
    Wednesday, May 16, 2012 2:12 AM
  • Hi Balmukund:

    Thank you for your reply.

    This worked...I'm able to login via SSMS.

    When I recreate the Windows login should I repeat the process on all databases or just those that I created before the original DC went down?


    Sy Computing


    • Edited by sycomputing Wednesday, May 16, 2012 7:23 PM
    Wednesday, May 16, 2012 5:35 PM
  • Hi Balkmund:

    Somehow I've broken things again...my sincere apologies but would you mind helping me out of this one as well?

    After I was able to login to SSMS, I went to InstanceName/Security/Logins/New login in order to finish the instructions you gave me to fix this problem.

    I added my domain user as a login using the default settings, including leaving the default database [master] as the db to which to logon.

    Immediately after this (I didn't even have a chance to logout of SSMS) a dialog box appeared and this failure occurred:

    2012-05-16 14:25:35.12 Logon       Error: 18456, Severity: 14, State: 40.
    2012-05-16 14:25:35.12 Logon       Login failed for user 'SYCOMPUTING\syarbrough'. Reason: Failed to open the database specified in the login properties. [CLIENT: <local machine>]

    I'm back to where I started...should I try to access the instance from the cmd prompt again and remove my domain account?

    Thank you.


    Sy Computing

    Wednesday, May 16, 2012 8:08 PM
  • That's a very different error message from what you had previously. This message indicates that the default database is missing or inacessible.

    You can work around this error by explicitly specify a database. You can use the Options button in the Connect dialog in SSMS to do this.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, May 16, 2012 9:41 PM
  • Indeed I discovered this option from doing more research into the error message today.

    What I've found is that while I can login and see the databases, I can't do anything else. I can no longer add users, edit databases, add a new database, edit instance properties, etc. from SSMS

    From viewing the properties of the domain user account I added, it looks like specified a named instance of a db to login to when I created the user, however, even that database is inaccessible to the user now.

    Basically it's a read-only scenario, with no option to move forward.

    Is it time to reinstall or is there a way to fix this?

    Again, thanks for your time.


    Sy Computing

    Wednesday, May 16, 2012 10:11 PM
  • Reinstall is going to help you none - you will only have to redo what you have already done.

    If all you did was to add a domain login and nothing more, that user will not be able to do much. If you want that user to be able to access a database, you need to add that user to the database. If you want that user to be able to access tables, you will need to grant permission for that user, and so on.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, May 17, 2012 8:57 AM