Do I have a Database Master Key in a database? #SQLNewBlogger

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

How can I tell if I have a Database Master Key in a database? It’s actually easy. I query the sys.symmetric_keys DMV for data. If I get a result that has a name of ##MS_DatabaseMasterKey##, then I have a database master key.

You can see this below. I’ve queried this DMV in my Sandbox database.

2017-01-11 12_21_55-SQLQuery1.sql - (local)_SQL2014.Sandbox (PLATO_Steve (59))_ - Microsoft SQL Serv

Now, what if I go to another database, say the Finances database. I see nothing.

2017-01-11 12_22_34-SQLQuery1.sql - (local)_SQL2014.Finances (PLATO_Steve (59))_ - Microsoft SQL Ser

Let’s add a master key here and then query. Note, I am not disclosing the real password here. Never do this, even in test systems.

2017-01-11 12_23_14-SQLQuery1.sql - (local)_SQL2014.Finances (PLATO_Steve (59))_ - Microsoft SQL Ser

This instance has been used with TDE, so if I go to master, I’ll get this:

2017-01-11 12_24_20-SQLQuery1.sql - (local)_SQL2014.master (PLATO_Steve (59))_ - Microsoft SQL Serve

You can see that I not only have a DMK, I have a Service Master Key (SMK), which protects the instance.

When I create my DMK, the only parameter I can provide is a password, after the optional “ENCRYPTION BY PASSWORD” keywords. I don’t name it, so I can count on the naming being fairly consistent. I don’t think that the name would change from version to version, but it could.

I’d prefer that MS not create magic numbers or names, and instead, add a column to the DMV that denotes this is a DMK.

References

sys.symmetric_keys – https://msdn.microsoft.com/en-us/library/ms189446.aspx

CREATE MASTER KEY – https://msdn.microsoft.com/en-us/library/ms174382.aspx

Connect Item to add a flag – https://connect.microsoft.com/SQLServer/feedback/details/3118588

Posted in Blog | Tagged , , , | Leave a comment

Securing Your Instances

I wrote a post about finding the port number of a SQL instance using PowerShell. Almost immediately I was taken to task by someone that noted port scanners can easily find SQL ports, so it’s silly to move off 1433. Just use it because applications expect it. I can see that, and changing ports doesn’t provide much security, but it does provide some obscurity, which may or may not be helpful. Certainly this also creates administrative and support burdens for a system. If you want other opinions, there’s a Q&A on Stack Exchange for this topic as well.

Tom LaRock wote a post that this can be a way to obfuscate your database, prevent simple default connections, and potentially detect security issues before they become a problem. I tend to learn towards this approach as well, because these small changes can potentially provide a little protection. A port scan is quick, but firewalls are getting better at detecting these. Certainly criminals get smarter, but changing a port number isn’t intended to stop everyone. If it stops a few, then that’s fine.

Security comes about because of layers, and limitations, and will never be perfect. There will always be ways that someone can get around security, but the more layers, the more obstacles you place in their way, the fewer people that will overcome all of them. I’d also note that plenty of attacks come from vandals. People that are just bored and looking for easy ways to get into a system. Most of these people are using pre-written scripts and programs that try out defaults. These aren’t concerted, directed attacks. They’re attacks from boredom.

I’m curious today how many of you think a little bit of security from obfuscating ports is a good idea. I wouldn’t recommend the SQL Browser be open, but that legitimate connections know about the port needed, and use that in their connection strings. I think it helps, and if this limits some attacks, especially inside-the-firewall virus attacks from trusted machines, I think it’s worth doing.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.4MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged , | Leave a comment

Installing the SQL Clone Agent and the VC++ Runtime

I was trying to get SQL Clone working recently and had an issue with the Agent. If you haven’t looked at SQL Clone, it’s a really cool product from Redgate that’s in beta right now. It will help you build and provision those large dev, test, customer service, etc. environments with full size copies of data without using the space (or time) for the actual files.

In any case, when I downloaded the agent and ran the installer, I saw this:

2017-01-12 16_09_57-Redgate SQL Clone Agent

That struck me as funny because I have all sorts of VC++ runtimes on my machine. In fact, in debugging, I saw all these:

2017-01-12 16_11_02-Settings

After the download completed, I got an error.

2017-01-09 07_57_09-

This was strange, since I actually had a 14.3 version, but apparently SQL Clone doesn’t realize this is the case. This should be fixed soon, but for now, here’s what to do.

First, run regedt32.exe. The setup program is checking for an installation in a key. I had the keys in the HKLM\SOFTWARE\Microsoft\VisualStudio\14.0\VC\Runtimes\x64, but not in the Wow6432 node.

