SQL Server Agent stops running after changing the default listening port
-
Dear Everyone
I changed the default listening port for SQL Server 2016 standard edition on windows 2012 R2 then i restarted the instance
Now when i try to start the Agent it starts but then stops automatically
I disabled the Named Pipes and Shared Memory protocols and left the TCP/IP oneRemote connections is checked in the properties tab
Did i miss any step or does the Agent listen to ports like the engine and i have to modify it somewhere?
The error messages in the agent are:
Date 4/3/2018 4:39:03 PM
Log SQL Server Agent (Current - 4/3/2018 4:39:00 PM)Message
[298] SQLServer Error: 2, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]Date 4/3/2018 4:38:57 PM
Log SQL Server Agent (Current - 4/3/2018 4:39:00 PM)Message
[000] Unable to connect to server '(local)'; SQLServerAgent cannot startDate 4/3/2018 4:39:03 PM
Log SQL Server Agent (Current - 4/3/2018 4:39:00 PM)Message
[298] SQLServer Error: 2, 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. [SQLSTATE 08001]I have seen other posts where they make sure the alias is blank but mine is blank and i think it has to do with the port being changed or the shared memory maybe
Any ideas?
thank you
Kal -
So i fixed the issue
I enabled the Shared Memory protocol and it worked
But does the SQL Server Agent require this protocol?
Any ideas?Kal
- hurricaneDBA - Tuesday, April 3, 2018 8:36 AM
It's not normally needed. But it's not going to hurt anything to enable it - it's enabled by default.
There have been different issues reported with the 13.0 ODBC driver and recommendations to update. And your issue is one I've seen with this version, error.
But I have that version on a few things with only TCP/IP enabled and Agent starts fine. You could check the version of your ODBC driver and try updating to 13.1 if you think you might have some problems with 13.0
Microsoft® ODBC Driver 13.1 for SQL Server® - Windows, Linux, & macOSSue
-
Hi Sue
I downloaded version 13.1 and tried to install it but it says on my system that there is a higher version
The file version there corresponds to SQL Server 2017 RTM which is strange i only applied SQL Server 2016 SP1 and CU5
Kal -
I was able to reproduce the error on my test machine which is running SQL Server 2012 on Windows 2008 R2
You change the default port to another number
Disable both Named Pipes and Shared Memory and then the Agent will not start and will show the following:Can someone else test and see if they get the same thing?
Kal
- hurricaneDBA - Wednesday, April 4, 2018 1:27 AMI was able to reproduce the error on my test machine which is running SQL Server 2012 on Windows 2008 R2
You change the default port to another number
Disable both Named Pipes and Shared Memory and then the Agent will not start and will show the following:Can someone else test and see if they get the same thing?
Kal
Do you have TCP protocol enabled for the client protocols on that server? It sounds more like the correct protocols aren't enabled. Agent is a client that signs in so the correct protocols need to be open between the server and client on that machine.
Sue
-
TCP protocol is enabled and the agent is on the same server as the engine and for client protocols only tcp is enabled.
Test it and see what you get
I installed default Sql server 2016 then I installed ssms 17.6 then SP1 then CU7
Then I changed the default port then disabled the shared memory protocol and that’s when it would start then stop
Ka
- hurricaneDBA - Monday, April 9, 2018 11:51 AM
And is the browser running?
Sue
-
No browser
-
Is this a named instance or default instance?
What port number are you changing from and to?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
DEfault instance
1433 to 7771
- hurricaneDBA - Tuesday, April 10, 2018 8:18 PM
Enable it when using non-default ports.
Sue
-
Is that a must? I didnt read anywhere that you should use the browser service with non-default ports.
Kal
- hurricaneDBA - Wednesday, April 11, 2018 6:18 AM
The protocols are coded to listen on certain default ports, pipes (depending on which protocol).
A client goes to connect to SQL Server instance using TCP/IP and will just try on the default 1433. So if that's not the port how is it suppose to know what port to use to connect?
The documentation doesn't explain it as well as it used to but there are a few on configuring for alternate ports, alternate pipes and how to connect:
Configure a Server to Listen on a Specific TCP Port
Configure a Server to Listen on an Alternate Pipe
Also if the instance isn't listening on 1433, you need the browser running to connect with the DAC. You would also want to take into consideration any firewall issues and ports that need to be open for connections.
Some of this is in this documentation of the service:
SQL Server Browser ServiceSue
-
Hi Sue
The link you sent me states at the bottom:
Program the client to connect using a custom connection string.
So we will have all applications using custom connection strings and hence no need to utilize the browser service. Is that manageable?
Kal
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply