# How to fix “your model is not manifold” error in Cura

You just pulled a file from Thingiverse and now when you try to slice it in Cura it says “your model is not manifold”.  If you just want to make the error go away you can skip to the paragraph “The Fix”  if you don’t want to be “filled in” on why it’s happening in the first place. Little bit of 3D printing humour there for ya.

So what does the error mean?

For a model to be manifold you can think of it as having logically enclosed space in a manner that can exist in real life with an outer geometry that can actually be 3D printed.

So what does non-manifold mean?

There’s a couple of reasons why a model is not manifold and here they are:

• Self-intersecting
• Separate Object
• Hole
• Inner Faces
• Overlapping Geometry

Self-intersecting: This is a bit of a weird analogy but imagine punching through yourself. You can’t do that in real life without making a big hole but in a virtual model of yourself you can have the objects of your fist and forearm pass through another body part of your choosing and that’s perfectly fine but in real life you can’t have two objects with mass occupy the same space at the same time so this cannot be printed.

Separate Object: Imagine a model of a figurine wearing sun glasses. If the sun glasses and the figurine were two separate objects and you shrank the figurine by rescaling it to 90% its original size the sun glasses might be left floating in midair. That’s fine for a virtual 3D model but in real life gravity might have something to say about that.

Hole: Pretty self explanatory, there’s a hole in the model and not like a window just a void that makes the model impossible to print successfully.

Inner Faces: Imagine trying to print a model within a model. The slicer reads the code and gets confused because there should only be one outer surface area not two.

Overlapping Geometry: Imagine you have created a 3D model of a house and you’ve accidentally copied the roof and then pasted it back on top of the model over the original roof. The model now has two roofs occupying the same space which cannot be printed.

The Fix:

Ideally you should open the file with some 3D modelling software and fix it manually but if you’re just pulling files from Thingiverse that’s a bit unrealistic. Luckily the following site allows you to upload files and it will try to fix them automatically.

https://3d-print.jomatik.de/en/index.php

If the process successfully fixes the file it will give you the option to download the file with a brief summary of what changes it made highlighting big changes in red.

Its a great solution especially for low risk models but the onus will always be on you to manually inspect the model to see if the problems have in fact been resolved. Also if you’re working on a super secret product design for a company probably best not to upload the model to be fixed online, but for files you’ve pulled from Thingiverse sure why not they’re already publicly available anyway.

# How to use a generic PC controller with GTA 5

So you plugged in the cheap PC controller you bought off eBay or Amazon (say one that is coincidentally shaped like an Xbox controller) and found it doesn’t work with GTA 5?

This is probably because the controller is using the DirectInput standard as opposed to the newer XInput standard. You can read more about these standards from Microsoft by clinking on this link.

GTA 5 (or GTA V if you’re feeling fancy) was not optimised to use the DirectInput standard unfortunately. However if you’re playing the game on PC you should be using a keyboard and mouse like a grown-up anyway, it’s way better for shooters. Ah but GTA isn’t all about the shooting I hear you say and you’re right. I’ll admit I switch to a controller for flying vehciles because they are horrible to pilot with direction keys. Analogue sticks are much better suited to aircraft.

So the workaround for being able to use the cheap generic controller is quite simple but requires using an “Xbox 360 Controller Emulator”.

Download the file x360ce_x64 from the following site by clicking on the “download for 64 bit games” button at the top of the screen.

https://www.x360ce.com/

(Be sure to test the downloaded file with whatever antivirus software you have installed)

Once you are satisfied the software is safe extract the file to the root directory of where you installed GTA 5.

(If you do not know where that folder is try searching GTAV in your Windows search bar. Note a folder called GTA V is often created in the Documents folder but this is not the correct directory.  The correct directory will have application files with the GTAV logo in it. The game maybe in a Rockstar folder or perhaps a Steam or Epic folder, it all depends on who you bought it from.)

Right click on x360ce_x64 and run the file as administrator and you should be given an option button to “Create”.

Click this button and a Xbox controller calibration window will open.

At this point if your controller has a large circular button at its center press it and make sure it lights up otherwise it may not send the right signals when the controller is being mapped.

Click “Auto” and then “Save”.

I found the A, B, X and Y face buttons were not mapped correctly and needed to be mapped manually.

To do this, beside each face button on the emulator interface there is a drop down menu. Click on it for each button and choose the option to record. The interface will highlight which button to press on your controller to map it correctly. Once the buttons are mapped correctly click “Save” again.

After following these steps you should now be able to play GTA V with the controller.

# How to secure Creality Ender 3 Pro TL-Smoothers once installed

This post will outline how to secure TL-Smoothers within the Creality Ender Pro circuit housing without blocking vents.

Prerequisites:

You will need insulation tape and double-sided sellotape to following the instructions.

What are TL-Smoothers?

A TL-Smoother, as displayed below, is an add-on circuit module for 3D printer stepper motor drivers. They seek to lower vibration, lower noise and provide a smoother result by way of cleaning up electrical signals. Their use is intended to compensate for less than premium motor control circuits that are used in more budget 3D printers.

(Note: there is an ongoing debate as to whether TL-Smoothers have any benefit on print quality for the Ender 3 Pro. This post will not cover this topic or the installation process as a whole. This post simply puts forth advice regarding good placement and how to secure the circuits.)

Steps:

The four pin sockets of the TL-Smoothers protrude on the back side of the circuit.

Clip the ends off of these protrusion using the electrical wire cable cutters, provided with the printer, leaving the back of each circuit flat.

Cover the four pins on either end of each circuit with insulation tape, this will prevent shorts.

Cut a small square of double-sided tape and secure it to the back of each circuit as shown below.

Secure the circuits as demonstrated below using the double-sided tape. These locations will ensure everything will fit inside the housing while leaving enough space for the fan. The green square in the picture marks where the fan will be situated when the housing is closed back up.

Happy Printing. ☮

# How to create a console application in C# that will solve crosswords

This tutorial will cover the following tasks in C#:

• How to count the characters in a string
• How to assign a file’s directory location to a variable
• How to create a list variable
• How to pull/read a CSV file column into a list variable
• How to clean strings using Regex to remove non alpha numeric characters as the strings are being read into a list
• How to remove duplicate word entries from a list
• How to order a list
• How to write variables to the console, including a list’s elements

Assumptions:

You already know how to create projects in Visual Studio.

If you do not how to do this search online using the following term “how to create C# console applications in visual studio”.

Prerequisites:

First you will need to generate a CSV file with random words using this site:

https://onlinerandomtools.com/generate-random-csv

For the option “how many columns to generate” set the value to 1.

For testing purposes create 1000 rows.

Download the csv file generated and save it using the name “words”.

Summary of how the code works:

The code works by reducing the initial list (i.e. the supplied CSV file of random words) down to only words that match the number of characters of the user word, typically referred to as “string length”.

Once that subset of words has been created the code will then compare the user word’s letters against each letter, referencing the relative position, in each word in the subset.

Note: there is still significant room for optimization but the code is functional and works well as an accessible, human readable tutorial.

Use case example:

If the user enters the word “apple” the dictionary subset will be reduced down to 5 letter words only. These five letter words are then compared to the user word, each word and letter at a time. So if the first word in the list was “cabin” the comparison would jump to the next word in the list as the “a” in “apple” does not match the “c” in “cabin”. If the next word in the dictionary was “acorn” the first letters would match but the comparison would jump to the next word when the “c” and “p” did not match.

Instructions:

Create a C# console application called CrosswordSolver in Visual Studio.

Move the CSV file called “Words” into the bin directory of the project folder, i.e. CrosswordSolver\CrosswordSolver\bin

Open the project CrosswordSolver and paste the C# code below into the default window replacing the default cs page code.

The hardcoded example of a user word is:

string userWord = “a****”;

The user can use * to represent characters unknown, for example ap*le.

Note: The CSV file you randomly generated may have no examples of 5 letter words begining with the letter “a” so experiment with other characters.

You can test the letter comparison functionality by uncommenting the two sections of code immediately following the comments “Test letter comparison”.

To test your CSV file has been read into memory you can uncomment the section of code immediately following “Test that dictionary has been read into memory”.

The C# code:

```using System;
using System.IO;
using System.Linq;
using System.Collections.Generic;
using System.Text.RegularExpressions;

namespace CrosswordSolver
{
class Program
{
static void Main(string[] args)
{
int c = 0;
//User input
//NOTE: Use * to represent characters unknown
string userWord = "a****";
int wordLength = userWord.Length;

//Assign directory location of the csv file containing the collection of words to a variable
string projectFolder = Directory.GetParent(Directory.GetCurrentDirectory()).Parent.FullName;
string file = Path.Combine(projectFolder, "words.csv");

//Display dictionary location in console
Console.WriteLine("Dictionary location: " + file);

var dictionary = new List<string>();
using (var rd = new StreamReader(file))

//Pull file column into dictionary list without cleaning
//{
//    while (!rd.EndOfStream)
//    {
//    }
//}

//Pull file column into dictionary list while cleaning
{
while (!rd.EndOfStream)
{
//string clean is done with Regex
}
}

//Test that dictionary has been read into memory
//Console.WriteLine("The dictionary contains the following words:");
//foreach (var element in dictionary)
//Console.WriteLine(element);

//Remove duplicate word entries
//c = dictionary.Count;
//Console.WriteLine("The dictionary contains " + c + " words");
dictionary = dictionary.Distinct().ToList();
//c = dictionary.Count;
//Console.WriteLine("The dictionary contains " + c + " words");

// Count the elements in the List and display test parameters
c = dictionary.Count;
Console.WriteLine("The dictionary contains " + c + " words");
Console.WriteLine("User entered the string: " + userWord);
Console.WriteLine(userWord + " has " + wordLength + " characters");
userWord = userWord.ToLower();

//Reduce the dataset size based on number of characters in string
IEnumerable<string> query =
dictionary.Where(word => word.Length == wordLength);

var subSet = new List<string>();
foreach (var word in query)

//Order List
subSet = subSet.OrderBy(x => x).ToList();

c = subSet.Count;
if (c != 0)
{
Console.WriteLine("The dictionary contains " + c + " words that are " + wordLength + " characters in length");

//Begin character and position match check
var result = new List<string>();
foreach (var word in subSet)

{
for (int i = 0; i <= wordLength - 1; i++)
{

if ((word.ToLower()[i] == userWord[i]) | (userWord[i] == '*'))
{

//Test letter comparison (Letters match)
//Console.WriteLine(
//"Letter " + i + ", which is " + "\"" + word[i] + "\"" + ", of the word " + "\"" + word + "\"" +
//" matches letter " + i + ", which is " + "\"" + userWord[i] + "\"" + ", of the user input " + "\"" + userWord + "\""
//);

if (i == wordLength - 1)

}
else
{
//Test letter comparison (Letters do not match)
//Console.WriteLine(
//"Letter " + i + ", which is " + "\"" + word[i] + "\"" + ", of the word " + "\"" + word + "\"" +
//" does not match letter " + i + ", which is " + "\"" + userWord[i] + "\"" + ", of the user input " + "\"" + userWord + "\""
//);

break;
}
}
}

//Test words that do not match
//foreach (var word in subSetToRemove)
//Console.WriteLine(word);

bool isEmpty = !result.Any();
if (isEmpty)
{
Console.WriteLine("No matches found");
}
else
{
c = result.Count();
Console.WriteLine("Potential matches found: " + c);
foreach (var word in result)
Console.WriteLine(word);
}
}
else
{
Console.WriteLine("No words of " + wordLength + " characters long found");
}
}
}
}
```

If you found this code useful be sure to like the post and comment. ☮

If you would like to know how to create a csv file with C# see this tutorial link.

If you would like to know how to create a console application in Visual Studio that won’t open a command window when it runs see this tutorial link.

# How I passed The CompTIA CySA+ exam

I did the exam back at the start of January 2020 and as someone who is not from a security or networking background I did find it difficult.

To put it bluntly there’s no point even attempting the exam without knowing ports, all the relevant security software (including the various common commands) and the IT security frameworks.

I found the Jason Dion Udemy course very helpful. (FYI not affiliated in anyway) The course however should only be considered supplementary. You should be doing your own research and researching the topics and terms within the course you think weren’t covered in as much detail as they could have been.

Most importantly of all do practice exams. There are several floating around on the internet and Jason Dion has an additional set of his own on Udemy.

As part of my research I prepared a list of definitions which are shared below. This is just a big dump of text but worth giving a few minutes of your time to freshen up on acronyms. (My apologies to whoever prepared the source material, I never took note where it came from for the sake of references.)

Internet Protocol (IP) is the principal set (or communications protocol) of digital message formats and rules for exchanging messages between computers across a single network or a series of interconnected networks, using the Internet Protocol Suite (often referred to as TCP/IP). Messages are exchanged as datagrams, also known as data packets or just packets.

IP is the primary protocol in the Internet Layer of the Internet Protocol Suite, which is a set of communications protocols consisting of four abstraction layers: link layer (lowest), Internet layer, transport layer and application layer (highest).

The main purpose and task of IP is the delivery of datagrams from the source host (source computer) to the destination host (receiving computer) based on their addresses. To achieve this, IP includes methods and structures for putting tags (address information, which is part of metadata) within datagrams.

Think of an anology with the postal system. IP is similar to the U.S. Postal System in that it allows a package (a datagram) to be addressed (encapsulation) and put into the system (the Internet) by the sender (source host). However, there is no direct link between sender and receiver.

The package (datagram) is almost always divided into pieces, but each piece contains the address of the receiver (destination host). Eventually, each piece arrives at the receiver, often by different routes and at different times. These routes and times are also determined by the Postal System, which is the IP.

However, the Postal System (in the transport and application layers) puts all the pieces back together before delivery to the receiver (destination host).

Note: IP is actually a connectionless protocol, meaning that the circuit to the receiver (destination host) does not need be set up before transmission (by the source host). Continuing the analogy, there does not need to be a direct connection between the physical return address on the letter/package and the recipient address before the letter/package is sent.

Originally, IP was a connectionless datagram service in a transmission control program created by Vint Cerf and Bob Kahn in 1974. When format and rules were applied to allow connections, the connection-oriented Transmission Control Protocol was created. The two together form the Internet Protocol Suite, often referred to as TCP/IP.

Internet Protocol version 4 (IPv4) was the first major version of IP. This is the dominant protocol of the Internet. However, iPv6 is active and in use, and its deployment is increasing all over the world.

Addressing and routing are the most complex aspects of IP. However, intelligence in the network is located at nodes (network interconnection points) in the form of routers which forward datagrams to the next known gateway on the route to the final destination. The routers use interior gateway protocols (IGPs) or external gateway protocols (EGPs) to help with making forwarding route decisions.

Routes are determined by the routing prefix within the datagrams. The routing process can therefore become complex. But at the speed of light (or nearly so) the routing intelligence determines the best route, and the datagram pieces and datagram all eventually arrive at their destination.

The process of putting these tags on datagrams is called encapsulation.

How TCP Works

Transmission Control Protocol (TCP) is the most commonly used protocol on the Internet.

TCP is all about reliability—packets sent with TCP are tracked so no data is lost or corrupted in transit. This is why file downloads don’t become corrupted even if there are network hiccups. Of course, if the recipient is completely offline, your computer will give up and you’ll see an error message saying it can’t communicate with the remote host.

TCP achieves this in two ways. First, it orders packets by numbering them. Second, it error-checks by having the recipient send a response back to the sender saying that it has received the message. If the sender doesn’t get a correct response, it can resend the packets to ensure the recipient receives them correctly.

User Datagram Protocol (UDP) is used by apps to deliver a faster stream of information by doing away with error-checking. When configuring some network hardware or software, you may need to know the difference.

How UDP Works

The UDP protocol works similarly to TCP, but it throws out all the error-checking stuff. All the back-and-forth communication introduces latency, slowing things down.

When an app uses UDP, packets are just sent to the recipient. The sender doesn’t wait to make sure the recipient received the packet—it just continues sending the next packets. If the recipient misses a few UDP packets here and there, they are just lost—the sender won’t resend them. Losing all this overhead means the devices can communicate more quickly.

UDP is used when speed is desirable and error correction isn’t necessary. For example, UDP is frequently used for live broadcasts and online games.

For example, let’s say you’re watching a live video stream, which are often broadcast using UDP instead of TCP. The server just sends a constant stream of UDP packets to computers watching. If you lose your connection for a few seconds, the video may freeze or get jumpy for a moment and then skip to the current bit of the broadcast. If you experience minor packet-loss, the video or audio may be distorted for a moment as the video continues to play without the missing data.

This works similarly in online games. If you miss some UDP packets, player characters may appear to teleport across the map as you receive the newer UDP packets. There’s no point in requesting the old packets if you missed them, as the game is continuing without you. All that matters is what’s happening right now on the game server—not what happened a few seconds ago. Ditching TCP’s error correction helps speed up the game connection and reduce latency.

Transport Layer Security (TLS) is a protocol that provides communication security between client/server applications that communicate with each other over the Internet. It enables privacy, integrity and protection for the data that’s transmitted between different nodes on the Internet. TLS is a successor to the secure socket layer (SSL) protocol.

TLS primarily enables secure Web browsing, applications access, data transfer and most Internet-based communication. It prevents the transmitted/transported data from being eavesdropped or tampered. TLS is used to secure Web browsers, Web servers, VPNs, database servers and more. TLS protocol consists of two different layers of sub-protocols:

TLS Handshake Protocol: Enables the client and server to authenticate each other and select a encryption algorithm prior to sending the data

TLS Record Protocol: It works on top of the standard TCP protocol to ensure that the created connection is secure and reliable. It also provides data encapsulation and data encryption services.

Port Descriptions:

File Transfer Protocol (FTP) is a standard network protocol used for the transfer of computer files between a client and server on a computer network.

FTP is built on a client-server model architecture using separate control and data connections between the client and the server.[1] FTP users may authenticate themselves with a clear-text sign-in protocol, normally in the form of a username and password, but can connect anonymously if the server is configured to allow it. For secure transmission that protects the username and password, and encrypts the content, FTP is often secured with SSL/TLS (FTPS) or replaced with SSH File Transfer Protocol (SFTP).

Ports:

• Send File Data Port 20
• Session Info 21

Secure Shell (SSH) is a cryptographic network protocol for operating network services securely over an unsecured network. Typical applications include remote command-line, login, and remote command execution, but any network service can be secured with SSH.

SSH provides a secure channel over an unsecured network in a client–server architecture, connecting an SSH client application with an SSH server. The protocol specification distinguishes between two major versions, referred to as SSH-1 and SSH-2. The standard TCP port for SSH is 22. SSH is generally used to access Unix-like operating systems, but it can also be used on Microsoft Windows. Windows 10 uses OpenSSH as its default SSH client. Port 22.

SSH File Transfer Protocol (SFTP) is a secure file transfer protocol. It runs over the SSH protocol. It supports the full security and authentication functionality of SSH.

SFTP has pretty much replaced legacy FTP as a file transfer protocol, and is quickly replacing FTP/S. It provides all the functionality offered by these protocols, but more securely and more reliably, with easier configuration. There is basically no reason to use the legacy protocols any more.

SFTP also protects against password sniffing and man-in-the-middle attacks. It protects the integrity of the data using encryption and cryptographic hash functions, and autenticates both the server and the user.

SFTP port number is the SSH port 22 (follow the link to see how it got that number). It is basically just an SSH server. Port 22.

Telnet is a computer protocol that provides two-way interactive communication compatibility for computers on the internet and local area networks. User data is interspersed in-band with Telnet control information in an 8-bit byte oriented data connection over the Transmission Control Protocol (TCP). Telnet has a command-line interface and is famous for being the original protocol from when the internet first launched in 1969. Where a telnet application is listening it operates on port 23.

Vulnerability of Telnet

