How to enable "Force Encryption" for SQL Server 2005?

I need to enable "Force Encryption" for SQL Server 2005, but I don't know which API can do it. I need to operate remote SQL server , so I cannot operate registry directly to do it. Who can help me?

You need to use WMI to accomplish this. You can create a VBScript program to do this. Here is an example how the flags are enumerated:

---

On Error Resume Next

Const wbemFlagReturnImmediately = &h10
Const wbemFlagForwardOnly = &h20

arrComputers = Array("MWORIES-N3")
For Each strComputer In arrComputers
WScript.Echo
WScript.Echo "=========================================="
WScript.Echo "Computer: " & strComputer
WScript.Echo "=========================================="

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement")
Set colItems = objWMIService.ExecQuery("SELECT * FROM ServerSettingsGeneralFlag", "WQL", _
wbemFlagReturnImmediately + wbemFlagForwardOnly)

For Each objItem In colItems
WScript.Echo "FlagName: " & objItem.FlagName
WScript.Echo "FlagValue: " & objItem.FlagValue
WScript.Echo "InstanceName: " & objItem.InstanceName
WScript.Echo
Next
Next
' See Also: http://blogs.msdn.com/mwories/archive/2005/04/27/sql-wmi-scriptomatic.aspx

---

Note there are SMO objects that allow you to do this in C# as well. See the ManagedComputer object in the SMO reference docs. This is quite a bit easier to use than WMI, but on the flipside, a VBS script is much easier deployed and modified.

|||Hi Michiel,

I did not find any reference for setting sthis flag, could you point me in the right direction ? Didn′t find anything in the Properties nor Protocol properties.

Thanks, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

If you modify the script to point to your machine then you should see something like:

FlagName: ForceEncryption
FlagValue: False
InstanceName: (your instance name)

This is the flag that you need to modify. It's not a hard coded property but this collection is a set of name-value pairs.

Makes sense?

|||i actually saw the previous post, but I asked how to do this with the ManagedComputer class ? I don′t see any property collection in there, could you point me in the right direction for this ?

-Jens.|||

I have found a way to do it in C#. Following is C# code.

using System.Management;

namespace EnableTCPIP
{
class Program
{
static void Main(string[] args)
{
ManagementScope scope = new ManagementScope(@."\\" + "test.machine"+ @."\root\Microsoft\SqlServer\ComputerManagement");
SelectQuery query = new SelectQuery("SELECT * FROM ServerSettingsGeneralFlag");
ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, query);
ManagementObjectCollection managementObjects = searcher.Get();
foreach (ManagementObject obj in managementObjects)
{
foreach (PropertyData p in obj.Properties)
{
Console.WriteLine(p.Name + ": " + p.Value);
}
}}}