Voice of the DBA

Tag: security

Clearing an Email from the Microsoft Directory
Years ago I was working on a demo with Microsoft and someone added me to their VSTS (now Azure DevOps) project. Somehow, I got an account in the Live/Passport/Joe-Schmo Azure directory with my red-gate.com email. This hasn’t been a problem for a long time, but recently I had issues.

At Redgate Software, we use Office 365 for a lot of stuff (email, apps, etc.). Lately I’ve been getting a number of documents to edit in our shared, Sharepoint folders. Mostly this is Kendra’s fault, but as I’ve done more prep stuff for customers, I get links from others.

While I can access them, if I click a link, I get a browser window that opens and tells me that it can’t access the document because I’m logged in with my way0utwestxxxxxx account. That’s true, since I use that for the portal. I click the “sign out and sign in again, and I’ll get taken to the login page.

However, this is a login for a personal account, not an organization account. As a result, as soon as I enter my password, I get:

That’s true, but it’s also not true. I do have a valid @red-gate.com account that was entered, but this is apparently stuck thinking I’m using a personal account and doesn’t let me change to an organizational account.

What’s weird is that if I go to a URL, like our info site, or the IT site, I get the option to enter an account. When I do, it goes to the organizational signin.

The Fix

I’ve let this go, mostly because I don’t do much document collaboration, but lately it’s been a problem. I finally pinged the IT staff and they gave me the fix.
- Log into https://account.microsoft.com/profile with the problem personal account.
- Click on “Contact info”
- Click on “Manage this email address”
- Click on “Add email”
- Create a new email Address
- Change the new email address to the primary
- Now remove the old personal email from the account.
I’d have probably been able to figure all of this out if I’d known about account.microsoft.com. I kept thinking Passport, Live, all the auth services I’ve used in the past.

Hopefully now I’ll remember if this happens again, or perhaps this will help someone else.
May 20, 2019
Always Use Roles–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Which of these is more complex?
```
GRANT SELECT ON dbo.Customer TO JoeDev
```
or
```
CREATE ROLE Sales
GRANT SELECT ON dbo.Customer to Sales
ALTER ROLE Sales ADD MEMBER JoeDev
```
The second one, right? What if I change this slightly. I have this code:
```
GRANT SELECT, INSERT, UPDATE ON dbo.Customer TO JoeDev

GRANT SELECT, INSERT, UPDATE ON dbo.Customer TO SallyDev

GRANT SELECT, INSERT, UPDATE ON dbo.Customer TO SaraDBA
```
or
```
GRANT SELECT, INSERT, UPDATE ON dbo.Customer TO Sales

ALTER ROLE Sales ADD MEMBER JoeDev

ALTER ROLE Sales ADD MEMBER SallyDev

ALTER ROLE Sales ADD MEMBER SaraDBA
```
What if I changed this slighly and told you that between the GRANTs to users, a few months of time had passed and you had to go figure out which rights JoeDev had because the request was “give Sally the same access as Joe.”

That’s the type of thing I’ve done often as a DBA. I’ve often had to move permissions between users, duplicate the access, or quickly remove lots of access from multiple users.
While it seems like there are just two extra statements using roles, there is often lots of time tracking down security and building statements to duplicate rights.

Always use roles and your life will be easier.

Plus you can script the permissions for objects once, log them, and forget about them. From that point forward you’re just adding/dropping users from roles.
May 10, 2019
A Double Failure

Update: Perhaps not, as Evisort dispute any customer data was exposed and they’ve updated security.

Every server ought to have a password. Every one.

It’s 2019, and apparently that advice hasn’t sunken in. I still can’t believe there are people without passwords/codes on their mobile devices or home PCs, but there are. It’s crazy, and while I can forgive some individuals for doing this, no IT infrastructure staff or developer ought to do this. And yet, a double fail recently from Evisort.

This is a startup doing some AI work, but apparently they set up an Elasticsearch server without a password. I’ve written about this before, and you set a password, but don’t have to. That’s both an Elasticsearch failure for not requiring one, but also a monumental failure on the part of whoever set this up inside a company.

Use. A. Password.

The second fail is with this server being claimed to be a “testing and development” server. If that’s the case, why was production, live data on it? I know many people do this, but if you use that data in non production environments, the data needs to be secured. I’m sure it’s especially hard for AI/ML systems to work without real data, unlike other database driven applications, but if you need live data, you need real security here.

If you don’t want to do this, then you need masked, obfuscated, pseudonymized, generated, or other data that can be used. I’ve realized the problems and scope of this across the last few years in my work with Redgate customers, while looking at the challenges and problems brought about by using this data. We’ve also see there is a lot of potential liability with new regulations like the GDPR and the CCPA for poor data security.

I used to worry about the state of our industry with the poor quality of so many applications written in the 80s and 90s. Now I worry even more about the problems of poor data security. I don’t have good answers, but I know we need to do better.

Steve Jones

Listen to the podcast at Libsyn.

May 7, 2019
Securing Code Early

Last year I started to get alerts from Microsoft Repos that someone had put a piece of security information in their code that pertained to one of my Azure services. At first I was worried, but then I realized this was the public version of AdventureWorks we maintain in Azure. We’ve published the login so people can test code against this if they want, and I started ignoring the warnings. Well, not ignoring. I still glance over them to verify the issue, but I’m less concerned.

That doesn’t mean that you shouldn’t be concerned about sensitive information in repos. I saw this quote: ” Bots are crawling all over GitHub seeking secret keys, a developer served with a $2,375 Bitcoin mining bill found.” This follows a sentence that says “It once caused Uber to leak the contact details of 75m users“. These are from an interesting look at a way to secure code that might leak API keys. The idea is that you secure code on local commits and prevent secrets from being stored in your VCS.

That’s a great idea. Can we prevent passwords in SQL code or ASP.NET config files? Can we actually start to teach developers to use secrets and other run-time mechanisms and prevent them from hard coding anything into a VCS? Perhaps, but we have a lot way to go and certainly more tool chains need to be updated to prevent what is a simple, but common, mistake.

We need to get better at security, and we are. I see more Static Code Analysis tools being used in all sorts of companies, and I’m glad when they start to impact developers. While I get that changing your favorite method of writing a query or procedure is a pain, often we can reduce potential problems by enforcing some standards and avoiding poor coding practices. We do need to have exceptions since a rule for code might really be a general guideline 99% of the time with a few edge cases.

Catching issues early in development is one of the goals. Having things like inline SCA in SQL Prompt (live demo), or continuous testing in Visual Studio, scanning with Sonarqube, and other tools are improving our code, while allowing us to build applications faster. There is plenty of other work to be done, especially for database code, but we are improving as an industry.

If you haven’t used any of these tools and you write code, start learning a bit about them. You’ll likely appreciate their benefits once you get over the learning curve. You’ll also start writing better code.

Steve Jones

Listen to the podcast at Libsyn.

April 17, 2019