A Fine Slice Of SQL Server
I’m doing some sql server security privilege troubleshooting , because a customer has reported an incident – they have privileges yesterday to a certain SQL table but today those privileges no longer. I’d like to know if there is a way to identify what changes were made and by which login those changes occured. Can you supply a method ?
Read on for the answer, though Tom has an important caveat.
Comments closedMika Sutinen builds a baseline:
I’ve recently had to work a bit more with the Microsoft Defender and the vulnerability assessment in Azure. Following those efforts, it dawned to me that the topic of security baselines is sometimes slightly misunderstood. So, in this post, we’ll look into what a security baseline should cover (and what they probably shouldn’t).
But first things first. Security baselines are provided by the Microsoft Defender for Cloud service, which I always recommend enabling for Azure workloads (unless there’s a 3rd party solution for it already). If you don’t have anything of the sorts enabled for your databases and servers, I highly recommend you go and turn Defender on. Seriously. Do it now.
Read on to learn more about why having a security baseline is so important and where to draw the cut-off between security and functionality.
Comments closedYou can’t just exec
DROP ROLE your_role_name;if it’s granted perms or other roles are granted to it. I had to go fishing to find all the grants to revoke them. Note: if you are worried about re-granting later, you can always fiddle with this to output the grants for these perms as a rollback.
Read on for a few scripts to help out with finding what that role owns, revoking rights, and reassigning ownership.
Comments closedVlad Drumea troubleshoots an issue:
This is one of those things that’s obvious for anyone with a bit of SQL Server and Windows experience, but stumps a lot of newcomers.
Especially when it comes to students going through tasks like creating and restoring databases.It’s also one of those things that I have to explain on a regular basis, so might as well have detailed explanation with examples I can direct people to in the future.
The latest example being this reddit post in r/SQL, where the wrongest answer has the highest upvotes.
Read on for a story about Operating system error 5 (Access is denied). And if I hear anybody switching the virtual service account running SQL Server to Local System, I will personally tell you it’s an awful idea and to stop it.
Comments closedAndy Brownsword works around an issue:
I recently had an issue when sourcing data in Power BI from a server which was accessed by a DNS alias. Here I’ll demonstrate the issue and how to resolve it.
After entering the server details, we could be greeted with the message below:
The server name provided does not match the server name on the SQL Server SSL Certificate. Please contact your administrator or try changing your Connection encryption settings
Click through for a solution if you cannot re-issue the certificate with the relevant DNS alias.
Comments closedSean Gallardy digs into an interesting problem:
Quite the interesting problem was brought to me by a friend, in that they had a database level audit setup and were having `select` queries trigger audits that the object had updates run on them. The question became, how is this possible as a select query doesn’t update anything. Let’s start with a quick setup and original output.
Read on for the solution, as well as a much more common place in which you’d find this issue.
Comments closedGilbert Quevauvilliers sets up a new warehouse:
In this blog post I am going to show you how to create a Microsoft Fabric Warehouse, where the owner will be the Service Principal.
As mentioned in the blog post here are some of the advantages of having the Service Principal as the Warehouse Owner.
- Using a Service Principal to create the warehouse avoids issue where the person who created the warehouse leaves the organization and issues arise when the users account is deleted from Entra ID.
- You avoid the painful logging in with the user account to ensure the password remains updated.
- The organization now owns the warehouse and not an individual user.
I will show you how I created a Warehouse with the owner being a Service Principal this using a Microsoft Fabric Notebook
Click through for the notebook and additional commentary.
Comments closedRayis Imayev hides some information:
One way to protect sensitive information from end users in a database is through dynamic masking. In this process, the actual data is not altered; however, when the data is exposed or queried, the results are returned with modified values, or the actual values are replaced with special characters or notes indicating that the requested data is hidden for protection purposes.
In this blog, we will discuss a different approach to protecting data, where personally identifiable information (PII – a term you will frequently encounter when reading about data protection and data governance) is actually changed or updated in the database / persistent storage. This ensures that even if someone gains access to the data, nothing will be compromised. This is usually needed for refreshing the production database or dataset containing PII data elements to a lower environment. Your QA team will appreciate having a realistic data volume that resembles production environment but with masked data.
Rayis goes into depth on the process. I could also recommend checking out the article on row filters and column masks for more information.
Comments closedJoey D’Antoni doesn’t have time to create a password:
Managed identities in Microsoft Entra have simplified authentication in Azure, particularly If all of your resources are in Azure. In this basic example, I’m going to walk you through connecting a Web App (aka App Service) running an API server to an Azure SQL Database. The best part? There are no passwords involved–let’s get started.
Read on for the process. It’s interesting to see how far identity-based security has developed in Azure over the years.
Comments closedVlad Drumea points out that SQL injection is still a thing:
SQL injection, also refereed to as SQLi, is a security vulnerability that allows attackers to modify the queries that an application makes to its underlying database.
This type of vulnerability can allow attackers to interact with data that they are not normally able to access, including data belonging to other application users.
For at least a decade, injection attacks were either number one or number two on OWASP’s top 10 list. It dropped all the way to number 3 in 2021. We’ll see how it looks with the 2025 OWASP top 10 application security vulnerabilities list, but in the meantime, check out Vlad’s post.
Comments closed