Security

Craig Porteous

How to secure Reporting Services with Group Managed Service Accounts

November 7, 2018 by

With Reporting Services’ (SSRS) ability to scale out to multiple hosts, you may quickly come across the need to reuse domain accounts. Updating passwords for such accounts becomes a lengthy and risky process often requiring downtime. You may have a reliable process, set in stone for updating service account credentials every 30, 60 or 90 days or you could be stumbling across accounts that haven’t changed in 5 years and the passwords are in a shared text file somewhere. In either case, there’s risk that’s hard to mitigate without magically creating more hours in the day.

Read more »
Timothy Smith

SQL Server security considerations with open source tools

June 21, 2018 by

As our company has grown, we’ve recently added developers to our team who want to use open source tools (open source languages and libraries). In the past, we built and used our own custom libraries, but our new developers to prefer to use open source libraries or add new languages that require new libraries. We’re concerned that the use of open source libraries may not be secure and may introduce new inputs and outputs in our system that we don’t fully understand. What should we consider when we think about allowing open source software, tools or languages in our environment from the standpoint of security? Read more »

Prashanth Jayaram

SQL Server data security feature RLS (Row-Level Security) and GDPR

March 9, 2018 by

Of late, there’s been a lot of noise around the term, GDPR. Chances are, some of us even had to go through learning sessions targeted at IT professionals to learn about what this new standard of data protection means. GDPR is primarily a European privacy law which sets a new bar, globally, on privacy rights, compliance, and security. GDPR is mainly about protecting the rights of every individual, providing the individual with more control over his personal data. It dictates how data should be handled, managed and protected going forward, the individual’s choice being the prime focus.

Today, data is widespread; many corporations handle part of the data on the cloud and part of it on premises. Our focus being SQL Server, we shall talk about what capabilities Microsoft gives us in order to be compliant with these laws that come into effect on the 25th of May, 2018. We would have to modify our data handling procedures keeping the focus on the security of the data processing.

Read more »
Sifiso W. Ndlovu

Impact of CLR Strict Security configuration setting in SQL Server 2017

February 13, 2018 by

Every seasoned SQL Server developer will tell you that no matter how hard you try, there are just operations in SQL Server better implemented elsewhere than relying on native Transact-SQL language (T-SQL). Operations such as performing complex calculations, implementing regular expression checks and accessing external web service applications can easily lead to your SQL Server instance incurring significant performance overhead. Thankfully, through its common language runtime (CLR) feature, SQL Server provides developers with a platform to address some of the inconveniences of native T-SQL by supporting an import of assembly files produced from projects written in. Net programming languages (i.e. C#, VB.NET). I have personally found CLR to be very useful when it comes to splitting string characters into multiple delimited lines.

Read more »
Jefferson Elias

How to drop a role in a SQL Server Database

September 6, 2017 by

Introduction

Let’s say we have a database role that we don’t need anymore because we defined another security policy using another database role. What are the steps to follow in order to properly drop this database role? That’s the question this article will try to answer, covering as many cases as possible.

In the following article, we will consider the simple steps we can follow in order to do this task using both SSMS and T-SQL. Then, we will focus on some facts that will lead us to the conclusion that, if we do it this way, it won’t work every time. We will list some situations where it could fail and define a test case situation in order to create a stored procedure that will do the job correctly, in all cases bymanaging these situations.

Read more »
Simon Liew

SQL Server Logins, Users and Security Identifiers (SIDs)

July 12, 2017 by

Abstract

Logins and Users are basic security concepts in SQL Server. They are often, and incorrectly, considered to be pretty much one in the same so it is sometimes confusing to some SQL Server users. Another important security concept tied to a login and user in SQL Server is Security Identifiers (SID). This article will explain these concepts through a step-by-step demonstration. This article will focus on the Windows login and provide some specific usage scenarios.

Read more »
Timothy Smith

SQL Server Database Architecture and Audits for Enhanced Security

June 5, 2017 by

This year, digital security has risen as a top concern for many people, especially after the recent security compromises with Cloudflare in February and ransomware in May. Throughout the last two years, we’ve also seen the rise of sim-swapping where a hacker is able to extract a user’s cell phone data and compromise all emails and two-factor authentication. Unfortunately, I expect these security issues to continue and cause compromises for user’s data while negatively impacting companies involved.

Read more »
Daniel Tikvicki

How to set and use encrypted SQL Server connections

May 8, 2017 by

As the standard for securing the host-server interaction, Secure Sockets Layer or SSL is implemented in a Web environment. However, the SSL can provide the encrypted connection and data transfer between a particular SQL Server instance and a client application. A trusted SSL certificate validates the SQL Server instance when the client application requests encrypted connection (or vice versa), while the SQL Server must be configured to follow the certificate authority (CA). This means that a certificate must be “signed” by a trusted source.

Read more »
Ahmad Yaseen

How to add a TDE encrypted user database to an Always On Availability Group

April 27, 2017 by

SQL Server Transparent Data Encryption, also known as TDE, is a “data at rest” encryption mechanism that is introduced in SQL Server 2008 as an Enterprise Edition feature. TDE is used to perform a real-time I/O encryption for the SQL Server database data, log, backup and snapshot physical files, rather than encrypting the data itself, using either Advanced Encryption Standard (AES) or Triple DES (3DES) encryption.

Read more »