Connect to Sql Server under a different login context

Here’s how to connect to sql server under a different windows login context. If you pass in the login name as ‘domain\loginname’ it will indeed fail. Only pass in the login name.

 #omit the domain name in the login, or it will fail
$cred = Get-Credential -Message "Enter your login name" -UserName $env:USERNAME

try{

    $srv = New-Object Microsoft.sqlServer.Management.smo.Server "ServerName"

    $srv.ConnectionContext.LoginSecure = $true
    $srv.ConnectionContext.ConnectAsUser = $true

    $srv.ConnectionContext.ConnectAsUserName = $cred.username 
    $srv.ConnectionContext.ConnectAsUserPassword = $cred.GetNetworkCredential().Password 
    $srv.ConnectionContext.Connect() 
    $srv.ConnectionContext.ExecuteWithResults("SELECT SUSER_SNAME() AS CurrentUser").Tables[0] | ft -AutoSize
}
catch{
    $_ | fl -Force
}
finally{
    $srv.ConnectionContext.Disconnect();
}

Capture Sql Server Information Messages in Powershell

Sometimes you have a need to capture the informational messages generated in sql server, for example sp_help_revlogin only prints the user information to the messages tab.
Import-Module SqlServer -DisableNameChecking cls try {


    $users = @('user1', 'user2')
    $users | % {

        $user = $_ 
        $sqlConn = New-Object System.Data.SqlClient.SqlConnection "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=servername"

        $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {

            param(

                $sender,

                $event

            ) 
            Write-host $event.Message 
        };

        $sqlConn.add_InfoMessage($handler);

        $sqlConn.FireInfoMessageEventOnUserErrors = $true;

        $sqlConn.Open();

        $sqlCmd = New-Object System.Data.SqlClient.SqlCommand("EXEC sp_help_revlogin '$user', NULL", $sqlConn);

        $sqlcmd.CommandType = [System.Data.CommandType]::Text

        [void]$sqlCmd.ExecuteNonQuery();

    }

} catch { $_ | fl -Force } finally { $sqlConn.Close(); }

Stop/Disable SqlAgent/MSSQLServer service via Powershell

One-liner to stop and disable the sqlagent and msssqlserver service.

Get-Service MSSQLSERVER, SQLSERVERAGENT | Stop-Service -PassThru -Force | Set-Service -StartupType disabled

BCP via Powershell

Quick post on how to use BCP with powershell. Adjust options to suit your needs.

Import-Module SqlPS -DisableNameChecking

$query = "SELECT * FROM TABLE"

try{

 $srcConn = New-Object System.Data.SqlClient.SqlConnection("PersistSecurityInfo = true;Server=Server1;Database=DBName;Integrated Security=True;Application Name=PowerShell_BCP");
 $destConn = New-Object System.Data.SqlClient.SqlConnection("PersistSecurityInfo = true;Server=Server2;Database=DBName;Integrated Security=True;Application Name=PowerShell_BCP");

 $srcCommand = New-Object System.Data.SqlClient.SqlCommand($query, $srcConn);
 $destBulk = New-Object System.Data.SqlClient.SqlBulkCopy($destConn);
 $destBulk.BulkCopyTimeout = 0;
 $destBulk.BatchSize = 1000
 $destBulk.DestinationTableName = 'DestTable'

 $srcConn.Open();
 $destConn.Open();

 $results = $srcCommand.ExecuteReader();
 $destBulk.WriteToServer($results);

 $srcConn.Close();
 $destConn.Close();

}
catch{
 $_ | fl -Force
}

31 Posts of using Sql Server with Powershell: Modules

This isn’t a 31 days series, as I’ll undoubtedly miss a day here and there, so instead, this will just be a ’31 posts’ series instead. I’m pragmatic. And lazy.

I have noticed there is a bit of discomfort amongst most database administrators when it comes to dealing with powershell. Do yourself a favor and learn it. You’ll wonder how you ever got by without it before.

So here we go with post 1.

Importing Sql Server Cmdlets

In order to begin this series, you’ll need to import the sql server cmdlets that will enable you to work with the smo objects that you’ll use to work with sql server. If you have sql server management studio 12 and above, you’re in luck. All you need to do to import the sql server cmdlets is this:

Import-Module SqlPS -DisableNameChecking;

The –DisableNameChecking is optional, but if omitted you’ll get a warning that some of the verbs in the cmdlets are unapproved:

A word of warning; this module loads extremely slow. This has been fixed in Sql Server 2016, but as noted in the closing notes of this post, that requires that SSMS for Sql Server 2016 be installed.

