1

My team is attempting to come up with a new naming standard for our Active Directory groups within SQL Server to help manage permissions.

DOMAIN\ENVIRONMENT_DATABASE_FUNCTION

EXAMPLES:

TEST\STAGE_StackOverflow_RW

TEST\STAGE_StackOverflow_RO

TEST\STAGE_StackOverflow_EX

However, before implementing this convention we decided to look up character limitations of both active directory and SQL Server Logins and while the character limit itself doesn't appear to be a direct issue, we did stumble upon this article from 2014: Max Length of AD Names for Security

Tl;dr of that forum post: enter image description here

My team and I used one of our dev servers to attempt to recreate this potential issue, but realized that it was very difficult to test as the permissions on AD groups sometimes don't propagate for hours even if you restart the server (tested this last night and proved it). I am wondering if anybody knows if this issue has been resolved on SQL Server 2016/2017. Is it safe to use the naming convention I posted at the very top where the first 20 characters are the same or do we have to come up with a way of abbreviating all of our objects so that the first 20 characters of a login are NEVER the same.

  • Personnaly, I like to use something like the account purpose instead of the permission itself... Ex : instead of a RO account, I would create it with "Report" instead... That way, if ever the reporting user need to have execute permission on some object, then the name of the group/login does not become missleading. Same for RW. If this account is for the application, then "App" makes more sense to me. Again, the idea is just to prevent from people to think the account only gives read and write when it may include some other permissions. P.S. Never had any issue with AG groups with 45 caracters – Dominique Boucher Jun 16 at 13:42
  • We discussed including appname in the naming convention and have not ruled it out. However, we would still like to know if having groups with the same first 20 characters could potentially cause issues were it to ever happen. – Dylan Janszen Jun 16 at 13:54
  • I was more talking about the user "function" and not the app name itself... Ex : for the same app, you could have an account for reporting, one for the dev support team, one for the app itself, etc.. Regarding the 20 caracters problem you are mentionning... we do use longer group name and I had never have any issue releated to that. – Dominique Boucher Jun 16 at 16:04
  • That makes sense. With regards to your last sentence though, I just want to reiterate that the character limit is not the issue I am trying to figure out. It is when the first 20 characters are the same between two separate logins/groups with different permissions. – Dylan Janszen Jun 16 at 16:29
  • I have to groups that have the same 41 caracters (in SQL2016) and never had issue so far... I can't tell 100% if it means the issue does not exist anymore but personnaly, never had any problem caused by this. Let's see what others think about it – Dominique Boucher Jun 16 at 16:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.