In time, Telnet’s use declined in favor of SSH (Secure Shell or Secure Socket Shell) due to serious security concerns when it was used over an open network. Telnet lacks authentication policies and data encryption.

Simple Mail Transfer Protocol (SMTP) is an internet standard for email supported by most email processing servers. Port 25. SMTPS (Simple Mail Transfer Protocol Secure) is a method for securing the SMTP using transport layer security. It is intended to provide authentication of the communication partners, as well as data integrity and confidentiality. Port 465.

SMTPS is not a proprietary protocol and not an extension of SMTP. It is a way to secure SMTP at the transport layer, by wrapping SMTP inside TLS. Conceptually, it is similar to how HTTPS wraps HTTP inside TLS.

WHOIS (pronounced as the phrase “who is”) is a query and response protocol that is widely used for querying databases that store the registered users or assignees of an Internet resource, such as a domain name, an IP address block or an autonomous system, but is also used for a wider range of other information. The protocol stores and delivers database content in a human-readable format. Port 43.

Terminal Access Controller Access-Control System (TACACS) refers to a family of related protocols handling remote authentication and related services for networked access control through a centralized server. The original TACACS protocol, which dates back to 1984, was used for communicating with an authentication server, common in older UNIX networks; it spawned related protocols:

Extended TACACS (XTACACS) is a proprietary extension to TACACS introduced by Cisco Systems in 1990 without backwards compatibility to the original protocol. TACACS and XTACACS both allow a remote access server to communicate with an authentication server in order to determine if the user has access to the network.

Terminal Access Controller Access-Control System Plus (TACACS+) is a protocol developed by Cisco and released as an open standard beginning in 1993. Although derived from TACACS, TACACS+ is a separate protocol that handles authentication, authorization, and accounting (AAA) services. TACACS+ have largely replaced their predecessors. Port 49.

The Domain Name System (DNS) is the phonebook of the Internet. Humans access information online through domain names, like nytimes.com or espn.com. Web browsers interact through Internet Protocol (IP) addresses. DNS translates domain names to IP addresses so browsers can load Internet resources. Port 53.

Each device connected to the Internet has a unique IP address which other machines use to find the device. DNS servers eliminate the need for humans to memorize IP addresses such as 192.168.1.1 (in IPv4), or more complex newer alphanumeric IP addresses such as 2400:cb00:2048:1::c629:d7a2 (in IPv6).

Dynamic Host Configuration Protocol (DHCP) is a protocol that provides quick, automatic, and central management for the distribution of IP addresses within a network. DHCP is also used to configure the subnet mask, default gateway, and DNS server information on the device. Port 67.

Bootstrap Protocol (BOOTP) is a computer networking protocol used in Internet Protocol networks to automatically assign an IP address to network devices from a configuration server Port 67.

Differences between DHCP and BOOTP:

The major difference between BOOTP and DHCP is that BOOTP support static configuration of the IP addresses while DHCP supports dynamic configuration. It means that DHCP automatically assigns and obtain the IP addresses from the computer connected to the internet and also have some additional features.

https://techdifferences.com/difference-between-bootp-and-dhcp.html

Trivial File Transfer Protocol (TFTP) is a technology that transfers files between network devices and is a simplified version of the more robust File Transfer Protocol. TFTP was developed in the 1970s for computers lacking sufficient memory or disk space to provide full FTP support. Today, TFTP is found on consumer ​broadband routers and commercial network routers. Home network administrators use TFTP to upgrade the router firmware, while professional administrators use TFTP to distribute software across corporate networks. Port 69.

Hypertext Transfer Protocol (HTTP) is an application protocol for distributed, collaborative, hypermedia information systems.[1] HTTP is the foundation of data communication for the World Wide Web, where hypertext documents include hyperlinks to other resources that the user can easily access, for example by a mouse click or by tapping the screen in a web browser. Port 80.

Development of HTTP was initiated by Tim Berners-Lee at CERN in 1989. Development of early HTTP Requests for Comments (RFCs) was a coordinated effort by the Internet Engineering Task Force (IETF) and the World Wide Web Consortium (W3C), with work later moving to the IETF.

Hypertext Transfer Protocol Secure (HTTPS) is an extension of the Hypertext Transfer Protocol (HTTP). It is used for secure communication over a computer network, and is widely used on the Internet. In HTTPS, the communication protocol is encrypted using Transport Layer Security (TLS) or, formerly, its predecessor, Secure Sockets Layer (SSL). The protocol is therefore also often referred to as HTTP over TLS, or HTTP over SSL. Port 443.

Secure Socket Tunneling Protocol (SSTP) is a form of virtual private network (VPN) tunnel that provides a mechanism to transport PPP traffic through an SSL/TLS channel. SSL/TLS provides transport-level security with key negotiation, encryption and traffic integrity checking. The use of SSL/TLS over TCP port 443 allows SSTP to pass through virtually all firewalls and proxy servers except for authenticated web proxies. Port 443.

Kerberos is a computer-network authentication protocol that works on the basis of tickets to allow nodes communicating over a non-secure network to prove their identity to one another in a secure manner. Port 88.

By default, the POP3 protocol works on two ports:

Port 110 – this is the default POP3 non-encrypted port

Port 995 – this is the port you need to use if you want to connect using POP3 securely

Network News Transfer Protocol (NNTP) is the underlying protocol of UseNet, which is a worldwide discussion system which contains posts or articles which are known as news. Network News Transfer Protocol is used to transfer news from one network to another. It is designed specifically to transfer news/articles. A NNTP client is included in browsers like Netscape, Opera and Internet Explorer or a special application named newsreader can be used as a NNTP client. NNTP servers manages the global network of news groups. Port 119.

Network time protocol (NTP) is a networking protocol for clock synchronization between computer systems over packet-switched, variable-latency data networks. In operation since before 1985, NTP is one of the oldest Internet protocols in current use. it has become a key component in security as when you are tracing through logs to pull together events from different devices, accurate time throughout your network is incredibly important. Port 123.

NetBIOS over TCP/IP (NBT, or sometimes NetBT) is a networking protocol that allows legacy computer applications relying on the NetBIOS API to be used on modern TCP/IP networks. Ports 137, 138, 139.

Internet Message Access Protocol (IMAP) is an Internet standard protocol used by email clients to retrieve email messages from a mail server over a TCP/IP connection. IMAP is defined by RFC 3501.

IMAP was designed with the goal of permitting complete management of an email box by multiple email clients, therefore clients generally leave messages on the server until the user explicitly deletes them. An IMAP server typically listens on port 143. IMAP over SSL (IMAPS) is assigned the port 993.

Simple Network Management Protocol (SNMP) is an Internet Standard protocol for collecting and organizing information about managed devices on IP networks and for modifying that information to change device behavior. Devices that typically support SNMP include cable modems, routers, switches, servers, workstations, printers, and more.

SNMP is widely used in network management for network monitoring. SNMP exposes management data in the form of variables on the managed systems organized in a management information base (MIB) which describe the system status and configuration. These variables can then be remotely queried (and, in some circumstances, manipulated) by managing applications.

Ports:

• 161 – SNMP (Agents receive requests)
• 162 – SNMP (Controller receives data)

Lightweight Directory Access Protocol (LDAP) is an open, vendor-neutral, industry standard application protocol for accessing and maintaining distributed directory information services over an Internet Protocol (IP) network. Directory services play an important role in developing intranet and Internet applications by allowing the sharing of information about users, systems, networks, services, and applications throughout the network. As examples, directory services may provide any organized set of records, often with a hierarchical structure, such as a corporate email directory. Similarly, a telephone directory is a list of subscribers with an address and a phone number. Port 389.

LDAPS is a distributed IP directory protocol similar to LDAP, but which incorporates SSL for greater security. Port 636.

Server Message Block (SMB), assoicated software referred to as SAMBA, is a protocol for sharing files, printers, serial ports, and communications abstractions such as named pipes and mail slots between computers. Port 445. Also,137,138,139

Internet Key Exchange (IKE) is a key management protocol standard used in conjunction with the Internet Protocol Security (IPSec) standard protocol. It provides security for virtual private networks’ (VPNs) negotiations and network access to random hosts. It can also be described as a method for exchanging keys for encryption and authentication over an unsecured medium, such as the Internet.

IKE enhances IPsec by providing additional features along with flexibility. IPsec, however, can be configured without IKE.

IKE has many benefits. It eliminates the need to manually specify all the IPSec security parameters at both peers. It allows the user to specify a particular lifetime for the IPsec security association. Furthermore, encryption can be changed during IPsec sessions. Moreover, it permits certification authority. Finally, it allows dynamic authentication of peers. Port 500.

Routing Information Protocol (RIP) is one of the oldest distance-vector routing protocols which employ the hop count as a routing metric. RIP prevents routing loops by implementing a limit on the number of hops allowed in a path from source to destination. The largest number of hops allowed for RIP is 15, which limits the size of networks that RIP can support. Port 520.

FTPS (also known as FTPES, FTP-SSL, and FTP Secure) is an extension to the commonly used File Transfer Protocol (FTP) that adds support for the Transport Layer Security (TLS) and, formerly, the Secure Sockets Layer (SSL, which is now prohibited by RFC7568) cryptographic protocols. Port 989/990.

FTPS should not be confused with the SSH File Transfer Protocol (SFTP), a secure file transfer subsystem for the Secure Shell (SSH) protocol with which it is not compatible. It is also different from FTP over SSH, which is the practice of tunneling FTP through an SSH connection.

SQL TCP port 1433 is the default port for SQL Server. This port is also the official Internet Assigned Number Authority (IANA) socket number for SQL Server. Client systems use TCP 1433 to connect to the database engine; SQL Server Management Studio (SSMS) uses the port to manage SQL Server instances across the network. You can reconfigure SQL Server to listen on a different port, but 1433 is by far the most common implementation. Port 1433.

Layer Two Forwarding (L2F) is a Cisco tunneling protocol that uses virtual dial-up networks for secure data packet transport. L2F functionality is similar to the Point-to-Point Tunneling Protocol (PPTP), which was developed by the Microsoft-led PPTP Forum. Port 1701.

