SqlServerDsc: SQL authentication does not work in any resource

[ofaruk09]

$sql = New-Object Microsoft.SqlServer.Management.Smo.Server
$sql.ConnectionContext.ConnectAsUser = $true
$sql.ConnectionContext.ConnectAsUserPassword = "myuser"
$sql.ConnectionContext.ConnectAsUserName = "mypassword"
$sql.ConnectionContext.ServerInstance = "myinstance"
$sql.ConnectionContext.connect()
Exception calling "Connect" with "0" argument(s): "The user name or password is incorrect.
"
At line:6 char:9

•     $sql.ConnectionContext.connect()
  

•     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  

  • CategoryInfo : NotSpecified: (:) [], MethodInvocationException
  • FullyQualifiedErrorId : ConnectionFailureException

I have verified the username and password is correct but it does not work. Using the following code instead did work:

$srvname = "myinstance"
$ServerConnection = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$ServerConnection.ServerInstance=$srvname
$ServerConnection.LoginSecure = $false
$ServerConnection.Login = "myuser"
$ServerConnection.Password = "mypassword"

$sql = new-object Microsoft.SqlServer.Management.SMO.Server($ServerConnection)
$sql.ConnectionContext.connect()

[johlju]

You are correct. I could reproduced the error. Currently SQL authentication is not supported. Was it SQL authentication that was your issue?

This works for me to impersonate a domain user (the current code).

Import-Module SQLPS

$SetupCredential = Get-Credential

$sql = New-Object Microsoft.SqlServer.Management.Smo.Server
$sql.ConnectionContext.ConnectAsUser = $true
$sql.ConnectionContext.ConnectAsUserName = $SetupCredential.GetNetworkCredential().UserName
$sql.ConnectionContext.ConnectAsUserPassword = $SetupCredential.GetNetworkCredential().Password
$sql.ConnectionContext.ServerInstance = 'server.company.local'
$sql.ConnectionContext.Connect()

$sql.Status

But to impersonate (or login) as user using SQL authentication, this code needs to be used, as @ofaruk09 proposed above.

Import-Module SQLPS

$SetupCredential = Get-Credential

$sql = New-Object Microsoft.SqlServer.Management.Smo.Server
$sql.ConnectionContext.LoginSecure = $false
$sql.ConnectionContext.Login = $SetupCredential.UserName
$sql.ConnectionContext.SecurePassword = $SetupCredential.Password
$sql.ConnectionContext.ServerInstance = 'server.company.local'
$sql.ConnectionContext.Connect()

$sql.Status

But how shall we go by and distinguish SQL authentication from domain credentials? Just check if the username contains '\', '/' or '@'? If $true, use the domain account impersonation, if $false, use SQL authentication.

[ofaruk09]

The method in question is called by xSqlServerLogin. One of the properties passed to it in dsc is the login type. Perhaps that can be passed to the password check method to select the correct way to connect

…

On 1 Feb 2017 16:55, "Johan Ljunggren" ***@***.***> wrote: You are correct. I could reproduced the error. Currently SQL authentication is not supported. Was it SQL authentication that was your issue? This works for me to impersonate a domain user (the current code). Import-Module SQLPS $SetupCredential = Get-Credential $sql = New-Object Microsoft.SqlServer.Management.Smo.Server $sql.ConnectionContext.ConnectAsUser = $true $sql.ConnectionContext.ConnectAsUserName = $SetupCredential.GetNetworkCredential().UserName $sql.ConnectionContext.ConnectAsUserPassword = $SetupCredential.GetNetworkCredential().Password $sql.ConnectionContext.ServerInstance = 'server.company.local' $sql.ConnectionContext.Connect() $sql.Status But to impersonate (or login) as user using SQL authentication, this code needs to be used, as @ofaruk09 <https://github.com/ofaruk09> proposed above. Import-Module SQLPS $SetupCredential = Get-Credential $sql = New-Object Microsoft.SqlServer.Management.Smo.Server $sql.ConnectionContext.LoginSecure = $false $sql.ConnectionContext.Login = $SetupCredential.UserName $sql.ConnectionContext.SecurePassword = $SetupCredential.Password $sql.ConnectionContext.ServerInstance = 'server.company.local' $sql.ConnectionContext.Connect() $sql.Status But how shall we go by and distinguish SQL authentication from domain credentials? Just check if the username contains '' or '@'? If $true, use the domain account impersonation, if $false, use SQL authentication. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#360 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AEH8OSekQvhHEdxJCtNCJfwF2pAUqn8_ks5rYLkZgaJpZM4LzzF2> .

[johlju]