If you’ve got Sql Server 2008 R2 and below, you’ll need to install a few things in order to get the sql server cmdlets working correctly. More or less, the SqlPS module requires the use of Sql Server 2012 Shared Management Objects, so you’ll need to download 3 components from the Sql Server 2012 (or 2014) feature pack:

First, you’ll need download and install the SystemCLR Types for Sql Server 2012/14:

Next, you’ll need to download and install the Sql Server Shared Management objects:

And finally, download and install the Windows Powershell Extensions for Sql Server:

Once you have all three of these installed and restart your powershell environment, you should be able to run the Import-Module SqlPS command correctly.

Okay, post 1 complete. Sort of. As of Sql Server 2016, the SqlPS module is being replaced by a new SqlServer module, but that requires that the Sql Server 2016 SSMS must be installed to use it (at the time of this writing), so we’re just going to plod along using SqlPS for now. Most of the functionality provided in the SqlServer module will still work.

Copy a Sql Server job via Powershell

This function will copy a job from one server to another using powershell.

function Copy-SqlJob{
    [cmdletbinding(SupportsShouldProcess=$true)]
    param(
        [Parameter(Mandatory=$true)]
        $ComputerName,
        [Parameter(Mandatory=$true)]
        $Destination,
        [Parameter(Mandatory=$true)]
        $JobName
    )
    begin{
        Import-Module SqlPS -DisableNameChecking
    }
    process{

        if($PSCmdlet.ShouldProcess($ComputerName)){

            $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Destination
            $job = $srv.JobServer.Jobs | where{$_.name -eq $JobName}
            if($job -ne $null){
                throw "Job $JobName already exist on server $Destination"
            }

            $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Destination
            if($srv.JobServer.Jobs | where{$_.Name -eq $JobName}){
                throw "Job $JobName already exists on server $Destination"
                return;
            }

	        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $ComputerName
	        $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($srv)
	        $scriptr.Options.DriAll = $True
	        $scriptr.Options.IncludeHeaders = $True

            $job = $srv.JobServer.jobs | where{$_.Name -eq $JobName} 
            if($job -eq $null){
                throw "Job $JobName was not found on server $ComputerName."
                return;
            }
            $strJob = $scriptr.Script($job) | Out-String
        
            Invoke-Sqlcmd -ServerInstance $Destination -Database 'msdb' -Query $strJob
        }
        else{
            Write-Host "Copying Sql Job $JobName from $ComputerName to $Destination"
        }
    }
    end{

    }
}

Windows Live Writer

More for me than you. If you like using windows live writer, the link to download it is now here.

Get File from AWS S3 via Powershell

Not battle-tested at all. Use at your own risk. Works for me though. You’ll need an $accessKey and $secretKey variables set outside the scope for this to work. The $bucketName is the bucket that the file is located in. The $fileName is the name of the file in said bucket. The $dest is where you want to write the file locally. The $logFile does nothing at this point.

function GetS3([string]$bucketName, [string]$fileName, [string]$dest, [string]$logFile)
{
	$AmazonS3 = [Amazon.AWSClientFactory]::CreateAmazonS3Client($accessKey, $secretKey)
	$S3GetRequest = New-Object  Amazon.S3.Model.GetObjectRequest
	$S3GetRequest.BucketName = $bucketName
	$S3GetRequest.Key = $fileName

	$S3Response = $AmazonS3.GetObject($S3GetRequest);
	if($S3Response -eq $null){
	    Write-Error "ERROR: Amazon S3 get requrest failed. Script halted."
	    exit 1
	}

	$stream = $S3Response.ResponseStream;
	$fileStream = new-object system.IO.FileStream($dest, [System.IO.FileMode]::Create, [System.IO.FileAccess]::Write);
	$byteArray = New-Object byte[] 32768
	[int]$bytesRead = 0;
	do
	{
		$bytesRead = $stream.read($byteArray, 0, $byteArray.length);
		$fileStream.write($byteArray, 0, $bytesRead);
	}
	while($bytesRead -gt 0)
	$fileStream.flush();

	$fileStream.close();
	$stream.close()

}

mmm…DBA beer

Code Formatting for Windows Live Writer and WordPress

Just to save you the trouble, here is the best code formatter for windows live writer when publishing to wordpress. This is more of a reminder for myself so’s I don’t have to scour the web to find this again, but hopefully this will save someone else some time in trying to track down a good code formatter. You can grab it here.

It’s the one that I’m using in the latest posts on this site. If you scroll to the earlier posts, you’ll definitely notice the difference.