SQL Server and Recent Security Patches

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.

NT AUTHORITY\ANONYMOUS Error Editing Procedures

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.

Avoiding the Kerberos Double-Hop Issue

Michael Bourgon shows us one extra thing to keep in mind to avoid errors when trying to use Kerberos in a double-hop situation:

Yesterday I ran into the dread Kerberos Double-Hop when trying to set up a linked server.  Thought it was the standard “Add an SPN using the Microsoft Kerberos Configuration tool”.  Which didn’t fix it.

Click through to see what Michael had to do.

Orphaned Users in SQL Server

Dave Bland walks us through one way to fix an orphaned user:

In my many years of working as a DBA, I have encountered many disabled logins.  However, I have never really encountered what looks to be a disabled database user account.  I didn’t even think it was possible to disable a user account in a SQL Server database.  I checked the user account properties just to makes sure I was correct.  Sure enough, no option to disable a user account. This finally turned out to be a simple case of looks can be deceiving.

You can also use the sp_change_users_login procedure to fix orphaned users.

Permissions Requirements for ML Services

Niels Berglund looks at the permissions required to create external libraries with SQL Server Machine Learning Services:

This post is the fourth in a series about installing R packages in SQL Server Machine Learning Services (SQL Server ML Services). To see all posts in the series go to Install R Packages in SQL Server ML Services Series.

Why this series came about is a colleague of mine Dane pinged me and asked if I had any advice as he had issues installing an R package into one of their SQL Server instances. I tried to help him and then thought it would make a good topic for a blog post. Of course, at that time I didn’t think it would be more posts than one, but here we are.

These permissions are a bit more complicated than they might first appear to be.

Multi-Tenant Security in Kudu + Impala

Grant Henke shows how you can combine Apache Impala’s fine-grained authorization with Apache Kudu’s coarse-grained authentication for multi-tenant scenarios:

Kudu supports coarse-grained authorization of client requests based on the authenticated client Kerberos principal. The two levels of access which can be configured are:
1. Superuser – principals authorized as a superuser are able to perform certain administrative functionality such as using the kudu command line tool to diagnose or repair cluster issues.
2.User – principals authorized as a user are able to access and modify all data in the Kudu cluster. This includes the ability to create, drop, and alter tables as well as read, insert, update, and delete data.

Access levels are granted using whitelist-style Access Control Lists (ACLs), one for each of the two levels. 

Read on to see how to tie it all together.

Importing a Private Key From VARBINARY

Solomon Rutzky tries out various methods of loading certificates and private keys in SQL Server:

These results confirm that:
1. You can import a certificate from a VARBINARY literal
2. You can import a private key when creating a certificate from a VARBINARY literal
3. You cannot import a private key when creating a certificate from an assembly
4. Except when creating a certificate from an assembly, any combination of sources for the certificate (i.e. public key and meta-data) and the private key should be valid

It’s a long post with a lot of detail and quite a few tests, so check it out.

Data Breach Causes

Grant Fritchey takes us through some of the immediate causes of data breaches:

Fine, let’s talk about a business then. How about 24 million loan records, including bank account information, email, phones, social security numbers and all the rest. Yeah, that was sitting on an Elasticsearch database with no password of any kind. Oh, and the S3 storage was completely open too. Security? Is that still a thing?

How about exposing your entire client list because you left the password off the database (Elasticsearch again, is it hard to add a password to Elasticsearch). How about stacks of resumes (ElasticSearch, again, and MongoDB).

Those are just breaches from this year. If we go back, we can find more and more. Please, put a password on your systems. 

The OWASP Top 10 application security risks is out there and provides a lot of useful information on how to prevent the problems Grant mentions.

The Prevalence of Persistent XSS

Adrian Colyer has a review of a security-minded paper:

Does your web application make use of local storage? If so, then like many developers you may well be making the assumption that when you read from local storage, it will only contain the data that you put there. As Steffens et al. show in this paper, that’s a dangerous assumption! The storage aspect of local storage makes possible a particularly nasty form of attack known as a persistent client-side cross-site scripting attack. Such an attack, once it has embedded itself in your browser one time (e.g. that one occasion you quickly had to jump on the coffee shop wifi), continues to work on all subsequent visits to the target site (e.g., once you’re back home on a trusted network).

In an analysis of the top 5000 Alexa domains, 21% of sites that make use of data originating from storage were found to contain vulnerabilities, of which at least 70% were directly exploitable using the models described in this paper.

Adrian’s been on a security paper kick the last few days, so be sure to check those out.

Using Windows Authentication on Non-Windows Devices

Drew Furgiuele shows us how to connect to SQL Server using Windows Authentication if you’re not coming from a Windows device:

SQL Server supports different kinds of authentication mechanisms and protocols: the older NTLM protocol, and Kerberos. A lot of people cringe when you mention Kerberos because, well, Kerberos is hard. It’s arcane, it’s complex, and it’s hard to even describe unless you use it on the regular.

Simply put, it’s a ticketing and key system: you, a user, requests a ticket from a store, usually by authenticating to it via a username and password. If you succeed, you get a ticket that get stored within your local machine. Then, when you want to access a resource (like a SQL Server), the client re-ups with the store you got your initial ticket from (to make sure it’s still valid), and you get a “key” to access the resource. That key is then forwarded onto the resource, allowing you to access the thing you were trying to connect to. It’s way, way more complex than this, with lots of complicated terms and moving parts, so I’m doing a lot of hand-waving, but that’s the core of the system. If that kind of stuff excites you, go Google it, and I promise you’ll get more than you ever bargained for.

Kerberos is a scary beast to me, mostly because I don’t spend enough time working directly with it.

Categories

May 2019
MTWTFSS
« Apr  
 12345
6789101112
13141516171819
20212223242526
2728293031