1

I'm facing a strange problem :

I have a sql server on the IP 80.248.XXX.XXX

I've also create an Alias on SQL Server like servername.sql.mydomain.com with this tutorial : https://www.mssqltips.com/sqlservertip/1620/how-to-setup-and-use-a-sql-server-alias/

When I use SSMS, if :

  • I use the IP with user sa, it's working like a charm
  • I use the domain name servername.sql.mydomain.com, with user sa

I have an error : (in French)

Impossible de se connecter à servername.sql.mydomain.com.

===================================

Échec de l'ouverture de session de l'utilisateur 'sa'. (.Net SqlClient Data Provider)

------------------------------
Pour obtenir de l'aide, cliquez sur : http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

------------------------------
Nom du serveur : servername.sql.mydomain.com
Numéro de l'erreur : 18456
Gravité : 14
État : 1
Numéro de la ligne : 65536

And I do not understand why...

2 Answers 2

2

I've also create an Alias on SQL Server

An alias is a client-side concept. To be effective you must configure the alias on every client computer connecting to SQL Server. The alternative is to create a Hostname Alias to enable that name to be resolved by all clients on the network.

There are four easy steps to creating a Hostname Alias, but they are poorly documented and often misunderstood. All you have to do is:

1) Create a DNS Record pointing to the IP address of the target server

2) Configure SQL Server to listen on port 1433 on that IP address

3) Add SPNs to enable the SQL Server Service account to use Kerberos Authentication (optional if you don't use Kerberos).

4) Add BackConnectionHostNames entries to enable NTLM Authentication

https://blogs.msdn.microsoft.com/dbrowne/2012/05/21/how-to-add-a-hostname-alias-for-a-sql-server-instance/

1

To resolve this work with your SA team or the team that manages your DNS, and have them create a CNAME or an A record that is associated with the IP address of the server. Wait for the record to propagate in your environment, and then try to login.

To test out add an entry into your LMHOST file on your local machine (C:\Windows\System32\drivers\etc).

Edit the hosts file, and add this entry to it (made adjustments as needed for naming conventions):

80.248.XXX.XXX servername.sql.mydomain.com

If this works then the DNS configs from your SA or DNS team will resolve your issue. GOOD LUCK!

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.