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

Question

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 !!

Answer 1

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:

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 …;

3.And then add the name to Administrator Group in the PC (VersionTest.yx.com).

4..Test

II. SQL Server authentication:

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

Answer 2

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.

Answer 3

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.

Answer 4

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.