Understanding DELETE Statements

After you are comfortable with selecting information and isolating it, I would then recommend moving on to DELETE statements.  You must be cautious when running a delete statement as this will remove data from your system.  Because you will be making a change to your data, I always recommend getting into the practice of creating a backup before running any statement that will alter your data.  You may think, I am extremely careful and this is unnecessary.  I used to think this and then accidently delete an entire table with a poor query.  The only way I was able to fix my mistake was by having a backup of the system.

Typically, I would create a select statement for the information that I want to delete and then replace SELECT * FROM table WHERE Condition with DELETE table WHERE Condition.  In Microsoft Dynamics GP I will typically use the Column Dex_Row_ID to narrow my results when I only want to remove one line.  This is because Dex_Row_ID is a unique key and does not duplicate in any of the tables.

SELECT BACHNUMB, MKDTOPST, Dex_Row_ID FROM SY00500 WHERE DEX_ROW_ID=’5′

Would then be changed into a delete statement with the following

DELETE SY00500 WHERE DEX_ROW_ID=’5′

When using a select statement and a delete statement in the same query window, it is best to also use two – in front of a statement that you do not want to run right away.  This can assist with accidently deleting the wrong row or table.

Top Tips from This Post

  1. Always make a backup before running queries that alter data.
  2. Isolate the information you want to delete with a select statement.
  3. With Microsoft Dynamics GP, the Dex_Row_ID Column is great to isolate information.
  4. Use — before delete statements to keep the statement from accidently running
  5. In Microsoft Dynamics GP it is recommended to delete records through the system if possible and only manually remove records via SQL as a last result.

Understanding the SQL Query Window

Knowing where to look to run a query is also extremely helpful to getting started.  There are three locations within SQL Server that you need to be aware of to get started.

Post 20.1.png

  1. You can select New Query and this will bring up a new window to type your SQL query.
  2. You will see a box appear under the new query with the name of one of the databases. Whatever is in this box is the database that the query will run against (except if specifies otherwise in your query).
  3. The third box is the name of the databases. If you right click on your database that you want to pull information from and select New Query.

Tips:

  • You can highlight part of a query and it will not run the entire script, only what is highlighted.
  • You can select F5 or the execute button in box 2 to run the query.
  • Adding – before a query will make it not able to run. This is useful if you have a select statement and a delete statement in same query window and don’t want the delete statement to run yet.

 

 

Security Mishap Friday: Hackable Passwords Part I

Password security is something everyone always hears about.  Make sure that you have a strong password.  Weak passwords are hackable.  What I find that is not mentioned enough is what constitutes as a strong password is constantly increasing as hackers get better.  15 years ago they recommended adding uppercases to lowercases.  10 year ago they recommended adding numbers.  5-10 years ago they also recommended adding in symbols.  The definition of a strong password keeps shifting.

This is typically not a problem if you keep up with the latest requirements/recommendations for passwords.  Additionally, humans seem to be psychologically wired to make passwords easier to remember.  This is typically by using words that have some meaning to them.

My favorite exercise is to make a sentence that means something to you.  With the sentence, you would then take the first and/or second letters of the words.  Capitalize some, replace some with numbers or symbols.

If you are not that creative and don’t want to remember the password.  You are in luck!  There are solutions for these two issues.  First, there are password generators that will create a password for you.  The two I like to use the most are LastPass Password Generator and Norton Password Generator.

The next problem comes from remembering the passwords.  It is not recommended to reuse passwords, because once they crack one then they will crack all of your accounts.  Creative companies have created an automatic solution for this.  Dashlane and Last Pass 4.0 Premium are two password vault solutions that store your passwords in an encrypted vault.  These two options are nice as they both offer a cloud solution that you can access your passwords anywhere on any device.

Proactive Not Reactive: Top 10 ideas to Protect Yourself From CryptoLocker

