Feeds:
Posts
Comments

Have you ever had to run a job in SQL Server Agent and needed it to run with different permissions than the login that runs SQL Agent?  I have come across this a few times.  Most recently, I was trying to run a SSIS Package as a SQL Agent Job and one step of the job was trying to launch WinSCP as part of an “Execute Process Task”.  The task was able to run and could launch WinSCP but it was running with the credentials as the SQL Agent Service.

The problem with that was that WinSCP stored their sessions in the registry as part of HKey\Current_User.  So, when the job launched using the SQL Agent Service Account, it could not access the values in the registry because it didn’t have the stored session in its hive.

Now, I know there are a few ways to fix this issue but I wanted to choose setting up a SQL Proxy.  A SQL Server Agent proxy gives the SQL Server Agent access to the security credentials for a Microsoft Windows User, which is to say it allows the Agent to impersonate a user when it runs that step of the job.

There are a few steps to using a Proxy.  First you need to set up Credentials in SQL Server Management Studio.  A credential is a record that contains the authentication information for connecting to resources outside of SQL.

Usually credentials consist of a Windows login name and password.

Here are the steps to create a credential.

In SSMS Object Explorer open the Security folder and right click on Credentials and choose “New Credential.

Once you open the New Credential window you are given the option to Name the credential. This can be anything you want to call it. Its for your reference. Choose something that will make sense to you.

Then in the Identity field type the name of the account you are going to use. I used a domain account so when I filled it in I put the format DOMAIN\UserName.

Then I typed the password and typed it again to confirm and clicked OK.

Now you are ready to use these credentials in creating a Proxy.

In Object Explorer scroll down to the “SQL Server Agent” and expand the folder.

Then go to Proxies.  In my example I need to be able to run command line applications so I choose the “Operating System (CmdExec) Proxy.

 

Right click on the proxy you want to use and choose “New Proxy”.

Once again you are given a window where you enter the name, and description for the new proxy.  Then you select the “Credential name”  by clicking on the three dots.

 

After that you just select the options for what this proxy is allow to access.  A good explanation of what each subsystem is can be found here http://msdn.microsoft.com/en-us/library/ms187100(v=sql.105).aspx

Once you have clicked OK you should now be able to use the Proxy in your SQL Agent Job.

 

Just go to the pull down for “Run As:” and select your new Proxy.
That is all there is to it.  Now your SQL Agent Job will run that step using the credentials you’ve supplied.

 

 

I recently needed to find all disabled computers in my Active Directory Installation.  So, rather than open up Active Directory Users and Computers and manually search through multiple OU’s and create a list I decided to use PowerShell.

So, I started playing around with the command Search-ADAccount

Using Get-Help Search-ADAccount I am able to determine that the syntax for a query is fairly simple.  There are several switches for the command all of which are self explanatory.

-AccountDisabled

-AccountExpired

-AccountExpiring

-AccountInactive

-LockedOut

So, my first reaction was to run the command

Search-ADAccount –AccountDisabled

Which returned a long list of items with more information than I needed and in a format that was very hard to read. 

AccountExpirationDate :
DistinguishedName     : CN=Guest,CN=Users,DC=DOMAIN,DC=com
Enabled               : False
LastLogonDate         :
LockedOut             : False
Name                  : Guest
ObjectClass           : user
ObjectGUID            : 1e4955ce-df97-4ecc-8a46-f4356ba2e6cc
PasswordExpired       : False
PasswordNeverExpires  : True
SamAccountName        : Guest
SID                   : S-1-5-21-1058032114-1936565697-1108674531-501
UserPrincipalName     : guest@DOMAIN.COM

My next addition to the command was to pipe the results to Format-Table to make it easier to read.

Search-ADAccount –AccountDisabled | Format-Table –AutoSize

This was much better, now I could see that my list not only included computers but users as well.  I didn’t want the users so I needed to look for another switch for the Search-ADAccount command.  Tada!  I found the switch –ComputersOnly which gives me exactly what I needed.  But now I only want to see some of the columns in my table so lets modify the command a little more. 

Search-ADAccount –AccountDisabled -ComputersOnly | Format-Table –AutoSize Name, LastLogonDate, DistinguishedName

This is perfect, now the results are readable and I can easily determine what computers in my environment are Disabled. 

Name LastLogonDate DistinguishedName
AUX-2B CN=AUX-2B,OU=OLD Computers,DC=DOMAIN,DC=com
TECH-GC 10/5/2010 4:11:36 PM CN=TECH-GC,OU=OLD Computers,DC=DOMAIN,DC=com
TS-2 8/19/2010 5:40:54 PM CN=TS-2,OU=OLD Computers,DC=DOMAIN,DC=com
WPIX CN=WPIX,OU=Newark,DC=DOMAIN,DC=com
CITRIX 5/5/2010 9:46:26 PM CN=CITRIX,OU=OLD Computers,DC=DOMAIN,DC=com
SERVER-TEST 6/3/2010 7:16:24 PM CN=SERVER-TEST,OU=OLD Computers,DC=DOMAIN,DC=com
ARCHIVE2 9/1/2010 11:36:09 AM CN=ARCHIVE2,OU=OLD Computers,DC=DOMAIN,DC=com
ACCT-USR2 CN=ACCT-USR2,OU=OLD Computers,DC=DOMAIN,DC=com
TECH-GC7 6/2/2011 12:24:32 AM CN=TECH-GC7,OU=OLD Computers,DC=DOMAIN,DC=com
TECH7-GC 4/9/2012 8:35:46 PM CN=TECH7-GC,OU=OLD Computers,DC=DOMAIN,DC=com

At this point I now have all the computer accounts in my Active Directory that are disabled.  And it took me all of about a minute to type out the command and hit return.  Much more efficient than going through AD manually and looking.

At this point I could manually go into AD and remove the accounts but since I am already in PowerShell all I need to do is change the command slightly and I could remove those accounts in a flash.  To do this I need to pipe this to another command and remove them

Remove-ADComputer

By piping the results to Remove-ADComputer PS will automatically remove the computers from Active Directory.

Search-ADAccount –AccountDisabled -ComputersOnly |

Remove-ADComputer –WhatIf

My advice is ALWAYS, ALWAYS run your command with the –WhatIF switch first so you are sure that the command will do what you expect it to.  You wouldn’t want to run this and find out that half of your AD has been unintentionally obliterated by the command.

This same command can be changed slightly to look for AD Users as well.

Search-ADAccount –AccountDisabled –UsersOnly | Format-Table –AutoSize Name, LastLogonDate, DistinguishedName

I successfully participated in the 2012 Scripting Games and am awaiting the results of the scoring.  The game consisted of a Beginner and Advanced level, and there were 10 individual events that you had to create a powershell script for.  I have to say I really had a great time and learned a lot.  There is nothing like having a defined ‘need’ for a script to help spur the creative juices.  So far my score isn’t bad, but honestly I didn’t participate to get a high score.  I did it to learn more about PowerShell.  When allowed I will try to post up some of the answers that I created. 

Today I am getting the opportunity to attend the Techstravaganza 2012 event http://www.techstravaganza.com at Microsoft’s office in NYC.  I hope to pick up a lot of good information on PowerShell and SharePoint.

List Sever Reboot Times

I was recently working on a Windows Server in my office when I noticed it had not been rebooted in a while. That got me to thinking, how many other servers in my environment had gotten in under the radar and were in need of a reboot. So, I decided to try my hand at writing another PowerShell Script. I started with a function called Get-RebootTime which is part of a module called BSonPosh. This module was created by Brandon Shell who is a MVP and who has his own blog at http://bsonposh.com.

What I was looking to do was to have a list of the servers I wanted to check on a regular basis and have it automatically check each server and write the results to a csv file. That way I could review the file at my leisure and sort it by oldest reboot date.

So, I set about creating my script.

First I created a variable to contain my list of servers.

$Servers = "DC1", "FS1", "RPTS1", "SQL1"

Then I wrote a ForEach statement to run a cmd for each item in the $Servers list.

ForEach ($Computer in $Servers) { Get-RebootTime -ComputerName $Computer -Last }

When I ran this in my PowerShell editor it produced the desired results but in a simple list.

RebootTime ComputerName
7/27/2011 1:39:12 PM DC1
2/10/2012 9:16:28 AM FS1
2/11/2012 11:40:59 AM RPTS1

This was great but the output was on the screen and not in a file that I could look at later and it wasn’t in a format I could manipulate. So, next step was to look into outputting the info into a file.

I tried using the Export-Csv cmdlet like this

ForEach ($Computer in $Servers) { Get-RebootTime -ComputerName $ComputerLast | Export-Csv ‘C:\Greg\boottimes.csv’ -notype}

This worked, or so I thought, but in reality all it did was each time overwrite the same file with an entry.  So that when I viewed the file it only showed the LAST server that the script queried.

That wouldn’t do…

So, I started looking into how to get the results of each Get-RebootTime cmd into an array so I could later output the array into csv. 

What I came up with was this:

First I created an empty array called $results to hold all my output.

$results = @()

I also created a variable for the path and name of the output file.

$outputFile = ‘C:\Greg\boottimes.csv’

Then I changed my script to use New-Object cmdlet to create a new object called $result and using the Add-Member cmdlet I was able to add members to my object in the form of the values of the server name and the reboot time each time I looped through. Then I would take the object and add it to my $results array. When the loop was completed I could then use the Export-Csv cmdlet to output $results all at once into a csv file.

Here is what the finished script looked like:

# ServerBootTimes.ps1
# Written by Greg Caporale Febrary 28, 2012
#
# Determines the last date a list of computers was rebooted
# Exports the results to a csv file.
#
# Adjust the server list accordingly
# Adjust the path and file name for the output file to your needs
$results = @()
$Servers = "DC1", "FS1", "RPTS1", "SQL1"
$outputFile = ‘C:\Greg\boottimes.csv’
ForEach ($Computer in $Servers) {
$result = New-Object psObject
$result | Add-Member -MemberType NoteProperty -Name ‘Computer Name’ -Value $Computer
$rbttime = Get-RebootTime -ComputerName $Computer -Last | select -ExpandProperty RebootTime
$result | Add-Member -MemberType NoteProperty -Name ‘Last Reboot’ -Value $rbttime
$results += $result
}
$results | Export-Csv $outputFile -notype

 

SUCCESS!!  I had my csv file and was able to see all the servers and their reboot times.  This was a great exercise for me and it also gave me a nice little utility script that I could have handy or even schedule to run automatically so I always know how long its been between reboots of my servers. 

On several occasions I have had to use SSIS to export data from a SQL database, and then transfer that data to a third party via sFTP.  SQL Server SSIS has an FTP task built in but it does not natively support sFTP.  There are several commercial products (Eldos Software, /n Software, CozyRoc) out on the market that can be used to add that functionality into SSIS but I am going to talk about using a free utility called WinSCP to accomplish this task.

The first step is to download and install WinSCP, you will need to do this on the SQL server that will be running the SSIS package.  Once you have installed WinSCP you should create a connection to the distant sFTP server in WinSCP so you can test and confirm that you can connect to it.  Create and name a saved session so that you can call that session from your script later.  I will name this session “MySecureFTP”.

image

Once you have done that you will need to create a WinSCP script to transfer your files.  For examples of scripts see the WinSCP documentation.  Here is a very basic script that I created to copy a csv file.

option batch on

option confirm off

open MySecureFTP

put c:\outbound\*.csv /inbounddirectory/

exit

After testing the script by running it from a command prompt and confirming that it works, you are ready to step into SSIS and call it from your workflow.

In Microsoft Visual Studio in your SSIS Package, go to the Control Flow tab and from your toolbox add an “Execute Process Task” to the workflow.  It should look like this after you’ve added it.

image

Double click the Execute Process Task to open the Task Editor.  You should now have a window like this.

image

Go to the Process section on the left and then you will need to fill out some information.

image

In the executable line you will need to fill out the correct path to the WinSCP executable.  Mine was in the (x86) directory but it may be different for your computer.

Then in the Arguments line you will need to put the /script= switch and then the path to that script.  I also included the /log= switch to log the execution to a file.  That way I can go back and review the FTP connection and make sure everything was a success. 

You may need to add an entry for the WorkingDirectory if you do not use absolute paths in your script.  I didn’t require it. 

 

Now you can build your SSIS package and deploy it.  That’s all that you need to do. 

 

One last comment that I’ve found is that if you’ve created a stored session in WinSCP it stores the value in the registry under the HKey\Current_User folder and depending on what credentials you use to run the SQL Job you may not have access to that registry key.  One option to fix this is to switch WinSCP to use an INI so all the stored sessions are stored in the INI.  That way the script can still access the stored session.  Another way to fix this is to set the SQL Job to run as a different user but that involves setting up a Proxy in SQL Server Agent which I will cover in another post.    

I was going through some old files and thought I would post this one as a good example of using VBScript.

I had a request to automate the upload of a file to an FTP site.  This example dates back to 2006 so I know there are better ways to do this now, but it is a good example of the flexibility of VB Scripting.

‘ VBScript source code

Dim Fso : Set Fso = CreateObject(“Scripting.FileSystemObject”)
Dim WshNetwork : Set WshNetwork = CreateObject(“Wscript.Network”)
Dim WshShell : Set WshShell = CreateObject(“WScript.Shell”)
Const LogFile = “C:\VBScript\FTP.log”
‘Declares the log file.
Set FSO = CreateObject(“Scripting.FileSystemObject”)
Dim strFileName : strFileName = “C:\VBScript\holdings.csv”

‘Checks to see if the file is ready.
If not Fso.FileExists(strFileName) then
‘File is not ready exit out and wait
Set WshShell = WScript.CreateObject(“WScript.Shell”)
RunString = “%comspec% /c echo ************************ “ & WeekDayName(WeekDay(Now), True) & ” “ & Now & ” FILE NOT READY ************************ “
Return = WshShell.Run(RunString & ” >> ” & LogFile & ” 2>&1″, 0, TRUE)
WScript.quit
Else

RunCmd

Sub RunCmd()
Set WshShell = WScript.CreateObject(“WScript.Shell”)
RunString = “%comspec% /c echo ********************************************** “
Return = WshShell.Run(RunString & ” >> “ & LogFile & ” 2>&1″, 0, TRUE)
RunString = “%comspec% /c echo “ & WeekDayName(WeekDay(Now), True) & ” ” & Now & “, “& strFileName & ” is available – sending via FTP”
Return = WshShell.Run(RunString & ” >> “ & LogFile & ” 2>&1″, 0, TRUE)
RunString = “%comspec% /c echo ********************************************** “
Return = WshShell.Run(RunString & ” >> “ & LogFile & ” 2>&1″, 0, TRUE)
‘FTP the file to client
RunString = “%comspec% /c %WINDIR%\System32\ftp.exe -i -n -s:C:\VBScript\instructions.txt “
Return = WshShell.Run(RunString & ” >> “ & LogFile & ” 2>&1″, 0, TRUE)
RunString = “%comspec% /c echo ********************************************** “
Return = WshShell.Run(RunString & ” >> “ & LogFile & ” 2>&1″, 0, TRUE)
RunString = “%comspec% /c echo FINISHED: “ & WeekDayName(WeekDay(Now), True) & ” “ & Now
Return = WshShell.Run(RunString & ” >> “ & LogFile & ” 2>&1″, 0, TRUE)
RunString = “%comspec% /c echo ********************************************** “
Return = WshShell.Run(RunString & ” >> “ & LogFile & ” 2>&1″, 0, TRUE)
Set WshShell = Nothing
End Sub
End

Basically this script will check to see if a file exists and if it does it will send the file via FTP to a remote location and log all the output to a log file.   This is a good simple script to quickly and repeatedly perform a task and log it.