SQL 2012 server alias works locally but not remotely

Question

I'm preparing for a server migration and am attempting to redirect a named instance of SQL Server 2012 (DW) to the default instance. There is currently no DW instance on the test server - I'd like to avoid re-creating it and consolidate all of our databases on the default instance when we upgrade. However, I need connections that are pointing at the named instance to continue to function.

I set up an alias as directed here and am able to use named instance (xx.xx.xx.xx\DW) to connect to the default instance (xx.xx.xx.xx) when I am RDP'd into the server. I'm able to connect to the default instance from my local SSMS as well. However, when I try to connect to the named instance (which should redirect to the default instance) from my local SSMS I get an error that says:

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

Is there any reason the alias would function from within the server but not remotely?

Solution 1

You have to create the alias on each users machine that will be connecting to the SQL Server via the named instance.

Solution 2

TO avoid having to install the ALIAS on every client machine you can created a computer object in Active Directory and assign it the IP address of the server where SQL is installed.

Then go to the TCP/IP protocols in SQL SERVER CONFIGURATION MANAGER and enable the IP ADDRESS. Set the TCP Dynamic ports to 0 and leave TCP PORT blank.

Works for me.

Comments

[davejroth]

Thanks for your answer but unfortunately that's not possible as I have many users who have built reports that access the named instance of the server. Is it possible to do via DNS?

[pauska]

@davejroth you can use DNS aliasing for the servers FQDN (as in hostname), but it can't help you with the named instance part.

[Ryan Ferretti]

Davejroth, sadly this is your only option. You can't use DNS to do this as DNS won't impact the instance name. You'll need to figure out the registry settings changes which are made to create the alias and use active directory to push those changes out to all your users. Your only other option is to install a new instance called DW on the new server and put your application database on that instance.

[Ian Murphy]

RFTFire, you say above 'assign it the ip address of the server', but don't say how. I'm not aware of any method to assign IPs from the users and computers admin tool.

[Jacques]

@RftFire I second that, I don't see how you can 'assign it the IP address of the server'?