Point-to-Point Tunneling Protocol (PPTP) Port 1723.

L2F creates point-to-point (PPP) network and user connections and allows high-level protocols to create tunnels via the link layer, including High-Level Data Link Control (HDLC) or SLIP frames. These tunnels separate server and termination points to facilitate network access.

In 1999, Microsoft and Cisco merged their respective L2F protocol versions and created L2TP, which is the Internet Engineering Task Force (IETF) tunneling standard.

H.323 is an ITU Telecommunication Standardization Sector (ITU-T) recommendation that describes protocols for the provision of audio-visual (A/V) communication sessions on all packet networks. H.323 provides standards for equipment, computers and services for multimedia communication across packet based networks and specifies transmission protocols for real-time video, audio and data details.

H.323 is widely used in IP based videoconferencing, Voice over Internet Protocol (VoIP) and Internet telephony. Users can communicate through the Internet and make use of a variety of products that are H.323 standard compatible. Port 1720.

RADIUS is a network protocol that is used to authenticate and authorize user access to a remote network. The term, RADIUS, is an acronym that stands for Remote Authentication Dial-In User Service. First introduced in 1991, RADIUS has remained a powerful tool for managing network user access. Port 1812,1813.

Media Gateway Control Protocol (MGCP) is a signaling and call control communications protocol used in voice over IP (VoIP) telecommunication systems. It implements the media gateway control protocol architecture for controlling media gateways connected to the public switched telephone network (PSTN). The media gateways provide conversion of traditional electronic media to the Internet Protocol (IP) network. The protocol is a successor to the Simple Gateway Control Protocol (SGCP), which was developed by Bellcore and Cisco, and the Internet Protocol Device Control (IPDC) Ports 2427, 2727.

Remote Desktop Protocol (RDP) is a proprietary protocol developed by Microsoft, which provides a user with a graphical interface to connect to another computer over a network connection. The user employs RDP client software for this purpose, while the other computer must run RDP server software.

Clients exist for most versions of Microsoft Windows (including Windows Mobile), Linux, Unix, macOS, iOS, Android, and other operating systems. RDP servers are built into Windows operating systems; an RDP server for Unix and OS X also exists. By default, the server listens on TCP port 3389 and UDP port 3389.

Real-time Transport Protocol (RTP) is a network protocol for delivering audio and video over IP networks. RTP is used in communication and entertainment systems that involve streaming media, such as telephony, video teleconference applications including WebRTC, television services and web-based push-to-talk features. Ports 5004, 5005.

The Session Initiation Protocol (SIP) is a signaling protocol used for initiating, maintaining, and terminating real-time sessions that include voice, video and messaging applications.[1] SIP is used for signaling and controlling multimedia communication sessions in applications of Internet telephony for voice and video calls, in private IP telephone systems, in instant messaging over Internet Protocol (IP) networks as well as mobile phone calling over LTE (VoLTE).

Ports:

• 5060 – SIP (unencrypted)
• 5061 – SIP (encrypted with TLS)

PuTTY is a free and open-source terminal emulator, serial console and network file transfer application. It supports several network protocols, including SCP, SSH, Telnet, rlogin, and raw socket connection. It can also connect to a serial port. The name “PuTTY” has no official meaning.

PuTTY was originally written for Microsoft Windows, but it has been ported to various other operating systems. Official ports are available for some Unix-like platforms, with work-in-progress ports to Classic Mac OS and macOS, and unofficial ports have been contributed to platforms such as Symbian,Windows Mobile and Windows Phone.

PuTTY was written and is maintained primarily by Simon Tatham, a British programmer.

Windows Server Update Services (WSUS) provides a cost-effective patch management solution to deploy updates to domain-joined Windows servers and workstation in a corporate network. WSUS is fully integrated in Windows Server 2012 and can be enabled on Windows clients by configuring settings in Group Policy Objects – GPO.

Secure Sockets Layer (SSL) is a standard protocol used for the secure transmission of documents over a network. Developed by Netscape, SSL technology creates a secure link between a Web server and browser to ensure private and integral data transmission. SSL uses Transport Control Protocol (TCP) for communication.

Internet Information Services (IIS) formerly known as Internet Information Server, is a web server producted by Microsoft. IIS is used with Microsoft Windows OSs and is the Microsoft-centric competition to Apache, the most popular webserver used with Unix/Linux-based systems.

Kiwi Syslog Server receives, logs, displays, alerts on, and forwards syslog, SNMP trap, and Windows event log messages from routers, switches, firewalls, Linux and UNIX hosts, and Windows machines.

Adaptive Security Device Manager (ASDM) is a Cisco technology that lets you manage Cisco Adaptive Security Appliance (ASA) firewalls and the Cisco AnyConnect Secure Mobility Client through a local, web-based interface.

AnyConnect is an Cisco Business App for Windows, iPhone and Android that facilitates user remote access to Cisco networks via VPN. (It is preferred method of remote access to Clientless SSL VPN)

Windows Remote Management (WinRM) is enabled in the firewall. WinRM is one component of the Windows Hardware Management features that manage server hardware locally and remotely.

AlienVault is a network monitoring system which uses OSSIM (Open Source Security Information Management) as a backbone of which it’s able to assist with intrusion detection and prevention operations.

Security Information and Event Management (SIEM) software products and services combine security information management (SIM) and security event management (SEM). They provide real-time analysis of security alerts generated by applications and network hardware.

Internet Control Message Protocol (ICMP) is a network protocol for Internet Protocol (IP) networking. ICMP transfers control information for the status of the network itself rather than application data. An IP network requires ICMP in order to function properly.

ICMP messages are a specific kind of IP message distinct from TCP and UDP.

The best-known example of ICMP messaging in practice is the ping utility, which uses ICMP to probe remote hosts for responsiveness and measure overall round-trip time of the probe messages. ICMP also supports other utilities like traceroute that identify intermediate routing devices (“hops”) on the path between a given source and destination.

pcap is an application programming interface (API) for capturing network traffic. While the name is an abbreviation of the technical term of art (jargon) packet capture, that is not the API’s proper name. Unix-like systems implement pcap in the libpcap library; for Windows, there is a port of libpcap named WinPcap that is no longer supported or developed, and a port named Npcap for Windows 7 and later that is still supported.

Monitoring software may use libpcap, WinPcap, or Npcap to capture network packets travelling over a computer network and, in newer versions, to transmit packets on a network at the link layer, and to get a list of network interfaces for possible use with libpcap, WinPcap, or Npcap.

Intrusion Detection Systems (IDS) analyze network traffic for signatures that match known cyberattacks.

Intrusion Prevention Systems (IPS) also analyzes packets, but can also stop the packet from being delivered based on what kind of attacks it detects — helping stop the attack.

Promiscuous Mode is a mode for a wired network interface controller (NIC) or wireless network interface controller (WNIC) that causes the controller to pass all traffic it receives to the central processing unit (CPU) rather than passing only the frames that the controller is specifically programmed to receive. This mode is normally used for packet sniffing that takes place on a router or on a computer connected to a wired network or one being part of a wireless LAN. Interfaces are placed into promiscuous mode by software bridges often used with hardware virtualization.

Universally Unique Identifier (UUID) is a 128-bit number used to identify information in computer systems. The term globally unique identifier (GUID) is also used, typically in software created by Microsoft.

OpenVAS is a framework of several services and tools offering a comprehensive and powerful vulnerability scanning and vulnerability management solution. The framework is part of Greenbone Networks’ commercial vulnerability management solution from which developments are contributed to the Open Source community since 2009.

The actual security scanner is accompanied with a regularly updated feed of Network Vulnerability Tests (NVTs), over 50,000 in total.

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is damn vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, help web developers better understand the processes of securing web applications and aid teachers/students to teach/learn web application security in a class room environment.

XAMPP is a free and open-source cross-platform web server solution stack package developed by Apache Friends, consisting mainly of the Apache HTTP Server, MariaDB database, and interpreters for scripts written in the PHP and Perl programming languages.

IPv4 and IPv6 are the versions of internet protocol where IPv6 is the enhanced version of IPv4. There are various differences between IPv4 and IPv6 protocol including their features, but the crucial one is the number of addresses (Address space) it generates.

IP version 4 (IPv4) generates 4.29 x 109 unique network addresses which is insufficient in quantity and as a result Internet is running out of space. Whereas IP version 6 (IPv6) produces 3.4 x 1038 addresses and is a scalable and flexible solution to the current problem.

Generic Routing Encapsulation (GRE) is a tunneling protocol developed by Cisco Systems that can encapsulate a wide variety of network layer protocols inside virtual point-to-point links or point-to-multipoint links over an Internet Protocol network. Both VPNs and GRE tunnels can be used to transfer data between remote locations. However, their similarities end there. If you are looking to provide a secure method of connecting remote users to resources stored within a central location, you should probably implement a VPN. However, if you need to pass traffic over an otherwise incompatible network, a GRE tunnel should be implemented. GRE tunnels don’t just have to run across the Internet; you can use them to join networks that couldn’t normally occur, perhaps enabling dynamic routing across a firewall for example. GRE tunnels can solve a few headaches in the real world so understanding them is very important.

One important point to note about GRE tunnels is that they do not inherently encrypt data. Everything that is sent across the tunnel is simply wrapped in GRE packets if you like. You will solve this issue in the next exercise by encrypting the data that is passed over the tunnel. This is especially important when creating a GRE tunnel over insecure networks such as the Internet.

Differences between VPN and GRE:

http://blog.boson.com/bid/92815/What-are-the-differences-between-an-IPSec-VPN-and-a-GRE-Tunnel

Multicast is group communication where data transmission is addressed to a group of destination computers simultaneously. Multicast can be one-to-many or many-to-many distribution. Multicast should not be confused with physical layer point-to-multipoint communication.

