I recently moved a database to a new Sql Server installation for a pro-bono client. They have a legacy Microsoft Access application (adp file) which needed to be re-pointed to the new server. Since they do not use domains, I simply entered the IP address and Sql Server credentials for the new server on the connections page and it worked.
Well, it worked for users that had an id on the server and had elevated privileges. Everyone else was getting the infamous “Sql Server does not exist or access denied message “ message. There are many things than can cause this message but they did not apply in this case.
How could user authentication be getting in the way of connecting when I am specifying Sql Server credentials and forcing the connection to use tcp/ip instead of named pipes?
The answer is that this instance of Sql Server is running on port 5000 instead of 1433. The authenticated users were able to connect because, during the connection attempt, the client software queried the Sql Server Browser service and determine that the correct port number was 5000. The anonymous clients did not have this advantage and therefore tried to connect on port 1433 and subsequently failed.
The All tab on the Data Link Properties page has a Property were the port number can be specified. This solved the problem for everyone.