Unable to Successfully Connect Using SQL Server Alias

  • lmarkum

    SSCertifiable

    Points: 7783

    I've tried on four different SQL instances on three different computers to configure a SQL Server alias and then connect to that SQL instance using SSMS on the machine that the alias was configured on. Each time I get the standard error message from SQL Server when it can't find the SQL instance name being used in SSMS.

    I've tried on my local machine here at work. I set up an alias for each SQL instance and could not connect. I just also tried to do this om a Dev box for myself.  The alias I made there is DBADEV01. From the local Dev machine I launched SSMS and input DBADEV01 as the Server name. I click Connect and I get the standard, "Can't find that SQL Server name" error.

    Once I can get this working I will then test out the second part of the article below by having infrastructure create a matching CNAME record so I can test using that SQL alias from outside the local Dev box. This work is all related to migrating off three old SQL Server 2008/08R2 instances.  I'm hoping to use aliases and CNAMES to prevent a lot of work changing connection strings in applications.

    In my SQL Server Config Manager on my own Dev box I have an alias of DBADEV01 for the name.  Port No value is 1433.  Server value is the name of the SQL Server instance, which in this case is the default instance of the machine name.  I've confirmed in SSCM that I'm using the default 1433 port, matching my alias.

    https://jackworthen.com/2017/03/23/creating-and-configuring-an-alias-in-sql-server/

  • Chris Harshman

    SSC-Forever

    Points: 41820

    I've used Aliases before, so I know it's possible.  Did you setup the same Alias under both "SQL Native Client Configuration" and "SQL Native Client Configuration (32bit)"  ?

    Also this method would require each client computer to have the aliases setup for this to work.  Maybe it would be easier if this was setup just as an extra DNS entry, then it would be configured once at the network level instead of for each client.

  • oogibah

    Say Hey Kid

    Points: 679

    This might be network related, when you ping the alias does it come back with the proper IP address?

  • lmarkum

    SSCertifiable

    Points: 7783

    Chris Harshman wrote:

    I've used Aliases before, so I know it's possible.  Did you setup the same Alias under both "SQL Native Client Configuration" and "SQL Native Client Configuration (32bit)"  ? Also this method would require each client computer to have the aliases setup for this to work.  Maybe it would be easier if this was setup just as an extra DNS entry, then it would be configured once at the network level instead of for each client.

     

    Chris, I used the SQL Server Configuration Manager to do the set up.  I did just now look at the SQL Native Client Config tool and my alias is there.  Not sure why I would need to complete a setup in the 32bit of the SQL Native Client Config.  The machine is 64 bit.

  • lmarkum

    SSCertifiable

    Points: 7783

    oogibah wrote:

    This might be network related, when you ping the alias does it come back with the proper IP address?

     

    PING from the machine where the alias was made says it can't find the hostname.

  • steve.powell 14027

    SSC Enthusiast

    Points: 190

    Can you ping the target server at all?

    It may be you need to set a firewall rule to allow the connection (firewall rules can open ports, but may limit them to specific incoming IP addresses). If so, the target server is where the rule needs setting.

  • lmarkum

    SSCertifiable

    Points: 7783

    steve.powell 14027 wrote:

    Can you ping the target server at all? It may be you need to set a firewall rule to allow the connection (firewall rules can open ports, but may limit them to specific incoming IP addresses). If so, the target server is where the rule needs setting.

     

    I'm sitting on the local host where the SQL Server alias is defined and it won't connect with the alias name and PING returns host not found.  The original SQL Server instance name still works fine for connections.  It's just the alias that doesn't work.

  • Sue_H

    SSC Guru

    Points: 90287

    Adding another - Is the protocol the alias is setup for enabled?

    Sue

  • Chris Harshman

    SSC-Forever

    Points: 41820

    Even if the OS is 64 bit, you mentioned that you are trying to use SQL Server Management Studio to connect using the alias.  This is a 32 bit program, so it is using the 32 bit Native Client library.  It's annoying that Configuration Manager makes you duplicate the alias list, but you do need to setup the alias in both for consistency.

  • lmarkum

    SSCertifiable

    Points: 7783

    Chris Harshman wrote:

    I've used Aliases before, so I know it's possible.  Did you setup the same Alias under both "SQL Native Client Configuration" and "SQL Native Client Configuration (32bit)"  ? Also this method would require each client computer to have the aliases setup for this to work.  Maybe it would be easier if this was setup just as an extra DNS entry, then it would be configured once at the network level instead of for each client.

     

    Chris was right.  Apparently on a 64 bit system you have to have the 64 bit and 32 bit alias created.  Once that was done, the SQL Server Alias worked locally.

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

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