User Security and Risks to Azure Costs May 16, 2019 by Timothy Smith We’ve looked at both the organization and development side of managing Azure costs. One risk we have is attackers who compromise an account and mis-scale resources (such as scaling up), driving up our costs. Another scenario is attackers scaling resources too low that affects client’s ability to do their work (performance problems) – a separate risk that may result in lower costs on the cloud side, but higher costs against our reputation. A third risk is reconnaissance of our Azure use: this allows the attackers to get information about our design and later make a wide range of attacks that will appear as normal to us – in this case, Azure costs may be only one of the impacts with other impacts being as severe. Read more »
Azure Costs Tracking with Security and Design Considerations April 30, 2019 by Timothy Smith Azure costs can quickly mount, without careful supervision and management. This article will detail cost mitigation strategies using security and design Read more »
PowerShell encrypt password techniques for SQL Server April 18, 2019 by Milan Rancic Challenge Automating SQL Server tasks with PowerShell can increase productivity and save time, but how do use PowerShell encrypt password techniques to avoid saving them as plain text. This article will show you how. Read more »
DevSecOps: Developing with Automated Security Testing March 6, 2019 by Timothy Smith A key component of DevSecOps and identical to running unit tests to validate code after a build, running automated security testing after an application has been deployed (such as automated penetration tests) can provide us with a tool that identifies security risks. As we’ve seen recently, there’s been a growth of many companies experiencing information being compromised and with the development culture of “move fast and break things”, I expect this trend of successful attacks will continue. Before we look at our options for automating this testing, we want to be aware of its limits, evaluate the requirements, and consider common designs that are useful. Read more »
How to Programmatically Pass Credentials in an Embedded Power BI Report November 20, 2018 by Sifiso W. Ndlovu In the article, How to embed a Power BI Report Server report into an ASP.Net web application, we looked at available options for embedding a Power BI Report Server report into an ASP.NET web application. Since the publication of the article, I have received several questions relating to how one goes about programmatically passing credentials for report server connection within an embedded Power BI Report Server report. The simple answer to such questions is that it is currently not possible to implement user impersonation in an embedded Power BI Report Server. However, like in most scenarios, there are workarounds that one could temporarily employ – at least until Microsoft comes up with a permanent solution to what is becoming a top requested feature at ideas.powerbi.com. Read more »
How to secure Reporting Services with Group Managed Service Accounts November 7, 2018 by Craig Porteous 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 »
SQL data classification – Add sensitivity classification in SQL Server 2019 October 31, 2018 by Rajendra Gupta SQL Server 2019 offers powerful new features to help in safeguarding your data and complying with various privacy regulations, which we’ll be covering in this article Read more »
Considerations and concerns when setting up Database mail for SQL Server August 31, 2018 by James Rhoat Database mail is widely used by DBAs and companies around the world and is one of the features of SQL Server that can be very important for startups. That is because it is a cheap solution for getting alerts from your SQL Server for potential hardware issues, early warning signs of corruption, along with potential resource constraints. However, it can be misused or pose potential security issues. Before we start, to my myself clear I am in favor of configuring Database mail for the DBA team when done properly. In the rest of the article, we will touch on some of the common mistakes people make with database mail and their SQL Servers. We will not discuss the setup of this feature as it was well documented by Bojan Petrovic on SQLShack. Read more »
Is SQL Server Always Encrypted, for sensitive data encryption, right for your environment July 27, 2018 by Aamir Syed So, your manager wants you to figure out how to encrypt sensitive Data? Well, Microsoft has introduced a fairly easy way to configure feature called Always Encrypted. Read more »
SQL Server security considerations with open source tools June 21, 2018 by Timothy Smith 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 »
Securing access for SQL Server auditing May 21, 2018 by Timothy Smith Our organization must restrict permissions and prove to an independent party that we investigate access and restrict permissions. We restrict permissions to objects using least permissions and give full access to an exceptional few based on an organizational design that follows best practices. What can we do additionally that will help us prevent unauthorized access or catch when someone who shouldn’t have access is able to infiltrate our systems, especially in the context of showing a third party that we track this. Read more »
SQL Server data security feature RLS (Row-Level Security) and GDPR March 9, 2018 by Prashanth Jayaram 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 »
Impact of CLR Strict Security configuration setting in SQL Server 2017 February 13, 2018 by Sifiso W. Ndlovu 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 »
Using production data for testing in a post GDPR world January 15, 2018 by Brian Lockwood To SQL Server DBAs who are the shepherds of data in organizations, key GDPR questions, in general, center around whether data will need to be treated differently, safeguarded more etc. and specifically, as it relates to allowing production data to be used in testing. That will be the focus of this article as we’ll work our way through the details of this regulation as well as various authoritative articles on the subject, to address this key question. Then we’ll look to ways and means to potentially ameliorate our findings to provide alternatives and workarounds if possible. Read more »
How to configure Always Encrypted in SQL Server 2016 using SSMS, PowerShell and T-SQL October 2, 2017 by Prashanth Jayaram In an era of remote storage and retrieval of data, including the cloud, data security plays a vital role, especially since it’s vulnerable during the transit. Situations like database backup or copy from or to the cloud, there is always a risk of data exposure to outside world lurking around one corner or the other. We have seen a noticeable surge in the technologies around protection and security of data from the world full of unsafe hands. Efforts are being made to protect data at a very granular level of the encryption hierarchy. Protection of business data cannot be stressed upon more. Read more »
How to drop a role in a SQL Server Database September 6, 2017 by Jefferson Elias 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 »
How to drop a SQL Server Login and all its dependencies July 19, 2017 by Jefferson Elias Introduction Sometimes, we need to drop a SQL Server Login because we gave an access to a person who left, this login was used by an application that has been decommissioned for example. Read more »
SQL Server Logins, Users and Security Identifiers (SIDs) July 12, 2017 by Simon Liew 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 »
SQL Server Database Architecture and Audits for Enhanced Security June 5, 2017 by Timothy Smith 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 »
Digital certificates: when and where to use them May 26, 2017 by Nikola Dimitrijevic Digital certificates are form of electronic authorizations used to verify the identities of persons, companies, computers, and other network entities. Read more »
How to set and use encrypted SQL Server connections May 8, 2017 by Daniel Tikvicki 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 »
Understanding Database Backup Encryption in SQL Server May 3, 2017 by Prashanth Jayaram Terabytes of data, millions of rows; the entire business depends on this — a database administrator’s responsibility is as massive as it sounds. Apart from keeping the data secure, a database administrator also has to keep the system up and running, and restore the data as needed, in case of a failure, with minimal impact to the business. Read more »
Five ways to protect your data in Azure SQL Database April 28, 2017 by Minette Steynberg When storing data in the cloud the main concern companies generally have is whether or not their data is safe. And what can be done to ensure that the following 4 scenarios are addressed: Read more »
Security considerations for your Azure SQL Databases April 27, 2017 by Kaloyan Kosev You have to agree with me, when public clouds were introduced your thought was that you will never put your production data in there. Our idea was that your data is exposed, insecure and vulnerable. This was not the case back then, it is not case at the moment as well. Read more »
How to add a TDE encrypted user database to an Always On Availability Group April 27, 2017 by Ahmad Yaseen 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 »