Group communication may either be application layer multicast[1] or network assisted multicast, where the latter makes it possible for the source to efficiently send to the group in a single transmission. Copies are automatically created in other network elements, such as routers, switches and cellular network base stations, but only to network segments that currently contain members of the group. Network assisted multicast may be implemented at the data link layer using one-to-many addressing and switching such as Ethernet multicast addressing, Asynchronous Transfer Mode (ATM), point-to-multipoint virtual circuits (P2MP)or Infiniband multicast. Network assisted multicast may also be implemented at the Internet layer using IP multicast. In IP multicast the implementation of the multicast concept occurs at the IP routing level, where routers create optimal distribution paths for datagrams sent to a multicast destination address.

Enhanced Interior Gateway Routing Protocol (EIGRP) is an advanced distance-vector routing protocol that is used on a computer network for automating routing decisions and configuration. The protocol was designed by Cisco Systems as a proprietary protocol, available only on Cisco routers. Functionality of EIGRP was converted to an open standard in 2013[1] and was published with informational status as RFC 7868 in 2016.

EIGRP is used on a router to share routes with other routers within the same autonomous system. Unlike other well known routing protocols, such as RIP, EIGRP only sends incremental updates, reducing the workload on the router and the amount of data that needs to be transmitted.

EIGRP replaced the Interior Gateway Routing Protocol (IGRP) in 1993. One of the major reasons for this was the change to classless IPv4 addresses in the Internet Protocol, which IGRP could not support.

Different types of NAT – Static NAT, Dynamic NAT and PAT

Static NAT (Network Address Translation) – Static NAT (Network Address Translation) is one-to-one mapping of a private IP address to a public IP address. Static NAT (Network Address Translation) is useful when a network device inside a private network needs to be accessible from internet.

Dynamic NAT (Network Address Translation) – Dynamic NAT can be defined as mapping of a private IP address to a public IP address from a group of public IP addresses called as NAT pool. Dynamic NAT establishes a one-to-one mapping between a private IP address to a public IP address. Here the public IP address is taken from the pool of IP addresses configured on the end NAT router. The public to private mapping may vary based on the available public IP address in NAT pool.

PAT (Port Address Translation) – Port Address Translation (PAT) is another type of dynamic NAT which can map multiple private IP addresses to a single public IP address by using a technology known as Port Address Translation.

Here when a client from inside network communicate to a host in the internet, the router changes the source port (TCP or UDP) number with another port number. These port mappings are kept in a table. When the router receive from internet, it will refer the table which keep the port mappings and forward the data packet to the original sender.

Errdisable (or Errdisable)is a feature that automatically disables a port on a Cisco Catalyst switch. When a port is error disabled, it is effectively shut down and no traffic is sent or received on that port.

The Errdisable error disable feature was designed to inform the administrator when there is a port problem or error.  The reasons a catalyst switch can go into Errdisable mode and shutdown a port are many and include:

• Duplex Mismatch
• Loopback Error
• Port Security Violation (psecure-violation)
• Unicast Flodding
• UDLD Failure
• BPDU Guard

Command Line Interface (CLI) Cheat Sheet: Networking

https://docs.paloaltonetworks.com/pan-os/7-1/pan-os-cli-quick-start/cli-cheat-sheets/cli-cheat-sheet-networking

VACLs (VLAN Access Control Lists) are another technology that you can use to help protect your network.

VACLs are particularly useful when you want to restrict access to certain hosts that may even be on the same VLAN. For example, you may have a scenario where you want to block communication between two servers on the same VLAN.

Denial-of-Service attack (DoS attack) is a cyber-attack in which the perpetrator seeks to make a machine or network resource unavailable to its intended users by temporarily or indefinitely disrupting services of a host connected to the Internet. Denial of service is typically accomplished by flooding the targeted machine or resource with superfluous requests in an attempt to overload systems and prevent some or all legitimate requests from being fulfilled.

Distributed denial-of-service (DDoS) attack is a malicious attempt to disrupt normal traffic of a targeted server, service or network by overwhelming the target or its surrounding infrastructure with a flood of Internet traffic. DDoS attacks achieve effectiveness by utilizing multiple compromised computer systems as sources of attack traffic. Exploited machines can include computers and other networked resources such as IoT devices. From a high level, a DDoS attack is like a traffic jam clogging up with highway, preventing regular traffic from arriving at its desired destination.

Personally identifiable information (PII) is information that, when used alone or with other relevant data, can identify an individual.

Cisco Internetwork Operating System (IOS) is a family of network operating systems used on many Cisco Systems routers and current Cisco network switches. Earlier, Cisco switches ran CatOS. IOS is a package of routing, switching, internetworking and telecommunications functions integrated into a multitasking operating system. Although the IOS code base includes a cooperative multitasking kernel, most IOS features have been ported to other kernels such as QNX and Linux for use in Cisco products.

RSA (Rivest–Shamir–Adleman), typically referred to as RSA key, is an algorithm used by modern computers to encrypt and decrypt messages. It is an asymmetric cryptographic algorithm. Asymmetric means that there are two different keys. This is also called public key cryptography, because one of the keys can be given to anyone. The other key must be kept private. The algorithm is based on the fact that finding the factors of a large composite number is difficult: when the factors are prime numbers, the problem is called prime factorization. It is also a key pair (public and private key) generator.

Out-of-band management is a device and system management technique primarily used in computer networking, but is also applied to other fields of IT where similar methods are used. This management method involves an alternate and dedicated connection to the system separate from the actual network that the system runs on. This allows an administrator to ensure the establishment of trust boundaries since there would only be a single entry point for the management interface.

Device management through out-of-band management is still done via a network connection, but this is entirely separate physically from the “in-band” network connection that the system is serving. Think of this as the door marked as “employees only” found in restaurants and shopping centers. No unauthorized user would be able to access the out-of-band network channel because there is simply no connection to it from the regular network channel that everyone uses, making this a very secure channel.

The out-of-band channel management interface is usually available even though the network may be down or even if the device is turned off, in sleep mode, hibernating or otherwise simply inaccessible through the operating system, making this a very powerful management control tool. This can be used to remotely manage powered-down devices outside working hours or during holidays, or it can be used to reboot devices whose operating system has hung or crashed. This is usually done through special operating system extensions and dedicated hardware specifically designed for out-of-band management.

An example configuration for out-of-band management is the use of Microsoft’s System Center together with Intel’s Active Management Technology and using Microsoft public key infrastructure.

Address Resolution Protocol (ARP) is used to determine the MAC Address of a node if you only know its IP address. When a host on a LAN wants to learn a MAC address, it sends out a broadcast ARP request: “Hey, anybody out there using 10.1.1.1?”

If there is, then the host will reply: “Yup, I’m 10.1.1.1 and I’m at 11:33:44:66:55:77!”

There is no relation between ARP and TCP. There is a direct correlation between ARP and IP, at the interface between layer 2 and layer 3.

The IP layer tells the ethernet layer that it needs a packet delivered to IP address 192.168.1.1. The ethernet layer checks its ARP table to see if the MAC address for that IP is known. If not, it broadcasts an ARP packet asking IP 192.168.1.1 to respond with its MAC address. The designated computer responds to the ARP request with its IP and MAC address. This info goes into the ARP table. At that point the IP packet is sent to the proper MAC address, and the receiving ethernet passes the packet up to the IP layer there. The packet may be TCP or UDP or any other protocol supported by IP.

Microsoft Baseline Security Analyzer (MBSA) The Microsoft Baseline Security Analyzer provides a streamlined method to identify missing security updates and common security misconfigurations.

Network Address Translation (NAT) is the process where a network device, usually a firewall, assigns a public address to a computer (or group of computers) inside a private network. The main use of NAT is to limit the number of public IP addresses an organization or company must use, for both economy and security purposes.

Access-Control List (ACL) with respect to a computer file system, is a list of permissions attached to an object. An ACL specifies which users or system processes are granted access to objects, as well as what operations are allowed on given objects.[1] Each entry in a typical ACL specifies a subject and an operation. For instance, if a file object has an ACL that contains (Alice: read,write; Bob: read), this would give Alice permission to read and write the file and Bob to only read it.

Open Web Application Security Project (OWASP) is an online community that produces freely-available articles, methodologies, documentation, tools, and technologies in the field of web application security.

OWASP ZAP (short for Zed Attack Proxy) is an open-source web application security scanner. It is intended to be used by both those new to application security as well as professional penetration testers. It is one of the most active Open Web Application Security Project (OWASP) projects and has been given Flagship status. When used as a proxy server it allows the user to manipulate all of the traffic that passes through it, including traffic using https.

National Institute of Standards and Technology (NIST) The NIST Cybersecurity Framework provides a policy framework of computer security guidance for how private sector organizations in the United States can assess and improve their ability to prevent, detect, and respond to cyber attacks.

Protected health information (PHI)

Whitelisting is the practice of explicitly allowing some identified entities access to a particular privilege, service, mobility, access or recognition. It is the reverse of blacklisting.

Federal Information Processing Standard Publication (FIPS)

Statement of Work (SOW)

Zero-day (also known as 0-day) vulnerability is a computer-software vulnerability that is unknown to, or unaddressed by, those who should be interested in mitigating the vulnerability (including the vendor of the target software). Until the vulnerability is mitigated, hackers can exploit it to adversely affect computer programs, data, additional computers or a network.[1] An exploit directed at a zero-day is called a zero-day exploit, or zero-day attack.

SYN Short for synchronize, SYN is a TCP packet sent to another computer requesting that a connection be established between them. If the SYN is received by the second machine, an SYN/ACK is sent back to the address requested by the SYN. Lastly, if the original computer receives the SYN/ACK, a final ACK is sent.

SYN flood is a form of denial-of-service attack in which an attacker sends a succession of SYN requests to a target’s system in an attempt to consume enough server resources to make the system unresponsive to legitimate traffic.

A port scanner is an application designed to probe a server or host for open ports. Such an application may be used by administrators to verify security policies of their networks and by attackers to identify network services running on a host and exploit vulnerabilities.

A port scan or portscan is a process that sends client requests to a range of server port addresses on a host, with the goal of finding an active port; this is not a nefarious process in and of itself. The majority of uses of a port scan are not attacks, but rather simple probes to determine services available on a remote machine.