Browse to HKLM\Software\Wow6432\Microsoft\VisualStudio\14.0\VC. Here’s what I had:

2017-01-12 16_15_00-Registry Editor

Here’s my x64 folder structure:

2017-01-12 16_15_54-Registry Editor

In here I have the sub-keys and the “Installed” value.

Since I know I have a later VC++ runtime, I did this. First, create the “Runtimes” key under VC, then add a “x64” key below that. Inside of x64, add an Installed value as a Reg_DWord with a value of 1. You should see this:

2017-01-12 16_37_30-Registry Editor

Now run the SQL Clone Agent installer:

2017-01-12 16_37_16-SQL Clone Agent Setup

Everything works.

If you’d like to learn more about SQL Clone, check out this short video:

Posted in Blog | Tagged , , | 1 Comment

Vote for @SQLBits Sessions (pick me, pick me)

The SQL Bits sessions are out and it’s time to vote for me. Well, vote for the sessions you’d like to see, but certainly include me in the list. I’ve got a few new ones, and a few that have been popular at other conferences. I am hoping to return to Telford, find a new costume for the party, and have a great week of SQL Server debates, discussions, and inspiration. Last time I did visit IronBirdge. This time, perhaps the birthplace of the modern Olympics and a little running.

You can view the complete list of sessions and then vote for up to 10. There are some great ones, and honestly, it’s going to be hard to choose 10. I really appreciate how many people have submitted and the work they’ve put into crafting some really exciting presentations.

I’ve written it before, but SQL Bits is really my favorite conference. It’s large, but not too large. There are lots of people and it’s exciting, but it’s also not too crowded, nor is it a security cumbersome event. Spouses are welcome, even kids, and it’s got a casual atmosphere at night and highly charged with learning and talking SQL all day.

My hope is that I get to go again, and I get to see lots of you there this spring.

Posted in Blog | Tagged , , | 1 Comment

Backup Data Security

It seems there is no end to the insecure ways in which people manage data. I haven’t seen this one before, but I’m sure it’s happened. In fact, I bet it’s happening right now in more than one company. A company was using rsync to keep data files copied between two insecure servers. Insecure because of a lack of username and password on the systems. In this case, the problem was a subcontractor that dealt with confidential US military personnel data.

I appreciate that many of you are talented scripters that solve problems and build great solutions. I wonder how many of you actually think about security and the potential implications of small mistakes in configuration that others might make. When you build that PoSh script to copy backup files, are you ensuring the transfer takes place in a secure manner? Do you assume that because you use an IP address or server with no DNS entries to receive data that no one else can find it?

All too often I find that sysadmins and developers make assumptions about the security of their process. They think because discovering the process or information would be hard for them or they wouldn’t bother, the data will be secure. And we find that time and time again that the ways in which we build systems without considering security aren’t secure. Someone will find a way to access the data, often with a fairly simple technique. Steal a laptop that’s unencrypted. Get a user to click on a link that installs keystroke logger or uses phishing to gain credentials. Scan a server for known software running with un-patched vulnerabilities or default accounts. I can’t tell you how many times I’ve logged into Oracle database servers with “System” and “Manager” in various organizations.

Certainly our software platforms haven’t been well designed with security in mind. All sorts of expected, happy path behaviors are assumed by software developers, many of which are susceptible to attacks. While modern platforms are better designed and patches are becoming widely available, far too few companies apply these patches and consider security in the software they build on top of the OSes, databases, and other frameworks used for software. As an industry, we are far too guilty of granting more security than needed, opening more ports than necessary, re-using accounts too often, and assuming that our network paths are secure.

Good security comes from having layers that don’t open our systems to a single mis-configuration or simple attack. Disks should be encrypted, minimal privileges granted, accounts not re-used across systems, and network communications, even for simple copies, secured. I know this can slow some development and be painful to implement, but as we become used to using secure credentials and techniques in all of our work, the complexity will fade into the background, and it will be as easy to deploy a secure system as an insecure one.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.2MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged | Leave a comment

More #SQLintheCity–Behind the Scenes

I posted this, but here it is again. A a shot from our engineer, behind the scenes of my live talk today.

//platform.twitter.com/widgets.js

 

When we decided to stream SQL in the City this year, I wondered how things would go. We’ve debated about a studio at the Redgate office but never built one. However, we did take over a room for this event, one on the top (2nd) floor, in a corner of the building away from most desks.

We didn’t have a red “on the air” light, but our engineers did put a whiteboard in the hall outside.

IMG_0212

Inside, we had a backdrop and podium for presenters, tape on the floor for our marks, and multiple cameras. Our setup allowed for a few microphones, multiple video feeds, and a few monitors on the wall.

