A Fine Slice Of SQL Server
Shane O’Neill has a bit of fun:
Paranoia Kicks In:
He can’t have updated it…right?
I mean, he doesn’t have db_datawriter permission; I’ve denied him update permission on my table. I’m safe!…right?
This is a fun article which highlights just how hard getting correct, granular permissions can be.
Comments closedK Brian Kelley has started a series on securing applications which connect to SQL Server:
In conjunction with the webinar I gave last month for MSSQLTips, I’ve started an article series on application database security design.
Read Part 1 – Authentication for SQL Server
The issue with a one hour webcast is one can’t cover a broad topic like application database security design in any depth. However, these webcasts are useful because they point out, at a high level, what to look for. It was my intent all along to do the webinar and follow up with a series of articles that cover each topic in detail. I’m not sure how many articles I’ll end up writing, as I want to make sure I cover each topic in the depth it needs while still keeping the article length manageable.
This first post is all about comparing and contrasting credentials options and authentication methods.
Comments closedChris Sommer points out that the SQL Browser does something useful:
Bingo. The application server could not connect to the SQL Browser service on UDP 1434. So maybe now you’re asking why, and that’s kinda the gist of this post. The SQL Browser provides a valuable service when an application tries to connect to a SQL Server named instance. The SQL Browser listens on UDP 1434 and provides information about all SQL Server instances that are installed on the server. One of those pieces of information is the TCP port number that SQL is listening on. Without that info, the application has no idea how to reach to your SQL Server, and will fail to connect. This was our exact issue.
Do read this, though my preference is to shut off the SQL Browser because it’s a mechanism attackers can use for gathering intel on where SQL Server instances live.
Comments closedA fundamental component of SQL Server is the security layer. A principle player in security in SQL Server comes via principals. In a previous article, I outlined the different flavors of principals while focusing primarily on the users and logins. You can brush up on that article here. While I touched lightly, in that article, on the concept of roles, I will expound on the roles a bit more here – but primarily in the scope of the effects on user permissions due to membership in various default roles.
Let’s reset back to the driving issue in the introduction. Frequently, I see what I would call a gross misunderstanding of permissions by way of how people assign permissions and role membership within SQL Server. The assignment of role membership does not stop with database roles. Rather it is usually combined with a mis-configuration of the server role memberships as well. This misunderstanding can really be broken down into one of the following errors:
The belief that a login cannot access a database unless added specifically to the database.
The belief that a login must be added to every database role.
The belief that a login must be added to the sysadmin role to access resources in a database.
Worth reading. Spoilers: database roles are not like Voltron; they don’t get stronger when you put them all together.
Comments closedRecently I was performing a security audit for a client. One of the many things I had to check was the enforcement of password policies for any SQL Server created accounts.
You know, that policy that says you must have some combination of 6 or more characters, upper and lower case, a number, and special characters, etc.
These policies are controlled by the server policy settings and were something easy to check. The actual passwords and that they were safe, not so much.
Click through for the script.
Comments closedMy ZoraDB database clearly stuck in an encryption in progress state.
With the database being small the encryption process should not take long at all, I was confused.
I then decided to try and turn off the encryption.
Read on for the solution.
Comments closedKenneth Fisher shows off some special server-level permissions:
So what’s our shiny new permissions toy? Well, over time we have been getting more and more server level permissions that affect database level objects. A lot of these can be really useful and I’m hoping we will see more and more of them.
Click through for a couple helpful tables of permissions.
Comments closedCedric Charlier shows how to manage Azure SQL Database firewall rules from within Management Studio:
When you create a new Azure database, you usually need to open the firewall to remotely administrate or query this database with SSMS. An option is to create rules from the Azure Portal. It’s surely a convenient way to do it when you create a database but I prefer to keep a minimum of tools and when the Azure portal is not open, I prefer to not have to open it just to define a few firewall rules.
Opening the firewall with SSMS is a kind of chicken and eggs problem: to connect to your database/server, you need to open the firewall. Hopefully, SSMS has a great suite of screens to call the underlying API of Azure Portal and open the firewall for the computer running SSMS.
Cedric shows off sp_delete_firewall_rule but there’s also a corresponding sp_set_firewall_rule.
Comments closedJustin Goodwin shows how to view Always Encrypted data from within Management Studio:
I am now able to view the encrypted data. Why is this?
Because I am connecting to the database from the database server, I have access to the Encryption Certificate that was generated. I can verify this by opening certmgr.msc and browsing to Personal -> Certificates:
Justin then goes on to show what happens when you don’t have access to the certificate and how to provide access to another machine.
Comments closedMelissa Coates configures Azure Disk Encryption for an already-existing Azure VM:
As I discussed in my previous blog post, I opted to use Azure Disk Encryption for my virtual machines in Azure, rather than Storage Service Encryption. Azure Disk Encryption utilizes Bitlocker inside of the VM. Enabling Azure Disk Encryption involves these Azure services:
- Azure Active Directory for a service principal
- Azure Key Vault for a KEK (key encryption key) which wraps around the BEK (bitlocker encryption key)
- Azure Virtual Machine (IaaS)
Following are 4 scripts which configures encryption for an existing VM. I initially had it all as one single script, but I purposely separated them. Now that they are modular, if you already have a Service Principal and/or a Key Vault, you can skip those steps. I have my ‘real’ version of these scripts stored in an ARM Visual Studio project (same logic, just with actual names for the Azure services). These PowerShell templates go along with other ARM templates to serve as source control for our Azure infrastructure.
The Powershell scripts are straightforward and clear, so check them out.
Comments closed