Anonymous
  • 1

Why does SQL Server Always On Secondary Database Connection fail after setup for SQLauth user?

  • 1

SQL Server: I have Always On setup. I get secondary database connection failure. When investigated I see the Secondary database no permission or privileges as in Primary. Why? How to make Secondary connectable?

Share

1 Answer

  1. When a SQL authentication login is created, it gets a new auto-generated SID assigned by the SQL Server. This auto-generated SID will be different in each SQL Server even if the login name and the password combination are the same. Can you try this as this is  non-production environment? Remove DB pRead more

    When a SQL authentication login is created, it gets a new auto-generated SID assigned by the SQL Server. This auto-generated SID will be different in each SQL Server even if the login name and the password combination are the same.

    Can you try this as this is  non-production environment?

    1. Remove DB privileges (db_owner) to the SQL user on primary for this database.
    2. Get the SID for the Login on primary.

    SELECT name, sid

    FROM sys.server_principals

    WHERE name = ‘your_SQL_user’

     

    /* sample Results:————————————————————

    name       sid

    USER1  0x8Cxx0E033BD83524180CF813A20C5265B

    */

    1. On the SECONDARY replica Create the Login with the same SID as in the PRIMARY replica

    CREATE LOGIN [USER1]

    WITH PASSWORD=N’yourPassword’

    , SID = 0x8CxyE033BD83524180CF813A20C5265B  — use the same SID retrieved above

    , DEFAULT_DATABASE=[master]

    GO

    Now go back to the PRIMARY replica and grant required privileges (db_owner) at the database level. This new database user will be automatically replicated on the SECONDARY replica with its permissions and correctly map to the login.

    Try connect. It should succeed.

    Best Regards.

    See less
    • 1

You must login to add an answer.