Press "Enter" to skip to content

Category: Security

Whitelisting SQL Server Access

Published 2017-05-26 by Kevin Feasel

Patrick Keisler has a script to whitelist access to SQL Server:

A while back, I posted an article about creating a WhiteList for access to SQL Server. Since then I have received a bit of feedback that it was not working as designed. My apologies for taking so long, but I believe I have come up with a fix.

The main issue is the trigger will block some or even all access to the server after it’s created. As it turns out, the issues were really permission being denied. To see it in action, let’s create everything using the original code from here.

This is an interesting concept.  Resource whitelisting makes sense, though we tend only to use authentication-based whitelisting (i.e., creating logins).

Comments closed

Azure AD On Azure SQL DB

Published 2017-05-24 by Kevin Feasel

Arun Sirpal shows how to set up Azure SQL Database to use Azure Active Directory accounts:

I think it is important to highlight a couple of points, more specifically around the requirement of ADALSQL.DLL and proper setup of AD which I will highlight below and reference some links, please do this as it lays the foundation for you.

ADALSQL.DLL

You need ADALSQL.DLL which is part of the latest SQL Server Management Studio (SSMS) to test access. This stands for Active Directory Authentication Library for SQL Server.

This goes through some of the issues Arun had setting everything up and provides workarounds and explanations.

Comments closed

Securing Kafka-To-Spark

Published 2017-05-22 by Kevin Feasel

Mark Grover explains how to secure communications between Apache Kafka and Apache Spark:

However, to read data from secure Kafka in distributed fashion, we need Hadoop-style delegation tokens in Kafka (KAFKA-1696), support for which doesn’t exist at the time of this writing (Spring 2017).

We considered various ways to solve this problem but ultimately decided that the recommended solution to read data securely from Kafka (at least until Kafka delegation tokens support is introduced) would be for the Spark application to distribute the user’s keytab so it’s accessible to the executors. The executors will then use the user’s keytab shared with them, to authenticate with the Kerberos Key Distribution Center (KDC) and read from Kafka brokers. YARN distributed cache is used for shipping and sharing the keytab to the driver and executors, from the client (that is, the gateway node). The figure below shows an overview of the current solution.

This turns out to be a bit more difficult than I would have anticipated.

Comments closed

Azure SQL Data Warehouse Security

Published 2017-05-09 by Kevin Feasel

Grant Fritchey looks at what security measures are available within Azure SQL Data Warehouse:

Login Security

You have two core choices on logins. First, you have to create a SQL login at the server level for both Azure SQL Database and Azure SQL Data Warehouse. You can’t remove this or disable it (to my knowledge, and I’ve tried), so make the password a good one (and don’t lose it). You can then create other SQL logins, but this is not a recommended best practice. In fact, I wouldn’t do it at all unless I was forced because of some third party product (few of which currently support Azure anyway).

The next choice, the preferred choice, is to set up Azure Active Directory. With Azure AD you get all the functionality you’re used to with your local AD. Further, you can federate Azure AD with your local AD to control and manage the logins from within your network. You also get multi-factor authentication with Azure AD. We are talking real security here. Read through the documentation on setting up authentication to get it right. You can do the whole thing using Powershell commands, so there’s no excuse on automating it.

There aren’t as many security-related toggles as in an on-prem product, but Grant demonstrates what is available.

Comments closed

Handling Expiring Encryption Keys

Published 2017-05-08 by Kevin Feasel

Ed Leighton-Dick explains how safely to replace a SQL Server certificate which is about to expire:

So, now that we know what we need to rotate, how do we do it?

First, obtain a new certificate. SQL Server has the capability to generate its own certificates. For many purposes, that’s enough. However, if your company has to comply with auditing or regulatory requirements, you may need to obtain the new certificate from an outside source. Often, this is a third-party certificate authority. Some companies use a system called Encryption Key Management (EKM, also known as a Hardware Security Module, or HSA, after the device used to store the master key). (Obtaining an external certificate is a subject for an upcoming post.)

However you obtained the certificate, install it. Make sure to back it up securely, including the private key.

Next, add the new certificate to the symmetric key. The ALTER CERTIFICATE command has a clause that does just that – ADD ENCRYPTION BY.

Finally, remove the old certificate from the symmetric key. You’ll again use ALTER CERTIFICATE, but this time with the DROP ENCRYPTION BY clause.

Click through for instructions, including scripts.  Ed also explains how to update the certificate used with Transparent Data Encryption.

Comments closed

The Basics Of Always Encrypted

Published 2017-05-04 by Kevin Feasel

Josip Saban has an article on Always Encrypted in SQL Server 2016:

