Group Managed Service Accounts

Jamie Wick explains Group Managed Service Accounts and uses Powershell to create them for use on a new SQL Server instance:

Service Accounts are a requirement for installing and running a SQL Server. For many years Microsoft has recommended that each SQL Server service be run as a separate low-rights Windows account. Where possible, the current recommendation is to use Managed Service Accounts (MSA) or Group Managed Service Accounts (gMSA
). Both account types are ones where the account password is managed by the Domain Controller. The primary difference being that MSA are used for standalone SQL instances, whereas clustered SQL instances require gMSA. In this post, we’re going to use PowerShell to create Group Managed Service Accounts, and then deploy them for use on multiple SQL servers that will be hosting an Availability Group.

Click through for more explanation as well as several scripts showing how to create and use them.

Shared Database Privacy

Duncan Greaves has some thoughts about safeguarding privacy in shared databases:

The difficulty with privacy (or more correctly, information confidentiality) in database terms is that databases are supposed to maintain huge amounts of information, and processing and recording data is difficult, if not impossible without them. Public bodies especially, have difficulty in defining and maintaining the boundaries of information disclosure that they should provide, whilst maintaining the utility of the information for the improvement of welfare and services.
  Privacy is contingent on first having a correctly secured database. Additional privacy controls are required when sensitive data leaves the protected trust perimeter of the database to be utilised by third parties.

Click through for more detail.

.Net Core On Docker Connecting Via AD To SQL Server

Michal Poreba shows us how to connect Windows Docker containers running .Net Core to SQL Server via Active Directory when the containers are not connected to the domain:

The good news is that it is not an unreasonable requirement and it has been done before. The solution is to use Group Managed Service Accounts (gMSA) and Credential Spec Files. A number of people have already documented their efforts. Some were more successful than others.

Click through for a detailed guide to getting this working.

Effective Identities And Power BI Embedded

Angela Henry shows how you can use Power BI Embedded for row-level security even when the accessing users don’t have Power BI accounts:

Now that you familiar with Row Level Security in Power BI, how do you make it work when you want to pass in your customer’s identifier because your customers don’t have Power BI accounts?  It seems like the only way to make dynamic row level security is to use the Username() DAX function?  But wait, doesn’t that require the user to have a Power BI account?  Sigh, it seems we are going in circles.

The one thing these articles don’t talk about is that when you are using Power BI Embedded, you can pass in whatever you like for the EffectiveIdentity via the Power BI API and it will “overwrite” the Username() function.  What?!  That’s right, it will completely ignore the Username() function and use whatever you give it.  WooHoo!

Read on for the details.

Testing SQL Logins For Weak Passwords

Tom LaRock shows how you can test SQL authenticated logins for weak passwords using a built-in SQL Server function:

DATA SECURITY IS HARD BECAUSE PEOPLE ARE DUMB.

Don’t just take my word for it though. Do a quick search for “common password list” and you’ll see examples of passwords scraped from breaches. These are passwords often used by default to secure systems and data.
Chances are, these passwords are in your environment, right now.
Here’s what you can do to protect your data.

Read on to see what you can do, both to detect weak passwords and to make it harder for users to use them.

On R Packages And Trust

Kevin Feasel

2019-02-06

R, Security

Colin Gillespie shares some thoughts about the potentially over-trusting nature of R developers:

One of the great things about R, is the myriad of packages. Packages are typically installed via

– CRAN
– Bioconductor
– GitHub

But how often do we think about what we are installing? Do we pay attention or just install when something looks neat? Do we think about security or just take it that everything is secure? In this post, we conducted a little nefarious experiment to see if people pay attention to what they install.

Packages are code and like any other code, R packages can contain malicious content.

Finding Failed Login Attempt Hostnames

Max Vernon shows us how we can find the hostnames for failed login attempts against a SQL Server instance:

If you manage a lot of SQL Server instances, you likely run into failed login attempts quite often. Perhaps you’re even wondering what client machine is causing all those failures. Since most environments run over TCP/IP; SQL Server helpfully logs the IP address of the client machine that made these failed login attempts to the SQL Server Error Log.

This solution is in T-SQL but shells out to cmd. It might be better suited for Powershell, but it does the trick.

SQL Injection Explained

Hugo Kornelis explains SQL injection and how to fix it:

Translating this back to technical terms, the root cause of actual SQL injection vulnerabilities is a front-end program (often, but not always, a web application) that allows the user to enter data, forms a query by concatenating that data into pre-made query strings, and sends the result to the backend database server.

Once you know the root cause, the fix becomes rather obvious.

When I explain SQL injection, I like to explain it using two concepts: code versus data and technology boundaries. Ideally, your code is separate from your data, and within a single technology (e.g., in that .NET web app), that’s typically the case. But when you start to traverse boundaries, it’s convenient (and wrong!) to combine code and data together to pass everything across as a single stream of information. Parameterization is the way of keeping code and data separate as you cross those boundaries. Your data isn’t code and your code isn’t data and conflating the two is how attackers can inject arbitrary code into your system.

Incidentally, technology boundaries can happen within a single product, too: dynamic SQL is an example of this.

SQL Server Permissions Manager Updates

Eric Cobb has updates to the SQL Server Permissions Manager project:

I recently made some updates to my SQL Server Permissions Manager project on GitHub. These updates mainly consisted of bug fixes and documentation updates, although I did also add the ability to snapshot server level permissions such as “sysadmin” or “securityadmin”.

Given how much we want to audit permissions and how difficult it is to do a really good job of that in SQL Server, there’s space for a good auditing tool.

Changing Red Hat’s SSH Port On An Azure VM

Paul Randal has a post showing you how to change the default SSH port on a Red Hat Enterprise Linux VM hosted in Azure:

The steps that need to be performed are:
– Allow the new port in the RHEL firewall
– Change the SSH daemon to listen on the new port
– Add an incoming rule in the VM network security group for the new port
– Remove the rule that allows port 22

The Ubuntu process will be pretty close to this as well.

Categories

July 2019
MTWTFSS
« Jun  
1234567
891011121314
15161718192021
22232425262728
293031