Voice of the DBA

Tag: SQLNewBlogger

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

I got a call from someone wanted to check how permissions were stored for encryption objects. I ran a quick double check for them and decided to write this short post.

Let’s say that you create a few encryption keys. In my case, I’ll use this code to create a symmetric key, an asymmetric key, and a certificate.
```
CREATE SYMMETRIC KEY MySalaryProtector
WITH ALGORITHM = AES_256,
    IDENTITY_VALUE = 'Salary Protection Key',
    KEY_SOURCE = N'Keep this phrase a secr#t'
ENCRYPTION BY PASSWORD='Us#aStrongP2ssword';
GO

CREATE ASYMMETRIC KEY HRProtection
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = 'Use4SomeStr0ngP@ssword%^';

GO

CREATE CERTIFICATE MySalaryCert
ENCRYPTION BY PASSWORD = N'UCan!tBreakThis1'
WITH SUBJECT = 'Sammamish Shipping Records',
    EXPIRY_DATE = '20161231';
GO
```
I do this, I have these objects.

Let’s now grant rights to these objects. I’ll use this code to grant CONTROL to a user.
```
GRANT CONTROL ON SYMMETRIC KEY::MySalaryProtector TO JoeDBA

GRANT CONTROL ON ASYMMETRIC KEY::hrprotection TO JoeDBA

GRANT CONTROL ON CERTIFICATE::MySalaryCert TO JoeDBA
```
Once I do this, I should see permissions, right? Let’s check.

I don’t see any permissions in the dialog above. That’s not exactly what I’d want to see. After all, if I’m trying to determine why a user can’t access a certificate, I’d want to know if they had rights here.

Instead of this, I need to use T-SQL, and check for specific classes in sys.database_permissions. Here’s the query looking for class 24 (symmetric keys), 25 (certificates) and 26 (asymmetric keys).

You can see that I have permissions in here, and if I check the principal_id, I’ll find these are for my user. I could also join to database_principals and get specific information for my user.

#SQLNewBlogger

This took a bit longer as someone asked me a question and I didn’t know the answer. I had to dig and read some documentation, but I found some answers and documented things myself.

Learned something, showed it, and hopefully will remember it from now on.
December 29, 2016
Administrative Native and ConEmu Command Prompts–#SQLNewBlogger

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

I would think many people know how to do this, but I’ve run into a few, so here you go.

There are times you need to run elevated commands on a machine as an administrator. I’ve had to run various apps, as well as command utilities in this way, so I’ve learned how. I’ll give you two ways to get admin command prompts running.

Cmd.exe

This is the command prompt most people on Windows use. You can hit the Windows key and type cmd, and you’ll see this:

On Win 7 or earlier, you’d get something along these lines.

In either case, if you hit Enter, you’ll get this:

A command line where you can type DOS style commands. Many of you might not use this much, but you should. The more you know about doing things from the command line, the more efficient and effective you’ll be. Plus you’re well on your way to being comfortable in DevOps type environments.

This isn’t a privileged command prompt, and certain things won’t work here. To get an administrative level prompt, do this. First, after typing “cmd” don’t hit enter. Instead, right click the icon. You’ll see this:

Or this:

In both of these, there is a “Run as administrator” option. You want that. Once you do, you’ll need to accept the UAC prompt to allow an administrative level command prompt to open.

ConEmu

I have switched to ConEmu. I think it’s a much better console, and it’s handy. I get it with Chocolatey, and then it’s just a hot key away. What’s more, I can easily open multiple command windows. For example, I’ve got two open, but I can open a third, administrative level one, but right clicking allows me to restart my prompt.

When I do that, I need to acknowledge the UAC prompt, but then I’ll have an admin level prompt.

SQLNewBlogger

These quick, handy tips showcase your continuous learning and improvement. As well as help you remember cool things like this.

December 26, 2016
Quick Command Shells–#SQLNewBlogger

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

This isn’t a SQL post, per se, but since I’ve been using lots of PowerShell for SQL stuff at times, this is a handy trick I picked up.

I was reading the announcement on the Windows Insider build 14971, which will have a few new things. One of these is the default command shell won’t be cmd.exe any longer. It will be PowerShell.

Buried in the announcement was a tip I’d never heard. I don’t have any real cmd additions to my Windows machines, so to get to a particular folder in a command shell, I usually do lots of “cd”s or I right click in the address bar, and copy the path. Thankfully Windows 10 lets me paste this easily into a command prompt.

There’s another way.

I can click the address bar and type “cmd”.

When I do this, I get a new command prompt at that location, as shown here. I do install ConEmu on my machines, so that’s where the prompt opens.

That’s super handy.

We’ll see how may workflow changes when PoSh is the default shell, but I’m looking forward to it.

#SQLNewBlogger

The quick tips that help me work more efficiently are what I’m writing in these posts. Here’s one that took my 5 minutes, and you can write this kind of post as well. Show off what you know, do, and have learned.

December 22, 2016
Connecting to a Specific Port–#SQLNewBlogger

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

I recently blogged about finding the port your SQL Server uses. At the end of that post I showed a connection to a server using a port. This is something I’ve used rarely, but it’s been very helpful.

The specification for specifying a port can vary. For example, my build server at home runs TeamCity on port 8077. In my case, the URL to connect is:

http://192.168.1.201:8077/overview.html

If I had an FQDN, I might have this instead:

http://Atlas.dkranch.net:8077/overview.html

When I connect to SQL Server, I get this dialog:

Some of you might note there is a “Connection Properties” tab, but there’s no port setting there, even if you choose TCP/IP.

When I connect, I give a server name. In the example above, I used “.\SQL2016”. The default is that my client will try to connect on 1433 or use the SQLBrowser to get the port. However, I could specify the instance name like this:

.\SQL2016, 1433

For this instance, that wouldn’t work as the port is 6077. I’d have to write this:

.\SQL2016, 60087

But since I’m choosing a port, I don’t need that. I can use

.,60087

and I’ll connect.

Learn to use the port in your connection string. At some point, this will help you to troubleshoot connection issues.

#SQLNewBlogger

This is a quick, easy post to write. It’s five minutes work for most of you. Maybe 10 if you have to Google/Bing.

December 15, 2016