Nitish Upreti shows us how to migrate data encrypted using Always Encrypted:
Our customers use the SQL platform to store volumes of high-valued data assets for their organization. With Always Encrypted, we want to deliver additional security while ensuring complete integrity of stored user data. To achieve this, in a regular workflow SQL Server/Azure SQL Database disallows an application to insert data directly into an encrypted column. If the application has not enabled Always Encrypted in the connection string, an insert statement targeting an encrypted column will fail. To insert a value into a column protected with Always Encrypted, the application must connect to the database with Always Encrypted enabled in the connection string and pass a plaintext value of the datatype configured for the target column. Subsequently, the SQL client driver encrypts the value and sends the ciphertext to the database. This ensures plaintext data is encrypted and stored appropriately.
Read the whole thing.
The Microsoft ODBC Driver 13 is now available (in preview form):
The preview ODBC drivers for Linux now supports Ubuntu, RedHat and SUSE. This is Microsoft’s first ODBC Driver for SQL Server release supporting Ubuntu. You can now enjoy enterprise level support while connecting to SQL Server from Ubuntu. It also updates the drivers to unixODBC driver manager 2.3.1 support.
Full interoperability with distributions of Linux is something I’ve waited a long, long time for. This is one tiny step closer.
Manoj Pandey investigates row-level security:
Here in this post I will talk about the new Block Predicate option available in the CTP 3.0 release. With this option we can restrict write access for specific users.
Block Predicates block all write operations like:
– AFTER INSERT and AFTER UPDATE
– BEFORE UPDATE
– and BEFORE DELETE
I want this to perform well in a busy production environment. I really, really do.
Warner Chaves has a video introducing Always Encrypted:
This is the big difference of this new feature, that the operations to encrypt/decrypt happen on the client NOT on SQL Server. That means that if your SQL Server is compromised, the key pieces to reveal the data are NOT with the server. This means that even if your DBA wants to see the data, if they don’t have access to the CLIENT application then they won’t be able to see the values.
Always Encrypted strikes me as something that will be incredibly useful for 2-3% of the population, somewhat painful for 3-5% of the population, and completely ignored by the rest. I’m currently on the fence about whether, three years from now, I will consider “completely ignored by the rest” to be a shame.
Kenneth Fisher asks if you check TRUSTWORTHY settings on your databases:
I wasn’t surprised (although a little disappointed) that out of the 9 people the answered only one person was, and of the rest 5 didn’t even know what TRUSTWORTHY is. I even had one person ask me later. That’s somewhat scary because under the right circumstances if you give me a database with TRUSTWORTHY turned on I can take over your instance. I’m NOT going to show you how but it isn’t terribly difficult.
I’ll admit that I have been a bit non-chalant about TRUSTWORTHY in the past, but turning it on is the smart move.
Andy Galbraith has a new permissions script:
I recently was tasked with this ticket:
Please add new login Domain\Bob to server MyServer. Grant the login the same permissions as Domain\Mary.
On the face of it, this seems relatively straightforward, right? It is the kind of request that we all get from time to time, whether as an ad-hoc task or as part of a larger project, such as a migration.
The catch of course is that it isn’t that easy – how do you know what permissions Mary has?
Andy’s script looks good. For bonus points, compare it to fn_my_permissions.
Jakub Szymaszek links to two articles on using SSIS with an Always Encrypted database.
The SQL Server 2016 Always-Encrypted feature is only supported by the ADO.NET provider currently. It is not supported by the OleDB provider and therefore any OleDB-provider-related transformation tasks such as Fuzzy Lookup will not support Always Encrypted feature.
In the “Execute SQL Task”, parameter binding for some encrypted SQL types is not supported, because of data type conversion limitations in Always Encrypted. The
unsupported types are money, smallmoney, smalldatetime, UniqueIndentifier, DatatimeOffset, time and date.
Add an ADO NET source connect to the table “Customers” (please ref to here get more detail about how to use ADO NET Source to connect encrypted table).
Then create a cache connection manager “Customer Cache” and set the column information as below:
Based on article #2, it looks like you can’t simply use a Lookup transformation on an Always Encrypted column; you need to pull the results into cache first and then query the cache. That’s not exactly difficult, but if you have an encrypted column, make sure you’re not writing those columns out in plaintext because of the cache option you selected.
Steve Jones shows us how to track logins with Extended Events:
I can select any number of fields for capture. You can see I’ve picked the client_hostname. I would also add the NT_Username and Username from the list. You could add more, but in this case, I’m more concerned with seeing who’s logged in.
I could add filters, but I choose not to. I click on Data Storage to determine where to store this data.
If you’re not already familiar with Extended Events, that grace period is slowly slipping away. Profiler’s going away sometime, and it’ll be a rude shock for a lot of DBAs. Don’t be one of those…
Allan Hirt with a public service announcement:
Microsoft recently published an official .NET team blog post reiterating that .NET Framework versions 4, 4.5, and 4.5.1 will no longer receive security updates, support, or hotfixes as of January 12, 2016. This was first announced back in August of 2014, so it’s not like this is new news, but I can say from experience virtually no one is talking about it. MS’ new post talks more about the upgrade path. To sum it up, you need to install .NET Framework 4.5.2, 4.6, or 4.6.1 to be considered supported when it comes to your .NET Framework version. Security is a real issue for many, and those responsible may not know that your version of .NET Framework could be a possible attack vector. Is your security team aware of this impending problem? How will this affect your version matrices (you do have those, right?)?
This is a cross-cutting concern, and I know a majority of database administrators aren’t directly responsible for .Net Framework patches, but work with whoever is responsible and keep them up to date.
Eli Leiba created a function to try to generate a blacklist against SQL injection:
The suggested solution presented here involves creating a user defined T-SQL scalar function that checks the input string for any suspicious key words that might indicate the SQL injection intents.
The function checks the input string against a set of pre-defined keywords that are known to be used in SQL injection cases.
I get the intent here, but blacklists don’t work.
The first line of defense that many developers come up with is a blacklist: we know that keywords like “select,” “insert,” and “drop” are necessary to perform a SQL injection attack, so if we just ban those keywords, everything should be fine, right? Alas, life is not so simple; this leads to a number of problems with blacklists in general, as well as in this particular case.
The second-biggest problem with blacklists is that they could block people from performing legitimate requests. For example, a user at a paint company’s website may wish to search for “drop cloths,” so a naïve blacklist, outlawing use of the word “drop” in a search would lead to false positives.
The biggest problem is that, unless extreme care is taken, the blacklist will still let through malicious code. One of the big failures with SQL injection blacklists is that there are a number of different white-space characters: hex 0x20 (space), 0x09 (tab), 0x0A, 0x0B, 0x0C, 0x0D, and 0xA0 are all legitimate white-space as far as a SQL Server query is concerned. If the blacklist is looking for “drop table,” it is looking for the word “drop,” followed by a 0x20 character, followed by the word “table.” If we replace the 0x20 with a 0x09, it sails right by the blacklist.
With this particular blacklist, you have a pretty high probability of false positives: the list includes dashes, “tran,” “update,” “while,” “grant,” and even “go.” These are tokens used in SQL injection attempts, but they’re also very common words or word segments in English. This means that if you’re trying to blacklist a publicly-accessible search box which reads common English phrases, the incidence of false positive is going to be high enough that the blacklist changes. But even if it doesn’t, a dedicated attacker can still get around your blacklist; as the old saying goes, the attacker only needs to be right once.
Kevin Feasel
2016-01-12
ETL, Security