none
Copy database error 18456 state 11

    Question

  • I am trying to copy a small database from a production server to a test server. 

    When using the Copy Database Wizard I get the error 18456 state 11 and then Login failed for user 'networkname\servername$'.

    Is there some permissions not set right?

    Fred

    Friday, March 09, 2012 9:41 PM

Answers

All replies

  • Hi,

    You may need to do something like below. The key is understanding the state information in the 18456 error message.

    http://sqlblogcasts.com/blogs/simons/archive/2011/02/01/solution-login-failed-for-user-x-reason-token-based-server-access-validation-failed-and-error-18456.aspx


    Sankar Reddy

    Blog: http://SankarReddy.com/
    Twitter: http://twitter.com/SankarReddy13/

    Saturday, March 10, 2012 12:36 AM
  • Have you confirmed that your account that you are logged in as when running the wizard has appropriate permissions to the source Instance Database as well as the Destination Database.

    You can check the error log on both servers to find out which server is giving you the error on and grant the appropriate permissions to be able to complete your task.

    I hope this helps


    Warwick Rudd
    MCT
    My SQL Server Blog
    Twitter
    -------------------------------------------------------
    Please mark as Answered if I have answered your question
    Please vote if this was useful
    -------------------------------------------------------

    Sunday, March 11, 2012 7:42 PM
  • Hi CRdesigner,

    State 11 indicates valid login but server access failure. Please make sure that the login you used has permission to access the database. You could make the user 'networkname\servername$' as db_owner of the database.


    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    Monday, March 12, 2012 8:07 AM
  • My login has sysadmin rights in SQL Server on both servers.  I am not sure what rights my login to the Windows server are.

    Fred

    Friday, March 16, 2012 5:07 PM
  • Fred,

    There is an item that I don't quite understand in your original post.

    What stands out is the fact that the login used is the computer object (as noted by servername$) and not a normal domain account as I would expect. I'm not familiar at all with the database copy wizard, but I would question why the computer object is being used to authenticate (possibly becuse the service is running under the network service account?) instead of your domain account. I'm not sure when choosing windows authentication in the wizard if it uses the user's account or the database engine service account (though in this isntance it looks like engine service account).

    Can you verify which method you are attempting to use to copy the database (if you even get that far?) SMO or detach/attach?

    -Sean

    Friday, March 16, 2012 5:22 PM
  • It looks like the destination server is giving me the errors.

    03/16/2012 15:06:20,Logon,Unknown,Login failed for user 'NETWORK\USERTEST$'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.

    Fred

    Friday, March 16, 2012 7:14 PM
  • Sean,

    I am not sure since I can not view the packages the wizard creates.  I am new in this role of managing a couple of databases.  My experience has been development views and stored procedures for reports, but I am looking forward to learning.

    What I am trying to do is update the test server with changes from the production server.  The database that I am trying to copy is not on the test server.

    It would be nice to have a stored procedure or SSIS package update the changes in the production server to the test server.

    But I am having problems just trying to copy a new database to the test server.  Any help is appreciated.

    Fred

    Friday, March 16, 2012 7:37 PM
  • go through below links 

    http://blogs.msdn.com/b/sqlsakthi/archive/2011/02/06/how-to-troubleshoot-connectivity-failure-error-with-sql-server.aspx

    http://sql-articles.com/articles/troubleshooting/troubleshooting-login-failed-error-18456/http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/85db3eff-32ca-4cc9-9dcf-039ea3897978/


    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    Friday, March 16, 2012 9:13 PM
  • I have tried both (SMO and detach/attach) and they get the same error.

    There is no servername$ login on either server.  Could this be why the error?  I do not know either why it is not using a normal domain account.  It does ask in the beginning of the wizard whether I want to use windows or sql server authorization.  I have been using windows.

    Fred


    • Edited by CRdesigner Monday, March 19, 2012 5:50 PM
    Monday, March 19, 2012 5:47 PM
  • Hello,

    I did some quick testing and here is what I found:

    1. The SQL Agent user account is used to run the SSIS package from the destination server (at least that's what it looked like) when choosing windows auth.

    2. The source server needs to have a login for the sql agent account from the destination server (I chose SMO transfer). This will allow it to pull the data (I'm assuming).

    3. Once I created a login for the destination server in the source server instance, the execution completed. When I didn't I had the same login error as noted in the OP.

    You'll have to make a login for the destination server's service account to run this. Note that you won't be able to find computer objects using the GUI. You'll have to create the login through TSQL as shown below:

    CREATE LOGIN [Domain\ComputerName$] FROM WINDOWS

    After you create the login you can assing rights through the GUI or if you like through TSQL to the databases in question.

    -Sean

    Monday, March 19, 2012 6:58 PM
  • Sean,

    I noticed that the login already exists but the Server Roles is set to public.  What rights are needed for copying a database?

    It would have been nice if the documentation for the wizard would have explained that.

    Fred

    Thursday, March 22, 2012 2:10 PM
  • Fred,

    I'm assuming it needs sysadmin for the duration of the copy but can be pulled after that. It isn't spelled out very well in the documentation.

    http://msdn.microsoft.com/en-us/library/ms188664.aspx

    -Sean

    • Marked as answer by CRdesigner Thursday, March 22, 2012 7:53 PM
    Thursday, March 22, 2012 2:27 PM
  • Thanks Sean.

    That worked.  Just needed to set the right permissions on the right user.

    Fred

    Thursday, March 22, 2012 7:54 PM