sql server
17 TopicsPassing credentials to SQL connection string
Hello. I am new to PowerShell and would really appreciate some help! I have a working script (Example A) which allows me to connect to SQL servers in my current domain using windows authentication. From there I can run a query and output the results to a file. Pretty handy! The problem arises when I try to connect to a SQL server in a separate domain - since PS by default uses the creds of the logged on user, I am not sure of how to work around this. I have added the $username = Read-Host "Enter your username" (Example B) etc. to the beginning of the script but have been unsuccessful in getting this to work. Can anyone tell me how I might modify my working script to accommodate this change? I am pretty certain my connection string needs to be modified but I can't get it to work. Example A $ServerInstance = "SQLServer" $Database = "database" $ConnectionTimeout = 30 $Query = "SELECT * FROM database" $QueryTimeout = 120 $conn=new-object System.Data.SqlClient.SQLConnection $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout $conn.ConnectionString=$ConnectionString $conn.Open() $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn) $cmd.CommandTimeout=$QueryTimeout $ds=New-Object system.Data.DataSet $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd) [void]$da.fill($ds) $conn.Close() $ds.Tables | Out-File -FilePath C:\file.csv Example B $username = Read-Host "Enter your username" $secureStringPwd = Read-Host -assecurestring "Please enter your password" $credObject = New-Object System.Management.Automation.PSCredential -ArgumentList $username, $secureStringPwd $ServerInstance = "SQLServer" $Database = "database" $ConnectionTimeout = 30 $Query = "SELECT * FROM database" $QueryTimeout = 120 $conn=new-object System.Data.SqlClient.SQLConnection $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout $conn.ConnectionString=$ConnectionString $conn.Open() $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn) $cmd.CommandTimeout=$QueryTimeout $ds=New-Object system.Data.DataSet $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd) [void]$da.fill($ds) $conn.Close() $ds.Tables | Out-File -FilePath C:\file.csv11KViews0likes5CommentsSqlServer 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!7.5KViews0likes1CommentSQL 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.txt3.6KViews0likes0CommentsDownload SSRS Artifacts From Reporting Services
I am trying to download the contens of our Reporting Services instance. I managed to find a very nifty PowerShell script which does just that. But for some reason, when running the script against one of our servers, I get an error message. Here is the script I found: #------------------------------------------------------ #Prerequisites #Install-Module -Name ReportingServicesTools #------------------------------------------------------ #Lets get security on all folders in a single instance #------------------------------------------------------ #Declare SSRS URI $sourceRsUri = "http://hqmnbi/ReportServer_sql08/ReportService2010.asmx?wsdl" #Declare Proxy so we dont need to connect with every command $proxy = New-RsWebServiceProxy -ReportServerUri $sourceRsUri #Output ALL Catalog items to file system Out-RsFolderContent -Proxy $proxy -RsFolder / -Destination 'C:\Users\arobinson\source\Workspaces\EDW\MAIN\SSRS\HQMNBI' -Recurse When I run this script against two of our Reporting Services servers, everythin works fine. But when it's run against the server above, I get the following: Failed to establish proxy connection to http://hqmnbi/ReportServer_sql08/ReportService2010.asmx : The HTML document does not contain Web service discovery information. At C:\Program Files\WindowsPowerShell\Modules\ReportingServicesTools\0.0.6.6\Functions\Utilities\New-RsWebServiceProxy.ps1:136 char:9 + throw (New-Object System.Exception("Failed to establish proxy ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : OperationStopped: (:) [], Exception + FullyQualifiedErrorId : Failed to establish proxy connection to http://hqmnbi/ReportServer_sql08/ReportService2010.asmx : The HTML document does not contain Web service discovery information. I've tried every permutation and still can't get it to run: I've used ReportService2010, ReportService2005, I've tried both with and without the ?wsdl, and none of them work - same error gets thrown. A bit more information, the server SSRS is on is a Windows Server 2008 server and it's running SSRS 2008 as well. Any assistance or insight anyone could provide would be great! Thanks!!3KViews0likes2CommentsPowershell issues running script from Windows Service 01/02 - powershell modules not available
Hi, I am facing problems executing some powershell scripts from a Windows Service. Background The Windows Service (started with an AD user) will execute a command through Process Class. It will launch a command like in the folder that we specify: powershell .\<script name>.ps1 -param1 'param1' -param2 'param2'... Issue Some of the powershell modules are not "available" to be imported, although they are available in the folder "C:\Program Files\WindowsPowerShell\Modules". E.g.: Import-Module -Name Az Import-Module -Name SqlServer If we run the same command from a Command Prompt the script executes correctly! Workaround Run the following command: robocopy "C:\Program Files\WindowsPowerShell\Modules" %USERPROFILE%\Documents\WindowsPowerShell\Modules /mir Thanks in advance, Tiago R.Solved2.9KViews0likes3CommentsAuditing 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 performed2.8KViews0likes4CommentsAuthentication 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 Thanks2.6KViews0likes1CommentHow 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 Gary1.8KViews0likes4CommentsParameterizing 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 and ZZZZZZZ999999\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.1.7KViews0likes4CommentsScript works in powershell and command line but fails from SQLAgent job step
I have a script using DBAtools cmd Get-DbaDatabase -sqlInstance $Inst which converts to dbaDataTable and writes to a SQL ServerInstance. If I run the script from Powershell ISE works without issue If I run from Command promt : C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe "<location>\<Get-DbaDatabase.ps1>" completes without issue however If I run from a SQLAgent job step type : PowerShell Runs As : SQL Server Agent Service Account Command : C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe "<location>\<Get-DbaDatabase.ps1>" I get a bulk load error WARNING: [07:05:37][Write-DbaDbTableData] Failed to bulk import to <database location>.<tablename> | The given ColumnMapping does not match up with any column in the source or destination. the same powershell script is called for all runs any ideas Thanks in advance Glyn1.7KViews0likes2Comments