Connect-SQL: Using GetNetworkCredential().UserName which does not pass NetBIOS domain

[johlju]

Details of the scenario you tried and the problem that is occurring

When using the parameter SetupCredential of the helper function Connect-SQL to impersonate a user, and the login type is 'WindowsUser', the username is extracted from the PSCredential object using the following

https://github.com/PowerShell/SqlServerDsc/blob/4ead35093c43954e69b413528355b1460bf7fdbd/SqlServerDscHelper.psm1#L76

This will not work if the username is written using the NetBIOS domain name. e.g. 'COMPANY\user'. Since the then the domain name is in the GetNetworkCredential().Domain property, and only the user name is in the GetNetworkCredential().UserName property.

This is not a problem when using the FQDN domain name, eg. '[email protected]'. Then the user name and the FQDN is part of the GetNetworkCredential().UserName property.

Verbose logs showing the problem

n/a

Suggested solution to the issue

Use the property $SetupCredential.UserName . That will always contain both the NetBIOS domain name or the FQDN domain name, together with the username.

PS> $password = ConvertTo-SecureString 'P@ssw0rd1' -AsPlainText -Force
PS> $username = '[email protected]'
PS> $credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $username, $password
PS> $credential.GetNetworkCredential().UserName
user@company.local
PS> $credential.UserName
user@company.local

PS> $password = ConvertTo-SecureString 'P@ssw0rd1' -AsPlainText -Force
PS> $username = 'COMPANY\user'
PS> $credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $username, $password
PS> $credential.GetNetworkCredential().UserName
user
PS> $credential.UserName
COMPANY\user
PS>
PS>

Although there is a slight issue with using that. Using the NetBIOS domain does not currently work in for example SQL Server 2016 (SQLPS). This might need to be documented as an requirement (to not use NetBIOS domain format).

PS> Import-Module -Name 'C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS'
PS> $sql = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
PS> $sql.ConnectionContext.ConnectAsUser = $true
PS> $sql.ConnectionContext.ConnectAsUserPassword = 'P@ssw0rd1'
PS> $setupCredentialUserName = 'COMPANY\user'
PS> Write-Verbose -Message ("Username: {0}" -f $setupCredentialUserName)
PS> $sql.ConnectionContext.ConnectAsUserName = $setupCredentialUserName
PS> $sql.ConnectionContext.ServerInstance = "$($env:COMPUTERNAME)\SQL2016"
PS> $sql.ConnectionContext.Connect()
Exception calling "Connect" with "0" argument(s): "The user name or password is incorrect.
"
At line:1 char:1
+ $sql.ConnectionContext.Connect()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ConnectionFailureException

PS>

But using FQDN or no domain name at all works.

PS> Import-Module -Name 'C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS'
PS> $sql = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
PS> $sql.ConnectionContext.ConnectAsUser = $true
PS> $sql.ConnectionContext.ConnectAsUserPassword = 'P@ssw0rd1'
PS> $setupCredentialUserName = '[email protected]'
PS> Write-Verbose -Message ("Username: {0}" -f $setupCredentialUserName)
PS> $sql.ConnectionContext.ConnectAsUserName = $setupCredentialUserName
PS> $sql.ConnectionContext.ServerInstance = "$($env:COMPUTERNAME)\SQL2016"
PS> $sql.ConnectionContext.Connect()
PS> $sql.Status
Online
PS>

PS> Import-Module -Name 'C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS'
PS> $sql = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
PS> $sql.ConnectionContext.ConnectAsUser = $true
PS> $sql.ConnectionContext.ConnectAsUserPassword = 'P@ssw0rd1'
PS> $setupCredentialUserName = 'user'
PS> Write-Verbose -Message ("Username: {0}" -f $setupCredentialUserName)
PS> $sql.ConnectionContext.ConnectAsUserName = $setupCredentialUserName
PS> $sql.ConnectionContext.ServerInstance = "$($env:COMPUTERNAME)\SQL2016"
PS> $sql.ConnectionContext.Connect()
PS> $sql.Status
Online
PS>

The DSC configuration that is used to reproduce the issue (as detailed as possible)

n/a

SQL Server edition and version the target node is running

n/a

SQL Server PowerShell modules present on the target node

n/a

The operating system the target node is running

n/a

Version and build of PowerShell the target node is running

n/a

Version of the DSC module that was used ('dev' if using current dev branch)

dev

[johlju]

This is not an issue as long as the FQDN domain format is used, e.g. '[email protected]'. So this might just be a documentation issue.

[johlju]

It is better that all $credential.GetNetworkCredential().UserName is changed to $credential.UserName that will correctly return the string user name including NetBIOS domain or FQDN that the user provided.

[johlju]

It seems that if not providing the NetBIOS name in the user name will successfully connect the user.

PS> import-module -name SqlServer
PS> $sql = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
PS> $sql.ConnectionContext.ConnectAsUser = $true
PS> $sql.ConnectionContext.ConnectAsUserPassword = 'P@ssw0rd1'
PS> $sql.ConnectionContext.ConnectAsUserName = 'DOMAIN\sqluser'
PS> $sql.ConnectionContext.ServerInstance = "localhost\SQL2017"
PS> $sql.ConnectionContext.Connect()
Exception calling "Connect" with "0" argument(s): "The user name or password is incorrect.
"
At line:1 char:1
+ $sql.ConnectionContext.Connect()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ConnectionFailureException

PS> $sql.ConnectionContext.ConnectAsUserName = 'sqluser'
PS> $sql.ConnectionContext.Connect()
PS> $sql.Status
Online
PS>

[johlju]

Reopening this issue since this should be documented in https://github.com/dsccommunity/SqlServerDsc/wiki/CredentialOverview.

[johlju]

On second thought, let re-close this a open a new issue that points to this,