January 22, 2021 at 4:16 pm
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.
January 22, 2021 at 4:23 pm
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
January 22, 2021 at 4:26 pm
Yes, Browser is running.
January 22, 2021 at 4:53 pm
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.
January 22, 2021 at 5:21 pm
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.
January 22, 2021 at 5:26 pm
When you set up the cName record, did you accept the default time to live ttl of 1 hour?
January 22, 2021 at 5:27 pm
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
January 22, 2021 at 5:33 pm
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?
January 22, 2021 at 5:35 pm
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/
January 22, 2021 at 5:36 pm
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.
January 22, 2021 at 5:57 pm
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
January 22, 2021 at 6:06 pm
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
January 22, 2021 at 6:11 pm
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.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply