Voice of the DBA

Tag: SQLNewBlogger

Shutting Off PowerShell Warnings–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Not really a SQL Server post, but I ran into this minor issue. While running a script, I saw this:

The warnings aren’t a big deal, and in fact, have no bearing on my script. At least, not on this script. Since I needed to give this to a customer as a PoC item, I didn’t want any weird warnings. As a result, I needed to suppress these. A little intellisense in VS Code gave me a hint, but what value to use for the parameter?

A quick search brought me to Server Fault, where I saw the value. I added the SilentlyContinue to my script and messages suppressed.

SQLNewBlogger

This is a helpful tip for me, and I think I showed you how I found an issue, researched, and solved it. It’s good to give a potential employer some confidence that you can work through issues and find answers.

This took me less than 10 minutes. I bet you could do the same thing in the same time. Write about how you solve a small issue and drop it on your blog.

October 2, 2019
Restoring an Expired Certificate–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One common task that you might need to handle if you work with encryption is dealing with a certificate. Whether this is for TDE, SSL, or something else, you will want to ensure that you know how to restore a certificate.

I’ve written about this before (restoring a certificate), but what about restoring an expired certificate? If you forget to replace one in TDE, does it still work?

Tl;dr yes.

Validating a Restore

I backed up an certificate from one instance that had expired. I used this in a customer demo a few years ago, and it was on my system. Here’s what I saw.

I ran the backup script shown to get the expired backup of the cert.

Next, I connected to a new instance and ran this.
```
CREATE CERTIFICATE FinanceCert

FROM FILE = N'C:\SQL\FinanceCert.cer'

WITH PRIVATE KEY

(
     FILE = N'c:\SQL\FinanceCert.pvk',
     DECRYPTION BY PASSWORD = N'AReallyStr0ngK#y4You'

);
```
This completed, but I get the warning that this cert is expired. It’s a warning. SQL Server will still use the certificate to decrypt anything necessary, like a DEK for TDE.

This is a good reason to keep an eye on certificates, as someone might be worried about expired certs. They work, but some people (auditors) do not like to see these in use.

To change the certificate used, see Key Rotation in TDE.

SQLNewBlogger

Anything DR related is a good skill to showcase. Blog about your practicing different skills. The writing will cement the skills further in your mind and employers will appreciate you showing the way you handle things.
September 2, 2019
Finding SQL Configuration Manager in Windows 10–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I went to check a network protocol setting for SQL Server the other day on my (newish laptop) and was disappointed.

This is Windows 10 and on this machine, I’d installed SQL Server 2014, 2016, and 2017. I thought that at least SQL Server 2014 had the SQL Server Configuration Manager installed, but it appears not. I know that this has been a tool that sometimes gets hidden in recent versions, but I was sure I’d seen it here.

Either I’m wrong or Windows 10 has changed.

In any case, the Computer Management MMC plugin has it. You can run this in a couple ways. First, hit the Start menu and type “Computer Man”. You’ll get something like this and can run this:

The other choice is to his Win+R (run) and type “compmgmt.msc”. Both will get you here:

If you now expand the Services and Applications, you’ll see the SQL Server Configuration Manager and the various items underneath it. For me, the top one (most recent?) was the SQL Server 2017 version. The others were below as other snap-ins.

The good thing about this is I can also manage local users and see the local logs, things I sometimes need when configurating SQL Server.

SQLNewBlogger

An easy post that solves a common problem, and shows I know some tips and tricks. How would you rewrite this post? You could show this knowledge with a quick 10 minutes of your time.

August 28, 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