Max Length of AD Names for Security

Question

Max Length of AD Names for Security

ImYourDBA

SSCertifiable

Points: 5654
More actions
September 24, 2014 at 10:19 am

#311095

Does anyone know definitely what the maximum usable character length is for AD objects (users and security groups) to be added as Logins for SQL Server? We want to create longer and more descriptive AD security groups (SQL_ServerName_DBName_Function) but are concerned with running into issues with group names being too long and SQL allowing them to be added but truncating them so any queries run against them for auditing wouldn't pull out the full name.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

If you look up the documentation for sys.database_principals and similar catalog view, you will find that the type for name is sysname, that is nvarchar(128). This is also the return type for SYSTEM_USER and similar functions.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

SQLJocky (9/24/2014)
Does anyone know definitely what the maximum usable character length is for AD objects (users and security groups) to be added as Logins for SQL Server? We want to create longer and more descriptive AD security groups (SQL_ServerName_DBName_Function) but are concerned with running into issues with group names being too long and SQL allowing them to be added but truncating them so any queries run against them for auditing wouldn't pull out the full name.

SAM account name length is 20 characters so you'll be limited to this.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

ImYourDBA SSCertifiable Points: 5654 More actions · Answer 3

Perry Whittle (9/25/2014)
SQLJocky (9/24/2014)
Does anyone know definitely what the maximum usable character length is for AD objects (users and security groups) to be added as Logins for SQL Server? We want to create longer and more descriptive AD security groups (SQL_ServerName_DBName_Function) but are concerned with running into issues with group names being too long and SQL allowing them to be added but truncating them so any queries run against them for auditing wouldn't pull out the full name.
SAM account name length is 20 characters so you'll be limited to this.

Thank you for your reply...that's what I'm seeing as well when I test. Do you know if there is any fix for this? We have much more descriptive AD Security Groups than 20 characters. It's hard to believe that MS wouldn't change this by now. Absurd.

Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

The SAM account name is only a single portion of the AD full object name, it will also inherit container and domain name.

The reason these objects are small is to ensure that AD can store, query and manage thousands of objects.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

ImYourDBA SSCertifiable Points: 5654 More actions · Answer 5

Not really buying that since we can create objects with long names and SQL Server is the only place where it's limited in length. I mean we're not asking for 1000 characters, but 50 would be nice.

Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

SQLJocky (10/10/2014)
Not really buying that

That's good cos i'm not selling 😉

As already pointed out the max length for a SQL server security principal is SYSNAME which is NVARCHAR(128). This would take account of any possible length from AD or a SQL Server account.

Now there's no actual hard limit on AD objects but i cant personally see any need for a user to have a domain prefix and username of more than 128 chars

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

Having AD names longer than 20 characters is (from what I understand) only a display problem for things like NET GROUP and NET USER because they're leftovers from the old "Lan Manager" days, which had such physical limits. You can have longer names and they can be displayed with PosH, VBS, etc, etc, or by using things like DSQUERY and DSGET as part of the RSAT package you can download.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ImYourDBA SSCertifiable Points: 5654 More actions · Answer 8

But it still causes an issue when trying to use them in their full 21+ character format when adding to SQL services and when adding to SQL security...right? That is at least according to my testing.

Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

SQLJocky (11/5/2014)
But it still causes an issue when trying to use them in their full 21+ character format when adding to SQL services and when adding to SQL security...right? That is at least according to my testing.

I wouldn't have thought so but I don't know because I don't make it a habit of using long names for user or service names.

What testing have you done? I wouldn't mind giving it a try myself.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ImYourDBA SSCertifiable Points: 5654 More actions · Answer 10

Jeff Moden (11/6/2014)
SQLJocky (11/5/2014)
But it still causes an issue when trying to use them in their full 21+ character format when adding to SQL services and when adding to SQL security...right? That is at least according to my testing.
I wouldn't have thought so but I don't know because I don't make it a habit of using long names for user or service names.
What testing have you done? I wouldn't mind giving it a try myself.

It only happens with AD accounts, not SQL Native accounts. You can create a SQL Native account up to 115 characters. However, when you create an AD account, it is limited to 20 characters after the '\' character. So if try to create a login of "Domain\ABCDEFGHIJKLMNOPQRSTUVWXYZ"...you only get "Domain\ABCDEFGHIJKLMNOPQRST" instead of the entire login. It allows you to create it, but when you look at it after creation...if you try to query it or if you try to add it to the SQL services, it will only verify to the "T"...if you enter the entire login to the "Z" on the service account, it will fail.

The reason we create longer logins is simply for the ability to create descriptive AD Security Groups that users can be added to in AD and the AD group added to the server/database. This helps in environments with thousands of users and an access control team with a standardized policy for adding users to the groups to gain specifically defined roles (read/write/execute) in the database without having to engage the DBA team.

For Example:

{Domain}\SQL_{ServerName}_{DatabaseName}_{Function}

Microsoft\SQL_ServerA_Northwind_Read (26 characters after the "\", won't work)

Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/

Perry Whittle SSC Guru Points: 234013 More actions · Answer 11

You're referring to service accounts, so can you confirm please.

You're creating an AD group that's longer than 20 chars and adding as a login to a SQL server instance?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

ImYourDBA SSCertifiable Points: 5654 More actions · Answer 12

Perry Whittle (11/6/2014)
You're referring to service accounts, so can you confirm please.
You're creating an AD group that's longer than 20 chars and adding as a login to a SQL server instance?

No I've never said anything about "service accounts". As referenced throughout this post, I'm talking about AD security groups being added...and by default, you add them as "logins" that pass through the user authenticated by the AD group.

Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

SQLJocky (11/6/2014)
Jeff Moden (11/6/2014)
SQLJocky (11/5/2014)
But it still causes an issue when trying to use them in their full 21+ character format when adding to SQL services and when adding to SQL security...right? That is at least according to my testing.
I wouldn't have thought so but I don't know because I don't make it a habit of using long names for user or service names.
What testing have you done? I wouldn't mind giving it a try myself.
It only happens with AD accounts, not SQL Native accounts. You can create a SQL Native account up to 115 characters. However, when you create an AD account, it is limited to 20 characters after the '\' character. So if try to create a login of "Domain\ABCDEFGHIJKLMNOPQRSTUVWXYZ"...you only get "Domain\ABCDEFGHIJKLMNOPQRST" instead of the entire login. It allows you to create it, but when you look at it after creation...if you try to query it or if you try to add it to the SQL services, it will only verify to the "T"...if you enter the entire login to the "Z" on the service account, it will fail.
The reason we create longer logins is simply for the ability to create descriptive AD Security Groups that users can be added to in AD and the AD group added to the server/database. This helps in environments with thousands of users and an access control team with a standardized policy for adding users to the groups to gain specifically defined roles (read/write/execute) in the database without having to engage the DBA team.
For Example:
{Domain}\SQL_{ServerName}_{DatabaseName}_{Function}
Microsoft\SQL_ServerA_Northwind_Read (26 characters after the "\", won't work)

The reason I ask is because I'm currently using a VBS script that I found that interrogates the same AD data as NET USER and NET GROUP. Both of those commands truncate user and group names at 20 characters, just as you say. But the VBS has returned much longer names from AD for me.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 14

SQLJocky (11/6/2014)
.if you enter the entire login to the "Z" on the service account, it will fail.

Yes of course how silly of me, you make no reference to service accounts here!!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