Alias woes on SQL Server 2022
-
Our company is moving to the cloud. I have SQL Developer Edition 2022 on Windows Server 2022 Datacenter setting up our QA SharePoint Farm..
I've created a named instance running over port 55055 using TCP/IP. THis is what I have in my on premises SQL server that we set up about 10 years ago.
The first issue I had in creating an alias is that the Create New Alias in Configuration Manager (ConfigMgr) did not allow me to add or edit any values. I could tab through the boxes but not make any additions.
I resorted to using cliconfg in both System32 and SysWOW64 which then showed up in ConfigMgr
I was not able to connect from my workstation to the DB, although I was able to connect to the nonaliased DB/Instance.
I opened ConfigMgr, and opened the Alias. When hitting 'OK' to exit I got a message that a valid name or server be entered.
These values were not missing but I tried using an IP instead of the server name and tried a different alias name that had no numerics, thinking there may have been a change in whats allowed since I last did this. No difference.
I have an inbound and outbound firewall rule to allow traffic over 55055 even though the firewall is turned off by our server team.
There is one thing that is odd which may or may not indicate the issue. When I open the Alias Property Window in ConfigMgr on my Windows 2012R2 server the protocol reads 'tcp/ip' when I open it on my Azure it reads 'tcp'. This reads as expected in the cliconfg interfaces.
When I run autospinstaller to set up my sharepoint farm it stops at the alias and fails.
Any idea from this description what I'm doing wrong r if more details are needed ask away.
Thank you!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings; -
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
Possibly a dumb question but where are you configuring the SQL Aliases? The aliases need to be configured on ALL instances that are going to connect to the server. SQL Aliases are a per-computer configuration. The reason why it may be greyed out for some things could be due to permissions. As it is a per-computer configuration option, you would need administrator access to the computer.
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. -
I've created the alias on all SharePoint Servers as well as on my laptop using clconfg in sys32 and syswow64. THe SQL Server 2022 is installed on an Azure Virtual Machine.
I'm connecting from SSMS 20.0 on Windows 11 Enterprise.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings; -
So it shouldn't need to be set up on the server hosting the SQL instance, just anything that will connect to that SQL instance.
My next thought would be to verify the settings in the windows registry. If you navigate to HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo, that will list all of the SQL aliases available to you for 64-bit applications. For 32-bit applications it is HKLM\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\ConnectTo. Offhand, I do not remember if SSMS 20.0 is 64-bit or 32-bit, but I am leaning towards 32-bit.
Once you go to both locations and verify that the registry is set up properly, I would re-test in SSMS. One thing to note - you cannot have duplicate entries. So if your server for on-site and in the cloud are expected to have the same alias, it is going to fail on you.
Again though, I tend to avoid aliases as they just create problems for you down the road. Updates to your SQL instances that result in server name/IP changing or the port needing to be changed means you need to update ALL computers that have that alias configured. I would much rather configure it at the application level when this changes and be "one and done". ALL apps that I develop have a .config file that contains the connection string. In there, I point it to the server, instance, and port. IF the server, instance, or port changes, I don't need to update each client machine to make sure the alias is good, I update the config in one location and push an update for the clients to download.
For those who say "you can manage SQL Aliases from GPO's", yes, you can, but how frequently do those "sync". I don't want to have to tell all of my users they need to reboot because I need the GPO's to update. I'd rather tell them "close the app and start it up again and it'll grab the updated config files".
For SSMS, I don't use aliases for the same reason - I don't trust that they are maintained. CMS I am in control of and I maintain it. I know with 100% certainty that my CMS is up to date and contains all of the instances.
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. -
SQL Server Configuration Manager is where Aliases have been defined, which would be on the server where SQL is installed. The use of Aliases is recommended for SharePoint which is why I've used them here. I made a correction to the "server\instance" where I had used a forward slash. The registry contains the unique Aliases in both hives. I tried to connect via powershell. Screenshot below are the results. The first connection using server\instance was successful. Switching to the Alias it is timing out before saying its inaccessible.
SSMS delivers a similar message:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The remote computer refused the network connection.) (Microsoft SQL Server, Error: 1225)
Remote Connections are enabled. ODBC Driver 17 is on both my client and on the Azure VM.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings; -
The aliases must be set up client side, not server side. You can set them up server side, but it doesn't benefit the client to have it set up on the server. The alias is how the CLIENT knows what it is connecting to. The server doesn't need the alias unless you are connecting from the server to the same server, but that is really only useful (in my opinion) to troubleshoot firewall issues.
The error of the server not found sounds to me like the alias is not set up client side.
With the registry entry (ignore the GUI as it may be what is causing issues), I just want to confirm that the entry in the registry in both locations has the name QA2019, type of REG_SZ and has data of DBMSSOCN,<FQDN of server>\\<instance name>,<port>? What I mean by FQDN of server is that you are using the full name of the server (server.host.com) and not just a friendly name (server)? I also recommend using the port number if it is known in the alias. And the double \'s is required as that is a reserved character (escape character) and if there is only 1 \, it'll pull the wrong instance name.
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. -
I made the changes you recommended. and get the same problem. I'm using a TCP/IP alias but do have named pipes enabled. The Registry entries on my client are both showing the same information, including the Alias name.
from powershell:
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [2]. .
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
from SSMS:
Cannot connect to QA2019.
------------------------------
ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The remote computer refused the network connection.) (Microsoft SQL Server, Error: 1225)
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-1225-database-engine-error
------------------------------
The remote computer refused the network connection
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings; -
I hope browser service is running on server.
I hope SQL server port haven't changed now.
Can we have alias configuration settings to look at it.
Regards
Durai Nagarajan -
Thanks for your reply. The Browser Service is running on the SQL 2022 box. The cliconfg doesn't expand to provide a better view. The Server name was updated to fqdn adding 'company.local' to the server name, 2 backslashes and then the instance name. There is a static port defined. The only change I've made based on recommendations was the servername. I'm wondering if this could be some issue resulting from the SQL Native Client being removed from the 2022 version? See this article. The article only mentions creating aliases via the cliconfg due to the SQL Configuration Manager no longer being used (the disabling of SSCM interface is by design not inadequate permissions) but gives a reason for this.
I am now receiving "The remote computer refused the network connection" when connecting from my client to the Azure SQL instance
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings; -
There is going to be a number of changes in the error messages that are put out by Windows as different alements are changed .
What I've done is uninstall from the server and reinstall with only a named instance and with the collation required by SHarepoint.
I removed the double backslashes from my local Alias entries. Gail, it doesn't appear to be requring doubles when defining the alias in cliconfg any longer. But when I did so I received 'The server doesn't support the requested protocol'. The TCP protocol was already enabled on the DB Server. So I turned off the Static Port piece and was able to connect. I'm thinking that there must be an appliance blocking that port and I've submitted a request for our infrastructure team to investigate.
Thanks for shiming in everyone!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply