How to create login in sql server with "fully qualified domain name"?

anil kumar 1,276 Reputation points
2020-09-09T14:53:03.763+00:00

HI,

We have a requirement to create logins with FQDN in SQL Server. We tried same but it gives following error.

Msg 15401, Level 16, State 1, Line 3
Windows NT user or group 'ALWAYSON.LOCAL\admin1' not found. Check the name again.
Msg 15151, Level 16, State 1, Line 5
Cannot add the server principal 'ALWAYSON.LOCAL\admin1', because it does not exist or you do not have permission.

By taking just the first part of domain, i am able to create new login. my questions are:

  • Does SQL Server supports Login with FQDN?
  • In real organisations, is multi-part domains are common ?

Appreciate your insightful response - Thank you !!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,487 questions
No comments
{count} votes

Accepted answer
  1. Miamiao-MSFT 4,216 Reputation points
    2020-09-10T08:15:49.95+00:00

    Hi @anil kumar ,

    Does SQL Server supports Login with FQDN?

    What do you mean supports login with FQDN? Using FQDN as one login name?

    In short, if FQDN is included in logins, you can connect with it, if it not in logins, you can not connect with it.

    Actually, you can achieve that by creating one new login name same as the FQDN,no matter using windows authentication or SQL Server authentication.

    Test on my side as next and you can also follow the steps as these docs : create-windows-login-in-sql-server, sql-server-create-user
    I windows authentication:

    1.Check and copy the FQDN:
    23773-1jpg.png

    2.Create one user name as your FQDN name in your DC and then add it to your Administrator Group and choose it as member of …;
    23774-2.png
    3.And then add the name to Administrator Group in the PC (VersionTest.yx.com).
    23801-3.png
    4..Test
    23775-4.png

    II. SQL Server authentication:

    23792-s1.png

    23811-s2.png

    In real organisations, is multi-part domains are common ?

    Yes. It is one common domain design model. However, choose single domain model or multi one is decided by your actual working environment and actual requirements.You can reference to choose the suitable domain model: https://learn.microsoft.com/en-us/windows-server/identity/ad-ds/plan/reviewing-the-domain-models

    More information:how-to-troubleshoot-error-15401,

    BR,
    Mia


    If the answer 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.

    7: +https://learn.microsoft.com/en-us/answers/articles/67444/email-notifications.html 9: https://support.microsoft.com/en-us/help/324321/how-to-troubleshoot-error-15401

    No comments

3 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 67,721 Reputation points Microsoft MVP
    2020-09-09T21:18:57.42+00:00

    Do these FQDN login names work elsewhere in your environment? For instance, can you grant file permissions to them?

    I don't know anything for sure, but I would assume that SQL Server just talks to the AD, and accepts anything for which the AD answers with a SID.


  2. Miamiao-MSFT 4,216 Reputation points
    2020-09-11T01:33:42.323+00:00

    Hi @anil kumar ,

    Is the reply helpful?

    BR,
    Mia

    If the answer 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.

    No comments

  3. anil kumar 1,276 Reputation points
    2020-09-22T05:52:34.777+00:00

    We set up environment to test this by creating new domain like AON.AG.Ad.Local. We couldn't rename underlying host name as AON.AG because full stop( .) is not allowed in machine name. Also under NETBIOS name, we couldn't name it AON.AG.

    Now we are able to create login with either FQDN like AON.AG.Ad.Local\admin or AON\admin but AON.AG\admin gives error as expected.

    No comments