IMG_0192

My view from the podium shows a few desks and the monitors on the back wall. One had the live stream, and the other was hooked up to a laptop where Annabel would type questions that we could see (or other notes).

IMG_0174]

The event kicked off with Annabel providing an opening each day. Here is the opening segment from Wednesday morning. I watched most of the first day in the room, and then did what most presenters did the second day; I’d work at my desk and then pop in 10 minutes or so before my talk.

IMG_0198

There were lots of wires, and while most were taped down to the floor, we had to be careful about where we walked. I suspect next year we’ll have a better setup. At least, I hope we do, and hope that we actually do more streamed content throughout the year.

IMG_0201

I think this was a success, and certainly an easier way for me to present content. It was strange to not have an audience, but it helped to have a few people in the room that were watching and paying attention.

While I really do enjoy live events and audiences, and I am pushing for a live SQL in the City 2017 in the UK, I also know that streaming and video broadcast is changing for all sorts of media. I’d like to experiment and do more here, and might do some more from home in 2017, but I do hope we’ll have more streaming from Redgate, perhaps even a Redgate TV show running on a regular basis.

Posted in Blog | Tagged , , | Leave a comment

Not Excited by Linux

You’re not excited by Linux. At least most of you in this community aren’t excited. A recent poll we ran showed that nearly two thirds of the people answering aren’t interested in even evaluating SQL Server on Linux. That’s an interesting result because in some groups I’ve spoken with, quite a few people are excited by the prospect of using a different host platform for their database.

When Microsoft first announced they were going to run SQL Server on Linux, I was excited, mostly because I think this means more applications and organizations would consider using SQL Server, which I think is a fantastic platform. It’s been the platform of choice for my entire career. However, from a business perspective, I wasn’t sure if this was a great idea. After all, Microsoft may reduce their revenue from Windows licenses if a large number of people moved to Linux for their underlying OS.

Does the OS matter? I don’t know if it does. I’m not sure how much revenue comes in from Windows Server licenses for hosting SQL Server. I’m sure it’s a significant number, but will the number decline substantially if some people move to Linux from Windows for their SQL Servers? Perhaps, but I wouldn’t think many people make that move.

Instead, I think the people excited by Linux hosting SQL Server are those that already are deeply committed to the Linux platform and avoid SQL Server because they don’t want to run Windows servers. There will be DB2 and Oracle databases that might move to a much less expensive SQL Server license. Certainly I expect lots of (free) Express editions installed by developers, which will allow them to gain experience, and perhaps be more comfortable in the future with SQL Server as a data platform.

It’s hard to know if the move to Linux will result in a big change to Microsoft’s bottom line, but I do think it opens up many new opportunities for us, as data professionals, to find work. With the way that SQL Server works and the seamless way the Linux version seems to behave, I don’t even think most of us need to know much about Linux to develop or manage an instance on that platform.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.5MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged , | 1 Comment

Please Fix This–T-SQL Tuesday #86

tsqltuesdayIt’s T-SQL Tuesday time, and this month is an interesting topic. The invite comes from Brent Ozar, and it’s designed to get you to look at SQL Server. I spent some time thinking about this one and getting a post together.

You can participate as well. Just write a post on your blog, the second Tuesday of the month, link to the invite or leave a comment on their post. If you want to host, contact Adam Machanic.

Better Checks

This Connect item was submitted by Erland Sommarskog, and I think it’s one that is worth spending development time on. The title is Add optional checks for more robust development, and I think it’s a great idea. I’m a big fan of testing and ensuring that you meet standards, that silly mistakes aren’t being made in code, and in general, finding ways to improve developer productivity.

I know that getting errors or failing tests are a pain, but these should drive you to change behavior. If you constantly code “select *”, then you want to get out of the habit. Tests, standards, or other checks are one way to do this.

This suggestion from Erland, which has quite a few votes, is a good one. Getting some sort of warning from deferred name resolution, is a good thing. Having the ability to prevent, or ignore some of the issues, is important. I’d really like to see all of these checks implemented, with the ability to turn them on/off as needed.

These don’t take the place of unit tests with something like tSQLt or automated testing, but they are a good start to helping us produce better software.

If you agree, go vote , and perhaps we can get this implemented.

Posted in Blog | Tagged , , | Leave a comment

SQL Server is Getting More Popular

At the db-engines.com site, SQL Server was named the DMBS of the year. This was the site’s choice based on a measure of how popularity changed from Jan 2016 -Jan 2017. Apparently SQL Server grew in popularity in a number of ways, including job offers and LinkedIn profiles, but also with various search engine metrics and forum discussions. A few people have noted that SQL Server isn’t the most popular DBMS, as Oracle and MySQL are above it in January 2017. However, the change in score was dramatic for SQL Server, with MySQL a close second.

Perhaps Microsoft’s embracing of open source is a reason why SQL Server is growing in popularity. That’s what some people think. With the announcement of SQL Server on Linux, as well as the regular commitments they make to Github, where Microsoft is the largest contributor. Microsoft has had a place for open source projects, CodePlex, for years, but now they have an open source site as well where they have numerous announcements and resources about how one might integrate some of the open source products with their closed source products.

I certainly think the popularity of SQL Server is increasing as it grows to encompass more and more features. Compared to Oracle, SQL Server is a much less expensive, and it integrates well with the .NET stack of programming tools. Microsoft has made strides to make it easier for connections from Java and Python code to SQL Server. I’ve run across quite a few people using Redis, ElasticSearch, and more in applications along with SQL Server, which implies it’s becoming more acceptable and easier to integrate different data platforms than in the past.

SQL Server has been a part of my career for over two decades, and in that time I’ve seen it become more and more popular, more useful, and easier to integrate into a wide variety of applications. I expect this will continue with the Azure SQL Database and Azure SQL Data Warehouse, as well as the Azure Data Lake. There might not be as many installations of SQL Server as Oracle or MySQL, but I think it’s the best database platform for me.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.9MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged | 1 Comment

Getting Started Blogging–#SQLNewBlogger

It’s a new year, and the perfect time for you to start blogging about your SQL Server work. I think blogging is a great way to showcase your knowledge, and over time, this can become as important to your potential employers and clients as your resume or CV.

This post won’t look at what to write, but rather, how to get started. I’ve divided this up for people that might have written some technical articles and those that haven’t. If you have, feel free to skip the next section.

Starting From Scratch

If you’ve never really written a blog or technical piece, I recommend do this:

  1. Create a folder on a shared drive
  2. Open Word (or another WYSIWYG) editor
  3. Begin Writing
  4. Save the post you’ve worked on as 1_SomeTopic.
  5. Repeat, incrementing the number so you get files such as 2_OtherTopic, 3_ReallyInteresting, etc.

That’s it. Just begin writing. Don’t worry about publishing, don’t worry about anything else. Just get in the habit of putting some thoughts down on paper that talk about the work you do.

When you get 10 posts, you are ready to move on.

I would recommend you calculate the average time it takes for you to produce a post. This would be the rate at which I’d look to schedule my posts.

Choosing a Platform

The next step when you are comfortable with your writing is to choose a place to publish your work. There are lots of choices, and a good comparison of sites is listed in this article.

I use WordPress and Blogger/Blogspot for different blogs. I chose two to have a comparison and after a few years, I think WordPress is better. You can certainly download the WordPress software and set up your own site on a VM somewhere, but I don’t think that’s a good place to start unless you want a project. If you want to write, wordpress.com is a nice start. It does enough for me, and it’s easy to let them update the software and just pick a theme.

I also recommend OpenLiveWriter to write with. I can easily draft posts in a WYSIWYG way, keep the drafts private (and on a OneDrive folder) and then publish to my platform at will. I find this easier than trying to work online. The project looks slightly abandoned for now, but it is stable and useful on the Windows platform.

Scheduling

How often should you blog? I think this is a hard question, but I’d blog at the pace that works for me. If you are a new writer, you should have written 10 pieces and tracked the time to produce those. For most people, this is between 2 and 4 weeks for a piece. Some might do 1 a week, but whatever is possible in your busy life is the pace I’d stick with.

My goal is to blog 3 times a week. Sometimes I can do more, but I don’t usually try to do more. It’s better to schedule out posts and ensure I can maintain some level of consistency than I get my posts out right away. Most of the time I have a couple of posts scheduled a few weeks out because I’m not producing news. I’m showcasing knowledge.

My advice is to schedule less frequently than you think you should. It’s easier to add in most posts later than try to maintain some pace that causes you stress.

This should be fun.

Post Reviews

No matter what topics you choose or the frequency of your posts, it is important that you do a good job in producing them. That doesn’t mean you need 3 peer reviews and a copy edit, but you should take the time to get some feedback from others on your work.

If you aren’t an experienced writer, or you worry about the impression your writing makes, then ask a friend, spouse, co-worker, etc. to review a few posts and help you with your grammar, spelling, and the way you communicate the concepts.

Communication is a skill, and you will get better if you work at it. However, the best way I have found to do this quickly is to get feedback from others.

Take the Challenge

Challenge yourself and start blogging today. Even as little as 15 minutes a week can really help you showcase your knowledge, and give you an edge for your next interview.

Posted in Blog | Tagged , , | 2 Comments