Have you ever had just one error message that you wish it would give you a lot more information. This is an error message which I don’t like.
What causes this error message to occur? If you rename a server with Sql Server on it and rename Sql Server with domain level security running Sql Server, you will get this error when you try to connect to Sql Server from a remote computer. You can Remote Desktop to the server and connect to Sql Server with no issues, but as soon as you try to connect remotely you get this error. This will also happen if you clone a virtual machine with Sql Server on it and then rename it.
What causes this error message? The Sql Server with a new name does not automatically create SPN (Server Principal Name) in Active Directory for Sql Server when you have a windows account running Sql Server.
One way to fix this issue go find your Active Directory admin and have him add SPN records for Sql Server. The other day when my Windows Admin was doing this, even though the account for the server did not exist as this was a new server, he was getting a duplicate error message. You have to be a domain admin to add new SPN records.
Here is an article from MS Sql Tips to set the SPN
But we found an easier way after reviewing several articles. We opened the Sql Server Configuration Management tool, changed Sql Server to start with Local System instead of starting with the Windows Domain account. Then we restarted Sql Server Engine. At this point, SPN records were created for Sql Server. We then switched the Sql Server startup account back to a Windows Domain account and restarting Sql Server again.
After doing this, we were able to login remotely to our Sql Server. I sure wish I would have found this years ago. Note, we were on a Windows 2012 and Sql Server 2012 server.