How to run Set-SqlNetworkConfiguration with current user's credentials

Question

I am trying to use Set-SqlNetworkConfiguration to set the TCP port of a SQL Server instance. The command looks like this:

Set-SqlNetworkConfiguration -Protocol TCP -Port 1433 -ServerInstance $ENV:ComputerName\SQLSERVER

However, the command prompts for credentials with a GUI prompt. There's the -Credential parameter, but examples I've found use Get-Credential which, again, shows a UI prompt.

How do I run Set-SqlNetworkConfiguration with the current user's credentials without displaying any GUI prompts and without hard-coding credentials in a script? I have absolutely no need to explicitly set a username and password; the current user has administrator permissions for the given SQL Server instance.

Question (s): So, are you saying, that you can open SSMS and connect to that server / DB instance and not get prompted, but with using the cmdlets, you are? Are you saying, you can use any other SQL cmdlet, and you can interact with SQL just fine and only this on fails? — postanote, Sep 02 '18 at 22:43
@postanote Frankly, I don't care how other SQL cmdlets behave. I need to use this very one in an unattended installation scenario, so *prompting interactively* for credentials is just forbidden. This is just against the very purpose of scripting. — Ondrej Tucny, Sep 02 '18 at 22:57
I get that, and that was not what I was driving at. If you are directly on the SQL Server interactively (locally or remotely via explicit remoting using Enter-PSSession, or via implicit remoting using New-PSSession), both require and local machine admin account, not just SQL Admin, then you should not be prompted. If you are remote to the SQL box, running these cmdlets without a established remote session or using this cmdlet directly or Invoke-Command with PSRemoting is enabled, then you are getting prompted by Windows first not SQL. PSRemoting requires local admin privs on the box for this. — postanote, Sep 02 '18 at 23:33
@postanote I am running directly on the server, logged in with a local admin account, powershell running elevated as admin. I am being prompted. — Ondrej Tucny, Sep 02 '18 at 23:39
@postanote To clarify further: I want to use this command in order to set TCP port as part of an unattended SQL installation script: runs setup with an answer file, installs updates, installs SSME, creates alias, sets TCP port. — Ondrej Tucny, Sep 02 '18 at 23:44

score 0 · Answer 1 · answered Sep 03 '18 at 00:46

I do SQL unattended install regularly and the SQL server installer sets TCP settings default to 1433 for every instance created. So, there should be no need to do this manually unless you are changing it to some other port number for whatever reason.

I get that this neither here or there and what your goals are, but as a quick sanity check on one of my SQL server in one of my labs, this appears to be what it is.

Of course you can set a credential file …

https://www.jaapbrasser.com/quickly-and-securely-storing-your-credentials-powershell

... or Windows Credential Manager ...

Accessing Windows Credential Manager from PowerShell

... before the SQL install part, and call the creds from there. I know, not what you are after, but that appears to be the only option based on all my testing shortly ago and it's not just this cmdlet. This occurs across the cmdlets.

For example …

Get-SqlInstance -MachineName $ENV:ComputerName

… will prompt as well, unless you pass the creds from a storage location, either via the XML file or Windows credential manager, which is what I use in my installs and PSRemoting session on-prem and online. Yet, you do have to get prompted at least once.

score 0 · Answer 2 · answered Sep 03 '18 at 06:30

I have made a function to set credentials object for such cases.

function Set-Credentials 
{
    [CmdletBinding()]
    Param(
        [Parameter(Mandatory = $true,ValueFromPipeline = $true,Position = 0)][ValidateNotNull()][String]$UserName,
        [Parameter(Mandatory = $false,ValueFromPipeline = $true,Position = 1)][ValidateNotNull()][String]$UserPassword
    )
    if (-not$UserPassword)
    {
        $Password = Read-Host -Prompt "Specify password for $UserName" -AsSecureString
    }
    else
    {
        $Password = (ConvertTo-SecureString -String $UserPassword -AsPlainText -Force)
    }
    $Credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList @($UserName, $Password)
    Write-Output -InputObject $Credentials
}

Example of usage:

Set-SqlNetworkConfiguration -Protocol TCP -Port 1433 -ServerInstance $ENV:ComputerName\SQLSERVER -Credentials $(Set-Credentials -UserName UserName -UserPassword UserPassword)

How to run Set-SqlNetworkConfiguration with current user's credentials

2 Answers2