On July 1, 2018, I was renewed as a Microsoft Data Platform MVP for 2018-2019.

Thank you, Microsoft, for this great honor. I look forward to continuing my work with the SQL Server community.

The post MVP Status: Renewed appeared first on The Data Files.

Fast forward to 2018, and SQLSaturday Iowa City is still going strong. Next week, we’re hosting our 10th annual event – one of only a few host cities to have reached that milestone to date. We’re proud of what has happened here, and we intend to celebrate!

Over twenty speakers will be on-site in Iowa City on June 23 to present twenty-five sessions on topics covering much of the Microsoft data platform, including performance tuning, security, cloud technologies, PowerShell, Power BI, and machine learning, just to name a few. And like that first event, all of the content at the conference on the 23rd is free, presented by experts from around the community. But even better than the content is the connections you make at a SQLSaturday. Many of us have seen the trajectories of our careers changed dramatically through the people we’ve met and talked to at these events.

If you want more in-depth instruction, we have that covered, also, through one of our more recent additions, pre-conference sessions. On June 21 and 22, we have three paid sessions available for you to choose from. Two engineers from Microsoft will be on hand to talk about how to get your databases up to Azure SQL Database; perennial favorite David Klee from Heraflux will be here to talk to you about what DBAs need to understand about infrastructure in the cloud; and Dave Bland will show you how to use the tools you already have in SQL Server to identify performance bottlenecks. There aren’t very many places in Iowa to get this kind of training on SQL Server!

Oh, and did we mention that there will be prizes? Our generous sponsors always provide fantastic prizes to give away at our end of day raffle, and we just might have a few special things to give away. (This is a celebration, after all!)

If you’re in the Midwest, we hope you’ll come see us next week to help us celebrate this milestone. We’re truly excited – it’s going to be a great time!

If you want to join us at this year’s SQLSaturday Iowa City, register at our event home page.

The post 10 Years Young appeared first on The Data Files.

My topic this year is on permissions – specifically, SQL Server’s permissions model and how to implement the Principle of Least Privilege in your environment to meet our ever-increasing requirements for security and privacy. This is not a new topic by any means, but it is rapidly becoming prominent as we look for solutions to the onslaught of data breaches and new regulations. We’ll talk about the Principle of Least Privilege (a simple concept but one that is often misunderstood) and how to implement it for users, applications, and administrators. A large part of the session will focus on implementing separation of duties, which is also a simple concept on the surface but which has many nuances under the surface. If you work with security in your company, you won’t want to miss this session.

I will also be presenting a preview of my Summit talk at the next 24 Hours of PASS webinar marathon. This is their annual Summit Preview Edition, in which twenty-four of the speakers selected for Summit are asked to present a shortened version of their talks. My session will be at 10:00p (US Central Daylight Time) on June 12. Registration is free and open now.

I hope to see you at one of these upcoming events!

PASS Summit 2018 is coming up on November 6-9 in Seattle, Washington. You can register on the event website.

The post Upcoming: Speaking on permissions at PASS Summit 2018 and 24 Hours of PASS appeared first on The Data Files.

It’s been quite a while since I’ve participated in a T-SQL Tuesday. For that matter, before my post earlier this month, it had been quite a while since I had posted anything in my blog. I routinely speak in front of groups both small and large, I communicate with people (orally and in writing) every day as a consultant, yet there’s something about writing in my blog that gives me pause.

This is just one example of something that most of us notice when we first start thinking about giving back: Giving back scares us. Our next thought frequently goes something like, “this doesn’t make sense – I know I should do it, and I know there’s nothing to be afraid of”… and that only makes the fear worse.

The thing is, giving back requires us to put something of ourselves on display to other people. It requires us to say, “Here, I made this.” It opens us up to the judgment of our peers. And that’s a terrifying thing to many of us. We wonder if it will be good enough. We wonder if we are good enough.

Whatever you have to contribute is good enough. It may be a hard-earned insight, won through succeeding in a difficult project. It may be a lesson learned through failure in a difficult project. It may be a simple trick that saves you five minutes a day. Whatever you have to teach could be an answer to a question that someone else has, and the answer you provide can change the direction of a project they’re on, of their career, of their life. What you have to share can and will make a difference. You won’t see that right away. But it does happen.

When I first found the SQL Server community back in 2008, I was as introverted as they come. I was convinced that I had nothing that anyone else would want, that I was so far behind everyone else that no one could possibly learn from me. It took a push (OK, a lot of pushes) from those around me in the community to get me to start contributing. Now, almost ten years later, it’s a part of who I am. And in those ten years, not one person has told me that what I shared wasn’t good enough.

So my challenge to you today is this: pick something from Riley’s list, then do it. It will be good enough. And we can’t wait to see what you have to share.

The post Giving back [T-SQL Tuesday #102] appeared first on The Data Files.

If you recall, Always Encrypted has two associated keys: a Column Master Key and a Column Encryption Key. The Column Encryption Key (CEK) is a symmetric key, stored in SQL Server. Like other symmetric keys, the CEK is not changed during a rotation. The Column Master Key (CMK), on the other hand, is a certificate, similar to the certificates we’ve used for transparent data encryption and for in-column encryption, and it therefore needs to be rotated regularly. The biggest difference is that the CMK is stored outside of SQL Server, in the Windows certificate store by default, so DBAs may need assistance from their Windows administrators or security administrators.

The first step of the rotation is to generate a new key. SQL Server Management Studio provides a convenient user interface for this under [your database name] > Security > Always Encrypted Keys > Column Master Keys in the Object Explorer. Right-click that folder and select New Column Master Key….

Give the new key a name, then select the location for the key. By default, you’ll see two options – Windows Certificate Store – Current User and Windows Certificate Store – Local Machine. For a shared environment (like production), you’ll want to put the new key into the Local Machine store so all users have access to it; for a lab environment, you can use either. Once you’ve selected the location, click Generate Certificate, located below the list of keys at the bottom right. When the new key appears in the list, click OK to save your changes.

Now, to rotate the key, browse the Object Explorer to [your database name] > Security > Always Encrypted Keys > Column Master Keys, then right-click the key you want to rotate.

Select Rotate, then select the new key from the wizard that follows. After a second or two, the window should close, indicating that the rotation is complete.

Finally, don’t forget to duplicate your new key to all client machines that need to be able to decrypt the data protected by Always Encrypted.

Note that all of this can also be automated with PowerShell. Microsoft has a great article on the subject in Microsoft Docs, containing instructions and examples for environments both with and without separation of duties.

The post Rotating encryption keys for Always Encrypted appeared first on The Data Files.