none
Error: 18456 Severity: 14, State: 11

    Question

  • I know this question has been asked lots of times...

    However, I cannot find the same scenario as mine. I have users that have access to databases. I also have users that have server logins. I can fix my problem if I add the user to the server logins but I shouldn't have to do that. I am moving 15 DB from a windows sql 2005 on an xp machine to a windows sql 2008 on a windows 2008 server. Each db has about 50 to 100 users and there are only 5 server logins. Every login uses a domain name. We have dsn's set up to use windows nt authenticity on the old machine with sql 2005. when moving the db to the new machine, if the user is not in the Servername->security->logins, they get this error. There has to be something I can do so I do not add 500 users to the login.

    Logon,Unknown,Login failed for user 'COMPANY\USER'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: XX.XXX.XX.XX]
    01/02/2013 13:20:54,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 11.

    I have disabled the uac and the firewall 

    I have found:

    The other way that this issue can be reproduced is by dropping the windows user and adding it back again. The reason this issue occurs is because SQL Server maps logins using SIDs. When the SID changes at the Windows/Domain level, the SID stored in the SQL system catalog is not updated. When an attempt is made to use that login to access SQL, a SID mis-match occurs which results in the error.

    but I would rather not drop and add 500 users to specific databases


    • Edited by skdoc36 Thursday, January 03, 2013 1:00 PM
    Wednesday, January 02, 2013 8:04 PM

Answers

All replies

  • Somehow these users need to have access to the server on server level. However, you should not have to each 500 of them one by one, but if there is a suitable Windows group, you can add them all in one bang.

    If you check the old server, you can probably find out which group that is.

    By the way, there is no SQL 2003. There was SQL 2000 and SQL 2005.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, January 02, 2013 10:37 PM
  • Hi there,

    As per erland you can do by adding them to a windows group and add back this group to your SQL server. THere is work around in case if you are not comfortable that way.

    Script out all your users with permissions on all objects from the original database. After migrate drop them all in one go and run your script in one go. you will get all your users recreated.

    otherwise run the script to find out the orphned users list and fix the SID. google for revlogin. thet will help you to update the SID.

    http://www.sqlserver-training.com/sql-script-to-script-sql-server-logins/-

    goodluck

    kumar

    Thursday, January 03, 2013 12:12 AM
  • Hello Skdoc36,

    You aren't using most correct way to move databases. As rightly mentioned above there is nothing sql 2003, please verify that first to start with.

    How to transfer logins and passwords between instances of SQL Server - Steps to transfer logins.

    How to resolve permission issues when you move a database between servers that are running SQL Server


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful, if a post was useful to help other user's find a solution quicker.

    Thursday, January 03, 2013 11:38 AM
  • @anuragsh

    Neither one of those links address Error: 18456 Severity: 14 State: 11. but they do address other states. The first link is not what I am looking for and the second link is for a different Error state as well, but thank you for trying.


    • Edited by skdoc36 Thursday, January 03, 2013 1:25 PM
    Thursday, January 03, 2013 1:24 PM
  • @Kumar muppa

    I am going to try the script on the link and let you know how it works.

    Thursday, January 03, 2013 1:27 PM
  • @Kumar muppa

    I tried it, but this only give me the 5 server logins. I have deleted them all and ran the script with no luck. Also I have tried to recreate them manually with no luck

    Thursday, January 03, 2013 1:44 PM
  • Could it be a Domain issue such as 

    Somehow these users need to have access to the server on server level. However, you should not have to each 500 of them one by one, but if t here is a suitable Windows group, you can add them all in one bang.

    I f you check the old server, you can probably find out which group that is??

    I am not the sysadmin so I need to explain to him the problem.

    Thanks

    Thursday, January 03, 2013 2:00 PM
  • Hi Skdoc36,

    When you script out and create you may able to create as SQL users. You can only connect to SQL server using this user and pass word.

    If you use windows authentication you never able to connect because They do not have permission in windows level. They are SQL server users not windows users. When users do not exist you can not get their windows SID.

    You and your net work admins to recollect the knowledge of impersonation. create one user or group in windows and use to your SQL group. Below are the links to refresh...

    http://msdn.microsoft.com/en-us/library/ms161965(v=sql.90).aspx

    http://help.synthesis8.com/weibull_alta8/sql_server_logins_or_using_impersonation.htm

    good luck

    kumar

    Thursday, January 03, 2013 11:07 PM
  • Hi skdoc36,

    After checking your post, it seems that you are confused with SQL Server logins and database users. When we login in SQL Server instance, we need a login account rather than a database user account. After moving databases from a SQL Server 2005 to SQL Server 2008, please use a valid login account on SQL Server 2008 rather than an login account on SQL Server 2005. Regarding the orphaned users after moving the databases, please refer to the following document resolve them:

    Troubleshoot Orphaned Users (SQL Server)
    http://msdn.microsoft.com/en-us/library/ms175475.aspx


    Allen Li
    TechNet Community Support

    Friday, January 04, 2013 3:22 AM
  • I do not have orphaned users. I have recreated the logins from the sql server 2005 on the sql server 2008 box.
    Friday, January 04, 2013 1:15 PM
  • Hi, do you mean that you have migrated the original logins with SQL Server 2005 to SQL Server 2008? Since you are not sysadmin, how did you achieve this? Additionally, in your first post, you said:
    >>if the user is not in the Servername->security->logins, they get this error.
    Yes, this is normal, SQL Server requires valid logins (in Servername->security->logins) when logging in. If you tried to login SQL Server with a database user, it will fail. You can refer to the following document about the difference between a SQL Server login and a database user.

    http://stackoverflow.com/questions/1134319/difference-between-a-user-and-a-login-in-sql-server


    Allen Li
    TechNet Community Support

    Wednesday, January 09, 2013 3:11 AM