Brent Ozar walks through one way to reduce SA account usage.
In a perfect world, you’d create a minimally-privileged AD login that only has limited access to specific databases.
However, when you’ve got a toddler running with scissors and razors, sometimes you’re happy just to get the razors out of their hands first, and then you’ll work on the scissors next. One step at a time. Preferably not running.
For now, create another SQL account with DBO permissions on all of the databases involved with the application. (If you’re dealing with multiple different tenants on the same server, give them each their own SQL login.) Let them be complete owners of their databases for now.
Part two: the not-as-nice way.
Power User: “EVERYTHING IS DOWN! THE SA ACCOUNT PASSWORD ISN’T WORKING! DID YOU RESET IT?”
Me: “Of course not. You told me not to.”
Power User: “THEN WHO DID IT?”
Me: “Oh, I have no way of knowing. Anyone who uses the account can change the password with theALTER LOGIN command. And you said everyone has it, right?”
That’s a nice account you have; it’d be a shame if something…unfortunate…were to happen to it.
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.
Kevin Feasel
2016-01-15
Security