PORT Numbers

Question

PORT Numbers

lucky-80472

SSCertifiable

Points: 7793
More actions
November 10, 2006 at 9:21 am

#61575

Hello Experts,
Does anybody know where does the TCP/IP port number(s) are saved in Master database when we build a new SQL instance. I know by default 1433 is assigned.

Lucky

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

the ports are actually saved in the registry, and not in the database; that's because a service advertises the server instance where the databases reside, so the OS needs the info prior to using the db.(cant get the port FROM the database if you can't connect to it with the right port to begin with.)

here's a way via TSQL to get the info:

CREATE TABLE #GetPort

(

token varchar(100),

value varchar(20))

go

DECLARE @inst varchar(200)

DECLARE @inst1 varchar(100)

--Determine registry path and key

IF(charindex('\',@@servername) > 0)

BEGIN

SELECT @inst = substring(@@servername,charindex('\',@@servername) ,50)

SELECT @inst = 'SOFTWARE\Microsoft\Microsoft SQL Server'+@inst+'\MSSQLServer\SuperSocketNetLib\Tcp'

--SELECT @inst1 = 'TcpDynamicPorts'

SELECT @inst1 = 'TcpPort'

END

ELSE

BEGIN

if SUBSTRING(@@VERSION,23,1) = '7'

begin

SELECT @inst = 'SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\VIA'

SELECT @inst1 = 'DefaultServerPort'

end

else

begin

SELECT @inst = 'SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\Tcp'

SELECT @inst1 = 'DefaultPort'

end

END

print @inst + '\\\\////'+ @inst1

INSERT #GetPort

EXEC master..xp_regread 'HKEY_LOCAL_MACHINE', @inst, @inst1

SELECT substring(@@servername,1,25) as ServerName, value as port FROM #GetPort

DROP TABLE #GetPort

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 2

I do not know if we can asssign each database with different port number. But if you would like to know the port number of network configuration, you can

Open the properties of your SQL Server;

Click Network Configuration in the General tab;

You could not miss it.