To portsweep is to scan multiple hosts for a specific listening port. The latter is typically used to search for a specific service, for example, an SQL-based computer worm may portsweep looking for hosts listening on TCP port 1433.

In cryptography, X.509 is a standard defining the format of public key certificates.[1] X.509 certificates are used in many Internet protocols, including TLS/SSL, which is the basis for HTTPS

In cryptography, Triple DES (3DES or TDES), officially the Triple Data Encryption Algorithm (TDEA or Triple DEA), is a symmetric-key block cipher, which applies the DES cipher algorithm three times to each data block. The Data Encryption Standard’s (DES) 56-bit key is no longer considered adequate in the face of modern cryptanalytic techniques and supercomputing power. However, an adapted version of DES, Triple DES (3DES), uses the same algorithm to produce a more secure encryption.

Public key cryptography standards (PKCS) are a group of specifications developed with the aim of accelerating the deployment of algorithms featuring two separate keys – one private and one public.

PKCS were first developed by RSA Laboratories with the cooperation of security developers from around the world.

The first published release of PKCS was in 1991 as a result of the cooperation of early adaptors. The standards promote the use of cryptography techniques such as the RSA algorithm and the Schnorr signature.

The Advanced Encryption Standard (AES), also known by its original name Rijndael (Dutch pronunciation: [ˈrɛindaːl]),[3] is a specification for the encryption of electronic data established by the U.S. National Institute of Standards and Technology (NIST) in 2001.[4]

AES is a subset of the Rijndael block cipher[3] developed by two Belgian cryptographers, Vincent Rijmen and Joan Daemen, who submitted a proposal[5] to NIST during the AES selection process.[6] Rijndael is a family of ciphers with different key and block sizes.

For AES, NIST selected three members of the Rijndael family, each with a block size of 128 bits, but three different key lengths: 128, 192 and 256 bits.

AES has been adopted by the U.S. government and is now used worldwide. It supersedes the Data Encryption Standard (DES),[7] which was published in 1977. The algorithm described by AES is a symmetric-key algorithm, meaning the same key is used for both encrypting and decrypting the data.

In the United States, AES was announced by the NIST as U.S. FIPS PUB 197 (FIPS 197) on November 26, 2001.[4] This announcement followed a five-year standardization process in which fifteen competing designs were presented and evaluated, before the Rijndael cipher was selected as the most suitable (see Advanced Encryption Standard process for more details).

AES became effective as a federal government standard on May 26, 2002, after approval by the Secretary of Commerce. AES is included in the ISO/IEC 18033-3 standard. AES is available in many different encryption packages, and is the first (and only) publicly accessible cipher approved by the National Security Agency (NSA) for top secret information when used in an NSA approved cryptographic module (see Security of AES, below).

A botnet is a number of Internet-connected devices, each of which is running one or more bots. Botnets can be used to perform distributed denial-of-service attack (DDoS attack), steal data,[1] send spam, and allows the attacker to access the device and its connection. The owner can control the botnet using command and control (C&C) software.[2] The word “botnet” is a combination of the words “robot” and “network”. The term is usually used with a negative or malicious connotation.

A command-and-control [C&C] server is a computer controlled by an attacker or cybercriminal which is used to send commands to systems compromised by malware and receive stolen data from a target network.

Industrial control systems (ICS)

Supervisory control and data acquisition (SCADA) is a control system architecture comprising computers, networked data communications and graphical user interfaces(GUI) for high-level process supervisory management, while also comprising other peripheral devices like programmable logic controllers (PLC) and discrete proportional-integral-derivative (PID) controllers to interface with process plant or machinery. The use of SCADA has been considered also for management and operations of project-driven-process in construction.

A domain controller (DC) is a server that responds to security authentication requests within a Windows Server domain. It is a server on a Microsoft Windows or Windows NT network that is responsible for allowing host access to Windows domain resources.

A domain controller is the centerpiece of the Windows Active Directory service. It authenticates users, stores user account information and enforces security policy for a Windows domain.

Advanced persistent threat (APT) is a stealthy computer network threat actor, typically a nation state or state-sponsored group, which gains unauthorized access to a computer network and remains undetected for an extended period.[1][2] In recent times, the term may also refer to non-state sponsored groups conducting large-scale targeted intrusions for specific goals

Common Vulnerabilities and Exposures (CVE®) is a list of entries — each containing an identification number, a description, and at least one public reference — for publicly known cybersecurity vulnerabilities.

Microsoft System Center Configuration Manager (SCCM), formerly Systems Management Server is a systems management software product developed by Microsoft for managing large groups of computers running Windows NT, Windows Embedded, macOS, Linux or UNIX, as well as Windows Phone, Symbian, iOS and Android mobile operating systems.

Security Assertion Markup Language (SAML) is an XML-based framework for authentication and authorization between two entities: a Service Provider and an Identity Provider. The Service Provider agrees to trust the Identity Provider to authenticate users. In return, the Identity provider generates an authentication assertion, which indicates that a user has been authenticated.

# How to create a job that tests whether SQL Server database mail is working

The following script will create a job that will run every minute to test if database mail can be sent from a job scheduled to run by the Sql Server Agent.

Simply find and replace the email address below with the email address you want to target:

testoperator@mail.com

Then run the script.

The operator ‘Test Operator’ and job ‘MailTest’ will be created.

The job is disabled by default, enable it to begin testing.

When you are finished run the commented out section at the bottom of the script to remove the test operator and job.

If you have just setup database mail for the first time the SQL Server Agent will need to be restarted.

```/*
FIND AND REPLACE

testoperator@mail.com

*/
USE msdb;
GO

EXEC dbo.sp_add_operator @name = N'Test Operator'
,@enabled = 1
GO

USE [msdb]
GO

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 31/07/2019 11:35:43 ******/
IF NOT EXISTS (
SELECT NAME
FROM msdb.dbo.syscategories
WHERE NAME = N'[Uncategorized (Local)]'
AND category_class = 1
)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB'
,@type = N'LOCAL'
,@name = N'[Uncategorized (Local)]'

IF (
@@ERROR <> 0
OR @ReturnCode <> 0
)
GOTO QuitWithRollback
END

DECLARE @jobId BINARY (16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N'MailTest'
,@enabled = 0
,@notify_level_eventlog = 0
,@notify_level_email = 3
,@notify_level_netsend = 0
,@notify_level_page = 0
,@delete_level = 0
,@description = N'No description available.'
,@category_name = N'[Uncategorized (Local)]'
,@notify_email_operator_name = N'Test Operator'
,@job_id = @jobId OUTPUT

IF (
@@ERROR <> 0
OR @ReturnCode <> 0
)
GOTO QuitWithRollback

/****** Object:  Step [Step 1]    Script Date: 31/07/2019 11:35:44 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId
,@step_name = N'Step 1'
,@step_id = 1
,@cmdexec_success_code = 0
,@on_success_action = 1
,@on_success_step_id = 0
,@on_fail_action = 2
,@on_fail_step_id = 0
,@retry_attempts = 0
,@retry_interval = 0
,@os_run_priority = 0
,@subsystem = N'TSQL'
,@command = N'SELECT 1'
,@database_name = N'master'
,@flags = 0

IF (
@@ERROR <> 0
OR @ReturnCode <> 0
)
GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId
,@start_step_id = 1

IF (
@@ERROR <> 0
OR @ReturnCode <> 0
)
GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId
,@name = N'Job Schedule'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 1
,@freq_relative_interval = 0
,@freq_recurrence_factor = 0
,@active_start_date = 20190731
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235959
,@schedule_uid = N'f0741db6-488e-44da-8f5e-a3f0ed13835e'

IF (
@@ERROR <> 0
OR @ReturnCode <> 0
)
GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId
,@server_name = N'(local)'

IF (
@@ERROR <> 0
OR @ReturnCode <> 0
)
GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0)
ROLLBACK TRANSACTION

EndSave:
GO

/*
REMOVE OPERATOR AND JOB
*/
/*
USE msdb;
GO

EXEC sp_delete_operator @name = 'Test Operator';

EXEC sp_delete_job @job_name = N'MailTest';

GO
*/
```

# How to get SQL Server Network Information using SSMS

The following code will work for a remote client request to SQL 2008 and newer.

Note: The local machine address (local_net_address) is that of the SQL Server while client_net_address is the address of the remote computer you have used to make the request.

```SELECT @@SERVERNAME AS ServerName
,CONNECTIONPROPERTY('net_transport') AS net_transport
,CONNECTIONPROPERTY('protocol_type') AS protocol_type
,CONNECTIONPROPERTY('auth_scheme') AS auth_scheme
,CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port

```

# How to NOT write a query

So the vendors for the call system software ran the code below on the live system on a Friday night. Thanks guys. I’ve changed the name of the tables in the code for security reasons if anyone would like to use this it as an example of how not to write T-SQL code!

The vendors claim the code came from the company that developed the call system and if this is the case I think someone in head office is trying to get fired or get someone fired.

Scroll down for a review of why the code is such a mess.

