SQL Server Performance

Mirroring - Login failed for user 'username'. (Microsoft SQL Server, Error: 4064)

Discussion in 'SQL Server 2005 General DBA Questions' started by sonnysingh, Apr 22, 2010.

  1. sonnysingh Member

    Hi All
    I am having this error while try connect automatically to the failover database by to application (failover). This is the user uses in the connection string with dbo rights on the both databases on both server.
    Pls help...
    Thanks in Advance
    sonnysingh, Apr 22, 2010
    #2

  2. satya Moderator

    Have you created that user when MIRROR server has been initiated, it should be done as DB Mirroring will only take transactions from user database and not from system databases. SEE KBA http://support.microsoft.com/kb/307864 fyi
    satya, Apr 22, 2010
    #3

  3. sonnysingh Member

    I have looked this KB... and this is work around. let explain the whole situation
    I have created the SQL Server user with DBO rights for the DB that sat for mirroring. same user with same rights on both principal and mirroring server. when manual failover carry (alter database <dbname> set partner failover) then this error occur. But when I have change the rights to sysadmin to this user then thi error doesn't occur.
    But I do not want to give sysadmin rights to this user as uses for application in connection string. Hence I looking for solution for this reason. Pls help
    Thanks
    sonnysingh, Apr 24, 2010
    #4

  4. rohit2900 Member

    Hi does this user has all the necessary access to both the server and the network share etc.
    rohit2900, Apr 26, 2010
    #5

  5. mguissine New Member

    Use Windows authentication and your problem should go away. As with SQL Server users, they have to be recreated after database is failed over to the mirror.
    mguissine, May 2, 2010
    #6

  6. arunyadav Member

    Neither windows Authentication nor sysadmin is the solution. As far as I think login is failing because of SID mismatch. (To cross-verify, after the failover check for orphaned user and fix it if found. After fixing the orphaned user the application should be able to connect.) Just re-create the user in the mirror server with the same SID as Principal and let me know if the problem is resolved.
    arunyadav, May 3, 2010
    #7

  7. sonnysingh Member

    Hi All
    I have created the same user with same DBO rights on the both server. Only different I have noticed that when I have given theis login user sysadmin rights then it works fine as no such error came up. But with dbo rights, it's came up with this error.
    Any how, my main purpose is when failover occurr application will autimatically redirected to secondry DB server as user credential are same on both server as with sysadmin rights application works fine. So, how we can solve this as you all knows we can not assign sysadmin level of rights for application access.
    Thanks
    sonny
    sonnysingh, May 9, 2010
    #8

  8. rohit2900 Member

    Sonny,Did u tried what Arun suggested cos once the user has sa rights even if the user is a orphaned login even then it will be able to connect the db. but if the user only has database level permissions not even dbo then this might be a issue. and to know the root cause once u failover to mirror then look for orphaned users and check if fixing it solves the prob.Cheers...!!!Rohit
    rohit2900, May 9, 2010
    #9

  9. satya Moderator

    Do you use any ASSEMBLY or CLR based stored procedures on PRIMARY server where those external procedures are not registered on MIRROR server?
    In the DB mirroring you must build a checklist outside of user database processes that will have such problems if you hvaen't registered/processed correctl.y.
    satya, May 3, 2010
    #10

  10. satya Moderator

    What is the default database set for that user?
    You can use:
    ALTER LOGIN [test] WITH DEFAULT_DATABASE = master
    GO


    You should determine what is the login you are connecting at, then login as administrator and issue an ALTER LOGIN statement to set the default database for that login to master.

    satya, Jun 29, 2010
    #11

  11. sonnysingh Member

    Is any one can help me here?

    sonnysingh, Jun 25, 2010
    #12

Share This Page