Powershell Environment Variable NULL - No repository named PSGallery
I have an SSIS package that runs as a specific user. It calls a Powershell script and it used to work. We rebooted the server and now we get the following error complaining about $env:LOCALAPPDATA being NULL. PS>TerminatingError(Join-Path): "Cannot bind argument to parameter 'Path' because it is null." Join-Path : Cannot bind argument to parameter 'Path' because it is null. At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\2.2.4\PSModule.psm1:95 char:81 + ... osoft.PowerShell.Management\Join-Path -Path $env:LOCALAPPDATA -ChildP ... + ~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidData: (:) [Join-Path], ParameterBindingValidationException + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.JoinPathCommand The variable '$script:PSGetAppLocalPath' cannot be retrieved because it has not been set. At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\2.2.4\PSModule.psm1:102 char:86 + ... werShell.Management\Join-Path -Path $script:PSGetAppLocalPath -ChildP ... We also get the following message: Set-PSRepository : No repository with the name 'PSGallery' was found. If the user the SSIS packages runs as, is logged onto the server, the SSIS package works just fine. If I log out and re-schedule the package, it fails again.
ansible.windows.win_dsc resource SqlPermission Not found
Hi I am trying to do SQL (SQL Server 2022) Automation along with Always_ON and failover Cluster setup using Ansible on Azure VM (Windows 2019 Server) . Since SQL VM do not have internet connectivity , I tried to install below module manually by downloading respective nupkg files. PowershellGet SqlServerDsc When we are trying to configure SQL Server Permission using below Ansible task, we receive following error messages ("msg": "Resource 'SqlPermission' not found.") - name: configure SQL server permissions ansible.windows.win_dsc: resource_name: SqlPermission ServerName: 'LocalHost' InstanceName: '{{ sql_server_instance_name }}' Name: '{{ item }}' PermissionToInclude: - State: Grant Permission: - 'AlterAnyAvailabilityGroup' - 'ViewServerState' - 'AlterAnyEndPoint' - 'ConnectSql' Credential_username: '{{ mssql_username }}' Credential_password: '{{ mssql_password }}' loop: "{{ [sql_server_svc_account, sql_server_agt_svc_account] | unique }}" Error Received failed: [<hostname>.<domain_name>] (item=<domain_name>\<Serviceaccount>) => { "ansible_loop_var": "item", "changed": false, "item": "<domain_name>\\<service_account>", "msg": "Resource 'SqlPermission' not found." } [<hostname>.<domain_name>] TASK: set_alwayson_perm : configure SQL server permissions (debug)> User interrupted execution. When we execute command Get-DscResource – Name SqlPermission, It errors out. I believe I am missing on some steps or configuration while doing DSC Module SQLServerDsc Please support.Powershell SQLLogin CIMException (Object not found)
Hi I am trying to add user to SQL Server using below Ansible task - name: add users to SQL Server ansible.windows.win_dsc: resource_name: SqlLogin Ensure: Present Name: "{{ item }}" LoginType: 'WindowsUser' ServerName: 'LocalHost' InstanceName: '{{ sql_server_instance_name }}' PsDscRunAsCredential_username: '{{ mssql_username }}' PsDscRunAsCredential_password: '{{ mssql_password }}' loop: "{{ [sql_server_svc_account, sql_server_agt_svc_account, cluster_svc_account] | unique }}" It fails with below error resource = Get-CimClass -ClassName $ClassName -Namespace root\Micros ... + CategoryInfo : ObjectNotFound: (root\Microsoft\...on:DSC_SqlLogin:String) [Get-CimClass], CimException + FullyQualifiedErrorId : HRESULT 0x80041002,Microsoft.Management.Infrastructure.CimCmdlets.GetCimClassCommand Please help to understand root cause and solutionHow to remove ".." in csv
Hello I am pretty green at this so please forgive me if this is a real stupid question... I need to run a SQLCMD on a server 2019 machine and export results to a CSV. I have tried to format the output file in the SQLCMD and while it almost works I Have had some issues with the formatting. It was suggested that I use PowerShell and the export-CSV command and I can tell the results are better and almost have it working but the export-csv command puts ".." around the data as you see below. the top line are headers and that is OK but I need to find a way to strip the quotes. If I open it in excel, excel strips the quotes but I do not use excel, it needs to be directly imported into another program and the quotes are causing a problem. I found an article on stackoverflow, but to be honest I get a little intimidated when I try to look at Stackoverflow because I am so green and when I have tried to ask a newbie question, they get frustrated at me. Below you will find the full PowerShell script I am trying to run... I tried to adopt the syntax from an example of the export-csv command from a stackoverflow post, but I had some confusion since they were talking about things that work and do not work on PowerShell version 7. and based on the error I am getting; I do not think this command is complete... invoke-sqlcmd -server 192.168.0.4 -Username PAReadonly2 -password <password> -database lc_V3_acephoto_net -inputfile "C:\Users\Amber\Documents\SQL Server Management Studio\MPNsimple.sql" | export-csv -NOTYPEINFORMATION | -path c:\junk\test.csv Select-Object -Skip 1 | ForEach-Object { $_ -replace '"' } | Set-Content $NewCSV I am running windows server 2019 PowerShell 5.1.14463 As FYI.. I have tried to install PowerShell 7 but I got some kind of error when I run my script because SQLCMD would not run. any help or guidance (even a little pity) would be appreciated. thanks GaryAuthentication errors when using Invoke-SQLCMD
Hello I have been using the below batch file to export some data out of SQL using this SQLcmd ....a (for obvious reasons passwords are changed but that is all that has changed). This works fine but because of the data format that is outputted, I need to edit it before I can use it. sqlcmd -S 192.168.0.4 -U PAReadonly2 -P testpw -i "C:\Users\Amber\Documents\SQL Server Management It was suggested that I do this in PowerShell so with the help in this community I have converted the batch script to PowerShell. Finally I got the PowerShell side working but as you see below I am now getting authentication errors and there is nothing wrong with the creds because it works fine in the batch file. This is a local SQL account defined in SQL server itself and there is no domain involved Invoke-Sqlcmd -Server 192.168.0.4 -Username "PAReadonly2" -Password "testpw" -Database "lc_V3_acephoto_net" -InputFile "C:\Users\Amber\Documents\SQL Server Management Studio\MPNsimple.sql" | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | ForEach-Object { $_.Replace("""", "") } | Out-File -FilePath "c:\junk\test.csv"; Any help or guidance to make this script work would be really GREAT errors... Invoke-Sqlcmd : Cannot open database "lc_V3_acephoto_net" requested by the login. The login failed. Login failed for user 'PAReadOnly2'. At line:1 char:1 + Invoke-Sqlcmd -Server 192.168.0.4 -Username "PAReadonly2" -Password " ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand Invoke-Sqlcmd : At line:1 char:1 + Invoke-Sqlcmd -Server 192.168.0.4 -Username "PAReadonly2" -Password " ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : ParserError: (:) [Invoke-Sqlcmd], ParserException + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand Thanks
SqlServer PS module and SQLPS Clarification
I've already started a thread last week about this on the SQL MSDN forums and have not gotten anywhere. So I'm hoping that since this might be more of PS thing, maybe you fine folks could shed some light. I understand that SqlServer has replaced SQLPS. I experienced this with SQL 2017. I'm now putting together a SQL 2019 with Server 2019 and I see that SQLPS is still part of the install. PS C:\Windows\system32> get-module sqlps -ListAvailable Directory: C:\Program Files (x86)\Microsoft SQL Server\150\Tools\PowerShell\Modules ModuleType Version Name ExportedCommands ---------- ------- ---- ---------------- Manifest 15.0 SQLPS {Backup-SqlDatabase, Save-SqlMigrationReport, Invoke-Polic... Before I do an install of SqlServer module, is it advised to uninstall SQLPS first? I did try to install SqlServer without trying to remove SQLPS and I'm then presented with all sorts of red text PackageManagement\Install-Package : The following commands are already available on this system:'Add-SqlAvailabilityDatabase,Add-SqlAvailabilityGroupListenerStaticIp,Add-SqlFirewallRule,Backup-SqlDatabase,blah blah blah....'. This module 'SqlServer' may override the existing commands. If you still want to install this module 'SqlServer', use -AllowClobber parameter. Should one just -AllowClobber? When I do that, both SQLPS and SqlServer appear to be installed. Someone from that SQL forum did suggest to uninstall SQLPS. However, that brings up more issues. I attempted to Uninstall-Module and Remove-Module with no luck. PS C:\Windows\system32> Uninstall-Module -Name sqlpsPackageManagement\Uninstall-Package : No match was found for the specified search criteria and module names 'sqlps'. At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\1.0.0.1\PSModule.psm1:2194 char:21 + ... $null = PackageManagement\Uninstall-Package @PSBoundParameters + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : ObjectNotFound: (Microsoft.Power...ninstallPackage:UninstallPackage) [Uninstall-Package] , Exception + FullyQualifiedErrorId : NoMatchFound,Microsoft.PowerShell.PackageManagement.Cmdlets.UninstallPackage and PS C:\Windows\system32> Remove-Module -Name "SQLPS" Remove-Module : No modules were removed. Verify that the specification of modules to remove is correct and those modules exist in the runspace. At line:1 char:1 + Remove-Module -Name "SQLPS" + ~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : ResourceUnavailable: (:) [Remove-Module], InvalidOperationException + FullyQualifiedErrorId : Modules_NoModulesRemoved,Microsoft.PowerShell.Commands.RemoveModuleCommand From what I can tell online, remove-module needs quotes while uninstall-module does not. Please advise and thanks!
How can I protect a password within this login script?
I have a powershell script that logs into a SQL server and writes data to a table whenever a user logs in. I want to protect the password and not just have it in cleartext within the script. What would be the best way to go about doing that? $server = "sql" $database = "login" $table = "UserLogins" $uid = "user" $pwd = "password" $username = $env:USERNAME $computername = $env:COMPUTERNAME $userProfilePath = "$env:USERPROFILE" $date = Get-Date -Format "yyyy-MM-dd" $time = Get-Date -Format "HH:mm:ss" $userProfileSize = ((Get-ChildItem -Path $userProfilePath -Recurse -Force | Measure-Object -Property Length -Sum -ErrorAction Stop).Sum / 1MB).ToString("#.##") $connectionString = "Server=$server;Database=$database;User ID= $uid;Password= $pwd;" $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString) $connection.Open() $command = New-Object System.Data.SqlClient.SqlCommand $command.Connection = $connection $command.CommandText = "IF NOT EXISTS (SELECT * FROM $table WHERE username = @username AND computername = @computername) BEGIN INSERT INTO $table (username, computername, date, time, profilesize) VALUES (@username, @computername, @date, @time, @profilesize) END ELSE BEGIN UPDATE $table SET date = @date, time = @time, profilesize = @profilesize WHERE username = @username AND computername = @computername END" $command.Parameters.AddWithValue("@username", $username) $command.Parameters.AddWithValue("@computername", $computername) $command.Parameters.AddWithValue("@date", $date) $command.Parameters.AddWithValue("@time", $time) $command.Parameters.AddWithValue("@profilesize", $userProfileSize) $command.ExecuteNonQuery() $connection.Close()
Auditing user activity across Enterprise servers
Hello Experts , We have been noticing some users are using shared service accounts to perform some activities which need to be traced and tracked for auditing. I am looking for help with a Powershell script which lets me see all the users connected across the Network of servers. Please let me know if there is any such script which I can use or may be tweak a little bit. Regards Faiz We need a simple report like User ID, Session_ID , Local Client_Host_ID, Remote Server_ID_Connected to, brief_descp_Activity performedSQL Database exists in PowerShell
Good evening everyone. I am on my 2nd homework assignment for PowerShell. Check for the existence of a database named ClientDB. Output a message to the console that indicates if the database exists or if it does not. If it already exists, delete it and output a message to the console that it was deleted. Here is my script I have so far but I need to add the database check in the beginning but I don't know how to write the check using PowerShell. Try { $DBname = "ClientDB" $SqlServer = ".\SQLEXPRESS" $CreateTableQuery = @" use $DBname CREATE TABLE Client_A_Contacts ( first_name varchar(20), last_name varchar(20), city varchar(20), county varchar(20), zip varchar(20), officePhone varchar(20), mobilePhone varchar(20) ) "@ Import-Module sqlps -DisableNameChecking $ServerObject = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServer) $DBobject = New-Object Microsoft.SqlServer.Management.Smo.Database($ServerObject, $DBname) $DBobject.Create() Write-Host $DBobject "create success @ " $DBobject.CreateDate Invoke-sqlcmd -ServerInstance $SqlServer -Database $DBname -Query $CreateTableQuery Pop-Location Import-Csv "NewClientData.csv" | ForEach-Object {Invoke-sqlcmd -ServerInstance $SqlServer -Database $DBname -Query "insert into dbo.Client_A_Contacts values ('$($_.first_name)','$($_.last_name)','$($_.city)','$($_.county)','$($_.zip)','$($_.officePhone)','$($ _.mobilePhone)')"} } catch [System.OutOfMemoryException] { Write-Host "Caught: System.OutOfMemoryException" } Invoke-Sqlcmd -Database ClientDB –ServerInstance .\SQLEXPRESS -Query ‘SELECT * FROM dbo.Client_A_Contacts’ > .\SqlResults.txt
Parameterizing in a PowerShell Script
I am new to PowerShell and I am at a loss. Working with Idera I have put together this script #To place the Instance in Maintenance Mode #To enable the SQLDM provider, type the following within PowerShell add-pssnapin sqldmsnapin #To verify the snapin is registered get-psprovider #To register the SQLdm drive, type the following within PowerShell. AAAAAAA000000\DEV01 is the instance where the Repository Database sits #Need to know what Repository instance the instance is registered on #Currently in Production there are 5: BBBBBBB000000\prod01, CCCCCCC000000\prod01, DDDDDDD000000\prod01, EEEEEEE000000\prod01, FFFFFFF000000\prod01 New-SQLdmDrive dm AAAAAAA000000\DEV01 SQLdmRepository #Change to the dm drive cd dm:\ #Sets maintenance mode to Until further notice. Schedule collection will not occur. ZZZZZZZ999999\DEV01 is the instance to be placed on maintenance mode. Set-SQLdmMonitoredInstance -Path (Escape-SQLdmName -Name ZZZZZZZ999999\DEV01) -MMAlways I need to parameterize where the values AAAAAAA000000\DEV01 andZZZZZZZ999999\DEV01 are. Can someone provide me with a web page that shows an example. They want me to save this as a ps1 file and then call it passing in the two parameters.
