SQL Server 2019 and Azure Data Studio

Azure Data Studio

At its Ignite conference in Orlando last week, Microsoft released a new version of its core relational database, SQL Server 2019. The new version takes the important capabilities we have admired in previous releases and expanded them to leverage data virtualization with PolyBase and combine Kubernetes with its container compatibility.

While PolyBase can connect to Hadoop clusters and Azure storage, this new version can now also connect to other SQL Server instances. This also allows the BDC master node to communicate with the BDC compute, plus data and storage pools, to allow the nodes in the storage pool to connect to data in the co-located HDFS storage.

Microsoft provides a tool named Azure Data Studio (works with all supported versions of SQL Server 2014 – SQL Server 2019) to do some of this new work. This new cross-platform tool can be used for T-SQL querying, notebook development, running Spark jobs on BDC deployments, etc. It is essentially a cross-platform database tool for on-premises and cloud data platforms that can be used on Windows, MacOS, and Linux endpoints.

Continue reading “SQL Server 2019 and Azure Data Studio”

PCI DSS 4.0 – Coming Soon

credit-cards

In the upcoming request for comments (RFC) for the first draft of the PCI Data Security Standard Version 4.0  (PCI DSS v4.0), there are some new and exciting changes. PCI DSS v4.0 has been in the works for a while, so a discussion of what is coming is important to anyone who has to meet the standards required to maintain their compliance with the payment card industry.

The October RFC documents will include the first draft of the new PCI DSS v4.0 standard as well as a sample of the new reporting template. This will help everyone understand the new validation method to help support business implementations. There is also a Summary of Changes document that will outline the changes in the draft as well as guidance for everyone on how to review the documents and provide feedback with any issues or questions.

This draft of PCI DSS v4.0 was crafted with feedback received during prior drafts and attempts to reflect changes in security technologies, customer environments, and payment industry changes. These updates to the standard are intended to strengthen security while also adding some flexibility to how the standards are implemented.

The 12 core PCI DSS requirements remain essentially the same while several new requirements are proposed to address evolving threats to significantly reduce the overall risk to payment data. The idea is to give more flexibility to organizations so that companies can use different methodologies and solutions to meet the intent of PCI DSS requirements.

Continue reading “PCI DSS 4.0 – Coming Soon”

Free Download: SQL Server Management Studio 18.3.1


SQL Server

SQL Server Management Studio (SSMS) is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SSMS combines a broad group of graphical tools with a number of rich script editors to provide developers and administrators of all skill levels access to SQL Server.

Microsoft has announced the latest release of SQL Server Management Studio (SSMS) in October as a free download. SSMS 18.3.1 is now available.

Get it here:

Download – The version number for the latest release is 15.0.18183.0

New in this release

New Item Description
Data Classification Add Data Classification information to column properties UI (Information TypeInformation Type IDSensitivity Label, and Sensitivity Label ID aren’t exposed in the SSMS UI).
Intellisense/Editor Updated support for features recently added to SQL Server 2019 (for example “ALTER SERVER CONFIGURATION”).
Integration Services Add a new selection menu item Tools > Migrate to Azure > Configure Azure-enabled DTExec that invokes Integration services (SSIS) package executions on Azure-SSIS Integration Runtime as Execute SSIS Package activities in ADF pipelines.
SMO/Scripting Added support for Support scripting of Azure SQL DW unique constraint.
SMO/Scripting Data Classification – Added support for SQL version 10 (SQL 2008) and higher. – Added new sensitivity attribute ‘rank’ for SQL version 15 (SQL 2019) and higher and Azure SQL DB.

Continue reading “Free Download: SQL Server Management Studio 18.3.1”

Hacking Attack Prevention Tips

Hackers - @SeniorDBA

The volume and sophistication of cyber attacks has increased in the last few years, and you should be worried if you have done enough to  protect your personal and business assets from attack by attacks by hackers on the internet. Companies of all sizes, including even small government agencies, have all been the target of malicious hackers over the past several years.  With increased publicity comes increased awareness by the general public about how dangerous data breaches can be so there has been increased interest in preventing hacker attacks.

Just to be clear, any device with internet access is subject to a cyber attack. This includes your cellphone, tablet, and laptop. With the increase in small devices with internet access, like thermostats, toasters, video cameras, etc. the huge numbers of devices subject to attack has made securing all devices from all attacks a huge undertaking.

