Category: Security

Do not forget about the certificate! Warning: The certificate used for encrypting the database encryption key has not been backed up. Imagine if you need to recover the backup and you can’t? You will get the dreaded thumbprint error.
Msg 33111, Level 16, State 3, Line 25 Cannot find server certificate with thumbprint ‘0x78FAB5A2A5D593FD3C4E163C90B745F70AB51233’. Msg 3013, Level 16, State 1, Line 25
RESTORE DATABASE is terminating abnormally.
So make sure you respect this certificate (and the key) and back it up and re-create them on the target server for a successful restore.

In SQL Server 2016 and 2017, there’s no reason not to encrypt backups; the marginal cost is practically nil even if you’re low enough on disk space that you need to do backup compression.

Comments closed

Active Directory On CentOS

Published 2017-10-05 by Kevin Feasel

Drew Furgiuele shows how to configure a box running CentOS to work with Active Directory:

Before we start though, there’s a few things you’re going to need to have already set up:

An Active Directory Domain to test in, and rights to administer it. Since we’re going to be creating (and possibly deleting, if there are errors) computer objects and a service account, you’ll need a domain account with adequate permissions.
My example assumes you have a Microsoft DNS server running alongside your domain services. It is possible to use a separate DNS server to get this to work, but you might need some additional network configuration (see below). Also, depending on your environment, you might need a reverse lookup zone defined. If you notice long ping times or other weird lookups, I’d set one up in your DNS.
A machine (virtual or otherwise) that is running CentOS 7 or later (and this guide was written and tested against CentOS 7). For this demo, we’ll be using the Server (minimal install) installation option. If you’re new to Linux, you might opt a desktop version (server with a GUI). When you download a CentOS disk image to install it, you get all these options on the default media; you won’t need separate downloads

There are a few more prereqs, so read the whole thing. This route is easier than Ubuntu, as Drew notes.

Comments closed

Trusted Assemblies And Module Signing

Published 2017-10-02 by Kevin Feasel

Solomon Rutzky continues his SQLCLR and trusted assemblies series:

Ownership chaining is quite handy as it makes it easier to not grant explicit permissions on base objects (i.e. Tables, etc) to everyone. Instead, you just grant EXECUTE / SELECTpermissions to Stored Procedures, Views, etc.
However, one situation where ownership chaining does not work is when using Dynamic SQL. And, any SQL submitted by a SQLCLR object is, by its very nature, Dynamic SQL. Hence, any SQLCLR objects that a) do any data access, even just SELECT statements, and b) will be executed by a User that is neither the owner of the objects being accessed nor one that has been granted permissions to the sub-objects, needs to consider module signing in order to maintain good and proper security practices. BUT, the catch here is that in order to sign any Assembly’s T-SQL wrapper objects, that Assembly needs to have been signed with a Strong Name Key or Certificate prior to being loaded into SQL Server. Neither “Trusted Assemblies” nor even signing the Assembly with a Certificate within SQL Server suffices for this purpose, as we will see below.

Read on for more details.

Comments closed

SQL Server Import From Excel And TLS 1.0

Published 2017-09-29 by Kevin Feasel

Chris Thompson has an important notice if you import data into Excel from SQL Server:

As you may know, TLS 1.0 is being deprecated due to various known exploits and will no longer be PCI compliant as of June 30th, 2018 (see PCI DSS v3.1 and SSL: What you should do NOW below). You may also know that Microsoft has provided TLS 1.1/1.2 patches for the SQL Server Database Engine (2008+) as well as the client connectivity components (see TLS 1.2 support for Microsoft SQL Server below). What you may NOT know is that there is a popular feature in Excel to import data from SQL Server. See the screen print below from Excel 2016.
The problem with this feature lies in the fact that this menu option will, by default, leverage SQLOLEDB.1 as the OLE DB provider when connecting to SQL Server. This provider is an older MDAC/WDAC provider (see Data Access Technologies Road Map below) that comes built into the Operating System (including Windows 10) but DOES NOT support TLS 1.1+. So, if you have SQL Servers that have TLS 1.0 Server disabled, you will no longer be able to use this feature. You will receive an error similar to the one below. You will also receive the same or similar error if you have existing workbooks that use this feature and attempt to refresh those workbooks.

Chris has a workaround for current versions of Excel and notes that future versions will hide this particular import option behind a legacy wizards menu.

Comments closed

Effective Permissions In SQL Server

Published 2017-09-28 by Kevin Feasel

Daniel Hutmacher has a helper procedure to assist you in understanding who has what effective rights on a SQL Server box:

