Press "Enter" to skip to content

Category: Security

Database Detachments And File Permissions

Daniel Hutmacher looks at what happens when you detach a database:

On most database servers, the SQL Server service account is granted full control of the directories that host the database files. It goes without saying that the service account that SQL Server runs on should be able to create, read, write and delete database files. Looking at a sample database on my local server, the .mdf and .ldf files don’t actually inherit permissions from their folder, although the permissions are very similar to that of the folder.

This all makes sense once you read the explanation, but it’s not intuitive behavior.  Read Daniel’s gotcha near the end of the post.

Comments closed

Deny Everything

Kenneth Fisher goes over grant, revoke, and deny for permissions:

This means that MyUser can not run a SELECT statement against any table, view or table valued function in the database.

That probably doesn’t sound like you are applying a permission does it? And that is probably where a lot of the confusion comes in. If, however, we take a look at the system views where the data resides then we can see proof that both commands, GRANT and DENY, add a permission.

Particularly interesting is exactly how the deny permission works—and that “deny” is in fact a “permission” in that you modify a permissions list.

Comments closed

Row-Level Security With Power BI

Koen Verbeeck discusses dynamic, row-level security using Power BI:

Apparently, in PBI the domain is used, not the email. Good to know if row level security is implemented in PBI someday (note: currently it’s only available in the Power BI Service).

OK, now let’s upload this report to the Power BI Service so we can implement the RLS. When the report is opened, it seems  that my carefully crafted measures are ignored. Maybe Power BI ignores the USERNAME() function as long as there is no RLS defined.

I like the fact that you can test by role, making it a lot easier to see what others would see under those circumstances and finding bugs with security implementation.  If you have any non-public information and you use Power BI, this is a must-read.

Comments closed

Credentials And Proxies

Kenneth Fisher shows how to use credentials and proxies to run external objects (like SSIS packages and Powershell scripts) through the SQL Server Agent:

There are purposes for credentials other than a proxy, but for our purposes you are just going to enter an AD username and password. Just to be even more clear, this is an AD/Windows user. Not a sql server login.

In Object Explorer: ServerName -> Security -> Right click on Credentials and select New Credential -> Fill in the Name, Identity and Password fields.

Kenneth’s getting fancy with animated GIFs, and gives us a good walkthrough of this aspect of SQL Agent security.

Comments closed

Joining Ubuntu To AD

Chrissy LeMaire shows us how to connect to AD from Ubuntu:

Since 2009, it seems that a couple things have changed in the client realm. In particular, winbindfell out of favor to Likewise Open (which I used to <3) which was bought by BeyondTrust and turned into PowerBroker Open. But that’s since fallen out of favor to the SSSD or “System Security Services Daemon“. SSSD seems pretty cool but everyone hates its name and assume that its name is keeping it from greater adoption.

Sometimes when researching SSSD, you’ll come across a few mentions of FreeIPA which is similar to Active Directory, OpenLDAP, and ApacheDS. Oh, and I recently found out thatSamba4 allows Linux servers to join Active Directory as Domain Controllers (!!) but I can’t tell if it can be a forest of its own (reddit review here).

There are other players I’m leaving out but after a bit of casual research, no others seem to stand out. Ultimately, while there are a number of ways to setup AD/Linux authentication with Ubuntu, it appears that SSSD is the current way to go. Let’s go ahead and set that up.

Cf Ryan Adams and LeMaire’s separate posts back in March on the topic.  As Microsoft gets serious about Linux integration, I would love to see them simplify this process significantly, either by updating an existing open-source project (my preference) or creating their own open-source project.

Comments closed

CLR Turned Off In Azure SQL Database

Brent Ozar reports that Azure SQL Database’s CLR will be turned off:

Details are still coming in, but in the Reddit AMA for the Azure database teams (going on as we speak), it’s one of the users reports that they got an email that SQL CLR will be shut off in one week due to a security issue.

The cloud: at the end of the day, it’s just someone else’s server, and they can – and will – take tough actions to protect their product, their users, their security, and their profits.

I’m curious for more details.  I’d like to know if this is particular to Azure or affect on-prem installations as well.

Comments closed

Securing Plaintext Passwords

John Morehouse shows you how to fix plaintext password storage when you can’t fix the application:

Once the data has been encrypted, we can move forward with creating a new view that will be used to “head fake” the application. The view is named the same as the original table therefore the change is seamless to the application.  The application doesn’t know if it’s calling a table or a view so that’s why this works.

You should never store passwords in plaintext.  You should almost never store passwords in a reversable format (i.e., encrypted).  The primary case in which I can see storing passwords encrypted rather hashed is if you have automated systems (or non-automated technicians) which need passwords to authenticate somewhere.  Even then, there’s a lot of value in using OAuth tokens.  But if you can’t get around any of this, John’s solution does remove the really bad decision of leaving passwords in plaintext.

Comments closed

HIBPwned

Steph Locke has created an R package to query Troy Hunt’s Have I Been Pwned? site:

The answer in life to the inevitable question of “How can I do that in R?” should be “There’s a package for that”. So when I wanted to query HaveIBeenPwned.com (HIBP) to check whether a bunch of emails had been involved in data breaches and there wasn’t an R package for HIBP, it meant that the responsibility for making one landed on my shoulders. Now, you can see if your accounts are at risk with the R package for HaveIBeenPwned.com, HIBPwned.

This is a nice confluence of two fun topics, so of course I like it.

Comments closed

Encrypt Your Connections

John Martin shows that part of securing your environment includes encrypting SQL Server connections:

In order to demonstrate just how easy it can be to get hold of the information inside the TDS packets I will be using Network Monitor from Microsoft, this will capture the network packets sent and allow me to see the details of what is being sent. Other tools such as Wireshark will also provide a level of insight into what is being sent between the application and SQL Server. I have configured three Windows Server 2012 R2 systems, one with the client (SQLCMD), one with SQL Server, and finally one which will act as a router between the two subnets that each server is on. This configuration can be seen below;

Wireshark is a good friend of mine.  It should be a good friend of yours, too, but only if your environment allows you to have a packet capture tool installed.

Comments closed

Querying Active Directory From SQL Server

Ryan Adams shows us how to use OPENROWSET and OPENQUERY to connect to a domain controller and query Active Directory using LDAP:

In the code below, the first thing we do is enable Ad Hoc Distributed Queries so we can try out the OPENROWSET method.  The advantage to this method is not having a linked server and being able to call it directly out of TSQL.  Once we have that enabled we write our query and you’ll notice that we are essentially doing 2 queries.  The first query is the LDAP query inside the OPENROWSET function.  Once those results are returned we are using another query to get what we want from the result set.  Here is where I want you to stop and think about things.  If my LDAP query pulls back 50 attributes, or “columns” in SQL terms, and I tell it I only want 10 of them, what did I just do?  I brought back a ton of extra data over the wire for no reason because I’m not planning to use it.  What we should see here is that the columns on both SELECT statements are the same.  They do not, however, have to be in the same order.  The reason for that is because LDAP does not guarantee to return results in the same order every time.  The attribute or “column” order in your first SELECT statement determines the order of your final result set.  This gives you the opportunity to alias anything if you need to.

You can query LDAP using SELECT statements, but the syntax isn’t T-SQL, so in my case, it was a bit frustrating getting the data I wanted out of Active Directory because I was used to T-SQL niceties.  Nevertheless, this is a good way of pulling down AD data.

Comments closed