This is the first of a series of posts where I cover common Interview questions in MS SQL Server in detail. Why? Some questions are not framed properly and often the answers cover just the first and most typical answer I often dive deeper into the answers to find out how much technical prowess the candidate has and this lets me know if they just googled the answers or actually work on them.
Today’s question is:-
Which TCP/IP port does SQL Server run on? How can it be changed?
First the question should be what is the default port number used by the default instance of SQL Server. Because named instances can run on any port number that is available. The answer is obviously 1433. If you shutdown SQL Server and then assign the port number to another application like say a website then the error messages you are likely to encounter are:-
- Server TCP provider failed to listen on [ ‘any’ <ipv6> 1433]. Tcp port is already in use.
- TDSSNIClient initialization failed with error 0x271d, status code 0xa. Reason: Unable to initialize the TCP/IP listener. An attempt was made to access a socket in a way forbidden by its access permissions.
- TDSSNIClient initialization failed with error 0x271d, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. An attempt was made to access a socket in a way forbidden by its access permissions.
- Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
- SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
So the question then becomes can we change the default port number of the default instance of MS SQL Server?
The answer is NO, you can assign a new port number to the default instance but it doesn’t become the default port number (which is always 1433) and it can be done using SQL Server Configuration Manager under TCP/IP. But is that all that needs to be done?
There are a few problems that will arise out of changing the default port number for the default instance such as connection strings need to specify the port number going forward. We can work around this issue by creating an alias or even changing the global default port number using client network utility, steps on how to configure each of the above options are mentioned here.
In addition you need to verify that a SPN was created successfully for the new port number else windows authentication will not work with Kerberos which is the default when using AD. If you find the below error message in event viewer you need to perform additional steps
- The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/XXXX.local ] for the SQL Server service. Windows return code: 0xffffffff, state: 63. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
For details on how to create a SPN see this link.
How do you verify if the Default instance is running on the new port number?
The first place to look would be the event viewer which is log information about which port number is being used. In addition you can also use the dmv below depending on your edition of SQL Server
SELECT * FROM sys.dm_server_registry
Obviously there is a lot more to Configuring port numbers in SQL Server but if you get past the above your Good in My book J