Principals, permissions and securables can all inherit each other. A principal could for instance be a group or a role, and will confer its permissions on to its group/role members. One permission can imply a number of other permissions – SELECT, for instance, requires you to also have VIEW DEFINITION rights to the object. Securables are also arranged in a hierarchy, with the server owning databases, which in turn own schemas that own objects, and so on.
To make things even more complicated, if you have multiple conflicting permissions (DENY and GRANT), the strictest rule applies, meaning that the effective permission is DENY.

Read on to get his procedure. For my money, the best method to get these details is to query sys.fn_my_permissions() but that requires that you be able to impersonate the user whose permissions you want to see.

Comments closed

Bugs With Backup Compression And TDE

Published 2017-09-25 by Kevin Feasel

Parikshit Savjani provides recommendations on combining backup compression with Transparent Data Encryption:

In past months, we discovered some edge scenarios related to backup compression for TDE databases causing backups or restores to fail, hence our recommendations have been

Avoid using striped backups with TDE and backup compression.
If your database has virtual log files (VLFs) larger than 4GB then do not use backup compression with TDE for your log backups. If you don’t know what a VLF is, start here.
Avoid using WITH INIT for now when working with TDE and backup compression. Instead, use WITH FORMAT.
Avoid using backup checksum with TDE and backup compression

Brent Ozar explains the risk:

When you install a new version of SQL Server, you get new features – and sometimes, you’re not told about them. For example, when 2016’s TDE compression came out, nobody told you, “If you back up across multiple files, your backups might suddenly be compressed.” You didn’t know that you had a new thing to test – after all, I don’t know a lot of DBAs who have the time to test that the new version of SQL Server successfully performs restores. They restore their production databases into the new version, test a few things, and declare victory – but testing restores FROM the new version’s backups isn’t usually on that list.

Keep up to date on those patches.

Comments closed

Clearing Cached Credentials With Powershell

Published 2017-09-22 by Kevin Feasel

Adam Bertram shows how to use Powershell and cmdkey to clear out cached credentials:

It is even easier to use cmdkey with PowerShell. IT can build a small wrapper script that can manage cached credentials on one remote computer at a time and perform the action just as quickly on multiple computers at once.
The following example uses a PowerShell module called PSCredentialManager. IT pros can download the module from the PowerShell Gallery by running Install-Module.

Read the whole thing.

Comments closed

Polybase And RPC Protection

Published 2017-09-22 by Kevin Feasel

Casey Karst announces that Polybase supports Hadoop RPC protection:

Supporting this configuration allows PolyBase to connect and query Hadoop clusters that have wire encryption turned on. This enables a secure connection between Hadoop and SQL Server; as well as, among the Hadoop Data Nodes.
To connect to a Hadoop cluster with the hadoop.rpc.protection set to privacy or integrity, you will need to alter the core-site.xml file that is installed with PolyBase. This file is generally found at C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\Polybase\Hadoop\conf.

That’s good news for Polybase users.

Comments closed

Active Directory Integration On Ubuntu

Published 2017-09-20 by Kevin Feasel

Drew Furgiuele shows how to configure SQL Server on Linux to use Windows authentication:

In the following post, we’ll walk through joining a Linux SQL Server on Unbuntu to an Active Directory domain, and here’s the steps we’re going to take:
Installing the required software and services to enable a Linux host to talk to and join an Active Directory Domain,
Configuring the Linux host’s network configuration to talk to the Domain Controller(s),
Setting up Samba, Kerberos, Winbind, and the System Security Services Daemon (SSSD) to properly talk to and digest authentication tokens from Active Directory, and
Creating a Kerberos Keytab file for the SQL Server service to run as a domain service account.
Seems like a lot, doesn’t it? If you’re new to Linux, a lot of this configuration can seem a little daunting and a lot tedious, but as we walk through it, I’ll stop and talk a little bit about each step and what it does.

Active Directory integration was a critical piece of functionality for SQL Server on Linux. There are still some odd edge cases (like weirdness when going cross-domain) but for the normal scenario, it works fine once you’ve configured Linux correctly.

Comments closed

Homoglyphs And SQL Injection

Published 2017-09-14 by Kevin Feasel

Bert Wagner gives us one more reason why blacklists are bad:

Homoglpyhs can exist within a character set (like the Latin character set examples above) or they can exist between character sets. For example, you may have the unicode apostrophe ʼ, which is a homoglyph to the Latin single quote character '.
How does SQL Server handle unicode homoglyphs?
Funny you should ask. If you pass in a unicode character to a non-unicode datatype (like char), SQL implicitly converts the unicode character to its closest resembling non-unicode homoglyph.

Bert’s examples show failure when converting Unicode data to VARCHAR, so this attack vector may not work if the input parameter is NVARCHAR, but even if that’s the case, it’s still one of many reasons why blacklists are awful for preventing against injection attacks.

Comments closed