none
SQL server cross replication login failed RRS feed

  • Question

  • Hello All,

    We have DEV and UAT domain. I am trying to create replication from UAT to DEV and vice versa. As we have two different domain we can't use domain account. So, i have created SQL account to add subscriber. But, somehow i am still reeving login failed for user Error 18456. I have checked SQL authentication has been set to allow SQL and Windows account. Can anyone please help as this is urgent issue and need to resolve asap. Thanks for your help in advance. 

    Tuesday, February 12, 2019 7:43 PM

All replies

  • Ensure that both SQL Servers support mixed authentication and that the password is the same on both servers. If you recently changed to mixed authentication you will need to restart your SQL Server.

    Also run the distrib command from the command line to detect exactly where it is failing - is it failing connecting to the publisher, subscriber or distributor?

    Tuesday, February 12, 2019 8:07 PM
    Moderator
  • Both server has mixed authentication. 
    Tuesday, February 12, 2019 8:09 PM
  • Can you connect to the subscriber using the account that the distributor uses to connect to the subscriber? Use Enterprise Manager to do this.
    • Proposed as answer by Abbottee Tuesday, February 19, 2019 9:03 AM
    Tuesday, February 12, 2019 8:13 PM
    Moderator
  • Hi Chickoo79,

     

    As mentioned by Hilary, if you changed the Server authentication, you should restart the SQL Server to ensure the change to take effect.

     

    Besides, can you connect to the SQL Server(work as subscriber) from the Server(publisher end) via the SQL account. If not, could you please tell us more specific error messages.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, February 13, 2019 4:00 AM
  • We didnt make any changes related to  Server authentication.

    I was able to connect UAT from DEV but not DEV from UAT using same test account.

    I am getting same login failed for use error 18456

    Wednesday, February 13, 2019 4:29 PM
  • Can you please check out this blog article from the data platform team.

    http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx

    we really need to look at the state part of the error message to find the root cause of the issue.
    2, 5 = Invalid user id
    6 = Attempt to use a Windows login name with SQL Authentication
    7 = Login disabled and password mismatch
    8 = Password mismatch
    9 = Invalid password
    11, 12 = Valid login but server access failure
    13 = SQL Server service paused
    18 = Change password required

    I hope it may help you.

    ===================================================================
    Please click "Mark as Answer" if it solved your issue and/or "Vote as helpful" if it helped. This can be beneficial to other community members reading this thread.

    Wednesday, February 13, 2019 5:16 PM
  • Try to logon locally using SQL Authentication on Dev. Does this work? Do you get an error message in the SQL Server error log?
    Thursday, February 14, 2019 3:09 PM
    Moderator
  • Here is the result after doing some testing today.

    We have two instances on each environment DEV and UAT.
    DEV01 (Default instance with default port 1433)
    DEV (Instance with port 2000)

    UAT01 (Default instance with default port 1433)
    UAT (Instance with port 2000)

    So, when i try to add subscriber from DEV instance -> UAT, i am receiving message please use default instance UAT01.
    SQL Server replication requires the actual server name to make a connection to the server .
    Same way from UAT as well.

    Thursday, February 14, 2019 3:31 PM
  • Hi Chickoo79,

     

    Please refer to the similar question.

    https://stackoverflow.com/questions/8212032/sql-server-replication-requires-the-actual-server-name-to-make-a-connection-to-t

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, February 15, 2019 9:54 AM