```/*
TableOne a 312 million row table
TableTwo a 55 million row table
TableThree a 22 million row table
*/

DELETE
FROM TableOne
WHERE SecurityPolicyId = @p_secPolId
AND RecordingId NOT IN (
SELECT c.RECORDINGID
FROM TableThree c
WHERE (
EXISTS (
SELECT *
FROM TableTwo b0
WHERE b0.RecordingID = c.RECORDINGID
AND b0.Workgroup = N'CS EMAIL - [Cancellations]'
)
OR EXISTS (
SELECT *
FROM TableTwo b1
WHERE b1.RecordingID = c.RECORDINGID
AND b1.Workgroup = N'CS EMAIL - [Claims]'
)
OR EXISTS (
SELECT *
FROM TableTwo b2
WHERE b2.RecordingID = c.RECORDINGID
AND b2.Workgroup = N'CS EMAIL - [Contact Request]'
)
OR EXISTS (
SELECT *
FROM TableTwo b3
WHERE b3.RecordingID = c.RECORDINGID
AND b3.Workgroup = N'CS EMAIL - [DD Enquiries]'
)
OR EXISTS (
SELECT *
FROM TableTwo b4
WHERE b4.RecordingID = c.RECORDINGID
AND b4.Workgroup = N'CS EMAIL - [Feedback]'
)
OR EXISTS (
SELECT *
FROM TableTwo b5
WHERE b5.RecordingID = c.RECORDINGID
AND b5.Workgroup = N'CS EMAIL - [MTA]'
)
OR EXISTS (
SELECT *
FROM TableTwo b6
WHERE b6.RecordingID = c.RECORDINGID
AND b6.Workgroup = N'CS EMAIL - [NB Cover Query]'
)
OR EXISTS (
SELECT *
FROM TableTwo b7
WHERE b7.RecordingID = c.RECORDINGID
AND b7.Workgroup = N'CS EMAIL - [O/S Documents]'
)
OR EXISTS (
SELECT *
FROM TableTwo b8
WHERE b8.RecordingID = c.RECORDINGID
AND b8.Workgroup = N'CS EMAIL - [Otherl]'
)
OR EXISTS (
SELECT *
FROM TableTwo b9
WHERE b9.RecordingID = c.RECORDINGID
AND b9.Workgroup = N'CS EMAIL - [SME]'
)
OR EXISTS (
SELECT *
FROM TableTwo b10
WHERE b10.RecordingID = c.RECORDINGID
AND b10.Workgroup = N'CS WebChat'
)
OR EXISTS (
SELECT *
FROM TableTwo b11
WHERE b11.RecordingID = c.RECORDINGID
AND b11.Workgroup = N'CUSTOMER SERVICE - [Cancellations]'
)
OR EXISTS (
SELECT *
FROM TableTwo b12
WHERE b12.RecordingID = c.RECORDINGID
AND b12.Workgroup = N'CUSTOMER SERVICE - [CBL enquiry]'
)
OR EXISTS (
SELECT *
FROM TableTwo b13
WHERE b13.RecordingID = c.RECORDINGID
AND b13.Workgroup = N'CUSTOMER SERVICE - [Claims]'
)
OR EXISTS (
SELECT *
FROM TableTwo b14
WHERE b14.RecordingID = c.RECORDINGID
AND b14.Workgroup = N'CUSTOMER SERVICE - [Commercial]'
)
OR EXISTS (
SELECT *
FROM TableTwo b15
WHERE b15.RecordingID = c.RECORDINGID
AND b15.Workgroup = N'CUSTOMER SERVICE - [DD Payment]'
)
OR EXISTS (
SELECT *
FROM TableTwo b16
WHERE b16.RecordingID = c.RECORDINGID
AND b16.Workgroup = N'CUSTOMER SERVICE - [Diary Team]'
)
OR EXISTS (
SELECT *
FROM TableTwo b17
WHERE b17.RecordingID = c.RECORDINGID
AND b17.Workgroup = N'CUSTOMER SERVICE - [Doc Request]'
)
OR EXISTS (
SELECT *
FROM TableTwo b18
WHERE b18.RecordingID = c.RECORDINGID
AND b18.Workgroup = N'CUSTOMER SERVICE - [DocChase CL]'
)
OR EXISTS (
SELECT *
FROM TableTwo b19
WHERE b19.RecordingID = c.RECORDINGID
AND b19.Workgroup = N'CUSTOMER SERVICE - [DocChase FN]'
)
OR EXISTS (
SELECT *
FROM TableTwo b20
WHERE b20.RecordingID = c.RECORDINGID
AND b20.Workgroup = N'CUSTOMER SERVICE - [DocChase IN]'
)
OR EXISTS (
SELECT *
FROM TableTwo b21
WHERE b21.RecordingID = c.RECORDINGID
AND b21.Workgroup = N'CUSTOMER SERVICE - [Life]'
)
OR EXISTS (
SELECT *
FROM TableTwo b22
WHERE b22.RecordingID = c.RECORDINGID
AND b22.Workgroup = N'CUSTOMER SERVICE - [MTA]'
)
OR EXISTS (
SELECT *
FROM TableTwo b23
WHERE b23.RecordingID = c.RECORDINGID
AND b23.Workgroup = N'CUSTOMER SERVICE - [NB Cover Query]'
)
OR EXISTS (
SELECT *
FROM TableTwo b24
WHERE b24.RecordingID = c.RECORDINGID
AND b24.Workgroup = N'CUSTOMER SERVICE - [Other Query]'
)
OR EXISTS (
SELECT *
FROM TableTwo b25
WHERE b25.RecordingID = c.RECORDINGID
AND b25.Workgroup = N'Customer Service Admin'
)
OR EXISTS (
SELECT *
FROM TableTwo b26
WHERE b26.RecordingID = c.RECORDINGID
AND b26.Workgroup = N'OUTBOUND - [Welcome Calls]'
)
OR EXISTS (
SELECT *
FROM TableTwo b27
WHERE b27.RecordingID = c.RECORDINGID
AND b27.Workgroup = N'OUTBOUND CS - [Cancelations]'
)
OR EXISTS (
SELECT *
FROM TableTwo b28
WHERE b28.RecordingID = c.RECORDINGID
AND b28.Workgroup = N'OUTBOUND CS - [DocChase]'
)
OR EXISTS (
SELECT *
FROM TableTwo b29
WHERE b29.RecordingID = c.RECORDINGID
AND b29.Workgroup = N'OUTBOUND CS - [Final Notice]'
)
OR EXISTS (
SELECT *
FROM TableTwo b30
WHERE b30.RecordingID = c.RECORDINGID
AND b30.Workgroup = N'OUTBOUND CS - [Initial Chase]'
)
OR EXISTS (
SELECT *
FROM TableTwo b31
WHERE b31.RecordingID = c.RECORDINGID
AND b31.Workgroup = N'OVERFLOW - [Claims]'
)
OR EXISTS (
SELECT *
FROM TableTwo b32
WHERE b32.RecordingID = c.RECORDINGID
AND b32.Workgroup = N'OVERFLOW - [Customer Service - MTA]'
)
OR EXISTS (
SELECT *
FROM TableTwo b33
WHERE b33.RecordingID = c.RECORDINGID
AND b33.Workgroup = N'RENEWALS - [Personal Enquiries]'
)
OR EXISTS (
SELECT *
FROM TableTwo b34
WHERE b34.RecordingID = c.RECORDINGID
AND b34.Workgroup = N'RENEWALS - [Personal Outbound]'
)
OR EXISTS (
SELECT *
FROM TableTwo b35
WHERE b35.RecordingID = c.RECORDINGID
AND b35.Workgroup = N'RENEWALS - [Personal Payments]'
)
)
AND c.RecordingDate >= @p_dateAfter
AND c.RecordingDate < @p_dateBefore
OR c.RecordingDate < @p_dateAfter
OR c.RecordingDate >= @p_dateBefore
)
```

Here are some of the issues with the above in no particular order.

The command is running against a 312 million row table, a 55 million row table and a 22 million row table. Although is was only planned to run once IT STILL NEEDS TO BE EFFICIENT!!!!!

It uses Select Star or (Select *) which raises the probability that SQL Server will query the whole table rather than accessing the data through indexes. Only an Id is needed to begin with.

There are subqueries used when a join would be much faster.

The subqueries are completely unnecessary when WHERE conditions could have been listed in an IN statement.

Correlated subquery!!! Literally designed to bring a server to its knees! Evaluated once for each row processed. See more on correlated sub queries here https://en.wikipedia.org/wiki/Correlated_subquery

There are EXISTS Statements used due to the poor design of the command.

A ridiculously giant case statement used! Typically the comparison data should be written to a temp table and assessed as a join when there are an excessive number of case statements.

Needless to say this query ballooned the tempdb and I’ll let you guess what happened next.

The lesson for today’s posting, trust no one!

# How to assess a SQL Server instance for GDPR compliance by writing every table and column to Excel

This post uses the script I had written before here LINK modified slightly to include a count of each table. If you are a DBA you’re likely assisting compliance offers to find personal identifiable data within the databases at this time. This script will allow you to provide them with a record of every database, table and column on an entire instance. The compliance offer can then sieve through all the columns and highlight any columns that look like they contain personal data for further investigation.

Below is a SQL query that will return the following metadata about each table from each database located on a SQL server database server:

• ServerName
• DatabaseName
• SchemaName
• TableName
• CountOfRows
• ColumnName
• KeyType

The output is provided in a table format with these additional formatted lines of text which can be used as queries or as part of queries.

• A Select table query
• A Count table row columns
• A Select column query
• Each column bracketed
• Each table and column bracketed

The table returned by the query can be exported to excel. Using excels filter option applied to the columns of the table makes finding and selecting specific tables and columns very easy.

This process can be repeated for every SQL Server instance used by the business to generate a single mapped servers master excel file allowing the user to find any table or column available to the organization quickly.

Applying some colour coding like below adds to the ease of use.

How to use:

Simply open SQL Server Management Studio and from object explorer right click on the server name and select new query. This will open a window set to the master database of the server. Copy and paste the SQL below into this SQL Server window and execute. When the query is finished you will have created the table above.

