Introduction

Security is paramount when it comes to database operations. Microsoft SQL Server Reporting Services (SSRS) is a powerful tool for generating dynamic reports from SQL Server databases. But with its vast capabilities, it's crucial to implement it securely.

One often overlooked security measure is limiting SSRS reports to readonly access to the database. This article dives into the reasons behind this and the dangers of enabling stored procedures with more than just read capabilities.

The Risks of Unrestricted Access

At first glance, it might seem convenient to grant SSRS reports full access to a database. However, this approach presents numerous potential security issues.

• Data Manipulation: A report with write access can unintentionally modify data, which might lead to data inconsistencies.
• Data Leakage: If an unauthorized person gains access to an SSRS report, they might be able to extract confidential data.
• Increased Vulnerability: Granting more permissions than necessary increases the surface area for potential attacks.

Stored Procedures: Not Just for Reading

Stored procedures are widely used in SQL Server because of their efficiency and modularity. But when used with SSRS, caution is advised.

EXEC report.sales_orders_today;

Consider the above stored procedure. If it's meant for reading data, it's fine. But what if it has capabilities beyond that?

With a stored procedure you could basically do whatever data manipulations you would like.

EXEC shipping.mark_for_loading @id = 'XXX';

The stored procedure above is letting a user mark a shipment for loading. With this possible from SSRS you wouldn't even need to start your warehouse application. You could build your own application from SSRS. Isn't it great? 😍

But WAIT a minute... is this really safe? 😱

Potential Dangers of Unrestricted Stored Procedures

Think again, if SSRS is granted access to manipulating data, a malicious user could easily go havoc on you database.

• Data Deletion: A stored procedure with delete capabilities might accidentally remove valuable data.
• Unauthorized Changes: A malicious user can use the stored procedure to introduce unauthorized changes.
• System Exploits: Some stored procedures can execute system-level commands, which might be exploited by attackers.

Setting Up Schemas and Logins for SSRS Security

As you venture deeper into the world of SQL Server Reporting Services (SSRS), understanding how to securely set up schemas and logins becomes paramount. These components play pivotal roles in determining how data can be accessed and manipulated.

Here are some best practices to ensure your SSRS configurations are both functional and secure:

1. Schema Segregation

Organizing database objects into distinct schemas based on their roles and access levels helps in achieving clarity and granularity in permissions.

• Data Schemas: Contain tables, views, and other objects housing your data.
• Report Schemas: Dedicated exclusively for SSRS, these schemas contain objects like stored procedures or views tailored for reporting purposes.

2. Principle of Least Privilege (PoLP)

Always assign the minimum necessary permissions to any login or role. For SSRS, a readonly role for the database is should be sufficient. Otherwise you should reevaluate how you use your reports.

3. Dedicated SSRS Service Account

Create a dedicated SQL Server login for the SSRS service. This account should:

• Only have permissions to the Report Schemas.
• Not be a member of the db_owner role.
• Be regularly audited for unusual activity.

4. Strong Password Policies

For SQL Server logins, enforce a strong password policy. This includes:

• Regular password rotation.
• Using complex passwords with a mix of characters, numbers, and symbols.
• Disallowing common or previously breached passwords.

5. Auditing and Monitoring

Implement regular audits on your SSRS configurations. This helps in:

• Identifying any misconfigurations or excessive permissions.
• Detecting suspicious activities, like multiple failed login attempts.
• Ensuring compliance with industry regulations.

6. Avoid using SA Account

Never use the System Administrator (SA) account for SSRS. The SA account has unrestricted access, making it a prime target for attackers. Instead, use the dedicated SSRS service account mentioned earlier.

Conclusion

While SSRS offers vast capabilities for dynamic reporting, it's crucial to employ best security practices. Limiting SSRS reports to readonly access and ensuring stored procedures only read from tables significantly reduces the chances of security breaches and data leakages. Always follow the principle of least privilege: grant only the minimum required permissions.

Stay Informed, Stay Secure

Database security is an ever-evolving field. By understanding potential vulnerabilities and mitigating them proactively, you safeguard your data assets. Always be on the lookout for new developments and best practices.

Introduction

Database locks are often considered a necessary evil in the world of SQL Server management.

While they're essential for maintaining data integrity, they can also wreak havoc on warehouse operations.

In this tutorial, we'll delve into methods for identifying, troubleshooting, and resolving database locks in SQL Server —focusing on how these issues can disrupt your Warehouse Management System (WMS).

The Issue: Database Locks

Database locks happen. When multiple users access a database, SQL Server uses locks to ensure transactions are processed in an orderly manner. But sometimes, these locks can hang around longer than expected, leading to performance issues and operational disruptions.

