Aliasing an instance name for remote connections

  • It's been a long time since I set up a 2012 server. We have an emergency where we have to set up a new one but can't upgrade for a few months, so we're doing another 2012 and I'm having issues with the SSMS connection string.

    Most of our servers we can connect remotely to using just the named instance. Think MickeyTest. We have a CName DNS record pointing to the server and we can use SSMS to connect to MickeyTest instead of using MyServer\MickeyTest. I've set up the CName for this new instance but have to use the MyServer\MickeyTest_2 instead of MickeyTest2. I tried setting up aliases in SQL Configuaration Manager, but if I use a port other than 1433, I have to connect using MickeyTest_2,Port# instead of MickeyTest_2. If I use 1433, I can connect with just MickeyTest_2.

    I tried configuring the alias using the Cliconfig.exe but that didn't work either.

    What am I missing here that could allow me to either use dynamic port numbers or a non-default port number to connect via SSMS with just the named instance?

    No other instances exist on this new server.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Is the SQL Browser service running?

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Yes, Browser is running.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Is IpAll set in your SQL configuration manager?

    I'm no domain admin, but has the name in AD propagated to everywhere it needs to? I ask because I've had times when I needed to wait for AD to do its thing.

     

    • This reply was modified 1 month ago by  Ed Wagner.
    • This reply was modified 1 month ago by  Ed Wagner.
  • RE: IPAll, yes I set it to the same port as the aliases.

    It's been a few weeks since the DNS setup, so I would assume it's propagated everywhere. My help desk admin says he sees the CName listed in the same format as all the other instances. But maybe I'll check AD Users and Computers to see if I can find it that way.

    I'm really scratching my head here. It has to be something simple.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • When you set up the cName record, did you accept the default time to live ttl of 1 hour?

     

  • I think part of the problem is that a CNAME only defines the IP address - and not a port.  Using just 'MickeyTest2' gets you to the IP address - but the specific port is not known.  Setting up a server alias might work - but you would have to specify the port for that instance (which excludes dynamic ports).

    It might be easier/better to just setup a client alias and push that out to all the systems that need to connect to this instance.

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Okay, I don't have control over the DNS process. I filled out a form and sent it along. I'm told it's set up the same as working instances. Also, I can't push a client alias (not even sure how I would do that) to other systems.

    The thing that confuses me...

    If I use port 1776 (for example), I have to use MickeyTest_2,1776. If I use port 1433 in the alias, I can use MickeyTest_2. No port number.

    Would the use of _ in the instance name have anything to do with this problem? I don't see how, but I'm asking anyway. And why would 1433 work but not 1776?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • On the client machines, make sure you have this in the registry:

    64 Bit alias

    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo]

    "AliasName"="DBMSSOCN,Your FQDN of the Server,1433"

    32 bit alias

    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\ConnectTo]

    "AliasName"="DBMSSOCN,Your FQDN of the Server,1433"

     

    You can copy and paste this into .reg files and automatically merge them

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Brandie Tarvin wrote:

    Okay, I don't have control over the DNS process. I filled out a form and sent it along. I'm told it's set up the same as working instances. Also, I can't push a client alias (not even sure how I would do that) to other systems.

    The thing that confuses me...

    If I use port 1776 (for example), I have to use MickeyTest_2,1776. If I use port 1433 in the alias, I can use MickeyTest_2. No port number.

    Would the use of _ in the instance name have anything to do with this problem? I don't see how, but I'm asking anyway. And why would 1433 work but not 1776?

    Port 1433 is the default port for SQL to listen. Not specifying a port will use 1433 by default.

  • Brandie Tarvin wrote:

    Okay, I don't have control over the DNS process. I filled out a form and sent it along. I'm told it's set up the same as working instances. Also, I can't push a client alias (not even sure how I would do that) to other systems.

    The thing that confuses me...

    If I use port 1776 (for example), I have to use MickeyTest_2,1776. If I use port 1433 in the alias, I can use MickeyTest_2. No port number.

    Would the use of _ in the instance name have anything to do with this problem? I don't see how, but I'm asking anyway. And why would 1433 work but not 1776?

    On your working instances - are those using the default port or are those named instances using dynamic port or static port?  Have you setup a server alias and specified the port and instance?

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey is right. Dynamic ports is default for named instances.

    this might be what you are looking for.

    https://www.sqltact.com/2020/07/on-dynamic-ports-named-instances-and.html

  • Jeffrey Williams wrote:

    Brandie Tarvin wrote:

    Okay, I don't have control over the DNS process. I filled out a form and sent it along. I'm told it's set up the same as working instances. Also, I can't push a client alias (not even sure how I would do that) to other systems.

    The thing that confuses me...

    If I use port 1776 (for example), I have to use MickeyTest_2,1776. If I use port 1433 in the alias, I can use MickeyTest_2. No port number.

    Would the use of _ in the instance name have anything to do with this problem? I don't see how, but I'm asking anyway. And why would 1433 work but not 1776?

    On your working instances - are those using the default port or are those named instances using dynamic port or static port?  Have you setup a server alias and specified the port and instance?

    Wellllllllll, phooey. Yes, they all use the default port except for the AGs which use different ports because there are multiple instances on the same server and we have to specify ports for them or use the servername\instancename.

    How annoying.

    I guess at this point, that's the solution. We can keep this thread open for debate about better ways to do it if you want, or for water cooler nonsense.

    Please excuse the vibrations from my typing. A cat has inserted herself between me and the back of the chair, ignoring my prior claim to the "warm comfy spot". She's purring. LOUDLY.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply