Feeds:
Posts
Comments

Archive for the ‘System Admin’ Category

October 2015 was declared National Cyber Security Awareness Month by President Obama as a way to educate both the private and and public sectors about the importance of Cybersecurity.

What is Cybersecurity?  In a nutshell Cybersecurity is a managing cyber risks; measures taken to protect a computer or computer system against unauthorized access or attack.

In today’s interconnected world the internet is a part of almost all aspects of your daily life, whether you realize it or not.  We all have Facebook, LinkedIn, Twitter, and Instagram accounts.  Almost all of us have online banking or some sort of personal information (PI) stored online.  Perhaps, prior to a visit to a new doctors office you had to fill out a medical history in an online web portal.

At home or at your place of work you may get phishing emails asking you to click on a link to update your bank account information or to confirm your Facebook password.  You could even get a phone call from ‘Microsoft’ telling you that your personal computer has been infected with a virus and that they need remote access to help you fix the problem.

All of these and the many other ways we interact online are subject to constant attack by unscrupulous ‘hackers’.

Here are some basic rules to follow to promote good online safety habits.

Always Keep a Clean PC

  1. Keep security software up to date:  Having the latest security software, web browser and operating systems are the best defense against viruses, malware and other online threats.
  2. Automate Software updates: Many software programs can automatically update themselves to defend against known risks.  Make sure you enable these automatic updates if the option is available.
  3. Protect all devices that connect to the Internet:  Computers are not the only devices that need protection. Smart phones, gaming systems, and other web-enabled devices also need protection from viruses and malware.  Make sure to regularly check with manufacturers to ensure that you have applied the latest patches and updates for each device.
  4. Plug and Scan:  USB drives, memory sticks, and other external devices can be infected by viruses and malware.  Use your security software to automatically scan these devices when plugged into your computer.

Protect your Personal Information

  1. Secure your accounts: Look for multi-factor authentication.  Many account providers now offer additional ways for you verify who you are before you conduct business on that site.  It could be a secondary image, or a security question or even a single use token code.  If it is available make sure you enable it for additional security.
  2. Make passwords long and strong: A longer password using a seemingly random combination of capital and lowercase letters with numbers and symbols is more secure and harder to crack.
  3. Don’t reuse passwords: Separate passwords for every online account helps to thwart cybercriminals.
  4. Keep your passwords safe: More complex passwords are easier to forget. If you must keep a list, store it in a safe, secure place away from your computer.
  5. Be responsible with your online presence:  You should always review your privacy and security settings for all online accounts.  Set the levels to a level you are comfortable with for sharing personal information.  It is far better to limit with whom you share information than to grant everyone access.

Connect Carefully

  1. When in doubt, throw it out: Links in email, tweets, posts, and online advertising are often the way cybercriminals compromise your computer. If it looks suspicious, even if you know the source, it’s best to delete or if appropriate, mark as junk email.
  2. Wi-Fi hotspots and you:  When you are connecting to Wi-Fi hotspots in public places you should always limit the type of online activities and business you conduct.  Make sure the security settings on your device limit the access to your machine.
  3. Protect your finances: When shopping or banking online, check to make sure that the site is secured with a valid SSL certificate.  Make sure the web address starts with with “https://” or “shttp://”, which means the site takes extra measures to encrypt your information. A web address that starts with “http://” is not secure.

Web Wisdom

  1. Back it up: Protect your valuable work, photos, music or other digital information by making sure you have a copy and storing it safely.  You could use the operating systems built in tools or third party applications to back up your computer or device and store it on another electronic medium.
  2. Think before you act:  Be wary of communications that implore you to “ACT NOW”, or offers something that sounds too good to be true.  Another red flag is anything that asks for personal information.
  3. Stay Current:  Always keep abreast of the latest security information to ensure that you know the newest ways to stay safe online.  Share this information among friends, family and colleagues.

The U.S. Department of Homeland Security has a dedicated section on their website for Cybersecurity (DHS Cybersecurity) with sections for all sorts of different areas of focus.

Another great place for resources and tips is the website StaySafeOnline.org they have sections on personal security, as well as business resources.

By using common sense and a little ‘healthy caution’ you should be able to stay safe while being online.

Read Full Post »

I recently got involved with a new website project and was trying to determine the best set up for our situation. I needed a Content Management System (CMS) so the end user could make the changes to the site without my intervention. So, I started looking into different solutions. My first attempt was DotNetNuke community edition. Since I am familiar with both MS SQL and ASP.NET I thought this would be the most logical choice. So, I set up a virtual machine running Windows Server 2008 R2 and set to work configuring DotNetNuke.

After a bit of wrangling I was able to get the site up and running with the DNN sample site running. It was pretty slick but there were a lot of confusing configuration settings. I was able to get a basic sample site running and let the end user mock up a few pages. It worked but it wasn’t great.

So, I remembered that I also have a BlogEngine.net site running on MSSQL and ASP.NET so I looked into that as well. Again not bad, but nothing I would say I absolutely loved.

Now, I am setting up another virtual machine and going through the steps of setting up a Windows installation of WordPress.

I found a great step by step tutorial from someone else that has helped me get up and running. http://www.vsysad.com/2012/04/create-a-wordpress-blog-on-windows-server-2008-r2-iis-7-5-and-mysql/

Now that the site is up and running I can choose a template and begin to customize the site.

So far, I have to say I am more comfortable with WordPress as a CMS and it seems easier to work with than the other systems I have tried. Of course the next big trick would be to create my own custom theme.

Read Full Post »

This past weekend I was rebuilding a storage array to increase the storage capacity and also reconfigure the drives to optimize the arrangement for better performance on our SQL Server.  The original arrangement was two Dell PowerEdge 1950 servers connected to a  Dell MD3000 Storage array.  This setup was using Windows Server 2003 Clustered for High Availability.  We had a single Raid 5 array in the MD3000 and on it were three partitions, one for the Quorum, One for Data and one for Log files.

Our new configuration was designed to increase the drive space and also split off the partitions onto separate raid arrays.  So, we created a RAID 10 for the Log Files, a RAID 5 for the Databases, and then another Raid 10 which contained the Quorum partition and also a partition for the Temp DB.

When we brought up the server after the reconfiguration we discovered that the Cluster Service would not start.  This was due to the fact that the Quorum drive had been moved and was no longer at the location that Cluster Service expected it.

After several minutes of searching I found this TechNet article http://technet.microsoft.com/en-us/library/cc738770(v=ws.10).aspx.  Basically, in order to start up the Cluster Service without the Quorum resource you need to edit the service and put in a switch.

Edit the ClusterService and add “/fixquorum” as a startup parameter and then start the service.  Once the service has started up with the /fixquorum option you can then run clusterrecovery.exe.  Clusterrecovery.exe will allow you to substitute a new disk for the Quorum disk.  Once you have done that and its replaced the quorum disk you will then be able to restart your ClusterService with the /fixquorum option and it should start up correctly.

Cheers!

Read Full Post »

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.

 

 

Read Full Post »

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.

Read Full Post »

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. 

Read Full Post »

Using sFTP in SQL Server SSIS

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.    

Read Full Post »

Older Posts »