SQL Server 2012 - adding listen ports causing service to not start
Client is running SQL Server 2012 Service Pack 1 on Server 2008 R2 SP1
Default instance (MSSQLSERVER) was listening on port 1433 as default on all IPs.
A few weeks back another consultant, who is working on installing a new Finance package and using this SQL2012 server as back-end was having problems getting his app server to see the SQL DB on the server. It was because his app server has to use port 55000 to connect to the SQL DB.
So I added the 55000 port to all the IPs in the Connection Manager as per http://blogs.msdn.com/b/sqlblog/archive/2009/07/17/how-to-configure-sql-server-to-listen-on-different-ports-on-different-ip-addresses.aspx?Redirected=true restarted the SQL Server Service and everything worked fine:
Last night we ran Windows Update on the server. Today, the same consultant has asked for port 94 to be added now. We added it., Restarted the SQL Service (from within the Connection Manager) and it failed to come up. This error appears in log:
Log Name: System
Source: Service Control Manager
Date: 18/06/2015 09:21:42
Event ID: 7024
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: sql01.domains.ads
Description:
The SQL Server (MSSQLSERVER) service terminated with service-specific error An attempt was made to access a socket in a way forbidden by its access permissions..
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="Service Control Manager" Guid="{555908d1-a6d7-4695-
<EventID Qualifiers="49152">7024</E
<Version>0</Version>
<Level>2</Level>
<Task>0</Task>
<Opcode>0</Opcode>
<Keywords>0x80800000000000
<TimeCreated SystemTime="2015-06-18T08:
<EventRecordID>44195</Even
<Correlation />
<Execution ProcessID="660" ThreadID="5124" />
<Channel>System</Channel>
<Computer>sql01.domains.ad
<Security />
</System>
<EventData>
<Data Name="param1">SQL Server (MSSQLSERVER)</Data>
<Data Name="param2">%%10013</Dat
</EventData>
</Event>
Users not happy as this took down about 8 differently linked systems ( :-) )
I removed port 94 (so that the ports matched the screens shots), started the service and it works. Users all happy again but cant figure out why adding a third port (just like when I added the second (55000) port) caused this error?
Thanks
Mark
Default instance (MSSQLSERVER) was listening on port 1433 as default on all IPs.
A few weeks back another consultant, who is working on installing a new Finance package and using this SQL2012 server as back-end was having problems getting his app server to see the SQL DB on the server. It was because his app server has to use port 55000 to connect to the SQL DB.
So I added the 55000 port to all the IPs in the Connection Manager as per http://blogs.msdn.com/b/sqlblog/archive/2009/07/17/how-to-configure-sql-server-to-listen-on-different-ports-on-different-ip-addresses.aspx?Redirected=true restarted the SQL Server Service and everything worked fine:
Last night we ran Windows Update on the server. Today, the same consultant has asked for port 94 to be added now. We added it., Restarted the SQL Service (from within the Connection Manager) and it failed to come up. This error appears in log:
Log Name: System
Source: Service Control Manager
Date: 18/06/2015 09:21:42
Event ID: 7024
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: sql01.domains.ads
Description:
The SQL Server (MSSQLSERVER) service terminated with service-specific error An attempt was made to access a socket in a way forbidden by its access permissions..
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="Service Control Manager" Guid="{555908d1-a6d7-4695-
<EventID Qualifiers="49152">7024</E
<Version>0</Version>
<Level>2</Level>
<Task>0</Task>
<Opcode>0</Opcode>
<Keywords>0x80800000000000
<TimeCreated SystemTime="2015-06-18T08:
<EventRecordID>44195</Even
<Correlation />
<Execution ProcessID="660" ThreadID="5124" />
<Channel>System</Channel>
<Computer>sql01.domains.ad
<Security />
</System>
<EventData>
<Data Name="param1">SQL Server (MSSQLSERVER)</Data>
<Data Name="param2">%%10013</Dat
</EventData>
</Event>
Users not happy as this took down about 8 differently linked systems ( :-) )
I removed port 94 (so that the ports matched the screens shots), started the service and it works. Users all happy again but cant figure out why adding a third port (just like when I added the second (55000) port) caused this error?
Thanks
Mark
Vitor Montalvão
Port 94 isn't a dynamic port. Must be reserved to something else.
ASKER
Hi Vitor
You'll have to excuse my SQL ignorance but can you elaborate the difference between adding port 55000 to the list which already add 1433 worked but then adding the third port of 94 in didn't?
Thanks
Mark/
You'll have to excuse my SQL ignorance but can you elaborate the difference between adding port 55000 to the list which already add 1433 worked but then adding the third port of 94 in didn't?
Thanks
Mark/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK Thanks man.
If I run that on the server will I need to restart the SQL Server Service?
Thanks
Mark/
If I run that on the server will I need to restart the SQL Server Service?
Thanks
Mark/
ASKER
I think I may have found why adding the third port manually and restarting the server service failed:
'Listen All' has to be set to 'No' in order for the ports listed on the 'IP Addresses' TAB to work - dont they?
Thanks
Mark/
'Listen All' has to be set to 'No' in order for the ports listed on the 'IP Addresses' TAB to work - dont they?Thanks
Mark/
Yes, you'll need to restart the SQL Server service.
'Listen All' has to be set to 'No' in order for the ports listed on the 'IP Addresses' TAB to work - dont they?You're right if you are limiting the ports to IP addresses.
ASKER
Ok. Thank you. We are going to test this at 1715 when any negative impact will affect less user and see what happens.
Thanks
Mark/
Thanks
Mark/
So, an endpoint was necessary then.