Sql Server the Specified File Is Read Only 0x80071779

By:   |   Updated: 2018-12-19   |   Comments   |   Related: More > SQL Server Configurations

Problem

If your SQL Server instance has both static and dynamic ports configured, you may occasionally feel connexion problems after a failover. Too, when using a unmarried static port, it is easier to manage the Firewall rules.  In this tip we will look at port settings for a SQL Server instance and how you lot tin brand changes.

Solution

If y'all have experienced connexion issues with dynamic ports, to set this event y'all will need to delete the dynamic port entry, including deleting the zero in the IP Addresses settings for the instance. Books Online clearly says, at the time of writing, "delete the 0" as stated in bullet point 3 in the referenced link. To exist thorough, you should so restart the SQL Server example. If it is a cluster so failover the cluster and fail it dorsum as well.

Nosotros will walk through how to tell if dynamic ports are being used and how to adjust the settings.

Step 1 - See if SQL Server Is Using Both Static and Dynamic Ports

Run this T-SQL lawmaking to encounter if you have any agile connections using both static and dynamic ports.

SELECT distinct @@SERVERNAME as [Instance], local_tcp_port FROM sys.dm_exec_connections  WHERE local_tcp_port is not null          

An case screen shot of the issue:

The local_tcp_port should be 1435 only. The 49981 shows a dynamic port.  If the TSQL above returns no port data then follow a procedure from tip 2495 by Ashish Kumar Mehta on how to identify the SQL Server TCP IP port being used.

Footstep two - Detect the nodes that the example is hosted on

You will need to configure the port information on every node that the instance is hosted on. If your instance is hosted on a Windows Cluster, then open up Failover Cluster Manager.  Go to Server Director > Features > Failover Cluster Manager. Meet tip 2672 by Brady Upton to discover out more most this.

Too, this T-SQL lawmaking will prove the active node name for an case.

Select @@servername as [Instance name], SERVERPROPERTY('ComputerNamePhysicalNetBIOS') every bit 'Node'          

Footstep three - Accommodate the TCP Port Settings with SQL Server Configuration Managing director

Remote desktop to each of the nodes hosting the instance in turn. Showtime SQL Server Configuration Manager. If using a GUI open a run command window using "Windows+R" then type SQLSERVERMANAGER11.msc (SQL Server 2012), SQLSERVERMANAGER12.msc (SQL Server 2014), SQLSERVERMANAGER13.msc (SQL Server 2016), SQLSERVERMANAGER14.msc (SQL Server 2017) to open it. SQL Server Configuration Manager is available in many versions. Check this Microsoft commodity for details.

If using a Windows Server with minimal shell configuration, so start the Microsoft Management Panel (mmc) and add the SQL Server Configuration Manager snap in. Type mmc.exe and printing Enter. Click on File and Add/Remove snap in.

If two SQL Server Configuration Manager snap-ins are present, try each one and choose the newest version as information technology will brandish both the current and older versions connection information.  Note that older mmc snapins cannot edit newer SQL Server configuration information.

The SQL Server Configuration Manager does many things and so it is worth your time going through each expanse to understand the functions.

Browse to the SQL Server Network Configuration. Then the protocols for the case.

Then open up the TCP/IP protocol name and then IP Addresses.

A tricky fleck

If the setting in the 'Protocol' tab for 'Mind All' is set up to 'Yeah' then information technology will affect how the information in the side by side tab is used.

The 'Aye' means the section under the championship IPALL volition exist used. (Come across the note in purple at this Microsoft site, information technology explains it.)

Remove any entry from the TCP Dynamic Ports area, including the zero, if present. Click OK to apply the alter.

RDP to the other nodes and remove whatever entry from the TCP Dynamic Ports area on that node.

Stride 4 - Restart and Failover

Failover the case and re-run the TSQL to confirm it has no dynamic port open up.  Fail the case dorsum and check the port over again.

Considerations and Definitions

• What is a port? A port number is an 'end-bespeak' of a service. A way of connecting a procedure to a server. A port number is held in both User Datagram Protocol (UDP) and Manual Command Protocol (TCP) packets. A port makes sense when associated with a protocol and IP number or host.
  
• What are some mutual port numbers? Run into this list of TCP and UDP port numbers at Wikipedia.
  
• Why utilize static ports? Considering that is easier to configure in a Windows Server Firewall.
  
• Isn't using a static port a security risk? No, changing the port is not a robust security step.
  
• What if I cannot failover right away? Brand the change and get dorsum and check it when you can.
  
• Why does the dynamic port alter? On startup SQL Server checks that the port is withal gratis. If not, it must change the port number. That means your firewall dominion will no longer piece of work.
  
• Why does the dynamic port not alter?  The dynamic port gets buried in the Windows Registry and will exist used next start-upward given the port is still free.

Static Port Allocation

If you configure a SQL Server instance to employ a static port and you restart the instance. Clients tin but connect to that instance using that port. The SQL Server Browser service volition direct clients to the port specified.

Instances can be organized in this mode. Specifying a different port to each case ways yous have a cross check for the clients to use. If the connection string specifies both the instance and port it means they need to get both correct to connect.

Dynamic Port Allocation

When the instance of SQL Server starts, it requests a costless port number from the operating system. See this Microsoft reference on How to configure SQL Server (2000-2008) to heed on a specific port.

Firewall tips

At some betoken you may need to check your firewall rules. See this PowerShell tip on listing Firewall Rules.

This Microsoft site lists the PowerShell commands relating to Firewalls.

Side by side Steps

• See Books Online as it conspicuously says, at the time of writing, 'delete the 0' in the TCP Dynamic Ports.
• Check out tip 2495 by Ashish Kumar Mehta How to identify SQL Server TCP IP port being used
• Check out tip 2928 by Thomas LeBlanc Updating the TCP Port on a SQL Server Cluster Node
• Check out tip 3542 past Thousand. Brian Kelley Automated way to become all port data for SQL Server instances

Related Articles

Popular Articles

About the author

Graham Okely is a contract SQL Server DBA and has been working with database systems since 1984 and has been specializing in SQL Server since 2007.

View all my tips

Article Last Updated: 2018-12-19

stanfordmung1940.blogspot.com

Source: https://www.mssqltips.com/sqlservertip/5786/remove-dynamic-port-configuration-from-a-sql-server-instance/

Share this post