Monday, September 22, 2014

SQL Alias doesn't work if network disconnects

Problem
I'm using a named pipe alias, and when the network disconnects I'm no longer able to connect using the alias.

My settings are as follows:
Alias Name: SERVER_NAME\INSTANCE_NAME
Protocol: Named Pipes.
PipeName: \\MY_COMPUTER_NAME\pipe\MSSQL$MY_INSTANCE\sql\query
server: MY_COMPUTER_NAME

Solution
Change the alias protocol to TCP/IP and specify the server\instance name in the Server textbox, as follows:
Alias Name: SERVER_NAME\INSTANCE_NAME
Protocol: TCP\IP
server: MY_COMPUTER_NAME\INSTANCE_NAME
Confirm this is working by disabling / disconnecting from your network and trying to connect to SQL using the alias.

Reference
I only found one other post that had the same problem as me. I answered their question on stacked overflow since it worked for me. http://stackoverflow.com/questions/10466700/local-sqlserver-with-alias-doesnt-respond-if-disconnect-from-lan-internet/25981672#25981672

No comments:

Post a Comment

There was an error in this gadget