Press "Enter" to skip to content

Category: Security

Backup Encryption

Published 2016-12-08 by Kevin Feasel

Daniel Jones shows how to use backup encryption in SQL Server:

The backup encryption in SQL server is needed due to following reasons:

  • Way to Keep Database File Secure: Users need to encrypt SQL server database backup files because this procedure provides complete security to copy of SQL server data. This security measure will keep transaction logs, tables, and other server data safe from any person, who wants to make use of these data in wrong manner.

  • Accessed Only By Authorized Person: It is impossible to restore an encrypted backup file, if a person is not having certificate or asymmetric key for decryption. Therefore, it means that only authorized persons who are knowing credentials of encrypted backup file can restore data with its full access.

Encrypting backups (and storing the encryption key somewhere independent of the backups themselves) can help prevent a very bad day.

Comments closed

Constrained Delegation

Published 2016-12-06 by Kevin Feasel

Regis Baccaro shows how to allow non-domain admins to configure Kerberos Constrained Delegation:

Now I need to add some special permissions to computer objects, so I click Add again. Once again, I’ll select the DBA group, then I need to switch to Descendant Computer objects. I click Write and then scroll down until I see Validated write to service principal name. I’ll click the box to enable it, and then OK, OK, and OK.

The end result looks like below :

2 permissions for DBA group,

  • All descendants objects : Write all properties

  • Descendant computer objects : Validate write to Service Principal Name

Regis has the whole process documented well, so check it out.

Comments closed

Apache Ranger On ElasticMapReduce

Published 2016-12-06 by Kevin Feasel

Varun Rao explains role-based access control using Apache Ranger on Amazon ElasticMapReduce:

Using the HUE SQL Editor, execute the following query.

These queries use external tables, and Hive leverages EMRFS to access the data stored in S3. Because HiveServer2 (where Hue is submitting these queries) is checking with Ranger to grant or deny before accessing any data in S3, you can create fine-grained SQL-based permissions for users even though there is a single EC2 role specified for the cluster (which is used by all requests the cluster makes to S3). For more information, see Additional Features of Hive on Amazon EMR.

If your job includes securing a Hadoop cluster, this is a nice read, even if you don’t use EMR.

Comments closed

Row-Level Security With Power BI

Published 2016-12-02 by Kevin Feasel

Callum Green shows how to use row-level security with Power BI Desktop:

In the June 2016 monthly Power BI release, Row Level Security (RLS) was introduced into Power BI desktop. This is great news for people using the application, especially as the configuration is stored within the Power BI model.  Previously, you had to create the security in the web environment, which could easily be overwritten when publishing multiple times from a desktop workbook.

In this blog, I will show you how to set up RLS in Power BI desktop and how to test it works. My example uses the AdventureWorksDW2014 database (download here), specifically applying permissions for a manager. Each manager will only be able to see data for the Sales Representatives that report to them.

This is different from the SQL Server 2016 feature of the same name, but the concept is the same.

Comments closed

Checking File Permissions

Published 2016-11-25 by Kevin Feasel

Andrew Peterson runs chmod 664 on a database backup:

You’re attempting to RESTORE a SQL Server database backup to your Linux installation and you get the message:

Msg 3201, Level 16, State 2, Line 17
Cannot open backup device ‘C:\home\user\Downloads\AdventureWorks2012.bak’.
Operating system error 2(The system cannot find the file
specified.).
Msg 3013, Level 16, State 1, Line 17
RESTORE HEADERONLY is terminating abnormally.

If you’re going to administer SQL Server on Linux, it’s a good idea to check out the Unix-style permissions model.  It’s a bit different than what we’re used to on Windows, though it does make sense with a bit of practice.

Comments closed

Viewing Power BI Audit Logs

Published 2016-11-25 by Kevin Feasel

Ginger Grant shows how to give a Power BI Administrator rights to view the audit logs:

The Audit Logs are the third menu item in the Power BI Admin Portal. As you can tell by looking at a copy of the screen below, Audit Logs are not really part of Power BI. Yes the ability to log all of the content in Power BI exists in the Audit Logs, but so does the ability to review the audit logs for things like Exchange Mailbox Activities and User Administration Activities.

If the Office 365 Administrator has granted a user Power BI Administration rights, this is what the newly minted Power BI Administrator will see when trying to access any search activities. It appears that you the user has rights, until that user tries to do anything on the screen. At that point, this error window appears.

Click through to see how to grant audit log access.

Comments closed

SQL Server R Service Users

Published 2016-11-16 by Kevin Feasel

John Pertell shows how to figure out which user account is running SQL Server R Services code:

You’re not running as yourself, even though that’s the account you signed into SSMS as.

You’re not running under the server account that SQL or SQL Launchpad run under.

You’re running as a new account created when you installed SQL R Service In Database for the purpose of running R code.

John also looks at a couple ways of showing which user is running this code and notes that this solves his file share issue.

Comments closed

Bulk Administration

Published 2016-11-11 by Kevin Feasel

Kenneth Fisher discusses the bulk administration right:

So as with all permissions we only grant them if there is an actual need right? And the best practice of least privilege says that if someone has to be able to do a bulk load on a table then we should grant the bulk load to that one table right? There’s the rub. Bulk admin permissions are at the instance level and are not granular in any way. Ie you can’t grant it specifically to a single database or table. It’s all or nothing.

Read on for Kenneth’s thoughts.

Comments closed