Press "Enter" to skip to content

Category: Security

Wireshark and the SQL Server Browser Service

Published 2019-06-06 by Kevin Feasel

Joy George Kunjikkur takes a look at the SQL Server Browser service using Wireshark:

Normally on development environment the 1434 port would be open and SQL Server Browser running in SQL Server machine. If developers code based on that assumption, it may fail in higher environments. Most of the developers know what might have happened. But some may not and ends up in complete darkness. The symptom of this issue will be connection failure from application to SQL Server database.

This post is mainly to demonstrate how to check network failures on SQL Server Browser operations. The prerequisite is the availability of Wireshark in the machine where application runs and basic knowledge how to run the same. The above link has some screenshots which would help to run Wireshark for UDP connections.

This is usually the first service I disable post-installation.

Comments closed

Antivirus and SQL Server

Published 2019-06-06 by Kevin Feasel

Randolph West proffers advice should your IT team require installing antivirus software on a server with SQL Server running:

This is why it is documented that we should exclude SQL Server from any AV (anti-malware) detection products, so that it can get on with doing what it does best.

Yes, it’s formally documented. This is why we should read documentation when installing things. While it’s super-easy to click “Next,” “Next,” “Next,” that should not be the case with a complex product like SQL Server.

Read on for the list of exceptions you should add and processes to avoid scanning.

Comments closed

Cosmos DB Role-Based Access Control

Published 2019-06-05 by Kevin Feasel

Hasan Savran takes us through role-based access control in Cosmos DB:

Role-based access control (RBAC) is available for Azure CosmosDB. By using RBAC, you can manage who has access to CosmosDB resources. You need to have a profile in Azure Active Directory to assign RBAC roles to users, groups or other targets. There are 4 built-in roles you can use. Microsoft announced the CosmosDB Operator
 role recently.

Click through for that list.

Comments closed

Securing Power BI

Published 2019-06-04 by Kevin Feasel

Andy Jones has 10 tips for securing your Power BI infrastructure:

9 Sharing Externally

Power BI offers the ability to share reports outside of your organisation or even publish to the public internet. If this causes you to shudder, turn these features off. Your Power BI admin (remember them from above) should open the admin portal and move a slider – problem solved.

Turn off sharing externally (unless needed)

Click through for the full list.

Comments closed

Testing dbcreator Privileges

Published 2019-06-03 by Kevin Feasel

Shane O’Neill wants to know whether the dbcreator built-in role has the ability to back up the databases it creates:

I knew from reading the documentation that dbcreator grants permissions to create, alter, drop, and restore databases. My question was does it give permission to backup databases?

It seems to give everything else so is backup databases just missing there? Or is it intentionally left out?

To find out whether it does, click on through.

Comments closed

Data Classifications on Azure SQL DW

Published 2019-05-31 by Kevin Feasel

Meagan Longoria takes us through data classifications on Azure SQL Data Warehouse:

Data classifications in Azure SQL DW entered public preview in March 2019. They allow you to label columns in your data warehouse with their information type and sensitivity level. There are built-in classifications, but you can also add custom classifications. This could be an important feature for auditing your storage and use of sensitive data as well as compliance with data regulations such as GDPR. You can export a report of all labeled columns, and you can see who is querying sensitive columns in your audit logs. The Azure Portal will even recommend classifications based upon your column names and data types. You can add the recommended classifications with a simple click of a button.

But read the whole thing, as Meagan sees a problem with it when you use a popular loading technique.

Comments closed

TDE + Copy-Only Backups

Published 2019-05-29 by Kevin Feasel

Jovan Popvic shows how you can take a copy-only backup of a TDE-protected database in Azure SQL Managed Instance:

We are recommending to rely on automatic backups only, with the build-in restore functionality to restore a database from a point-in-time, restore a database to another instance (for instance from production to dev)or Geo-restore functionalities to move your database. These automatic backups can be kept up to 35 days. These built-in automatic backups are secure and enables you to be fully compliant. In this scenario COPY_ONLY backups are only in some specific cases.

Strict TDE protection don’t allow you to take your own custom backups. If you need a backup of a TDE protected database, you would need to temporary disable TDE, take a backup, and then enable TDE again.

It’s not really a Managed Instance-specific thing, but rather TDE: if you want to take a non-encrypted backup of an encrypted database, you’ve got to kill encryption first.

Comments closed

SQL Server and Recent Security Patches

Published 2019-05-20 by Kevin Feasel

Allan Hirt takes us through recent security updates and how they pertain to SQL Server:

After Spectre and Meltdown a few months back (which I cover in this blog post from January 4), another round of processor issues has hit the chipmaker. This one is for MDS (also known as a ZombieLoad) This one comprises the following security issues: CVE-2019-11091, CVE-2018-12126, CVE-2018-12127, and CVE-2018-12130. Whew! Fun fact: CVE stands for “Common Vulnerabilities and Exposures”.

As of now, this is only known to be an Intel, not AMD, issue. That is an important distinction here. The official Intel page on this issue can be found at this link. This issue does not exist in select 8th and 9th generation Intel Core processors as well as the 2nd generation Xeon Scalable processor family. (read: the latest stuff) 

Be sure to read through all of this. Most of the notes are for non-SQL Server items which have an impact rather than bugs in SQL Server itself, but that doesn’t make patching any less important.

Comments closed

NT AUTHORITY\ANONYMOUS Error Editing Procedures

Published 2019-05-09 by Kevin Feasel

Kenneth Fisher takes us through a security issue:

If you have to deal with linked servers then you probably have or will run into the following error:

Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’

But I’m not trying to use the linked server. I’m trying to create/alter a stored procedure.

Kenneth explains why you might get this even when you’re just editing a procedure and not directly hitting a linked server, as well as a few tips for fixing the issue.

Comments closed