SqlDatabaseUser: PowerShell DSC does not support execution of commands in an interactive mode #1647

ArjenB96 · 2020-12-11T13:35:42Z

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

The DatabaseUser resource fails every time when trying to add a new Windows-authenticated user to the database of a fresh MSSQL Server 2019 install. Manually adding the user through SSMS does work without any hiccups.

Verbose logs showing the problem

... (some prior setup here)
VERBOSE: [TESTENV]:                            [[SqlDatabaseUser]TESTBOXdboperator_AddUser] Retrieving information about the database user 'TESTBOX\dboperator' from the database 'TESTENVDB'. (SDU0001)
VERBOSE: [TESTENV]:                            [[SqlDatabaseUser]TESTBOXdboperator_AddUser] The database user 'TESTBOX\dboperator
' does not exist in the database 'TESTENVDB'. (SDU0005)
VERBOSE: [TESTENV]:                            [[SqlDatabaseUser]TESTBOXdboperator_AddUser] Creating the database user 'TESTBOX\dboperator' in the database 'TESTENVDB' with the user type 'Login'. (SDU0012)
VERBOSE: [TESTENV]: LCM:  [ End    Set      ]  [[SqlDatabaseUser]TESTBOXdboperator_AddUser]  in 0.6410 seconds.
PowerShell DSC resource DSC_SqlDatabaseUser  failed to execute Set-TargetResource functionality with error message: 
System.InvalidOperationException: Failed creating the database user 'TESTBOX\dboperator' in the database 'TESTENVDB' with the 
user type 'Login'. (SDU0013) ---> System.Management.Automation.RemoteException: PowerShell Desired State Configuration does not 
support execution of commands in an interactive mode. Please ensure that the underlying command is not prompting for user input, 
such as missing mandatory parameter, confirmation prompt etc.
   at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
   at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
   --- End of inner exception stack trace --- 
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : localhost
 
VERBOSE: [TESTENV]: LCM:  [ End    Set      ]
The SendConfigurationApply function did not succeed.
    + CategoryInfo          : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : MI RESULT 1
    + PSComputerName        : localhost
 
VERBOSE: Operation 'Invoke CimMethod' complete.
VERBOSE: Time taken for configuration job to complete is 1.757 seconds

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

