October 28, 2021 at 2:38 pm
I have a migration problem. I cannot get my SQL Server instance aliases to work remotely. I've set up aliases before, and I've double-checked everything. The assigned ports are open, the protocol has been updated with the correct port, the aliases are set up in both spots on the config manager, browser is running, TCP was used initially and changed to Named Pipes when that didn't work and then changed back. I've tried using localhost, the server name, the IP address on the server part.
We have DNS CNames that point to the correct servers. Ping is working as is RDP using these names. It's just SSMS which is giving me fits.
I see lots of articles on Google on how to fix this issue but nothing I've tried works. We can't configure the aliases on client computers because there are far too many of them and no one has admin permissions on their computers to run cliconfig.exe.
Does anyone know of any other options here? I would appreciate any help you can give me.
October 28, 2021 at 3:14 pm
Are you using the port in the connection string? As in myserver,1433 to verify this works? Can you connect in any way from SSMS? What about ADS?
It's not quite clear what is happening or what you've changed/set up.
October 28, 2021 at 3:25 pm
Okay, so it does work with the port number. Now how do I get it to connect without the port number?
October 28, 2021 at 3:28 pm
Nevermind. Apparently 50 restarts of the service and recreating the aliases a dozen times will finally get it to work. And must use port 1433 to get the alias to work without a port.
Sigh.
3 hours I have wasted on this today. 3 hours. <headdesk> Anyone got a free ticket to New Zealand I can steal? I need a vacay away from the onsense-a.
October 28, 2021 at 3:43 pm
Sorry.
What I would say, from my limited networking memory, is that the CNAME and alias for finding a server don't usually include a port. I know there are some advanced capabilities, but when you are resolving a network address, is this case trying to get to a TCP socket, the alias typically only gets to the IP for the machine. Picking a specific port is usually up to the application. I'd expect that 1433 works as a default, but I honestly have no idea how the browser might come into play with aliases. It's possible that you're making a UDP 1434 connection and the server is reporting there is no instance by that name. If you use 1433, then this forces a direct TCP socket on that port.
If you have time to do a longer post on what you want to happen and what was configured, I can try to help, but for now, maybe take a break.
Plus, New Zealand is still closed for us ;(
October 28, 2021 at 4:25 pm
> Okay, so it does work with the port number. Now how do I get it to connect without the port number?
Start the SQL Browser service.
Eddie Wuerch
MCM: SQL
October 28, 2021 at 4:44 pm
Whenever I need to create an alias, i use the following powershell script, it creates an SQL alias for 32 and 64 bit versions.
#Name of your SQL Server Alias
$AliasName = "My_Alias"
# Actual SQL Server Name
$SQLServerName = "Server_URL"
#TCP Port
$Port = "1433"
#These are the two Registry locations for the SQL Alias
$x86 = "HKLM:\Software\Microsoft\MSSQLServer\Client\ConnectTo"
$x64 = "HKLM:\Software\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo"
#if the ConnectTo key doesn't exists, create it.
if ((test-path -path $x86) -ne $True)
{
New-Item $x86
}
if ((test-path -path $x64) -ne $True)
{
New-Item $x64
}
#Define SQL Alias
$TCPAliasName = "DBMSSOCN,$SQLServerName,$Port"
#Create TCP/IP Aliases
New-ItemProperty -Path $x86 -Name $AliasName -PropertyType String -Value $TCPAliasName
New-ItemProperty -Path $x64 -Name $AliasName -PropertyType String -Value $TCPAliasName
Up to now it always worked for me.
October 28, 2021 at 5:21 pm
> Okay, so it does work with the port number. Now how do I get it to connect without the port number?
Start the SQL Browser service.
It was already started. And I finally got it to work (see the "3 hours wasted" post). Had to switch to port 1433 to get the alias to log in without using the port. Which is stupid considering all the work Microsoft went to in order to keep people from hacking port 1433.
I have a server in another environment that has no aliases set up in SQL Config Manager but can still be connected to in SSMS using the DNS name. That one seriously confuses me. But it's working, so I'm only going to chase it when I have time.
October 28, 2021 at 8:56 pm
I am pretty sure that SQL Configuration Manager aliases just put aliases on the server or on one individual workstation, not on all the client machines (don't quote me on that).
We took a very similar approach that Louis Hillebrand did except rather than just have a script that people would run to update aliases, we built it into the logon script. So any time someone logs into their PC, the SQL aliases are updated to be correct. The process happens nearly instantly and if we do end up renaming a server, we just reboot the client machines and it is back up and running for everyone.
Having no aliases configured on the server doesn't mean there are no aliases configured on the client.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 29, 2021 at 12:57 pm
Well, some instances may not use port 1433 !
You'll actually need to double check that the instance you are trying to address is actually the one listening at that port number !
( select serverproperty( 'servername') )
We don't use client-local SQLAliasses, as they will sooner or later bite in the back.
We also do not use port addressing for sqlinstances, but have udp1434 open so the instance names can be resolved by SQLBrowser.
We have AD-aliasses in use by which we provide "user friendly" server names to be used in conjunction with the instance name.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 29, 2021 at 1:29 pm
Well, some instances may not use port 1433 !
You'll actually need to double check that the instance you are trying to address is actually the one listening at that port number !
You misunderstand what I'm saying and making a large assumption about my knowledge of SQL ports.
In order to use MyInstanceName as an alias (no server, no port) it needs to be forced to use 1433. In my situation, I had forced it to use a different port number to make the alias work (2020 for instance), but then I had to use MyInstanceName,2020 to connect, which I was trying to avoid.
October 29, 2021 at 2:45 pm
No offense intended at all, nor questioning any level of knowledge.
So you're talking DNS aliasses, wright?
You could connect using "MyInstanceName\MyInstanceName" , no port number needed and SQLBrowser will resolve the port number for you.
but, of course, as your solution now is running the instance on port 1433, there is no need to specify '\MyInstanceName' and SQLBrowser doesn't come in to play.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 29, 2021 at 2:57 pm
Brandie - are you using SQL Aliases OR DNS Aliases? If it is DNS aliases, you will likely need to include the instance name (for a named instance) when you have the "MyInstanceName" in there. If it is a default instance, then just the server name should work.
If it is a SQL Alias, I believe you would need to push that alias out to all client machines for it to be recognized and the SQL Alias should include the port.
And to add to what Johan said, if port 1434 is blocked OR the SQL Browser Service isn't running, you will need to provide the port number when connecting as the client has no idea what port to connect to and there are a lot of them (65,535 if memory serves).
As for us making assumptions about your knowledge of SQL ports, we have to. You provided us no indication about how much you knew about SQL ports, so we have to assume something. And, in my opinion, it is MUCH safer to assume that the person knows nothing rather than assume they are an expert; especially when it has been pointed out that connecting including the port number worked, but was untested. I am sure that Johan meant no disrespect by what they said, he was just trying to help.
Now, to add to the above, I have used SQL Aliases a lot at my company (and been bitten a few times by them too) and have never needed to include the port nor have it running on port 1433. We have multiple named instances installed on a single box (don't ask why...) and our SQL aliases work without a problem. We looked into the DNS approach for aliases but decided against it as DNS replication to all of our sites doesn't happen immediately and in the event of a failover or an upgrade resulting in a new instance being created and things being migrated over, it is much easier to ask people to reboot and the logon script updates things than it is to get DNS updated and wait for it to replicate across all the DC's and out to all client workstations.
Just my 2 cents. I am glad you got it figured out, but I am not certain you are correct on it needing to use port 1433 for that to work.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 29, 2021 at 3:34 pm
We're using both DNS aliases and SQL aliases. We have too much code connection strings using just the instance names, so I was trying to make this migration easy by just using MyInstanceName without anything else.
Yes, I could use MyInstanceName\MyInstanceName, but that would require our Dev & Reporting teams to change lots of code in lots of places and they literally have no time or resources to spend on that particular project. Plus, if I did that, I might as well go with ServerName\MyInstanceName or MyInstanceName,####.
Fortunately I found a resolution. Unfortunately, it still breaks the primary reason behind avoiding port 1433.
I wish Microsoft would allow SQL aliases to reference only the instance name and be able to use other port numbers instead of forcing us to show our port numbers to all connections or use the default. It's stupid they way they've "fixed" this particular problem.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply