A Fine Slice Of SQL Server
Dennes Torres warns us of the dangers of setting a database’s Trustworthy flag on:
Trustworthy database setting is an easy solution for some problems in the server, especially cross database access. However, this setting has also a security problem in some scenarios.
For more on this setting, Erland Sommarskog’s essay on permissions granting has one of the best explanations I’ve read.
Comments closedSlava Murygin gives an introduction to firewall configuration using Powershell:
The Script has list of adjustable filters:
$Direction – Direction of firewall rule: Inbound or Outbound;
$Action – Action rule performs: Allow or Block;
$Enabled – Status of a rule: Enabled – True or False;
$RuleGroup – Group rule has been assigned. By default script uses “$Null” variable, which filters all rules without assigned group. However you can specify a group a name if necessary;
$DisplayName – Name of a rule. By default I use an expression “*SQL*” to search for rules which have word “SQL” in their name. To retrieve all rules us “*”. To retrieve any particular rule use rule name.
He looks at viewing rules as well as creating, modifying, and deleting them.
Comments closedVikash Selvin shows how to secure instances of Elasticsearch and Kibana:
The most popular options for securing Elasticsearch and Kibana are compared in the table below.
Shield is a security plugin developed by the same company that developed Elasticsearch. It allows you to easily protect this data with a username and password while simplifying your architecture. Advanced security features like encryption, role-based access control, IP filtering, and auditing are also available when you need them.
NGINX is an open source web server. It can act as a proxy server and can do load balancing, among other things. In combination with LUA and external scripts, it can be used for securing Elasticsearch and Kibana. We will be using this approach in this tutorial.
Searchguard is an open source alternative for Shield. It provides almost all the same functionalities as Shield, except for some features like LDAP authentication. However, these features are available in the paid variant.
Click through for a detailed NGINX setup.
Comments closedSyed Mahmood and Srikanth Venkat discuss two security features in Apache Ranger:
Dynamic data masking via Apache Ranger enables security administrators to ensure that only authorized users can see the data they are permitted to see, while for other users or groups the same data is masked or anonymized to protect sensitive content. The process of dynamic data masking does not physically alter the data, or make a copy of it. The original sensitive data also does not leave the data store, but rather the data is obfuscated when presenting to the user. Apache Ranger 0.6 included with HDP 2.5, introduces a new type of authorization policy called “Masking Policy” that can used to define which specific data fields are masked and what are the rules for how to anonymization or pseudonymize the specific data. For example, a security administrator may choose to mask credit card numbers when displayed to customer service personnel, such that only last four digits are rendered in the form of XXXX-XXXX-XXXX-0123. The same would be true of sensitive data such as social security numbers or email addresses that are masked to be rendered in a different formats based on data masking rules.
This is part one of a two-part series; part two will dig into the technical details. I have to wonder if Ranger’s dynamic data masking is as easy to circumvent as SQL Server’s.
Comments closedYou then need to trust the certificate on all the linux hosts
From the IBM article:
Create ‘/etc/pki/ca-trust/source/anchors/activedirectory.pem’ and paste the certificate contents
Trust CA cert: sudo update-ca-trust enable; sudo update-ca-trust extract; sudo update-ca-trust check
Trust CA cert in Java:
mycert=/etc/pki/ca-trust/source/anchors/activedirectory.pem sudo keytool -importcert -noprompt -storepass changeit -file ${mycert} -alias ad -keystore /etc/pki/java/cacerts
And at last, please make sure every node on your cluster has access to the ad host.
LDAP support is a key part of setting up a production Hadoop cluster.
Comments closedKevin Hill diagnoses an SSPI error:
Apparently, the account was either locked out from our failed logon attempts, or had been disabled in Active Directory due to its age. They do that sometimes. Most likely the issue was locked.
We restarted the SQL Server (O/S restart) and that resolved it once the AD group unlocked it.
My assumption is that the lockout either blocked Kerberos authentication due to SPN no longer being valid, or the SPN itself got corrupted. It was still there, just not working. Verified its existence through running SetSPN -L with the account name.
This is on my top five list of least helpful error messages. Even if it is literally true, it does not help you diagnose and correct the issue. There are a number of potential causes and it’s up to you to troubleshoot each one (assuming you even know that it could be an issue) until it just works again.
Comments closedJohn Martin on Dynamic Data Masking:
For the rest of this blog post, I will be working with the following scenario:
I have an SSRS Server, hosting a number of reports that display information about my SQL Server estate. From performance metrics through to details of failed jobs and poorly performing queries. I want to add an additional layer of security, restricting who can see the names of servers, databases, and other internal infrastructure information. Permission to view these reports will be granted to both support teams and business users, with the business users not being permitted to see the sensitive data.
John is much more optimistic about this feature than I am.
Comments closedKenneth Fisher provides musings based on an Azure security document:
It gives you a map on how to manage your security as you move into the cloud. Note: one of the main points is that your on premise security is equally as important and has to be managed with and as a part of your cloud security.
Now if you are like me and want more than just dry reading they also provide a link to a Microsoft Virtual Academy training course called Security in a Cloud-Enabled World that follows this roadmap and provides more detail and guidance.
Read the whole thing.
Comments closedRunbooks are very powerful tools which allow you to automate PowerShell commands which need to be run at different times. One of the problems that I’ve run across when dealing with Azure Runbooks is that there is no way to use the same script on prem during testing and the same script when deploying. This is because of the way that authentication has to be handled when setting up a runbook.
The best way to handle authentication within a runbook is to store the authentication within the Azure Automation configuration as a stored credential. The problem here is that you can’t use this credential while developing your runbook in the normal Powershell ISE.
This is a clever idea.
Comments closedDave Mason has a good post about onerous third-party software requirements:
If you’re not familiar with SQL Server, the “sysadmin” server role conveys the highest level of authorization available to a login. “db_owner” also conveys a high level of authorization. Both requirements are far more than what is necessary and violate the Principle of Least Privilege. While I strongly disagree with the install-time requirements, I can at least understand the argument: it’s a one-time activity. But elevated permissions at run time are inexcusable.
Most of the time, software companies publish that because they want to avoid the hassle of support calls when people don’t grant privileges correctly. I’ve worked with one third-party vendor in the past who sent me the actual permissions requirements after I pestered them a bit, as I wasn’t going to let just anyone have sysadmin on my servers. But that’s not a scalable approach and does nothing for the next guy who reads the documentation and just gives sysadmin away.
Comments closed