There are a few things that you can constantly do to minimize your risk of attack from a random attacker looking for an easy target.

  1. Apply Updates – No system is immune from flaws in the software and firmware used by your device. Flaws are found every day, sometimes in systems that have been working correctly for many years. When these flaws are found, patches are released to remove the vulnerability and make the system safer. Once a vulnerability is found and made public, many hackers start looking for system specifically missing the vendor patch so they can successfully attack the vulnerable system and gain entry into the system so they can cost you money.  The easiest way to prevent these easy attacks is to apply vendor updates as soon as they are available.
  2. Password Security – Passwords are the key to access into your systems. The more complex the key, the harder it is to bypass the lock. Use complex passwords (at least 10 characters long, include uppercase, lowercase, numbers, and at lease one special character), don’t use the same password on more that one site or application, and change your passwords often. If possible, enable multi-factor authentication. This allows you to use a username and password (something you know)  with a special code sent to your cellphone (something you have). If a hacker steals or guesses your password, he still has the extra hurdle of getting the code from your cellphone. While not foolproof, it will slow down casual attacks.
  3.  Email Phishing Awareness- Everyone knows email is the easy entry point for malware into your business and personal systems.  We all have email accounts, and we often read and respond to email without really spending time to verify the email was sent from the person we think it was sent from before we open the attachments or click on embedded links. Hackers know this and target you with fake emails intended to get users to allow malware into their systems or to provide credentials that can be stolen before we realize what happened. Training on how to spot and delete phony emails is important.
  4. Anti-Virus Software  – A good anti-virus program will help protect your system from virus programs, malware, phishing attacks, drive-by downloads, malicious attachments, and ransomware. You should use anti-virus software on all systems, including servers, laptops, desktops, and even MacOS and Linux systems. While no tool will make you 100% safe from malware and other attacks, they will stop most automated attacks with little or no work required from the user.
  5. Network Segmentation – When a hacker attacks an exposed endpoint, that endpoint is rarely the intended final target. The target is the entire network, with your laptop as the entry point so they can move from your laptop to any other endpoint, including servers and databases that contain company assets, customer data, bank accounts, credit cards, etc. Network segmentation is attempting to build virtual walls around groups of systems to prevent uncontrolled access between laptops and servers, and to better protect those systems that contain sensitive data. This is work normally done by trained IT staff.
  6. File Backups – While you may still be a victim of a successful attack even if you make just one mistake, the impact of that attack will be much smaller if you have consistent backups of your important data. A ransomware attack can encrypt all the files on your laptop and cost you thousands of dollars to recover them from the hacker. With a simple backup of your files, if you are attacked with ransomware you can format the drive, reinstall your operating system, and recover your files without paying the hacker any money.
  7. Detection and Alerting – Building systems into your network that will alert you when an abnormal condition exists is important to alerting you as an attack is happening. Having a system that collects and analyzes system logs (SIEM) and can alert you in real-time as malicious activity is occurring is essential to reacting to an attack before they have compromised your network. Most social media sites will also alert you to abnormal or suspicious activities, so don’t ignore those messages.

While you will never be 100% protected from cyber-attacks as long as you use the internet, it is important that you learn how to protect yourself to reduce the risk of a successful attack.

Common Database Design Mistakes

Project Management

When creating a new database instance, people will often make mistakes. While I can’t list all the mistakes that people can or will make, I hope this brief list will help you know what mistakes are possible, and help guide you to not making as many mistakes. Sometimes we attack a design problem with the idea that we will just get the work done, but most times it is better to take the extra time to do it right.

I’m not perfect, and I have made these (any many other) mistakes in database design. I’m not trying to tell you what to do or even how to do it. I’m just trying to take my lessons learned and provide a simple list so that you might not make the same mistakes. I also want to point out that no list will ever be the only way to do anything. With database design questions, the best answer is usually “it depends”. When considering the many variables that make up your environment, you will need to make many decisions that help your database instance work best in your unique environment. You have to take into account the personnel you are working with, limits of your hardware, company policies, etc.

Database design and implementation is the cornerstone of any database related project and should be treated will the importance that deserves. If you do your job really well, people will tend to minimize how important your job is in getting their  projects completed. Like a police department that does a good job catching and locking up criminals, people start wondering why they need so many policemen when the crime rate goes down. People might start asking why they need your help in getting good database design, but it will only take a few failed projects for them to come back to you for your professional help.

Continue reading “Common Database Design Mistakes”

Using PowerShell to Manage SQL Server Audits

PowerShell is a power scripting tool that can also be used to manage your SQL Server audits. In this article by Colleen Morrow we learn some of the advanced techniques. You can also start at the beginning here.

Creating an Audit Object

The first step in implementing SQL Audit is to create the audit object, so that’s where we’ll start. Let’s look at the whole script and then break it down.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$instance = 'MyServer'
$auditName = $instance+"_TestAudit"
$auditDir = '\MyServerD$Audits'
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance
$newAudit = new-object Microsoft.SqlServer.Management.Smo.Audit($srv, "$auditName")
$newAudit.DestinationType = [Microsoft.SqlServer.Management.Smo.AuditDestinationType]::File
$newAudit.FilePath = $auditDir
$newAudit.MaximumRolloverFiles = 10
$newAudit.MaximumFileSize = 100
$newAudit.QueueDelay = 1000
$newAudit.Create()
$newAudit.Enable()

The first thing we’re doing is simply declaring some variables to hold our instance name, the name of the audit we want to create, and the folder where we want our audit file to be written. For re-usability, we could even make these into parameters, but I wanted to keep this simple. Next we create a new SMO connection to our instance with the command

1
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance

Continue reading “Using PowerShell to Manage SQL Server Audits”

10 Cybersecurity Interview Questions

Interview

If you are interested in getting a job in cybersecurity and starting a rewarding career in protecting information systems, you should be prepared to answer a wide range of questions to demonstrate your knowledge of the subject matter. Generally speaking, cybersecurity is the protection of information or data stored on computer systems from unauthorized access and malicious attacks.

I can’t predict the specific questions you will be asked, but I know the general category of the questions relevant for this type of position. Interviewers are interested in the candidates who have the necessary general technical knowledge, and any specific skills relevant to the specific position posted.

Continue reading “10 Cybersecurity Interview Questions”