Enabling SQL Server 2008 TCP/IP connections via script

Mar 10 2010

As part of allowing users to connect to SQL Server remotely we often need to enable TCP/IP connections which are off by default in Express and other scenarios. While it’s simple to use SQL Server’s “Configuration Manager” to enable or disable certain protocols, sometimes we need to do things in a script, such as for an installer.

This solution uses the SQL PowerShell which is installed automatically with SQL Server 2008. I show how to do it directly or from a wrapper script such as VBScript or DOS.

Enabling TCP/IP through PowerShell

The basic PowerShell (“PS”) commands for doing this are:

$MachineObject = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') .

$ProtocolUri = "ManagedComputer[@Name='" + (get-item env:\computername).Value + "']/ServerInstance[@Name='instance-name']/ServerProtocol"

$tcp = $MachineObject.getsmoobject($ProtocolUri + "[@Name='Tcp']")

$tcp.IsEnabled = $true

$tcp.alter()

Where “instance-name” is the name of the SQL Server instance to make this adjustment for.

This can be used to set up other protocols too, like Named Pipes by making the appropriate changes to the $ProtocolUri variable. Google around for these commands for the proper syntax.

Scripting the Change

The real challenge I ran into using this was that it needed to be run from a DOS batch file, not directly from within PowerShell.

Step #1 – invoking a PowerShell script from the command line.

Turns out powershell.exe expects the next text to be actual PS commands, not filenames such as for scripts. So to invoke the executable and have it execute a saved script, you need to use syntax like this:

powershell.exe -noexit &'c:\scripts\my powershell script.ps1'

  • “&” tells it the filename may contain spaces.
  • -noexit tells PowerShell not to exit without running the commands in the next file. (Intuitive, I know.)

Of course you can do this through normal VBScript shell commands and other techniques as well:


Set objShell = CreateObject("Wscript.Shell")
objShell.Run("powershell.exe -noexit &'c:\scripts\my powershell script.ps1'")

If you want commands like this to invoke with annoying users, check out Scott Hanselman’s blog entry on using a third-party utility library for running scripts in hidden windows.

Step #2 – running from DOS.

What I really wanted was a single line that could be run from within a DOS style batch file without referencing separate scripts. Turns out, we can.

  • Multiple PS commands can be combined in one line, separated with semi-colons (;).
  • We use single quote marks (') instead of doubles because the whole command needs to be a single argument to the PowerShell.exe invocation. In the case of nested quotes, escape the singles with 2-single quotes (''). Yeah, this can get a bit messy.

Here’s the first 5-line PowerShell script invoked by a single command from DOS. (This is all one line.)


"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLPS.exe" "$MachineObject = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') .;$ProtocolUri = 'ManagedComputer[@Name=''' + (get-item env:\computername).Value + ''']/ServerInstance[@Name=''instance-name'']/ServerProtocol';$tcp = $MachineObject.getsmoobject($ProtocolUri + '[@Name=''Tcp'']');$tcp.IsEnabled = $true;$tcp.alter();"

 

Voila.

MS SQL Server ,

Comments

Add comment




biuquote
  • Comment
  • Preview
Loading