1. User Training
This is probably the biggest item. The worst aspect of CryptoLocker is that they are not actively hacking your network. They are sending spam e-mail or having your user’s go to websites that are not for work purposes and download the installer without them seeing it. Train your users to be wary of emails from senders you don’t know and not to go to non-work sites.

2.  Apply a spam filter to your exchange that blocks executable files being attached
This will keep them from accidently activating an executable. They can still receive zip files and will have to know not to extract or unzip them.

3.  Consider Software Restriction Policies and Whitelisting
Locking down what your users can and cannot install will assist with prevention. Also, disable hidden file extensions in Windows to keep them from unintentionally installing something.

4.  Apply a Firewall
Restrict outbound traffic only on ports that you actually use. Apply content filters to keep people honest on the types of sites that they are going on at work. Clock known malware domains.

5.  Apply good antivirus.
I would recommend that any antivirus you do choose has heuristic scanning abilities as these seem to be the best at locating when Cryptolocker is active in a system. Heuristic scanning looks for typical patterns of behavior. Such antivirus includes Kaspersky, Eset, Webroot, etc. Please do your research and find the right antivirus for your organization.

6.  Apply correct security to everyone in your network
If everyone is a domain admin then this provides the keys to the kingdom to all your users. If one of them gets CryptoLocker, then you are essentially giving that virus full range to your network. Give limited domain access to users and restrict access to vital points on your network, such as your SQL server. Also, restrict who is a local admin on the computers.

7.  Strong passwords
Newer versions of CryptoLocker are able to guess at simple passwords. If you only have capitals and lowercase letters then this can be hacked within a short period of time. In 2013. Hackers were able to crack 16-character passwords in less than an hour, to view details of the article go here. It is recommended to make your passwords vary and have capital, lowercase, numbers and atypical symbols (Such as %, &, ^, }). Normally users select ! or @. These are common and more likely to be guessed. The more complicated you make your password, the better your network security will be.

8.  Regularly clean up old logins
Let’s say your users follow your password policy. What about an old login that has been sitting there for years before the password policy has been in place? This is a hacker’s dream and they will take full advantage. I recommend putting a practice into place that cleans up old logins every 3-6 months.

9.  Backup, backup, Backups!
Backups that are on their own backup device is best. You want to backup all of your critical files. Have shares for each user and train them to store their critical files on the server. I tell clients that if it will take more than a week to recreate (if they are able to recreate the file), then to place it on your personal share where it is backed up by the company. Clients that have had CryptoLocker and a backup system were back up and running with minimal downtime.

10.  Apply 3rd party programs that act as preventions
CryptoPrevent Malware Prevention or CryptoLocker Tripwire I have not personally used these two. However, have heard good reviews for the both. I would not rely solely on either and would add them as an additional layer in addition to your antivirus.

Please never pay the ransom. Every time these criminals receive a ransom it only encourages them to target another company or person and continues the cycle.

CryptoLocker and Microsoft Dynamics GP

CryptoLocker is the boogeyman of the IT world.  It is a silent predator until you get an error that says your file is not legible.  It affects a multitude of programs.  It also affects Microsoft Dynamics GP.  Typically, when a computer is a victim of CryptoLocker and has Microsoft Dynamics GP installed it will get an error stating that the dictionary is not loaded.  What has occurred is your Reports Dictionary location has been encrypted and GP will not launch with an encrypted dictionary.

There are several options depending on your setup and preparedness for CryptoLocker about what this means for your data with Microsoft Dynamics GP.

  1. If your Microsoft Dynamics GP’s data is stored on your SQL server and there is limited access to this server. This will minimize risk of the virus spreading to your SQL server.  Meaning the SQL server is separate from the desktops or Terminal Servers.
  2. There are exceptions for businesses that don’t lock down access to their SQL server or do not have a separate machine for their SQL instances. Some companies run SQL express and Microsoft Dynamics GP on the same computer with an external hard drive backup plugged into the computer.  This is the most vulnerable of the setups as CryptoLocker will encrypt and lock down everything the user has access to.
  3. If the user gets CryptoLocker on their local desktop and the user remotes into a terminal server, typically you will not see the virus on the terminal server. This depends on the access the users has in the network.