A deadlock occurs when two or more processes are unable to proceed because each is waiting for another to release resources. This creates a standstill where none of the processes can continue execution.

Impact on Warehouse Operations

When database deadlocks occur in a WMS like Manhattan SCALE, the ramifications are immediate. Inventory updates lag, order processing slows down, and in worst-case scenarios, the entire system can grind to a halt.

For a bustling warehouse, these delays can translate to thousands of dollars lost in productivity.

Common Symptoms

• Delayed order fulfillment
• Inventory inaccuracies
• User interface lag in WMS software

Identifying Database Locks

The first step in solving a problem is recognizing there is one. To identify database locks, SQL Server provides various system views and functions. One commonly used query is:

SELECT * FROM sys.dm_tran_locks;

Troubleshooting

Once you've identified the locks, the next step is to determine why they occurred. This often involves examining query execution plans and transaction logs.

Fixing Database Locks

Here are some approaches to resolve database locks:

1. Kill the blocking process: This is a last-resort action. Use with caution.
2. Optimize queries: Better-written queries can prevent locks in the first place.
3. Use Query Hints: SQL Server allows you to add query hints to control locking behavior.

Conclusion

Understanding the ins and outs of database locks in SQL Server 2019 is crucial for maintaining an efficient Manhattan SCALE WMS. A small investment in monitoring and troubleshooting can yield significant operational benefits.

Introduction: The Need for Separate Databases

If you're tempted to run KPI reports directly from your production database, think again. There's a better way, and it's called a dedicated reporting database. In this article, we'll dive into the many reasons why a separate database is the smarter choice for reporting and KPIs.

Introduction: Why Unit Testing?

Untested code is broken code.

🧨 Period. 🧨

If you're not unit testing your applications, you're leaving room for bugs, making future changes harder, and possibly, compromising the quality of your software.

In this article, we'll delve into the art of unit testing in C#, focusing on the libraries FakeItEasy for mocking and Fluent Assertions for assertive, readable checks.

My Personal Experience with Unit Testing

When I first encountered unit testing, I was skeptical. It seemed like writing tests would almost double the amount of code I'd have to maintain. Why spend that extra time on something that wasn't a core feature of the application? 🤷‍♂️

Fast forward six months, a client requested some changes to the application. Thanks to the unit tests, I could make those changes swiftly and verify that everything still worked as intended. No new bugs were introduced, and what could have taken hours of manual testing and troubleshooting was done in a fraction of the time.

The "extra" time spent writing tests initially had saved me hours of work later on. 🕒

Benefits of Unit Testing

Before we jump into the technical aspects, let's understand why unit testing is crucial:

• Code Quality: Well-tested code usually equates to high-quality code.
• Maintainability: Unit tests act as a safety net, making future changes less risky.
• Documentation: Unit tests can serve as examples, thereby explaining how the code works.
• Confidence: The more you test, the more confident you are in your code's robustness.

Getting Started with Unit Testing in Visual Studio

To kick things off, you'll need to set up a test project in Visual Studio. Here's how:

1. Create a new Project and choose "xUnit Test Project (.NET Core)" or "MSTest Project (.NET Core)".
2. Add a reference to the project you're testing.
3. Install the necessary packages, like FakeItEasy and Fluent Assertions.

Writing Your First Unit Test

Let's start by writing a basic test for a Calculator class, which has a method to add two integers.

// Calculator class
public class Calculator
{
    public int Add(int a, int b)
    {
        return a + b;
    }
}

And here is how you would write a unit test for this method:

using FluentAssertions;
using Xunit;

public class CalculatorTests
{
    [Fact]
    public void Add_ReturnsCorrectSum()
    {
        // Arrange
        var calculator = new Calculator();

        // Act
        int result = calculator.Add(2, 3);

        // Assert
        result.Should().Be(5);
    }
}

Using FakeItEasy to Mock Dependencies

FakeItEasy helps in creating "fakes" or mocks for your dependencies. This makes testing in isolation easier. Let's say we have an `IOrderService` interface that we want to mock:

// Using FakeItEasy to create a fake object
var fakeOrderService = A.Fake<IOrderService>();

// Set up a fake behavior
A.CallTo(() => fakeOrderService.GetOrderById(1)).Returns(new Order { Id = 1, TotalPrice = 100 });

// Use the fake in test
fakeOrderService.GetOrderById(1).TotalPrice.Should().Be(100);

Conclusion

Unit testing is a must for any serious development effort. It offers an array of benefits from improving code quality to making your software maintainable.

FakeItEasy and Fluent Assertions make the process even more straightforward and enjoyable. Happy Testing!