Ah, okay. The LoginType parameter is used to tell what type of account the resource is changing. Not how to connect. There is no Credential parameter that can be used to impersonate a login, of any kind, to the SQL Server. And to use your suggested method we would need a CredentialLoginType parameter as well. That could work. That would also mean that we would support PowerShell 4.0. But it is a lot of work to get this work done for all resources. I don't see that happening.

Currently to use xSQLServerLogin (and most resources) you need to use the built-in parameter PsDscRunAsCredential and assign the Windows authentication credentials to use to logon to the SQL Server which have the correct permission to change the login specified in the resource. PsDscRunAsCredential comes with PowerShell 5.x.
https://msdn.microsoft.com/en-us/powershell/dsc/runasuser

[johlju]

Do you think PsDscRunAsCredential will work for you?

[iyoumans]

I'm hitting this issue trying to run xSQLAOGroupEnsure to create an availability group. I'm passing in a PSCredential for a domain account and getting the "bad username or password" error. Passing in PsDscRunAsCredential doesn't help.

[randomnote1]

Could you try with the new xSQLServerAlwaysOnAvailabilityGroup? This will replace AOgroupEnsure. I'm currently working on a new resource to build the replicas.

[iyoumans]

Got this error with xSQLServerAlwaysOnAvailabilityGroup: Creating the availability group 'myavg' failed with the error 'Microsoft.SqlServer.Management.Smo.SmoException: The current instance 'localhost\myinst' must be included as an availability replica to create the availability group 'myavg'.

Edit: just saw your line about "new resource to build the replicas". I assume I need to run that first?

Edit 2: On the bright side, no SQL connectivity issue...

[doug-reimer]

I'm also encountering a requirement for SQL Login support from the Connect-SQL helper function as the Test-TargetResource for the xSQLServerLogin resource fails on a password validation (of the SQL Login).

If as you suggest, we add an optional CredentialLoginType parameter to the Connect-SQL helper function, I think we would be able to do this relatively non-disruptively (wouldn't necessarily need to update other resources).

Something like:

[ValidateSet("Windows","SQL")]
[System.String]
$CredentialLoginType`

if ($SetupCredential)
{
    
    $sql = New-Object Microsoft.SqlServer.Management.Smo.Server
    
    if ($CredentialLoginType -eq "SQL") 
    {
        $sql.ConnectionContext.LoginSecure = $false
        $sql.ConnectionContext.Login = $SetupCredential.GetNetworkCredential().UserName
        $sql.ConnectionContext.SecurePassword = $SetupCredential.Password
    }
    else 
    {
        $sql.ConnectionContext.ConnectAsUser = $true
        $sql.ConnectionContext.ConnectAsUserPassword = $SetupCredential.GetNetworkCredential().Password
        $sql.ConnectionContext.ConnectAsUserName = $SetupCredential.GetNetworkCredential().UserName
    }
    $sql.ConnectionContext.ServerInstance = $connectSQL
    $sql.ConnectionContext.connect()
}

I don't know enough about some of the other resources do know for sure what the impact would be, but I think this would be relatively easy.

[johlju]

Yes that will work for the Connect-Sql helper function, but also setting the $CredentialLoginType to the default value of 'Windows' so that it is not a breaking change.
The resources could use the same parameter name as Connect-Sql, or they could use a Boolean parameter, for example $UseSqlAuthentication with a default value of $false so there is no breaking change there either.
All the resources (like xSQLServerLogin) must also add the $SetupCredential parameter for this to work.

There will then be four different ways to authenticate.

1. SYSTEM account - Default for WMF 4.0 and WMF 5.0.
2. Built-in parameter PsDcsRunAsCredential - Optional for WMF 5.0, if set, the resource is run as this user.
3. Windows authentication using $SetupCredential - If set, should impersonate using this user.
4. SQL authentication using $SetupCredential and $UseSqlAuthentication - If set, should connect using this sql login.

If that is done, then we have WMF 4.0 support as well.

[johlju]

Same issue for xSQLServerSetup, see issue #139

[johlju]

We no longer should try to get WMF 4.0 support. So I think authenticate option 3 is today out of scope. For this PsDscRunAsCredential should be used. What's missing is SQL authentication. I will change this issue title to reflect that this issue is to get SQL Authentication support for all resources.

[johlju]

This issue is also hit when using the Windows Authentication username format DOMAIN\user. If switching to user@domain , e,g, [email protected], then then we don't get the error The user name or password is incorrect..

[johlju]

See more information in issue #1223. Closing this issue, if there is still a problem that need to be addressed. Please open a new issue.