Microsoft SQL Server 配置启用TCP和1433端口

在安装完成SQL Server 后，有很多兄弟会发现为什么telnet 1433不通？连不上数据库？

安装默认有时TCP协议未启用；

TCP协议启用，单1433端口仅指定给127.0.0.1；

通过运行下面脚本可以完成TCP协议的启用和端口分配给当前服务器的ALL IP

SQL Server 企业版（标准版）

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") > $null

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") > $null

$MachinObj = New-Object("Microsoft.SqlServer.Management.SMO.WMI.ManagedComputer")

$uri = "ManagedComputer[@Name='$env:COMPUTERNAME']/ ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']"

$SQLTcp = $MachinObj.GetSmoObject($uri)

$SQLTcp.IsEnabled = $true

$SQLPort = $SQLTcp.IPAddresses["IPAll"]

$SQLPort.IPAddressProperties["TcpDynamicPorts"].value="0"

$SQLPort.IPAddressProperties["TcpPort"].value="1433"

$SQLTcp.Alter()

#restart service

$mssql_service = Get-Service -Name 'SQL Server (MSSQLSERVER)'

if($mssql_service.CanStop){ Stop-Service -Force -Name $mssql_service.Name }

Start-Service $mssql_service.Name

#############################################

SQL Server Express 版

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") > $null

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") > $null

$MachinObj = New-Object("Microsoft.SqlServer.Management.SMO.WMI.ManagedComputer")

$uri = "ManagedComputer[@Name='$env:COMPUTERNAME']/ ServerInstance[@Name='SQLEXPRESS']/ServerProtocol[@Name='Tcp']"

$SQLTcp = $MachinObj.GetSmoObject($uri)

$SQLTcp.IsEnabled = $true

$SQLPort = $SQLTcp.IPAddresses["IPAll"]

$SQLPort.IPAddressProperties["TcpDynamicPorts"].value="0"

$SQLPort.IPAddressProperties["TcpPort"].value="1433"

$SQLTcp.Alter()

#restart service

$mssql_service = Get-Service -Name 'SQL Server (SQLEXPRESS)'

if($mssql_service.CanStop){ Stop-Service -Force -Name $mssql_service.Name }

Start-Service $mssql_service.Name

来源 https://mp.weixin.qq.com/s/fo7o3E_D54t87srTHggzqA