none
SQL question RRS feed

  • Question

  • I have set up Alwayson on a database db1, Server1 is Primary, Server2 is secondary. Now I am trying to add a user to both Primary and secondary servers. But when I add the user to the secondary server, getting below error:

    The target database, db1 is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability group is not enabled for read access. 

    The availability mode is in synchronous mode and secondary is not readable. So how do we add new users who needs access to the database db1 which is configured with ALwayson. Any ideas?

    Thanks.


    sqldev

    • Moved by Tom Phillips Tuesday, August 11, 2015 12:13 PM Always on question
    Monday, August 10, 2015 9:21 PM

All replies

  • The login must exist on all replicas unless you are using contained database users.  The database level security is stored inside the databases and could only be modified on the primary replica. 

    For all your SQL Server authenticated users I recommend making sure the SID's of the users match on all replicas. The SID is the unique identifier for logins.  If they don't match you will see orphaned logins when you failover. 

    The following is my recommended way to sync logins for availability groups.
    http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror

    Let me know if this helps you.

    Monday, August 10, 2015 11:09 PM
  • Are you adding a LOGIN or a USER?

    Logins need to be added on each node of the AG and should be able to be added without error.  Make sure that if you are adding a SQL login that the passwords and SID are set the same.

    For a User.  As this is scoped to the database, the DDL statements to create the User will be included in the transaction log for that database and will therefore be distributed to the secondary and replayed with the other statements as the secondary is sync'd.   Therefore you do not need to create the user directly on the secondary.


    Martin Cairney SQL Server MVP

    Monday, August 10, 2015 11:16 PM
  • Thanks for the responses.

    Actually I need to give access to a new user, will be using AD login to give access. So just by adding the user's AD login to the Primary Server, will take care of adding that user to the Secondary Server when it synchronizes? 

    But I have a AD login that's present in the Primary Server, but don't see that login in the Secondary Server. As that user is present in the Primary Server for almost over 2 weeks now. So was trying to add that user onto the Secondary Server, hence was getting this error:

    The target database, db1 is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability group is not enabled for read access. 


    sqldev

    Tuesday, August 11, 2015 2:47 AM
  • I reviewed the script which you provided in the link, but that's for mirroring, will it work for Always on Availability group? I understand that Alwayson uses Mirroring concepts, but still curious? Thx.

    sqldev

    Tuesday, August 11, 2015 2:55 AM
  • Yes, but if you are using windows authenticated accounts via AD its not needed as you won't run into orphaned user problems that exist with sql authenticated accounts.  

    If you are creating a new login you can use the script below for all your replica instances.  This will create the login and store that information in the master database.  Then on the primary replica you can add the database level security needed and this will be replicated to the other secondary replicas.

    Don't try to assign database level security on the secondary replicas as you cannot write to the user databases involved in the availability group.  Unless you configure your availability group to allow secondary replicas to read you won't be able to connect to them.  Once LOGIN is created on your secondary replica instance you can either failover or enable readable secondaries to validate connectivity to your other replica.

    The following is how you would create the LOGIN for the instance.

    USE [master]
    GO
    CREATE LOGIN [Domain\User] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    GO


    • Edited by JohnSterrett Tuesday, August 11, 2015 4:26 AM update
    Tuesday, August 11, 2015 4:24 AM
  • Are you trying Windows or Mixed Authentication? I do not think you will have problems with Windows Authentication..

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, August 11, 2015 5:08 AM
  • I created the user on the secondary replica, but I still don't see under the user properties having access to the databases in the secondary replica. do we also add the system databases to the availability group? In my case, Master db is not added to the availability databases, hence I think is not getting replicated to the secondary replica. Any thoughts? Thanks. 

    sqldev

    Wednesday, August 12, 2015 2:25 PM
  • Any ideas please?

    sqldev

    Thursday, August 13, 2015 1:33 AM