修改MSSQL的端口地址_TcpPort_数据库安装工具_连载_2
修改MSSQL的端口地址_TcpPort,可在程序中调用,从而修改TcpPort
主要是用在那个恢复数据库修改端口的程序
Use master
Go
------------------------------
--1)在注册表中查询 PipeName,使用 xp_instance_regread可查询得到
declare @PipeName nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Np', N'PipeName', @PipeName Output
Select @PipeName As [PipeName]
--Remark:可正常查询出结果: PipeName
\\.\pipe\sql\query
--2)在注册表中查询 PipeName,使用xp_regread可查询到
declare @PipeName nvarchar(512)
exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Np', N'PipeName', @PipeName Output
Select @PipeName As [PipeName]
--Remark:可正常查询出结果: PipeName
\\.\pipe\sql\query
---------------------------------------------------
--3)在注册表中查询 TcpPort,使用 xp_instance_regread,没有查到
declare @TcpPort nvarchar(12)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp', N'TcpPort', @TcpPort Output
Select @TcpPort As [TcpPort]
--使用xp_instance_regread,可正常读出端口地址 TcpPort
1466
---------------------------------------------------
--4)在注册表中查询 TcpPort,使用 xp_regread,可查到
declare @rc int,@dir nvarchar(5)
exec @rc = master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp',N'TcpPort', @dir output
select @dir as TcpPort
--@dir returns 1433
--使用xp_regread,可正常读出端口地址 TcpPort
1433
-----------------------------------------------------------------------------------------------------------------------------
--下面2段,只是放在一起对比,为什么获得的结果不一致(xp_regread获得的结果 1433正确)
--参考此处: https://www.sqlservercentral.com/forums/topic/xp_instance_regread
declare @rc int,@dir nvarchar(5)
exec @rc = master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp',N'TcpPort', @dir output
select @dir as TcpPort
--使用xp_regread,可正常读出端口地址 TcpPort
1466
---------------------------------------------------
--xp_instance_regread获得结果不正确
declare @rc int,@dir nvarchar(5) exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp',N'TcpPort', @dir output select @dir
--使用xp_instance_regread,可正常读出端口地址 TcpPort
1466 (此结果与电脑中的值不一致)
---------------------------------------------------
--路径不同,得到的结果是 2433,但这个是什么TcpPort的端口地址? declare @rc int,@dir varchar(5) exec @rc = master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL\MSSQLServer\SuperSocketNetLib\Tcp','TcpPort', @dir output select @dir
--使用xp_regread,读出端口地址 TcpPort
2433
---------------------------------------------------
xp_regread
xp_instance_regread
xp_regenumkeys
xp_instance_regenumkeys
xp_regenumvalues
xp_instance_regenumvalues
xp_regwrite
xp_instance_regwrite
xp_regaddmultistring
xp_instance_regaddmultistring
xp_regremovemultistring
xp_instance_regremovemultistring
xp_regdeletevalue
xp_instance_regdeletevalue
xp_regdeletekey
xp_instance_regdeletekey
----------------------------------------------------------------------------------------------------
Apr 23,2020,稍后还是将这个修改不一致的问题弄明白.
《Securing SQL Server: Protecting Your Database from Attackers》
OnionYang@