We all know that for security reasons we should be using Windows Authentication as much as possible when connecting to SQL Server. However, if you ever tried using Windows Authentication to connect to a remote network over the Internet via VPN, you probably discovered that it doesn't always work and very likely ended up using SQL Server Authentication. The problem here is that SSPI context for the VPN credentials cannot be generated over TCP/IP so you need to connect with Named Pipes.
The easiest way to enforce Named Pipes on your connection is to create an alias with the SQL Server Client Network Utility and configure it as follows:
Network Libraries: select "Named Pipes"
Server Alias: arbitrary name (my personal convention in this case is to use the server name plus "NP" suffix to indicate Named Pipes)
Server Name: Remote SQL Server name
Pipe Name: either the TCP/IP address of the SQL Server or the pipe address
Now you can try connecting with QA or EM using the aliased name. It works for me on some, but not all remote networks. If you still can't connect, you need to do the following to create a trust relationship between your workstation and the remote server:
- Open Internet Explorer or Windows Explorer
- Type "\\servername" in the URL text box
- When prompted, enter the VPN credentials
Once you are authenticated, your named pipe connection should be able to use your VPN credentials. You will need to repeat this step after you log off or reboot your workstation.
This solution is not perfect and it's not suitable to provide 24/7 connectivity, but it's good enough for scenarios where you want to connect to a remote SQL Server with Windows Authentication to do some administration or maintenance tasks.