SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Logins For Windows Domain Accounts Limited To Pre-Windows 2000 Format

You may have noticed that when you create a login on a SQL server that's mapped to a Windows domain account you have to use the pre-Windows 2000 format [domain\login]. Did you also notice that there's a limitation of 20 characters on the login portion of this format?

Let's pretend that you use nice descriptive names for application accounts, for example:

  • Sales.ReportUtil.ProdService
  • Sales.ReportUtil.ProdWebuser

When you try to add these logins the 20 character limit cuts them both off at "Sales.ReportUtil.Pro". In other words, without employing a workaround (e.g. create AD groups for these accounts and add logins for the groups) you cannot add both of them to the same SQL Server. There just so happens to be another format, User Principal Name (looks like username@domain), that is not limited to 20 characters…but is not supported by SQL server. Maybe I'm missing something here, but this is 2009 and you can use the UPN format in just about every other product Microsoft makes.

I'm hoping there's not some ugly technical reason why the UPN format can't be used and that it was just a minor oversight on Microsoft's part that it was left out. Assuming the latter, I've submitted a suggestion to Microsoft Connect to correct this. If you're annoyed by this too please visit connect and vote for Connect ID 477636.


No comments.

Leave a Comment

Please register or log in to leave a comment.