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:
1.JPG2.JPG3.JPG
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-8e1e-26931d2012f4}" EventSourceName="Service Control Manager" />
    <EventID Qualifiers="49152">7024</EventID>
    <Version>0</Version>
    <Level>2</Level>
    <Task>0</Task>
    <Opcode>0</Opcode>
    <Keywords>0x8080000000000000</Keywords>
    <TimeCreated SystemTime="2015-06-18T08:21:42.870161900Z" />
    <EventRecordID>44195</EventRecordID>
    <Correlation />
    <Execution ProcessID="660" ThreadID="5124" />
    <Channel>System</Channel>
    <Computer>sql01.domains.ads</Computer>
    <Security />
  </System>
  <EventData>
    <Data Name="param1">SQL Server (MSSQLSERVER)</Data>
    <Data Name="param2">%%10013</Data>
  </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
LVL 13
Mark GalvinManaging Director / Principal ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Port 94 isn't a dynamic port. Must be reserved to something else.
0
Mark GalvinManaging Director / Principal ConsultantAuthor Commented:
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/
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Port 94 seems to be reserved already. Here's the list of the well known ports.

If you want to go keep going with Port 94 you may try creating an endpoint:
USE master
GO
CREATE ENDPOINT [EndPointNameHere]
STATE = STARTED
AS TCP
   (LISTENER_PORT = 94, LISTENER_IP =ALL)
FOR TSQL() ;
GO
GRANT CONNECT ON ENDPOINT::[EndPointNameHere] to [LoginUserOrGroup] ;

Open in new window

Now you can go and try to add the Port 94 again to see if it works.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mark GalvinManaging Director / Principal ConsultantAuthor Commented:
OK Thanks man.

If I run that on the server will I need to restart the SQL Server Service?

Thanks
Mark/
0
Mark GalvinManaging Director / Principal ConsultantAuthor Commented:
I think I may have found why adding the third port manually and restarting the server service failed:
4.JPG'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/
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, you'll need to restart the SQL Server service.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
'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.
0
Mark GalvinManaging Director / Principal ConsultantAuthor Commented:
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/
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
So, an endpoint was necessary then.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.