Create users(AD) for Alwayson enviroment

Question

Hi our environment is SQL Server 2016 SP2 CU14 Enterprise /Wndows 2012 R2 and Always on setup.
Looking to have application users to create which need to remain permission same of the database same as prod primary and not blow away every time when failover fail back / or need database users need to match same SID on the both instances (primary and secondary ) or need to run following every time when failover /failback But AD user will remain same permission when do a failover/failback scenario

use databasename
go

sp_change_users_login Auto_Fix, [dbuserSql];

++++++++++++++++++++++++++++++
Q1. What is the recommended to have users in alwayon env whether to have AD user or database user for the application to connect through AG Listener (Expecting to work smoothly with no any issue with the permission )

Many thanks for the advice

thanks

Answer 1

. What is the recommended to have users in alwayon env whether to have AD user or database user for the application to connect through AG Listener (Expecting to work smoothly with no any issue with the permission )

I think you need to first understand difference between user and login. To simply put A user uses a login to connect to database. A login is server level object while user is database level object. When you mirror, restore, replicate a database users move but logins do not as they are stored in master database. Also note that Security Identifier( SID) binds users to login, its not binded by any name which most people think.

If you create a AD account ( a domain account) its SID will be same through out the domain. And further if this AD account is added as login in SQL Server and mapped to a user they would be binded by this SID. Now post failover, this AD login will exists on secondary replica, or mirror database log secondary logshipped database if you have created it before hand ( you can do that as logins are created in master database) and will automatically bind with user present in database and you would not see login failure or orphaned user. So to solve your problem and if you really do not want to use sp_change_users_login to map logins to users I suggest you ONLY use AD account as login in SQL Server. This AD account will work just fine with listener also.

Answer 2

Hi @Ashwan ,

Suggest you using AD user. Windows/AD logins and groups already come with a SID (which is what connects logins to users). This means that you can create a login for a given Windows account on any number of servers, and all those logins will get the same SID. But SQL Server authenticated accounts cannot inherit a SID, so a new one is created in SQL Server. If you using SQL server authentication, you need to manually specify the SID of a SQL Server login when you create it on secondary replica.

Please refer to MSSQL AlwaysOn – Create Login with SID for Availability Group with SQL Script and Availability Groups: How to sync logins between replicas to get details information.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.