A Fine Slice Of SQL Server
Koen Verbeeck talks permissions:
We are starting a new analytics project in Microsoft Fabric, and our data will land in a warehouse. This is the first time we’re using Fabric, and we are wondering about the different options for sharing access to a warehouse we developed in a workspace.
Click through for more information on providing and limiting access to data in a Microsoft Fabric warehouse.
Comments closedThanks for tuning into our posts for the 30 SQL Server security checks in 30 days series this month. I want to recap the entire month of posts with a few homework assignments to get you started today.
Read on for those three assignments, including adopting a security mindset, remembering that humans tend to be the weak points of security, and trying out sp_CheckSecurity.
Comments closedJaime Garcia de Alba makes a connection:
In this guide I am going to show steps to connect Windows Azure VM to Azure SQL DB using Managed Identity covering create user in Azure SQL DB , connect using SSMS and connect using powershell
Click through to see how you can get this information from an Azure VM and connect to the SQL Server instance either via SSMS or Powershell.
Comments closedSagheer Ahmed sets up TLS encryption:
This technical guide provides detailed instructions on how to configure SSL/TLS encryption for SQL Server using a self-signed or CA-issued certificate. Secure your data transmissions and enhance your security posture with these essential steps.
Read on for the instructions.
Comments closedJeff Iannucci gives us the details:
SQL Server database ownership may seem like an insignificant concern, but choosing the wrong owner for your database can be a main contributor to security disasters like ransomware. Let’s talk a bit about how to choose an owner that doesn’t create a huge security vulnerability for you and your SQL Server instance.
(Note: this isn’t the same as being in the db_owner role, although we will look at that later in this post.)
Click through for more information. This is one of the big reasons to avoid cross-database ownership chaining or setting TRUSTWORTHY on any database. Jeff has another way of resolving this particular problem that works, but the best solution is not to use either of those features.
Comments closedMike Lynn talks Transparent Data Encryption:
Transparent Data Encryption uses certificates in its architecture for protecting your data while at rest. One attribute of a certificate is they have an expiration date. Certificates expire for a couple reasons, but the main reason is to enforce security. When a website certificate expires it forces the website owners to get a new certificate by proving they are who they say they are with a trusted third party.
SQL Server certificates that are used for TDE also have an expiration date, but these dates are only checked when you are creating a self-signed certificate using the “CREATE CERTIFICATE” T-SQL command. If you don’t supply an expiration date when creating your certificate SQL Server will assign one that is 1 year into the future.
Read on to learn more about how it works with TDE. I will say that with encrypting backups, SQL Server does care about the expiration date when it comes to creating a new encrypted backup, but not when it comes to restoring a backup.
Comments closedJeff Iannucci talks xp_cmdshell:
That most likely means the hacker in the incident gained access to SQL Server, and then used xp_cmdshell to open a Windows command shell. With that shell opened, they could then pass DOS and/or PowerShell commands to collect information and download malware onto the server.
It sounds dangerous. It is. But whether or not xp_cmdshell is enabled is irrelevant.
Exactly. This is something Sean McCown has harped upon for years, and I’ve done my best to follow. You can do bad things with xp_cmdshell, but disabling it doesn’t actually help for reasons Jeff mentions. Instead, assuming you need xp_cmdshell for some work, don’t go beyond the pre-set limitations (where a user needs sysadmin or CONTROL SERVER) and it’s fine.
Amit Khandelwal has good news for us:
We’re thrilled to share that adutil, the Active Directory Utility for SQL Server, is now officially supported on RHEL 9 and Ubuntu 22.04. If you’ve been waiting for this, your patience has paid off! Let’s dive into the details.
I had released a video on Active Directory in SQL Server on Linux that included a workaround to get adutil going on Ubuntu 22.04. I’m glad that part of the video is now obsolete.
Comments closedJeff Iannucci talks about OLE Automation:
It’s rare to see OLE Automation procedures enabled on a SQL Server instance. Most folks aren’t using these specially system procedure because they didn’t have a need to use them, have a compliance requirement that prohibits using them, or they tried using them and had adverse results. As the Microsoft documentation on OLE Automation stored procedures notes:
“Don’t directly or indirectly call Automation procedures from any SQL Server common language runtime (CLR) objects. Doing so can cause SQL Server to crash unexpectedly.”
But if this setting is enabled in any of your SQL Server instances, you need to consider it similarly to the xp_cmdshell setting. By that I mean: this is probably not a problem, but you should try to figure out why the setting is enabled, and how its associated system stored procedures are being used.
I find this perfectly reasonable. There’s a lot of fear around xp_cmdshell, when in practice, it doesn’t affect security at all unless you completely mess things up and start granting rights to non-sysadmins.
Comments closedJeff Iannucci shares good advice:
If you have ever used our free tool to check SQL Server security, you may have seen the check for the “TRUSTWORTHY database owned by sysadmin” show up as one of the highest of priority items, requiring action. When we started reviewing the security permissions and configurations for our clients’ instances, we didn’t expect to find it very often since TRUSTWORTHY database setting is off by default.
Unfortunately, this has been discovered with some frequency, and when combined with a few other common practices, it presents a tremendous vulnerability to escalate privileges for both authorized users and hackers.
Read on to learn more about this. And to supplement, I will once again link Solomon Rutzky’s outstanding guide on the topic.
Comments closed