I tested below code with SQL Server 2008 R2 Express and I believe we should have solution for all 6 steps you outlined. Let's take on them one-by-one:
We can enable TCP/IP protocol with WMI:
set wmiComputer = GetObject( _
"winmgmts:" _
& "\\.\root\Microsoft\SqlServer\ComputerManagement10")
set tcpProtocols = wmiComputer.ExecQuery( _
"select * from ServerNetworkProtocol " _
& "where InstanceName = 'SQLEXPRESS' and ProtocolName = 'Tcp'")
if tcpProtocols.Count = 1 then
' set tcpProtocol = tcpProtocols(0)
' I wish this worked, but unfortunately
' there's no int-indexed Item property in this type
' Doing this instead
for each tcpProtocol in tcpProtocols
dim setEnableResult
setEnableResult = tcpProtocol.SetEnable()
if setEnableResult <> 0 then
Wscript.Echo "Failed!"
end if
next
end if
I believe your solution will work, just make sure you specify the right port. I suggest we pick a different port than 1433 and make it a static port SQL Server Express will be listening on. I will be using 3456 in this post, but please pick a different number in the real implementation (I feel that we will see a lot of applications using 3456 soon :-)
We can use WMI again. Since we are using static port 3456, we just need to update two properties in IPAll section: disable dynamic ports and set the listening port to 3456:
set wmiComputer = GetObject( _
"winmgmts:" _
& "\\.\root\Microsoft\SqlServer\ComputerManagement10")
set tcpProperties = wmiComputer.ExecQuery( _
"select * from ServerNetworkProtocolProperty " _
& "where InstanceName='SQLEXPRESS' and " _
& "ProtocolName='Tcp' and IPAddressName='IPAll'")
for each tcpProperty in tcpProperties
dim setValueResult, requestedValue
if tcpProperty.PropertyName = "TcpPort" then
requestedValue = "3456"
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
Note that I didn't have to enable any of the individual addresses to make it work, but if it is required in your case, you should be able to extend this script easily to do so.
Just a reminder that when working with WMI, WBEMTest.exe is your best friend!
I wish we could use WMI again, but unfortunately this setting is not exposed through WMI. There are two other options:
Use LoginMode property of Microsoft.SqlServer.Management.Smo.Server class, as described here.
Use LoginMode value in SQL Server registry, as described in this post. Note that by default the SQL Server Express instance is named SQLEXPRESS, so for my SQL Server 2008 R2 Express instance the right registry key was
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQLServer.
You got this one covered.
Since we are using a static port assigned to our SQL Server Express instance, there's no need to use instance name in the server address anymore.
SQLCMD -U sa -P newPassword -S 192.168.0.120,3456
Please let me know if this works for you (fingers crossed!).