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 ?
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)'
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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 ?
Can you post the exact messages you are getting ?
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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
Just to make sure, did you correct the typo -ArguementList into -ArgumentList ?
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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
any other ideas on this guys ?
Do you get that same error when you execute those lines on the SQL itself ?
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
no , it works fine
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.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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
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.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
I just ran it from a powershell window on the target machine
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...