Always Encrypted is a client-side encryption technology that Microsoft introduced with SQL Server 2016. Always Encrypted keeps data automatically encrypted, not only when it is written, but also when it is read by an approved application. Unlike Transparent Data Encryption, which encrypts the data and log files on disk in real time but allows the data to be read by any application that queries the data, Always Encrypted requires your client application to use an Always Encrypted-enabled driver to communicate with the database. By using this driver, the application securely transfers encrypted data to the database that can then be decrypted later only by an application that has access to the encryption key. Any other application querying the data can also retrieve the encrypted values, but that application cannot use the data without the encryption key, thereby rendering the data useless. Because of this encryption architecture, the SQL Server instance never sees the unencrypted version of the data.

At this time, the only Always Encrypted-enabled drivers are the .NET Framework Data Provider for SQL Server, which requires installation of .NET Framework version 4.6 on the client computer, and the JDBC 6.0 driver. That will probably change in time, but these are the official Always Encrypted requirements as of April 2017.

This is a good intro to the topic if you aren’t familiar and are thinking of migrating to SQL Server 2016 or later.

Comments closed

Schema Modification Locks With CC Compliance

Published 2017-05-04 by Kevin Feasel

Lori Brown explains why you might see schema modification locks after enabling Common Criteria compliance:

We have a client who has no idea how or when Common Criteria was enabled on their production system. All they know is that performance has been slowly degrading. After collecting performance data, we found that there were high LCK_M_SCH_M waits which is a schema modification lock that prevents access to a table while a DDL operation occurs. We also found blocked process records where a LOGIN_STATS table in the master database was waiting a lot. This table is used to hold login statistics. When there are a lot of logins and outs there can be contention in this table.

When you enable Common Criteria compliance, something called Residual Information Protection (RIP) is enabled. RIP is an additional security measure for memory and it makes it so that in memory a specific bit pattern must be present before memory can be reallocated(overwritten) to a new resource or login. So with lots of logins and outs, there is a performance hit in memory because overwriting the memory allocation has to be done.

It’s worth reading the whole thing.

Comments closed

Stop Using Domain Admin!

Published 2017-05-04 by Kevin Feasel

Sean McCown has had it with people using Domain Admin accounts as service accounts:

If you paid close attention, you’ll notice the ‘DomainAdmin’ portion of that name.  Yep, you got it right… they were running SSRS under the domain admin account.  The Windows guy thought that it would be too much trouble to manage the permissions and get everything right on all the shares and DBs that it needed to access.

So this is when I pretty much lost it.  These guys were running  SSRS under a domain admin account because they were too lazy to do the right thing.  It’s unthinkable.  There may be some reasonable excuses why you’re not able to change your current security model to something better.  You may even be able to convince me that you’re not just being lazy.  But to actively be lazy about your security isn’t something I’m going to take lying down.  Hey, I know it’s your shop, and I know you can ultimately do whatever you like, but I’m going to make sure you know what you’re doing.

Your SQL Server (and related) service accounts should not be Domain Admin.  Period.  This isn’t one of those “Well, it depends…” types of scenarios; there is no reason ever to use an account with Domain Admin rights as a SQL Server service account, and it is security malpractice to do so.

Comments closed

Database Role Members

Published 2017-05-03 by Kevin Feasel

Nate Johnson has a quick demo showing how to add users to database roles:

The “old” method involves calling system stored-procedures, sp_addrolemember and sp_droprolemember, in which you pass the role-name and username.  The “new” method, supported starting with SQL 2012, is to use the command-phrases ALTER ROLE [role] ADD MEMBER [user], and ALTER ROLE [role] DROP MEMBER [user].

Nate shows both methods, so check it out.

Comments closed

One Surefire Guarantee For Slow Performance

Published 2017-05-03 by Kevin Feasel

Patrick Keisler troubleshoots an issue where the buffer pool gets flushed each morning:

What is the McShield service? A quick Bing search revealed that it’s one of the services for McAfee VirusScan Enterprise. Could this be the cause? To get a quick look at all the history, I filtered the application log for event IDs: 17890 and 5000. Each time McAfee got an updated virus DAT file, SQL Server soon followed that by paging out the entire buffer pool. I checked the application log on several other SQL Servers for the same event IDs, and sure enough the same events occurred in tandem each morning. I also got confirmation from the security administration team that McAfee is scheduled to check for new DAT files each morning around 8AM. Eureka!

This seems like it could be the cause of our paging, but a little more research is needed. Searching the McAfee knowledge base, lead me to this article about the “Processes on enable” option.

Enabling this option causes memory pages of running processes to get paged to disk. And the example given is “Oracle, SQL, or other critical applications that need to be memory-resident continually, will have their process address space paged to disk when scan Processes On Enable kicks in”. OUCH! So when the McAfee service starts up or it gets a new DAT file, it will page out all processes.

Fortunately, this is a setting you can turn off, and Patrick shows how.

Comments closed