Assume that the following are all true:
- You have a running SQL Server 2005 (or later version based) named instance, such as MyServer\MyInstance, and SQL Browser service running also;
- You are able to connect successfully to databases from that server instance over a local network connection (i.e. LAN) using SQL Server Management Studio or other software tools and applications, using Windows Authentication;
- You have a VPN connection available on your server, working correctly and allowing you to: view LAN resources from a remote location (such as browsing \\MyServer in Windows Explorer), run Remote Desktop to available network computers configured to accept Terminal Services (such as MyServer itself), etc.
Unfortunately, you could test and find out that this may also be true:
- Trying to connect to MyServer\MyInstance[,port] from a remote location over the VPN connection using Windows Authentication doesn’t work. You receive an error message such as: “A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)” or “Cannot generate SSPI context”.
To (partially) resolve the situation you need to choose from one of these workarounds when connecting over VPN:
- Enable and use SQL Server Authentication instead of Windows Authentication and a static port for the SQL Server instance (always specifying the port value in the connection string);
- Use a static port for the SQL Server instance (always specifying the port value in the connection string), enable that TCP port in your server firewalls, and specify the public server name or its external IP address (provided that it is exposed outside the local network);
- Enable trust between your source and destination domains (not applicable for connections “on the fly”).
Personally I decided to use the first workaround as it was the easier to implement and the most secure. If you know a better choice, I’d appreciate if you could share its details as a short comment, whenever you have the time and opportunity to post it – thank you in advance. (Google searches didn’t get me anything better at this time…)