Configuration ExampleScript
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential
    )

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {

        SqlDatabaseUser 'TESTBOXdboperator_AddUser'
        {
            ServerName           =  $env:COMPUTERNAME
            InstanceName         = 'MSSQLSERVER'
            DatabaseName         = 'TESTENVDB'
            Name                 = 'TESTBOX\dboperator'
            UserType             = 'Login'
            LoginName            = 'TESTBOX\dboperator'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}

ExampleScript
# insert configuration here

I run this script with configuration data that allows plain text passwords saved in a var with Get-Credential:

$cd = @{
    AllNodes = @(
        @{
            NodeName="localhost"
            PSDscAllowPlainTextPassword=$true
        }
    )
}

SQL Server edition and version the target node is running

SQL Server 2019 / 15.0.2000.5

SQL Server PowerShell modules present on the target node

SQLPS 15.0 is the output.

The operating system the target node is running

OsName               : Microsoft Windows Server 2016 Datacenter
OsOperatingSystemSKU : DatacenterServerEdition
OsArchitecture       : 64-bit
WindowsBuildLabEx    : 14393.4048.amd64fre.rs1_release_inmarket.201115-1326
OsLanguage           : en-US
OsMuiLanguages       : {en-US}

Version and build of PowerShell the target node is running

PSVersion 5.1.14393.3866
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.14393.3866
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

Version of the DSC module that was used

15.0.0

The text was updated successfully, but these errors were encountered:

johlju · 2020-12-11T15:38:04Z

It must be this that throws an error

SqlServerDsc/source/DSCResources/DSC_SqlDatabaseUser/DSC_SqlDatabaseUser.psm1

Lines 365 to 367 in fdc7608

    
           Invoke-Query @invokeQueryParameters -Query ( 
        
               'CREATE USER [{0}] FOR LOGIN [{1}];' -f $Name, $LoginName 
        
           )

Maybe there is a bug in the helper function Invoke-Query.

johlju · 2020-12-11T15:41:34Z

The error message PowerShell Desired State Configuration does not support execution of commands in an interactive mode could be because a PowerShell cmdlet is asking for a (mandatory) parameter value. In that case it is a bug in the resource.

Fiander · 2020-12-11T18:26:45Z

@ArjenB96 you say you use a fresh sql Install.
does the user get created somewhere? i dont see that in the configuration above

This resource does not create a server login, only a database user.

ArjenB96 · 2020-12-12T10:32:03Z

Hi @Fiander,

My apologies for not being more detailed about that. I actually run my configuration through Ansible with the Win_dsc module, so it is a bit more complex to simply include the additional config.
From the top of my head, the following tasks are performed:

Spin up two fresh windows VM's
Enable ADDS/ADCS feature on VM 1
Set up VM 1 as DC
Join VM2 computer to domain
Install SQLServer 2019 dependencies on VM2
Create domain users in AD for SQL Server (Service account, Service Agent Account) on VM1
Install SQLServer 2019 with SqlServerDSC on VM2
Create additional users in AD for new database (DB Owner, DB Operator) on VM1
Add both users to server login with SQLLogin on VM2
Create new database with DB Owner account set as the owner on VM2
Attempt to add DB Operator to the database -> Results in error seen above.

All steps until step 11 are successful and indicate a change when run for the first time.
Manual inspection through SSMS confirms these steps. In SSMS, I can manually perform step 11 without any pop-up confirmation or error codes.
Digging through the logs of SQL Server, Windows / DSC does not provide me with more information than is seen above.

johlju · 2020-12-12T13:00:50Z

We should bre able to reproduce this with integration tests. Creating the same local user, sql login, and sql database user.

@ArjenB96 could you try this manually to verify that `Invoke-Query' is the problem? On the target node that gives the error run this (I haven't tested it myself):

    # Make sure to run this as the same user as the config uses.

    # Change to full path to the module SqlServerDsc.Common inside module SqlServerDsc.
    Import-Module -Name .\SqlServerDsc\Modules\SqlServerDsc.Common

    $Name = 'TESTBOX\dboperator'
    $LoginName = 'TESTBOX\dboperator'

    $invokeQueryParameters = @{
        ServerName   = $env:COMPUTERNAME
        InstanceName = 'MSSQLSERVER'
        Database     = 'TESTENVDB'
    }

    Invoke-Query @invokeQueryParameters -Verbose -Query ( 
        'CREATE USER [{0}] FOR LOGIN [{1}];' -f $Name, $LoginName 
    )

ArjenB96 · 2020-12-14T10:16:23Z

Invoke-Query : The term 'Invoke-Query' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

Hmm, I didn't know it was an external command. What module is supposed to install this command?

I tried looking for the command and came across the InvokeQuery Ps Module, but installing the newest version (1.0.1) does not provide me with a general Invoke-Query commands, only specific commands such as Invoke-SqlServerQuery.

Fiander · 2020-12-14T10:24:29Z

then i think you missed the
Import-Module -Name .\SqlServerDsc\Modules\SqlServerDsc.Common

It should be in that module .

ArjenB96 · 2020-12-14T10:33:00Z

Ah my apologies. I changed the folder for that command, but never actually executed it when running it line-by-line.

Unable to find type [Microsoft.SqlServer.Management.Smo.Server].
At C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\15.0.0\Modules\SqlServerDsc.Common\SqlS
erverDsc.Common.psm1:1398 char:9
+         [Microsoft.SqlServer.Management.Smo.Server]
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (Microsoft.SqlSe...ment.Smo.Server:TypeName) [],
   RuntimeException
    + FullyQualifiedErrorId : TypeNotFound

This is the error that is given after running the Invoke-Query command on a Powershell instance with the correct user.

Might be possible that the barebones installation (only SQLENGINE as feature) of SQL Server 2019 does not bring SMO functionality?

Fiander · 2020-12-14T11:00:00Z

oke, SMO is not loaded.
Are you testing on a machine with SMO installed?

johlju · 2020-12-14T11:19:03Z

@ArjenB96 you should run this on the target node once it fails so that all prerequisites are installed.

ArjenB96 · 2020-12-14T11:28:40Z

@johlju I am running this directly on the target node, with proper RunAs credentials to match the PsDscCredentials vars in Dsc config.

@Fiander Apparently, since SQL Server 2017 SMO is not installable as a feature through the standard setup. It is now distributed as a NuGet package. The problem with NuGet in this case is that it only works project-wide, so I am not sure how I would apply that to a Dsc config. I guess I can test it for now for the simple PS script to see if it works with SMO installed.

ArjenB96 · 2020-12-14T13:42:35Z

Installed SMO with nuget.exe into Documents\testproject\packages, but still get the same error code as above after running the script.

PS C:\Users\testUser\Documents\testproject> (Get-Package -Name *Objects*).name
SQL Server 2019 Shared Management Objects Extensions
SQL Server 2019 Shared Management Objects

johlju · 2020-12-14T15:37:30Z

SMO is either part of the PowerShell module SQLPS as part of SQL Server setup or the PowerShell module SqlServer. For SQL Server 2019 I think SQLPS is no longer part of setup, in that case the SqlServer module need to be downloaded from PowerShell Gallery and added to the node to get the correct SMO version as it is distributed inside that module.

There should be a verbose message saying it loads the correct module (SqlServer). 🤔

ArjenB96 · 2020-12-14T16:43:56Z

Ah, that might be the issue. I have made numerous changes, but when I added the SQLServer PS Module, it worked! I'll narrow it down to make sure.

ArjenB96 · 2020-12-16T10:57:47Z

Reran the configuration today with the SqlServer module. Still the same SMO TypeNotFound error. Using Get-Package I do see that SMO is installed. Any reason why it is not using it? Do I need to manually load SMO in order to use it?

EDIT: After manually loading the assemblies, it worked.
According to Microsoft docs about loading SMO, the reason why this needs to happen might be because:

"If your script references a SMO object before the first command that references the provider or cmdlets from the SQL Server snap-ins."

For some reason SqlServer/SMO is not automatically loaded / dynamically imported.
Loading SMO with the code sample in the docs or loading SqlServer manually before calling the Invoke-Query script above with Import-Module -Name SqlServer makes it work.

ArjenB96 · 2020-12-21T09:26:19Z

Hi there, just wanted to ask if this is an issue that should be handled on my side or on the resource's side?

Due to using Ansible, there's an additional layer of abstraction when incorporating DSC in it. For now, I made a workaround by making Ansible execute a PowerShell script that manually loads the assemblies and executes this DSC resource, but I would rather just install the prereqs, define my DSC config in Ansible and run it like that.

gaelcolas · 2020-12-21T09:34:08Z

@johlju is it possible/desirable to bake in the SqlPS module within the DSC Resource the same way as DscResource.Common?
Just a thought to explore.

johlju · 2020-12-21T10:16:38Z

@ArjenB96 the Invoke-Query should import the SqlServer (or SQLPS) module automatically, and in turn load the SMO libraries into the PowerShell session. I would like to see the verbose output to see what happens if that is not the case.

The flow to import SMO libraries.

Invoke-Query calls Connect-SQL

SqlServerDsc/source/Modules/SqlServerDsc.Common/SqlServerDsc.Common.psm1

Line 1437 in f1b15bf

$serverObject = Connect-SQL @connectSQLParameters
Connect-Sql calls Import-SQLPSModule

SqlServerDsc/source/Modules/SqlServerDsc.Common/SqlServerDsc.Common.psm1

Line 499 in f1b15bf

Import-SQLPSModule

Contrary to the name Import-SQLPSModule first tries to import the module SqlServer, if that fails it will import the module SQLPS. When the module is imported, that module is responsible for loading its SMO assemblies (which for module SqlServer are unique and distributed with the module, while for SQLPS they are loaded from GAC).

SqlServerDsc/source/Modules/SqlServerDsc.Common/SqlServerDsc.Common.psm1

Lines 828 to 831 in f1b15bf

    
           # Get the newest SqlServer module if more than one exist 
        
           $availableModule = Get-Module -FullyQualifiedName 'SqlServer' -ListAvailable | 
        
               Sort-Object -Property 'Version' -Descending | 
        
               Select-Object -First 1 -Property Name, Path, Version

SqlServerDsc/source/Modules/SqlServerDsc.Common/SqlServerDsc.Common.psm1

Line 882 in f1b15bf

    
           $importedModule = Import-Module -Name $availableModuleName -DisableNameChecking -Verbose:$false -Force:$Force -PassThru -ErrorAction Stop

@gaelcolas I would be possible to add module SqlServer (if we are allowed license wise since it bundles proprietary code), but it is not desirable. DSC module SqlServerDsc is not dependent on a specific version of module SqlServer and a user should be able to upgrade to a newer version or pin a certain version of module SqlServer. For example if module SqlServer has breaking changes for a certain major version of Microsoft SQL Server or a new version of Microsoft SQL Server is released that a newer version of module SqlServer supports (we do not have to change the SqlServerDsc in those cases). Also, there might be circumstances where a user are not allowed to use the module from PowerShell Gallery and need to use SQLPS that is part of certain versions of Microsoft SQL Server.

A user can use DSC resource PSModule in the module PowerShellGet to download the correct version of module SqlServer either from PowerShell Gallery or an internal repository.

https://github.com/PowerShell/PowerShellGet/tree/development/DSC/DscResources/MSFT_PSModule

johlju · 2020-12-21T10:39:42Z

@ArjenB96 The resource SqlDatabaseUser calls Connect-SQL in Get-TargetResource (which is called by Test, and Set-TargetResource). which then have the same flow as in previous comment.

SqlServerDsc/source/DSCResources/DSC_SqlDatabaseUser/DSC_SqlDatabaseUser.psm1

Line 50 in f1b15bf

    
           $sqlServerObject = Connect-SQL -ServerName $ServerName -InstanceName $InstanceName

Love to see the verbose output from the resource to see what is going on. To figure out why you need to load the assemblies manually.

ArjenB96 · 2020-12-21T11:13:23Z

Here is the verbose output from the Invoke-Query script you initially posted with $VerbosePreference="Continue" in an elevated RunAs Powershell instance.
Removing the Verbose parameter from the Invoke-Query method provides the same output.

VERBOSE: Loading module from path 'C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\15.0.0\Modules\SqlServerDsc.Common\SqlServe
rDsc.Common.psd1'.
VERBOSE: Importing function 'Compare-ResourcePropertyState'.
VERBOSE: Importing function 'Connect-SQL'.
VERBOSE: Importing function 'Connect-SQLAnalysis'.
VERBOSE: Importing function 'Connect-UncPath'.
VERBOSE: Importing function 'ConvertTo-ServerInstanceName'.
VERBOSE: Importing function 'Copy-ItemWithRobocopy'.
VERBOSE: Importing function 'Disconnect-UncPath'.
VERBOSE: Importing function 'Find-ExceptionByNumber'.
VERBOSE: Importing function 'Format-Path'.
VERBOSE: Importing function 'Get-FilePathMajorVersion'.
VERBOSE: Importing function 'Get-PrimaryReplicaServerObject'.
VERBOSE: Importing function 'Get-ProtocolNameProperties'.
VERBOSE: Importing function 'Get-RegistryPropertyValue'.
VERBOSE: Importing function 'Get-ServerProtocolObject'.
VERBOSE: Importing function 'Get-ServiceAccount'.
VERBOSE: Importing function 'Get-SqlInstanceMajorVersion'.
VERBOSE: Importing function 'Import-Assembly'.
VERBOSE: Importing function 'Import-SQLPSModule'.
VERBOSE: Importing function 'Invoke-InstallationMediaCopy'.
VERBOSE: Importing function 'Invoke-Query'.
VERBOSE: Importing function 'Invoke-SqlScript'.
VERBOSE: Importing function 'Restart-ReportingServicesService'.
VERBOSE: Importing function 'Restart-SqlService'.
VERBOSE: Importing function 'Set-PSModulePath'.
VERBOSE: Importing function 'Split-FullSqlInstanceName'.
VERBOSE: Importing function 'Start-SqlSetupProcess'.
VERBOSE: Importing function 'Test-ActiveNode'.
VERBOSE: Importing function 'Test-AvailabilityReplicaSeedingModeAutomatic'.
VERBOSE: Importing function 'Test-ClusterPermissions'.
VERBOSE: Importing function 'Test-ImpersonatePermissions'.
VERBOSE: Importing function 'Test-LoginEffectivePermissions'.
VERBOSE: Importing function 'Test-PendingRestart'.
VERBOSE: Importing function 'Update-AvailabilityGroupReplica'.
Unable to find type [Microsoft.SqlServer.Management.Smo.Server].
At C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\15.0.0\Modules\SqlServerDsc.Common\SqlServerDsc.Common.psm1:1398 char:9
+         [Microsoft.SqlServer.Management.Smo.Server]
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (Microsoft.SqlSe...ment.Smo.Server:TypeName) [], RuntimeException
    + FullyQualifiedErrorId : TypeNotFound

Running it through the DSC resource suddenly works. I am very confused. It now correctly does import SQLServer if I look at the verbose logs, even in a new powershell instance where i did not manually load the binaries.

Somehow, running it through Ansible does give me the original error output as shown in my OP, even though the passed values are exactly the same.

ArjenB96 · 2020-12-21T11:49:16Z

Here is the DSC-related output from Ansible. It seems that it does not execute the query? verbose_set is of interest here.

"verbose_set": [
        "Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = ResourceSet,'className' = MSFT_DSCLocalConfigurationManager,'namespaceName' = root/Microsoft/Windows/DesiredStateConfiguration'.",
        "An LCM method call arrived from computer TESTENV with user sid S-1-5-21-602572105-637538369-4031179522-500.",
        "[TESTENV]: LCM:  [ Start  Set      ]  [[SqlDatabaseUser]DirectResourceAccess]",
        "[TESTENV]:                            [[SqlDatabaseUser]DirectResourceAccess] Setting the database user 'TESTBOX\\dboperator' in the database 'TESTENVDB' to the desired state. (SDU0019)",
        "[TESTENV]:                            [[SqlDatabaseUser]DirectResourceAccess] Found PowerShell module SqlServer already imported in the session. (SQLCOMMON0026)",
        "[TESTENV]:                            [[SqlDatabaseUser]DirectResourceAccess] Connecting as current user 'TESTBOX\\pkisandbox' using integrated security. (SQLCOMMON0054)",
        "[TESTENV]:                            [[SqlDatabaseUser]DirectResourceAccess] Connected to SQL instance 'TESTENV'. (SQLCOMMON0018)",
        "[TESTENV]:                            [[SqlDatabaseUser]DirectResourceAccess] Retrieving information about the database user 'TESTBOX\\dboperator' from the database 'TESTENVDB'. (SDU0001)",
        "[TESTENV]:                            [[SqlDatabaseUser]DirectResourceAccess] The database user 'TESTBOX\\dboperator' does not exist in the database 'TESTENVDB'. (SDU0005)",
        "[TESTENV]:                            [[SqlDatabaseUser]DirectResourceAccess] Creating the database user 'TESTBOX\\dboperator' in the database 'TESTENVDB' with the user type 'Login'. (SDU0012)",
        "[TESTENV]: LCM:  [ End    Set      ]  [[SqlDatabaseUser]DirectResourceAccess]  in 0.6890 seconds.",
        "Operation 'Invoke CimMethod' complete.",
        "Time taken for configuration job to complete is 0.825 seconds"
    ],
    "verbose_test": [
        "Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = ResourceTest,'className' = MSFT_DSCLocalConfigurationManager,'namespaceName' = root/Microsoft/Windows/DesiredStateConfiguration'.",
        "An LCM method call arrived from computer TESTENV with user sid S-1-5-21-602572105-637538369-4031179522-500.",
        "[TESTENV]: LCM:  [ Start  Test     ]  [[SqlDatabaseUser]DirectResourceAccess]",
        "[TESTENV]:                            [[SqlDatabaseUser]DirectResourceAccess] Determining if the database user 'TESTBOX\\dboperator' in the database 'TESTENVDB' is in the desired state. (SDU0003)",
        "[TESTENV]:                            [[SqlDatabaseUser]DirectResourceAccess] Found PowerShell module SqlServer already imported in the session. (SQLCOMMON0026)",
        "[TESTENV]:                            [[SqlDatabaseUser]DirectResourceAccess] Connecting as current user 'TESTBOX\\testbox' using integrated security. (SQLCOMMON0054)",
        "[TESTENV]:                            [[SqlDatabaseUser]DirectResourceAccess] Connected to SQL instance 'TESTENV'. (SQLCOMMON0018)",
        "[TESTENV]:                            [[SqlDatabaseUser]DirectResourceAccess] Retrieving information about the database user 'TESTBOX\\dboperator' from the database 'TESTENVDB'. (SDU0001)",
        "[TESTENV]:                            [[SqlDatabaseUser]DirectResourceAccess] The database user 'TESTBOX\\dboperator' does not exist in the database 'TESTENVDB'. (SDU0005)",
        "[TESTENV]:                            [[SqlDatabaseUser]DirectResourceAccess] The database user is not in desired state. (SDU0007)",
        "[TESTENV]: LCM:  [ End    Test     ]  [[SqlDatabaseUser]DirectResourceAccess] False in 0.2190 seconds.",
        "[TESTENV]: LCM:  [ End    Set      ]    in  0.2500 seconds.",
        "Operation 'Invoke CimMethod' complete.",
        "Time taken for configuration job to complete is 0.383 seconds"
    ]
}

If I execute a DSC file directly on the Windows host I get the following additional output:

VERBOSE: [TESTENV]:                            [[SqlDatabaseUser]TESTBOXdboperator_AddUser] Creating the database user 'TESTBOX\dboperator' in the database 'TESTENVDB' with the user type 'Login'. (SDU0012)
VERBOSE: [TESTENV]:                            [[SqlDatabaseUser]TESTBOXdboperator_AddUser] Found PowerShell module SqlServer already imported in the session. (SQLCOMMON0026)
VERBOSE: [TESTENV]:                            [[SqlDatabaseUser]TESTBOXdboperator_AddUser] Connecting as current user 'TESTBOX\testbox' using integrated security. (SQLCOMMON0054)
VERBOSE: [TESTENV]:                            [[SqlDatabaseUser]TESTBOXdboperator_AddUser] Connected to SQL instance 'TESTENV'. (SQLCOMMON0018)
VERBOSE: [TESTENV]:                            [[SqlDatabaseUser]TESTBOXdboperator_AddUser] Found PowerShell module SqlServer already imported in the session. (SQLCOMMON0026)
VERBOSE: [TESTENV]:                            [[SqlDatabaseUser]TESTBOXdboperator_AddUser] Connecting as current user 'TESTBOX\testbox' using integrated security. (SQLCOMMON0054)
VERBOSE: [TESTENV]:                            [[SqlDatabaseUser]TESTBOXdboperator_AddUser] Connected to SQL instance 'TESTENV'. (SQLCOMMON0018)
VERBOSE: [TESTENV]:                            [[SqlDatabaseUser]TESTBOXdboperator_AddUser] Executing the query `CREATE USER [TESTBOX\dboperator] FOR LOGIN [TESTBOX\dboperator];`. (SQLCOMMON0058)
VERBOSE: [TESTENV]: LCM:  [ End    Set      ]  [[SqlDatabaseUser]TESTBOXdboperator_AddUser]  in 1.2190 seconds.
VERBOSE: [TESTENV]: LCM:  [ End    Resource ]  [[SqlDatabaseUser]TESTBOXdboperator_AddUser]
VERBOSE: [TESTENV]: LCM:  [ End    Set      ]
VERBOSE: [TESTENV]: LCM:  [ End    Set      ]    in  5.1100 seconds.
VERBOSE: Operation 'Invoke CimMethod' complete.
VERBOSE: Time taken for configuration job to complete is 5.29 seconds seconds.
VERBOSE: Operation 'Invoke CimMethod' complete.
VERBOSE: Time taken for configuration job to complete is 5.29 seconds

Seems like for some reason it crashes when trying to find the SqlServer for the second time?

Fiander · 2020-12-21T12:50:06Z

I'm looking at the code, and i think i have found it.
Invoke-Query @invokeQueryParameters -Query ( 'CREATE USER [{0}] FOR LOGIN [{1}];' -f $Name, $LoginName )

i am missing in what database the login should be created.
Invoke-Query @invokeQueryParameters -Query ( 'USE [{0}] CREATE USER [{1}] FOR LOGIN [{2}];' -f $DatabaseName, $Name, $LoginName )

might work better?

SMO is being used to read if a Login exists in a database.
But then a query is used to create the Login. That query does not know of the database where the Login should be created

@johlju do you agree?

johlju · 2020-12-21T13:24:42Z

@ArjenB96 This row says that the module SqlServer have already be imported into the session, so then the SMO assemblies should also have been loaded.

VERBOSE: [TESTENV]:                            [[SqlDatabaseUser]TESTBOXdboperator_AddUser] Found PowerShell module SqlServer already imported in the session. (SQLCOMMON0026)

@Fiander the database is set in the splatted parameters and then the query is executed on the database

SqlServerDsc/source/DSCResources/DSC_SqlDatabaseUser/DSC_SqlDatabaseUser.psm1

Lines 219 to 224 in f1b15bf

    
           # Default parameters for the cmdlet Invoke-Query used throughout. 
        
           $invokeQueryParameters = @{ 
        
               ServerName   = $ServerName 
        
               InstanceName = $InstanceName 
        
               Database     = $DatabaseName 
        
           }

SqlServerDsc/source/Modules/SqlServerDsc.Common/SqlServerDsc.Common.psm1

Line 1477 in f1b15bf

$serverObject.Databases[$Database].ExecuteNonQuery($Query)

johlju · 2020-12-21T13:28:18Z

@ArjenB96 This is the helper module inside SqlServerDsc that fails (SqlServerDsc.Common) becuase it reference a type that module SqlServer loads. For some reason the module SqlServer is not available in the session at that time. But is a non issue now that DSC actually works. 🤔

Unable to find type [Microsoft.SqlServer.Management.Smo.Server].
At C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\15.0.0\Modules\SqlServerDsc.Common\SqlServerDsc.Common.psm1:1398 char:9
+         [Microsoft.SqlServer.Management.Smo.Server]
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (Microsoft.SqlSe...ment.Smo.Server:TypeName) [], RuntimeException
    + FullyQualifiedErrorId : TypeNotFound

johlju · 2020-12-21T13:41:56Z

@ArjenB96

It seems that it does not execute the query?

This is strange, it says it does it

Creating the database user 'TESTBOX\\dboperator' in the database 'TESTENVDB' with the user type 'Login'. (SDU0012)",

But then the test does not seem to find it when testing.

"[TESTENV]:                            [[SqlDatabaseUser]DirectResourceAccess] The database user 'TESTBOX\\dboperator' does not exist in the database 'TESTENVDB'. (SDU0005)",

Maybe it's an issue that the database scope as per @Fiander suggest is not set? If so where does it create the user, in the master database (default database for the user)?

johlju · 2020-12-21T13:47:11Z

@ArjenB96 Looking at the integration tests for this there is a line missing in your verbose output.

VERBOSE: [WIN-NKTGMBI3Q61]:                            [[SqlDatabaseUser]Integration_Test] Executing the query `CREATE USER [User1] FOR LOGIN [WIN-NKTGMBI3Q61\DscUser1];`. (SQLCOMMON0058)

https://dev.azure.com/dsccommunity/SqlServerDsc/_build/results?buildId=3337&view=logs&j=625a4531-fc5f-5ee3-3db6-040613245e3f&t=169db761-3577-5616-1f7f-492fca3ce8e6&l=7450

johlju · 2020-12-21T13:55:49Z

I'm scratching my head here. The line in your verbose output from Ansible:

Creating the database user 'TESTBOX\\dboperator' in the database 'TESTENVDB' with the user type 'Login'. (SDU0012)"

That is created by the Write-Verbose below. After that the switch-statement should go into 'Login' since that is what you have in your configuration and call Invoke-Query. If anything goes wrong it should throw an error, but it doesn't.

SqlServerDsc/source/DSCResources/DSC_SqlDatabaseUser/DSC_SqlDatabaseUser.psm1

Lines 351 to 402 in f1b15bf

    
           # Create the database user. 
        
           try 
        
           { 
        
               Write-Verbose -Message ( 
        
                   $script:localizedData.CreateDatabaseUser -f $Name, $DatabaseName, $UserType 
        
               ) 
        
               switch ($UserType) 
        
               { 
        
                   'Login' 
        
                   { 
        
                       # Assert that the login exist. 
        
                       Assert-SqlLogin @PSBoundParameters 
        
                       Invoke-Query @invokeQueryParameters -Query ( 
        
                           'CREATE USER [{0}] FOR LOGIN [{1}];' -f $Name, $LoginName 
        
                       ) 
        
                   } 
        
                   'NoLogin' 
        
                   { 
        
                       Invoke-Query @invokeQueryParameters -Query ( 
        
                           'CREATE USER [{0}] WITHOUT LOGIN;' -f $Name 
        
                       ) 
        
                   } 
        
                   'AsymmetricKey' 
        
                   { 
        
                       # Assert that the asymmetric key exist. 
        
                       Assert-DatabaseAsymmetricKey @PSBoundParameters 
        
                       Invoke-Query @invokeQueryParameters -Query ( 
        
                           'CREATE USER [{0}] FOR ASYMMETRIC KEY [{1}];' -f $Name, $AsymmetricKeyName 
        
                       ) 
        
                   } 
        
                   'Certificate' 
        
                   { 
        
                       # Assert that the certificate exist. 
        
                       Assert-DatabaseCertificate @PSBoundParameters 
        
                       Invoke-Query @invokeQueryParameters -Query ( 
        
                           'CREATE USER [{0}] FOR CERTIFICATE [{1}];' -f $Name, $CertificateName 
        
                       ) 
        
                   } 
        
               } 
        
           } 
        
           catch 
        
           { 
        
               $errorMessage = $script:localizedData.FailedCreateDatabaseUser -f $Name, $DatabaseName, $UserType 
        
               New-InvalidOperationException -Message $errorMessage -ErrorRecord $_ 
        
           }

johlju · 2020-12-21T13:56:41Z

@ArjenB96 can you run SQL Profiler on the instance to see if that SQL statement is ever run and if so in what database?

Fiander · 2020-12-21T14:42:21Z

@ArjenB96 could you also please share the script you use to create the database?

ArjenB96 · 2020-12-21T14:57:05Z

@Fiander I create the SQL server and database through Ansible as well, using the win_dsc module with the SqlSetup and SqlDatabase resources. Everything within double curly braces are variables, which I have defined somewhere else.

- name: Install SQL Server
  win_dsc:
    resource_name: SQLSetup
    Action: Install
    InstanceName: "{{ mssql_instance_name }}"
    Features: "{{ mssql_features }}"
    UpdateEnabled: True
    SourcePath: "{{ mssql_install_path }}\\Media"
    InstallSharedDir: C:\Program Files\Microsoft SQL Server
    InstallSharedwowDir: C:\Program Files (x86)\Microsoft SQL Server
    InstanceDir: C:\Program Files\Microsoft SQL Server
    TcpEnabled: yes
    # SQL Admin Accounts
    SQLSysAdminAccounts: "{{ mssql_sqladmin_accounts }}"
    # SQL Service Account
    SQLSvcAccount_username: "{{ mssql_sqlsvc_account }}"
    SQLSvcAccount_password: "{{ mssql_sqlsvc_account_pass }}"
    # SQL Agent Service Account
    AgtSvcAccount_username: "{{ mssql_agentsvc_account }}"
    AgtSvcAccount_password: "{{ mssql_agentsvc_account_pass }}"
  register: sql_install

- name: Create DB {{ mssql_db_name }} under instance {{ mssql_instance_name }} with as owner {{ mssql_venafidb_owner_account }}
  win_dsc:
    resource_name: SqlDatabase
    Name: "{{ mssql_db_name }}"
    InstanceName: "{{ mssql_instance_name }}"
    OwnerName: "TESTBOX\\{{ mssql_venafidb_owner_account }}"
    PsDscRunAsCredential_username: "{{ runas_cred_user }}"
    PsDscRunAsCredential_password: "{{ runas_cred_pass }}"

Fiander · 2020-12-21T15:02:50Z

@ArjenB96 Just wanted to make sure, the database is not in "Containment=partial". can you please confirm?

you said "when i add the login through ssms i works." could you please script that action? When i do that, i get a

use [databasey] go Create user [test] for login [domain\user]

ArjenB96 · 2020-12-21T15:06:25Z

@ArjenB96 Just wanted to make sure, the database is not in "Containment=partial". can you please confirm?

I've checked the config for the database, since I saw the requirement on the wiki for this. The database is not in partial containment mode.

Even though we do not explicitly say which database to use, the SQL Profiler shows that it autoselects the correct database before executing the batch query. I have tested it so far only with the Invoke-Query script and loaded assemblies. I will try to test it now through Ansible.

Fiander · 2020-12-21T15:09:43Z

the reason i ask for a scripted version of the ssms code, is because you run SQL2019. at this moment i only have a few SQL2016 and 2017 servers running

ArjenB96 · 2020-12-21T15:25:54Z

I'm not sure what exactly you mean by scripting the manual action I do through SSMS.
If I perform the action through SSMS and check the profiler, then the executed command is CREATE USER [test] FOR LOGIN [domain\user].

When running through Ansible, it does some miscellaneous stuff, retrieves the proper database, selects the SQL user according to the passed name, but then does not do anything else. That is the last call that is logged.

johlju · 2020-12-21T15:50:07Z

@ArjenB96 I think what @Fiander is asking is for the configuration script for the SqlLogin. There should be the following resources used.

SqlSqtup (in you Ansible config above)
SqlLogin
SqlDatabase (in you Ansible config above)
SqlDatabaseUser

ArjenB96 · 2020-12-21T16:25:16Z

Ahh, right, my bad. I add two users to the SQL instance, a db_owner and a db_operator.

- name: Add {{ mssql_venafidb_owner_account }} to SQL login
  win_dsc:
    resource_name: SqlLogin
    InstanceName: "{{ mssql_instance_name }}"
    LoginType: WindowsUser
    Name: "TESTBOX\\{{ mssql_venafidb_owner_account }}"
    PsDscRunAsCredential_username: "{{ runas_cred_user }}"
    PsDscRunAsCredential_password: "{{ runas_cred_pass }}"

- name: Add {{ mssql_venafidb_operator_account }} to SQL login
  win_dsc:
    resource_name: SqlLogin
    InstanceName: "{{ mssql_instance_name }}"
    LoginType: WindowsUser
    Name: "TESTBOX\\{{ mssql_venafidb_operator_account }}"
    PsDscRunAsCredential_username: "{{ runas_cred_user }}"
    PsDscRunAsCredential_password: "{{ runas_cred_pass }}"

Fiander · 2020-12-21T16:25:34Z

no, ten days ago you mentioned: "Manually adding the user through SSMS does work without any hiccups."
when you do that action, just before hitting the "OK" button. please press the "script" button. that can be found above the user type.

script to new query window. i want to make sure that for SQL2019 nothing changed

Fiander · 2020-12-21T16:27:34Z

Ahh, right, my bad. I add two users to the SQL instance, a db_owner and a db_operator.

- name: Add {{ mssql_venafidb_owner_account }} to SQL login
  win_dsc:
    resource_name: SqlLogin
    InstanceName: "{{ mssql_instance_name }}"
    LoginType: WindowsUser
    Name: "TESTBOX\\{{ mssql_venafidb_owner_account }}"
    PsDscRunAsCredential_username: "{{ runas_cred_user }}"
    PsDscRunAsCredential_password: "{{ runas_cred_pass }}"

- name: Add {{ mssql_venafidb_operator_account }} to SQL login
  win_dsc:
    resource_name: SqlLogin
    InstanceName: "{{ mssql_instance_name }}"
    LoginType: WindowsUser
    Name: "TESTBOX\\{{ mssql_venafidb_operator_account }}"
    PsDscRunAsCredential_username: "{{ runas_cred_user }}"
    PsDscRunAsCredential_password: "{{ runas_cred_pass }}"

is "TESTBOX" your domain, or a windows machine name?

ArjenB96 · 2020-12-21T16:32:41Z

no, ten days ago you mentioned: "Manually adding the user through SSMS does work without any hiccups."
when you do that action, just before hitting the "OK" button. please press the "script" button. that can be found above the user type.

script to new query window. i want to make sure that for SQL2019 nothing changed

Ahh ok. I just shutdown the environment so I'll rebuild it on Wednesday and share the result of the script.

is "TESTBOX" your domain, or a windows machine name?

Domain. It's actually something different but I used a dummy name for anonymization.
To be fair I agree it might look confusing.

Sudman1 · 2020-12-29T19:05:51Z

I have experienced the same problem. Downgrading to version 13.5.0 of SqlServerDsc has resolved the issue for me.

… commands in an interactive mode dsccommunity#1647 $servername is not passed thru @PSBoundParameters because the default is used and it is not explicitly set by the caller

bschapendonk · 2021-01-29T15:18:18Z

Workaround: Explicitly set ServerName on the SqlDatabaseUser resource.

… commands in an interactive mode dsccommunity#1647 $servername is not passed thru @PSBoundParameters because the default is used and it is not explicitly set by the caller

- SqlDatabaseUser - Added parameter `ServerName` to the call of `Assert-SqlLogin`. `@PSBoundParameters` doesn't capture the default value of `ServerName` when it is not explicitly set by the caller (issue #1647).

johlju · 2021-02-01T07:16:49Z

Pushing a new release during the day that will have a fix to this thanks to @bschapendonk. 🙂

bschapendonk · 2021-02-01T09:02:14Z

Glad to be of help, also I want to thank @daanroeterink, for the extra set of eyes in tracking this down.

johlju added bug The issue is a bug. help wanted The issue is up for grabs for anyone in the community. labels Dec 11, 2020

ArjenB96 changed the title ~~DatabaseUser: PowerShell DSC does not support execution of commands in an interactive mode~~ SqlDatabaseUser: PowerShell DSC does not support execution of commands in an interactive mode Dec 16, 2020

bschapendonk mentioned this issue Jan 29, 2021

SqlDatabaseUser: Pass parameter ServerName to Assert-SqlLogin #1681

Merged

johlju added a commit to bschapendonk/SqlServerDsc that referenced this issue Jan 30, 2021

Merge branch 'main' into fix-issue-dsccommunity#1647

aee57b3

johlju closed this as completed in #1681 Feb 1, 2021

johlju removed the help wanted The issue is up for grabs for anyone in the community. label Feb 1, 2021

SqlDatabaseUser: PowerShell DSC does not support execution of commands in an interactive mode #1647

SqlDatabaseUser: PowerShell DSC does not support execution of commands in an interactive mode #1647

Comments

ArjenB96 commented Dec 11, 2020 • edited Loading

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

Verbose logs showing the problem

Suggested solution to the issue

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

SQL Server edition and version the target node is running

SQL Server PowerShell modules present on the target node

The operating system the target node is running

Version and build of PowerShell the target node is running

Version of the DSC module that was used

johlju commented Dec 11, 2020

johlju commented Dec 11, 2020

Fiander commented Dec 11, 2020

ArjenB96 commented Dec 12, 2020 • edited Loading

johlju commented Dec 12, 2020 • edited Loading

ArjenB96 commented Dec 14, 2020

Fiander commented Dec 14, 2020

ArjenB96 commented Dec 14, 2020 • edited Loading

Fiander commented Dec 14, 2020

johlju commented Dec 14, 2020

ArjenB96 commented Dec 14, 2020

ArjenB96 commented Dec 14, 2020 • edited Loading

johlju commented Dec 14, 2020

ArjenB96 commented Dec 14, 2020

ArjenB96 commented Dec 16, 2020 • edited Loading

ArjenB96 commented Dec 21, 2020

gaelcolas commented Dec 21, 2020

johlju commented Dec 21, 2020 • edited Loading

johlju commented Dec 21, 2020

ArjenB96 commented Dec 21, 2020

ArjenB96 commented Dec 21, 2020 • edited Loading

Fiander commented Dec 21, 2020 • edited Loading

johlju commented Dec 21, 2020

johlju commented Dec 21, 2020

johlju commented Dec 21, 2020 • edited Loading

johlju commented Dec 21, 2020

johlju commented Dec 21, 2020

johlju commented Dec 21, 2020

Fiander commented Dec 21, 2020

ArjenB96 commented Dec 21, 2020 • edited Loading

Fiander commented Dec 21, 2020

ArjenB96 commented Dec 21, 2020

Fiander commented Dec 21, 2020

ArjenB96 commented Dec 21, 2020

johlju commented Dec 21, 2020

ArjenB96 commented Dec 21, 2020

Fiander commented Dec 21, 2020

Fiander commented Dec 21, 2020

ArjenB96 commented Dec 21, 2020 • edited Loading

Sudman1 commented Dec 29, 2020 • edited Loading

bschapendonk commented Jan 29, 2021

johlju commented Feb 1, 2021

bschapendonk commented Feb 1, 2021

ArjenB96 commented Dec 11, 2020 •

edited

Loading

ArjenB96 commented Dec 12, 2020 •

edited

Loading

johlju commented Dec 12, 2020 •

edited

Loading

ArjenB96 commented Dec 14, 2020 •

edited

Loading

ArjenB96 commented Dec 14, 2020 •

edited

Loading

ArjenB96 commented Dec 16, 2020 •

edited

Loading

johlju commented Dec 21, 2020 •

edited

Loading

ArjenB96 commented Dec 21, 2020 •

edited

Loading

Fiander commented Dec 21, 2020 •

edited

Loading

johlju commented Dec 21, 2020 •

edited

Loading

ArjenB96 commented Dec 21, 2020 •

edited

Loading

ArjenB96 commented Dec 21, 2020 •

edited

Loading

Sudman1 commented Dec 29, 2020 •

edited

Loading