locked
user creation RRS feed

  • Question

  • Hi,

    We having an issue with one of our application that uses SQL 2016 AG.

    we have an application that using SQL authentication and  has its own configaration program for creating users in DB.

    When creating new user in our application config program we can see that user get created in primary Node DB and also we see new use under SQL server login, this new user can access the SQL DB and no problem.

    We also see that user get synced to DB on secondary node but new users is not added to the SQL login on the secoundery Node.

    Now when we fail over to the secondary node the newly created user cannot login to the application, becuase users is not added to SQL server login, we can add this user manually to the SQL login, but when go to the property of this new user to map access to the database we get error that we cannot do this because user already exist in DB.

    How can we add a new user that us SQL authentication to both SQL servers in AG?

    Thanks


    Shahin

    Monday, January 20, 2020 3:29 PM

All replies

  • Hope this thread give a help:

    Creating a SQL Login and User on an AlwaysOn Replica


    A Fan of SSIS, SSRS and SSAS

    Monday, January 20, 2020 3:46 PM
  • Hi Shahin,

    >> How can we add a new user that us SQL authentication to both SQL servers in AG?

    Always On availability group supports a replicated environment for a discrete set of user databases, but it cannot synchronize logins, so you need to synchronize it manually.

    Please refer to the following articles to synchronize logins between replicas:

    Availability Groups: How to sync logins between replicas

    keeping availability group logins in sync automatically

    Hope this could help you.

    Best Regards,

    Amelia


    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.

    Tuesday, January 21, 2020 2:38 AM
  • Hi Amelia,

    To run a test I did create a test SQL account on the Primary node ( in my test AG).

    Also did download the Script ag-sync and ran the query SyncLogins and query is running without error but the SqL account that I created is not synced to the second node.

    I did run the query on both Node with the same result. should I change something in the query?

    Thanks


    Shahin

    Tuesday, January 21, 2020 10:30 AM
  • Hi Amelia,

    I did create a new test user account from my DB config program and I can see user is created on the primary node SQL login and also in DB users. user is also synced to the DB on second node, but user is not created in SQL login at the node 2

    I did run this query Sync Logins Between Nodes and create the sane test user at the Node 2, but still this new test user cannot connect to the DB!?

    Any suggestion?

    Thanks


    Shahin

    Wednesday, January 22, 2020 11:09 AM
  • Wednesday, January 22, 2020 6:33 PM
  • Hi Shahin,

    >>I did run the query on both Node with the same result. should I change something in the query?

    Could you please remove -whatif from script?

    # define the AG name
        $AvailabilityGroupName = 'AGListenerName'
     
    # internal variables
        $ClientName = 'AG Login Sync helper'
        $primaryInstance = $null
        $secondaryInstances = @{}
     
     
    try {
        # connect to the AG listener, get the name of the primary and all secondaries
            $replicas = Get-DbaAgReplica -SqlInstance $AvailabilityGroupName 
            $primaryInstance = $replicas | Where Role -eq Primary | select -ExpandProperty name
            $secondaryInstances = $replicas | Where Role -ne Primary | select -ExpandProperty name
        # create a connection object to the primary
            $primaryInstanceConnection = Connect-DbaInstance $primaryInstance -ClientName $ClientName
        # loop through each secondary replica and sync the logins
            $secondaryInstances | ForEach-Object {
                $secondaryInstanceConnection = Connect-DbaInstance $_ -ClientName $ClientName
                Copy-DbaLogin -Source $primaryInstanceConnection -Destination $secondaryInstanceConnection -ExcludeSystemLogins 
            }
    }
    catch {
        $msg = $_.Exception.Message
        Write-Error "Error while syncing logins for Availability Group '$($AvailabilityGroupName): $msg'"
    }
    
    

    >>I did run this query Sync Logins Between Nodes and create the sane test user at the Node 2, but still this new test user cannot connect to the DB!?

    The script in the link is work for me.

    You also could use this method from Microsoft support.

    Hope this could help you.

    Best Regards,

    Amelia



    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.

    Thursday, January 23, 2020 6:15 AM
  • Problem is resolved, it looks like I could not use the new test user becuase the secound node was not configured for the SQL authentication, when done that, I could login.

    Thanks


    Shahin

    Thursday, January 23, 2020 2:46 PM
  • Hi Shahin,

    Thank you for your reply .  In order to close this thread, please kindly mark useful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

    Best Regards,

    Amelia


    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.


    Thursday, January 30, 2020 9:36 AM