Steps to take once you realize you have CryptoLocker. 
Once CryptoLocker is discovered there are several steps that I take.

  1. I disconnect all network drives and remove the computer from the network.
  2. I then check the network locations for any damage and see what needs to be restored from a backup
  3. Verify that the user is not a domain administrator. If they are then you need to check the entire network to see what the possible damage may be.
  4. Check all computers in the network for variations of the CryptoLocker files. Depending on the size of the network, I ask managers to assist going to computer to computer to locate all possible infections.
  5. Analyze the problem. It is imperative to analyze the damage done by CryptoLocker.  Where you find the virus may not be where it is originating.  Several newer variations of crypto locker have become more intelligent and have given their bots the ability to hack throughout the networks by guessing simple passwords.
  6. Isolate the malware and then create your plan of action to remove it from your network. You can run scans to remove the virus and after it is removed, you can put the computer back on the network.  I like to use SuperAntiSpyWare, Malwarebytes, Eset Online Scanner.  In extreme cases, we had to complete a complete wipe and reload of the OS.
  7. After the malware is removed from the network, you can then proceed with restoring data from your backups. You don’t want to start restores until you can verify the malware is completely gone.  I made the mistake once of not doing this and the malware encrypted the restored items as we were in the process of restoring the data.

Security Mishap Friday: The Fake Wi-Fi Scam

Let’s face it, people like free items.  Free is amazing.  You get something for nothing.  In the case of most businesses, they get more patrons coming into their venue and staying longer when they offer this server.  People stick around longer and potentially spend more money.  Seems like a win, win.

In all reality, free Wi-Fi should be treated as a creepy stranger offering a child a swirly lollypop.  It’s enticing.  Just within your reach.  It also has the unforeseen consequence of stealing your life away.

There are two common ways that hackers accomplish this. The first is that they hack the free Wi-Fi and view all the connections currently connected to the Wi-Fi connection. Why go through all that trouble.  People are trusting.  If they see a free Wi-Fi connection at a business they will assume that it is offered through the business.  In highly congested areas with many cafés or shops, there could be many options that show up on the Wi-Fi connection search.  Because of this aspect of social engineering, many hackers do not bother to hack into a network, they will simply create their own.

Once they create their own free Wi-Fi connection, all they have to do is sit with a cup of coffee until someone connects to it.  Once their victim is connected, they have access to everything they are doing on their device.

What you can do to avoid being a victim

  1. Don’t connect to a public Wi-Fi (not always possible)
  1. If you have Xfinity, then you can connect to the Xfinity anywhere. Xfinity offers secure connections to all their customers to connect through other routers by logging in with their Xfinity accounts. Other companies may offer similar user experiences.
  1. Don’t access banking or financial information while connected to a public Wi-Fi. Even if you turn off your network discovery they can still use packet sniffers to see what you are accessing.  Don’t access anything that you would want someone to see.
  1. Secure Access to your computer. Go to Control Panel >> Network and Sharing Center>>Change Advanced Sharing Settings.  Verify that network discovery is turned off for Guest or Public

post_netword-discovery

SQL Saturday

Learning SQL and becoming involved in the technical community can be difficult if you do not know where to look.  A fantastic event that encourages people of all ability levels is through PASS.  PASS is an independent, not-for-profit organization run by and for the community.

One event that they offer for each of their communities is SQL Saturday.  This event takes an entire Saturday for free training sessions that varies in topics.  The sessions are offered once a year per area and have a variety of locations around the work.  They can include technical sessions lead by many local speakers.  I encourage checking out the calendar to see when it is offered in your area.   Sessions are available all around the world.

If you are interested in becoming a DBA, please consider attending an event near you and supporting your community.