SQLTeam.com | Weblogs | Forums

SQL SERVER 2016 - Properties TCP / IP - Open IPALL TCPPort 1433

tsql
sql2016

#1

Hello, an issue that is driving me crazy for a week

I need to open 1433 without outside help from users

For this I have tried the following
1º Access the registry key and modify it from vb.net, Framework 4.0 Visual Studio 2010, but the key does not allow to modify it
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVERMSSQLServer\SuperSocketNetLib\Tcp\IPAll

2nd Launch a .bat that launches a vb.script
Contents of .bat
@echo off
if %1==payload goto :payload

:getadmin
echo %~nx0: elevating self
set vbs=%temp%\getadmin.vbs
echo Set UAC = CreateObject^("Shell.Application"^) >> "%vbs%"
echo UAC.ShellExecute "%~s0", "payload %~sdp0 %*", "", "runas", 1 >> "%vbs%"
"%temp%\getadmin.vbs"
del "%temp%\getadmin.vbs"
goto :eof

:payload

::ENTER YOUR CODE BELOW::

cd \

cd C:\Program Files (x86)\MiCarpeta
dir
pause

start 1433_SQL2016.vbs

pause

::END OF YOUR CODE::

echo.
echo...Script Complete....
echo.

pause

Contents of a vbscript

set wmiComputer = GetObject( _
"winmgmts:" _
& "!\.\root\Microsoft\SqlServer\ComputerManagement13")

set tcpProperties = wmiComputer.ExecQuery( _
"select * from ServerNetworkProtocolProperty " _
& "where InstanceName='MSSQLSERVER' and " _
& "ProtocolName='Tcp' and IPAddressName='IPAll'")

for each tcpProperty in tcpProperties

	dim setValueResult, requestedValue
	Wscript.Echo tcpProperty.PropertyName
	if tcpProperty.PropertyName = "TcpPort" then
		requestedValue = "1433"
	elseif tcpProperty.PropertyName ="TcpDynamicPorts" then
		requestedValue = ""
	end if
	setValueResult = tcpProperty.SetStringValue(requestedValue)
	if setValueResult = 0 then 
		Wscript.Echo "" & tcpProperty.PropertyName & " set."
	else
		Wscript.Echo "" & tcpProperty.PropertyName & " failed!"
	end if
next

If I launch this .bat from a double click with mouse help, it executes and changes the port to 1433
However, if the .bat is thrown from a process.start + admin permissions "runas" does not find
image

3rd

I have also tried with the query to try changing the registry key of windows but nothing
DECLARE @portNumber NVARCHAR(10)=1433

EXEC xp_instance_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key =
'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IPAll',
@value_name = 'TcpPort',
@value = @portNumber OUTPUT

SELECT [Port Number] = @portNumber
GO

Someone in myself situation ????
Thank you


#2

Why is it closed? That's the default port.


#3

If the TCP IPALL 1433 not exist any client can't connect to my machine


#4

You're talking about a firewall setting here, right?

read this blog: https://blogs.msdn.microsoft.com/timomta/2016/11/04/how-do-i-open-ports-with-powershell/


#5

Hi gbritton, not firewall rules
I'm testing all clients and server on firewall of


#6

Oh, then I don't get it. If you don't have a fw blocking, then all ports are open and 1433 is the default. so anyone that can reach the server and connect.