Invoke VM Script to create a SQL user

[scriptermad]

Hi

I am trying to create a sql user using powercli and invoke vm script. I got the script working locally on the vm using powershell but am trying to get this working now using the invoke vm script method

The user account is a local admin on the vm already , sql is installed using a default instance

$vm = "testvm"

$servicecreate = @"

[system.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | null

$sqlsrv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $vm

$login = New-object -TypeName Microsoft.SqlServer.Management.Smo.Login -ArguementList $sqlsrv $vm\user

$login.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::WindowsUser

$login.PasswordExpirationEnabled = $false

$login.Create()

"@

Invoke-VMScript -VM $vm -ScriptType PowerShell -ScriptText $servicecreate -GuestUser administrator -GuestPassword

Here is what works internally in the VM

$servicecreate = @"

[system.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | null

$sqlsrv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') 'testvm'

$login = New-object -TypeName Microsoft.SqlServer.Management.Smo.Login -ArguementList $sqlsrv,'testvm\user'

$login.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::WindowsUser

$login.PasswordExpirationEnabled = $false

$login.Create()

"@

The error im getting is an expression was expected after '('

any ideas ?

[LucD]

I suspect that your inline string, between double quotes, gets a number of variables substituted before the script is send to the VM.

For the $vm variable that is what you want, but for the other variables you don't want that.

Try placing the here-string between single quotes.

Then you have to set value for $vm yourself, but that can be done with the Replace method that exists on a string object.

Something like this

$cmd = @'

Just some text #vm#

'@

$cmd.Replace('#vm#','real_value)'

[scriptermad]

ok thanks. im getting past the errors but the script just wont create the user in sql

tells me that unable to find type Microsoft.SqlServer.Management.Smo.LoginType, make sure that the assembly that contains this type is loaded.

This commands works fine when run locally on the vm that i am trying to run it on

ANy ideas ? If i cant do this i can just put a batch file on the vm and use invoke-vmscript to call it , is that possible ?

[LucD]

Can you post the exact messages you are getting ?

[scriptermad]

Sorry for delay

Error message is

Unable to find type [Microsoft.SqlServer.management.Smo.LoginType]. Make sure that the assembly that contains this type is loaded

When i run the exact same command locally , i dont see this error at all

[LucD]

Just to make sure, did you correct the typo -ArguementList into -ArgumentList ?

[scriptermad]

yep

This is the  exact syntax

$servicecreate = @'

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

$sqlsrv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') 'testvm'

$login = New-object -TypeName Microsoft.SqlServer.Management.Smo.Login -ArgumentList $sqlsrv,'testvm\testuser'

$login.LoginType =[Microsoft.SqlServer.Management.Smo.LoginType]::WindowsUser

$login.PasswordExpirationEnabled = $false

$login.Create()

'@

Invoke-VMScript -VM $SQLVM -ScriptType PowerShell -ScriptText $servicecreate -GuestUser administrator -GuestPassword xxxx

Using ' then the whole syntax should just be taken as it is , correct ? I am putting in vmname and username as it is on the vm itself, i can get the replace working afterwards

[scriptermad]

any other ideas on this guys ?

[LucD]

Do you get that same error when you execute those lines on the SQL itself ?

[scriptermad]

no , it works fine

[LucD]

Ok, that mean sthat all required modules and pssnapins are present on the server.

So there are probably a number of Add-PSSnapin lines missing.

Now to find out which ones.

[scriptermad]

yes but the command is being run on the server that the script is being invoked in , correct ?

so if the snapin is present in there (which it is ) then it should run

[LucD]

That is correct for a module, with module auto-load active, but not for a pssnapin.

Those you will have load explictely (with the Add-PSSnapin cmdlet).

From which prompt did you run the script on the server ?

I'm pretty sure that the prompt comes with an initialisation script, or that user has a profile, where the pssnapins are loaded.

[scriptermad]

I just ran it from a powershell window on the target machine

[Craig_Baltzer]

It may also be worth a look at your path variables when you're logged on interactively to the machine vs what you get when your script runs via the invoke; it could be something that needs to be on one of the paths isn't there running as the invoke

https://msdn.microsoft.com/en-us/library/hh245202(v=sql.110).aspx references a number of assemblies that need to be loaded before the SQL objects can be used from PowerShell. Those may be getting loaded automatically by the interactive session but aren't present when run remotely.

There was a also a reference to using

Add-Type -Path 'C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll'

to load the assembly rather than LoadWithPartialName...