```/*
SCRIPT UPDATED
20180316
*/

USE [master]
GO

/*DROP TEMP TABLES IF THEY EXIST*/
IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL
DROP TABLE #DatabaseList;

IF OBJECT_ID('tempdb..#TableStructure') IS NOT NULL
DROP TABLE #TableStructure;

IF OBJECT_ID('tempdb..#ErrorTable') IS NOT NULL
DROP TABLE #ErrorTable;

IF OBJECT_ID('tempdb..#MappedServer') IS NOT NULL
DROP TABLE #MappedServer;

IF OBJECT_ID('tempdb..#TableCount') IS NOT NULL
DROP TABLE #TableCount;

IF OBJECT_ID('tempdb..#Count') IS NOT NULL
DROP TABLE #Count;

DECLARE @ServerName AS SYSNAME
DECLARE @Count INT

SET @ServerName = @@SERVERNAME

CREATE TABLE #DatabaseList (
Id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
,ServerName SYSNAME
,DbName SYSNAME
);

CREATE TABLE [#TableStructure] (
[DbName] SYSNAME
,[SchemaName] SYSNAME
,[TableName] SYSNAME
,[ColumnName] SYSNAME
,[KeyType] CHAR(7)
) ON [PRIMARY];

CREATE TABLE [#TableCount] (
[Id_TableCount] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
,[ServerName] SYSNAME
,[DatabaseName] SYSNAME
,[SchemaName] SYSNAME
,[TableName] SYSNAME
,[SQLCommand] VARCHAR(MAX)
,[TableCount] INT
);

CREATE TABLE #Count (ReturnedCount INT);

/*THE ERROR TABLE WILL STORE THE DYNAMIC SQL THAT DID NOT WORK*/
CREATE TABLE [#ErrorTable] ([SqlCommand] VARCHAR(MAX)) ON [PRIMARY];

/*
A LIST OF DISTINCT DATABASE NAMES IS CREATED
THESE TWO COLUMNS ARE STORED IN THE #DatabaseList TEMP TABLE
THIS TABLE IS USED IN A FOR LOOP TO GET EACH DATABASE NAME
*/
INSERT INTO #DatabaseList (
ServerName
,DbName
)
SELECT @ServerName
,NAME AS DbName
FROM master.dbo.sysdatabases WITH (NOLOCK)
WHERE NAME <> 'tempdb'
ORDER BY NAME ASC

/*VARIABLES ARE DECLARED FOR USE IN THE FOLLOWING FOR LOOP*/
DECLARE @sqlCommand AS VARCHAR(MAX)
DECLARE @DbName AS SYSNAME
DECLARE @i AS INT
DECLARE @z AS INT

SET @i = 1
SET @z = (
SELECT COUNT(*) + 1
FROM #DatabaseList
)

/*WHILE 1 IS LESS THAN THE NUMBER OF DATABASE NAMES IN #DatabaseList*/
WHILE @i < @z
BEGIN
/*GET NEW DATABASE NAME*/
SET @DbName = (
SELECT [DbName]
FROM #DatabaseList
WHERE Id = @i
)
/*CREATE DYNAMIC SQL TO GET EACH TABLE NAME AND COLUMN NAME FROM EACH DATABASE*/
SET @sqlCommand = 'USE [' + @DbName + '];' + '

INSERT INTO [#TableStructure]
SELECT DISTINCT ' + '''' + @DbName + '''' + ' AS DbName
,SCHEMA_NAME(SCHEMA_ID) AS SchemaName
,T.NAME AS TableName
,C.NAME AS ColumnName
,CASE
WHEN OBJECTPROPERTY(OBJECT_ID(iskcu.CONSTRAINT_NAME), ''IsPrimaryKey'') = 1
THEN ''Primary''
WHEN OBJECTPROPERTY(OBJECT_ID(iskcu.CONSTRAINT_NAME), ''IsForeignKey'') = 1
THEN ''Foreign''
ELSE NULL
END AS ''KeyType''
FROM SYS.TABLES AS t WITH (NOLOCK)
INNER JOIN SYS.COLUMNS C ON T.OBJECT_ID = C.OBJECT_ID
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS iskcu WITH (NOLOCK)
ON SCHEMA_NAME(SCHEMA_ID) = iskcu.TABLE_SCHEMA
AND T.NAME = iskcu.TABLE_NAME
AND C.NAME = iskcu.COLUMN_NAME
ORDER BY SchemaName ASC
,TableName ASC
,ColumnName ASC;
';

/*ERROR HANDLING*/
BEGIN TRY
EXEC (@sqlCommand)
END TRY

BEGIN CATCH
INSERT INTO #ErrorTable
SELECT (@sqlCommand)
END CATCH

SET @i = @i + 1
END

/*
JOIN THE TEMP TABLES TOGETHER TO CREATE A MAPPED STRUCTURE OF THE SERVER
*/
SELECT DISTINCT @@SERVERNAME AS ServerName
,DL.DbName
,TS.SchemaName
,TS.TableName
,TS.ColumnName
,TS.[KeyType]
,',' + QUOTENAME(TS.ColumnName) AS BracketedColumn
,',' + QUOTENAME(TS.TableName) + '.' + QUOTENAME(TS.ColumnName) AS BracketedTableAndColumn
,'SELECT * FROM ' + QUOTENAME(DL.DbName) + '.' + QUOTENAME(TS.SchemaName) + '.' + QUOTENAME(TS.TableName) + '--WHERE --GROUP BY --HAVING --ORDER BY' AS [SelectTable]
,'SELECT ' + QUOTENAME(TS.TableName) + '.' + QUOTENAME(TS.ColumnName) + ' FROM ' + QUOTENAME(DL.DbName) + '.' + QUOTENAME(TS.SchemaName) + '.' + QUOTENAME(TS.TableName) + '--WHERE --GROUP BY --HAVING --ORDER BY' AS [SelectColumn]
,'SELECT COUNT(*) FROM ' + QUOTENAME(DL.DbName) + '.' + QUOTENAME(TS.SchemaName) + '.' + QUOTENAME(TS.TableName) + 'WITH (NOLOCK)' AS [PerformTableCount]
INTO #MappedServer
FROM [#DatabaseList] AS DL
INNER JOIN [#TableStructure] AS TS ON DL.DbName = TS.DbName
ORDER BY DL.DbName ASC
,TS.SchemaName ASC
,TS.TableName ASC
,TS.ColumnName ASC

/*
HOUSE KEEPING
*/
IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL
DROP TABLE #DatabaseList;

IF OBJECT_ID('tempdb..#TableStructure') IS NOT NULL
DROP TABLE #TableStructure;

INSERT INTO #TableCount (
[ServerName]
,[DatabaseName]
,[SchemaName]
,[TableName]
,[SQLCommand]
)
SELECT DISTINCT [ServerName]
,[DbName]
,[SchemaName]
,[TableName]
,[PerformTableCount]
FROM #MappedServer
ORDER BY [ServerName] ASC
,[DbName] ASC
,[SchemaName] ASC
,[TableName] ASC

SET @i = 1
SET @z = (
SELECT COUNT(*) + 1
FROM #TableCount
)

WHILE @i < @z
BEGIN
SET @SQLCommand = (
SELECT SQLCommand
FROM #TableCount
WHERE Id_TableCount = @i
)

--ERROR HANDLING
BEGIN TRY
INSERT INTO #Count
EXEC (@SqlCommand)
END TRY

BEGIN CATCH
INSERT INTO #ErrorTable
SELECT (@sqlCommand)
END CATCH

SET @Count = (
SELECT ReturnedCount
FROM #Count
)

TRUNCATE TABLE #Count

UPDATE #TableCount
SET TableCount = @Count
WHERE Id_TableCount = @i;

SET @i = @i + 1
END

SELECT *
FROM #ErrorTable;

IF OBJECT_ID('tempdb..#ErrorTable') IS NOT NULL
DROP TABLE #ErrorTable;

/*
THE DATA RETURNED CAN NOW BE EXPORTED TO EXCEL
USING A FILTERED SEARCH WILL NOW MAKE FINDING FIELDS A VERY EASY PROCESS
*/
SELECT DISTINCT ms.ServerName
,ms.DbName
,ms.SchemaName
,ms.TableName
,ts.TableCount AS CountOfRows
,ms.ColumnName
,ms.KeyType
,ms.BracketedColumn
,ms.BracketedTableAndColumn
,ms.SelectColumn
,ms.SelectTable
FROM #MappedServer AS ms
LEFT JOIN #TableCount AS ts ON ms.ServerName = ts.ServerName
AND ms.DbName = ts.DatabaseName
AND ms.SchemaName = ts.SchemaName
AND ms.TableName = ts.TableName
ORDER BY ms.DbName ASC
,ms.SchemaName ASC
,ms.TableName ASC
,ms.ColumnName ASC;

IF OBJECT_ID('tempdb..#MappedServer') IS NOT NULL
DROP TABLE #MappedServer;

IF OBJECT_ID('tempdb..#TableCount') IS NOT NULL
DROP TABLE #TableCount;

IF OBJECT_ID('tempdb..#Count') IS NOT NULL
DROP TABLE #Count;
```

# How to solve the SQL Server error ‘String or binary data would be truncated’

The ‘String or binary data would be truncated’ error will occur if an insert or update statement is trying to put too many characters into a field, defined in a table, which has been assigned too few character spaces. For example trying to write an email address with 255 characters into a table where the column email has been assigned 40 characters.

The easy fix is assign more characters to the column or columns you have determined are experiencing the problem. The more complicated but potentially necessary fix might be to change the logic or introduce validation at the source of data entry.

Finding the columns experiencing the problems however can be time consuming.

( . . . without the little script below of course)

SQL Server will kindly direct you to the stored procedure or insert/update statement that is experiencing the problem. However it will not pin point the exact column or columns that cannot be written to. The pain then is determining where the data won’t fit.

To speed things up take the entire query or query section you know to be causing the problem and write the results it into a temp table called #temp, i.e. SELECT * INTO #temp FROM SomeTable

Once the data has been written to the temp table #temp run the scrip below in the same window.

```DECLARE @sql VARCHAR(MAX)

SET @sql = (
SELECT (
SELECT ',MAX(LEN(' + NAME + ')) AS [' + NAME + ']'
FROM tempdb.sys.columns
WHERE object_id = object_id('tempdb..#temp')
FOR XML PATH('')
)
)
SET @sql = 'SELECT ' + RIGHT(@sql, LEN(@sql) - 1) + ' FROM #temp'

EXEC (@sql)
```
This will output results giving you the max character length of each field.
You can then compare these results to the defined destination table that the data could not be written to.
The source of the error will be where the max character number is greater than the assigned character spaces on the destination table.
For example the last time I used this query it easily highlighted that an agent had written a customers full address to the county name field which had a limit of 30 characters.