April 24, 2019 at 8:53 pm
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/
April 24, 2019 at 9:05 pm
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.
April 24, 2019 at 10:05 pm
This might be network related, when you ping the alias does it come back with the proper IP address?
April 25, 2019 at 2:00 am
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.
April 25, 2019 at 2:48 pm
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.
April 25, 2019 at 4:09 pm
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.
April 25, 2019 at 4:17 pm
Adding another - Is the protocol the alias is setup for enabled?
Sue
April 25, 2019 at 6:04 pm
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.
April 25, 2019 at 6:57 pm
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply