Hello people,
Good Morning!
In this post I will show how to identify the port used by the instance of SQL Server. As you may know, the default port for TCP / IP connections is 1433 and for UDP connections is 1434. However, if you have more than one installation on the same server, you can use these ports for only one instance, making it interesting to identify this information.
Identifying the port by reading SQL Server error logs
The SQL Server error log is a great place to learn what happens to the instance of SQL Server. Using the extended stored procedure xp_readerrorlog, we can easily get this information.
| 1 | EXEC master.dbo.xp_readerrorlog 0, 1, N'Server is listening on', 'ipv', NULL, NULL, N'asc' |
Identifying the port by catalog views (DMV)
Another quick way to obtain the information is to consult system catalog views (DMV's), as examples below:
| 1 2 3 | SELECT TOP 1 local_tcp_port FROM sys.dm_exec_connections WHERE local_tcp_port IS NOT NULL |
Using the sys.dm_server_registry DMV (Only from SQL Server 2008 R2):
| 1 2 3 4 5 | SELECT value_data FROM sys.dm_server_registry WHERE registry_key LIKE '%IPALL%' AND value_name LIKE 'Tcp%Port%' AND NULLIF(value_data, '') IS NOT NULL |
Identifying the port via Windows registry
As practical as the first alternative would be to perform a Query T-SQL by querying Windows registry data through the extended stored procedure. xp_regread.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | DECLARE @Instancia NVARCHAR(50) DECLARE @Porta VARCHAR(100) DECLARE @RegKey_Instancia NVARCHAR(500) DECLARE @RegKey NVARCHAR(500) SET @Instancia = CONVERT(NVARCHAR, ISNULL(SERVERPROPERTY('INSTANCENAME'), 'MSSQLSERVER')) -- SQL Server 2000 IF ( SELECT CONVERT( VARCHAR (1), (SERVERPROPERTY ('ProductVersion'))) ) = 8 BEGIN IF (@Instancia = 'MSSQLSERVER') SET @RegKey = 'SOFTWARE\Microsoft\' + @Instancia + '\MSSQLServer\SuperSocketNetLib\TCP\' ELSE SET @RegKey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @Instancia + '\MSSQLServer\SuperSocketNetLib\TCP\' EXEC master.dbo.xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = @RegKey, @value_name = 'TcpPort', @value = @Porta OUTPUT SELECT @@SERVERNAME AS Servidor, @Instancia AS Instancia, @Porta AS Porta END -- SQL Server 2005 ou superiores IF ( SELECT CONVERT( VARCHAR (1), (SERVERPROPERTY ('ProductVersion'))) ) <> 8 BEGIN SET @RegKey_Instancia = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL' EXEC master.dbo.xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = @RegKey_Instancia, @value_name = @Instancia, @value = @Porta OUTPUT SET @RegKey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @Porta + '\MSSQLServer\SuperSocketNetLib\TCP\IPAll' EXEC master.dbo.xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = @RegKey, @value_name = 'TcpPort', @value = @Porta OUTPUT SELECT @@SERVERNAME AS Servidor, @Instancia AS Instancia, @Porta AS Porta END |
Identifying the port with SQL Server Configuration Manager
Another alternative to this if you have access to the server is by using SQL Server Configuration Manager.
To open this utility, simply use the Start Menu> Programs> Microsoft SQL Server 2008 R2 (Or your version)> Configuration Tools and open the app SQL Server Configuration Manager.
A faster alternative is to open the Run menu, and type SQLServerManager10.msc (10 represents the version of your SQL Server)
Identifying the port with the server event viewer
Finally, we can check the port used by our instance by looking at the Application event log.
To open this app you must go to: Control Panel> Administrative Tools> Event Viewer or open the Run menu and type: eventvwr.msc
In the left panel, expand the "Windows Log" menu and then check the "Application" option. In the right panel, click on the option “Filter current log” and filter by the Event ID 26022
That's it folks!
To the next!
I would like to make the change via the command prompt, how would I do it?
Changing the Dynamic TCP Port *
Hello, good afternoon! I ran Xp_regread and the result came like this:
Server Instance Port
DC14SQL14 \ INST01 INST01 NULL
I don't have access to the bank's Master, so I can't do it in other ways…
Any other ideas?
Excellent post, helped a lot.
Thanks ?