As far as I tried, I am able to get ServerInstances only when running on 64bit program.
How to enable TCP protocol of SQL Server Express 2019 through C# code?
I have MS SQLExpress 2019 installed in Windows 11 desktop. I want to enable SQLExpress TCP protocol programmatically from Windows Application using c#.
So, I have written following simple code.
using Microsoft.SqlServer.Management.Smo.Wmi;
ManagedComputer mc = new ManagedComputer();
###1st way###
ServerProtocol srvprcl = mc.ServerInstances[0].ServerProtocols[2];
srvprcl.IsEnabled = true;
srvprcl.Alter();
###2nd way###
Urn uri = new Urn("ManagedComputer[@Name='computer-name']/ServerInstance[@Name='SQLEXPRESS']/ServerProtocol[@Name='Tcp']");
ServerProtocol srvprcl = (ServerProtocol)mc.GetSmoObject(uri);
srvprcl.IsEnabled = true;
srvprcl.Alter();
This code is returning mc.ServerInstances.count = 0, so eventually I am getting error, though this code is running with administrative permission.
In the alternate way same thing is happening when I am passing urn object.
To get around I have created following PowerShell script.
Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force
Install-Module -Name SqlServer -AllowClobber -Force
Import-Module SQLServer -Force
$wmi = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
$Wmi
$uri = "ManagedComputer[@Name='" + (get-item env:\computername).Value + "']/ServerInstance[@Name='SQLEXPRESS']/ServerProtocol[@Name='Tcp']"
$Tcp = $wmi.GetSmoObject($uri)
$Tcp.IsEnabled = $true
$Tcp.Alter()
$Tcp
With administrative permission, in the manual run, this PowerShell script ran well and it enabled TCP protocol.
Because I need to automate this process using C# code, so I have written C# code and ran above mentioned script from Windows Application with Administrative code.
Here I have received an error. From the investigation, I have found that $Wmi object is returning zero ServerInstances.
When I was running script manually then I was getting {SQLEXPRESS} from output of $Wmi ServerInstances but this not happening when same script is running from C# code.
I think this problem will be related to code access security but not sure how to resolve it. Any help will be appreciated.