Voice of the DBA

Tag: security

High Security Prices–Always Encrypted 2019 needs Datacenter Edition

I was recently doing some work on Always Encrypted in SQL Server 2019 and needed to set up an HGS server. I went through most of the setup, and then encountered this issue:

After embarrassing myself to other MVPs and Microsoft, I realized I hadn’t read the requirements for the secure enclaves. I need 2 machines, but the SQL Server has to run on Windows 10 or Windows 2019 Server – Datacenter Edition.

Grrr, I had built a machine for this and need to tear it down and restart the process. It’s not that hard, but it’s annoying.

I should learn to read more carefully.

May 30, 2019
Long Live All Passwords

I remember the first time I had to create a password for a system. It was an application that a friend and I wrote, and we wanted to separate the ways that we stored data, so we added a login routine to the system. It stored a (plain text) password, but hey, this was 1983. We loaded the password and made a string comparison with the entry by the user.

Since that time, most organizations I’ve worked in have had password policies in place on the network. These passwords often had expiration dates, with short warnings for users to change their passwords. The requirement to change never seemed to come at a good time, and with the requirement to not reuse one of the last 5 or 10 passwords, this resulted in users often adding a 1, 2, 3, etc. to the same password. As an IT pro, I often tried to get users to choose new passwords, which fell on deaf ears.

Microsoft is rethinking the idea of passwords expiring. There’s a piece that explains how they feel this requirement is not helping security. This is similar to a piece at SANS that notes the practice is not helpful. Changing passwords has a cost to your organization in extra tickets for a help desk, lost productivity from dealing with expiration, and frustration from users. What’s worse, far too many users just “update” a password with a number or year. Neither of which is effective.

The current advice is MFA (multi-factor authentication), passphrases, and password managers. Personally I try to use all of these where I can, and choose long passwords. It does seem that many companies are updating apps to allow longer passwords and 2FA. I’d like to see this embedded into more frameworks, making it easier for developers to implement secure authentication systems. That, or use existing systems out there, like oAuth, though there is an argument how secure this is. Even if there are issues, it’s likely better than what most developers would build.

In SQL Server, we have group Managed Service Accounts (gMSA), which are a good way to avoid password management, and ensure complex passwords. We should disable, and set a complex password for, the sa account. While I wouldn’t advocate changing this regularly, I would have alerts if the account is enabled.

Like most people, I try to avoid changing passwords unnecessarily, especially these days where I have accounts at dozens (or hundreds) of places. I do, however, ensure that all are different and unrelated. At least then if a password is compromised, I don’t worry about someone logging into a different service I use with the same credentials.

Steve Jones

Listen to the podcast at Libsyn

May 29, 2019
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