Sql-server – Create an alias for a SQL Server named instance pointing to sql default instance

sql serversql-server-2012

I have an installation of SQL Server 2012 Std Ed on Windows 2012 R2 running in a default instance, and an app that wants to connect to it using a named instance (can't be changed)

E.g.

  • ServerName – mySQLServer
  • Alias required by app – mySQLServer\CrazyApp

I've tried to create an alias as follows:

enter image description here

  • Alias Name – .\crazyapp
  • Port No – 1433
  • Protocol – TCP/IP
  • Server .

I've:

  • checked TCP/IP protocol is enabled
  • have started SQL browser (not needed, I believe)
  • restarted SQL (several times)
  • tried with the machine name instead of "."
  • tried with no port number instead of 1433.
  • banged head against wall

…all to no avail.

Is it possible to do this, if so, would some kind person tell me what I'm doing wrong.

Thanks!

Best Answer

My observation is it is necessary to use cliconfg.exe on the application server in order for it to map a specific instance (e.g. '\crazyapp') to the default instance on the remote SQL server.

On the application server, I have set aliases using cliconfg.exe, using both:

  • C:\Windows\System32\cliconfg.exe (64bit) and
  • C:\Windows\SysWOW64\cliconfg.exe (32bit) (thanks, @DaniSQL)

From the application server, I have configured: alias settings

I then successfully tested this from the application server, using SSMS to the remote SQL instance.

Creating the alias on the server hosting SQL Server does not work, even if the application server can resolve the SQL Server name via DNS. In order to map from a named instance to a default instance, the alias must be created on the application server.

Thanks to @ypercube for guiding me in the right direction.

Related Question