Voice of the DBA

Tag: security

  • Assigning a Default Schema to a New User–#SQLNewBlogger

    I had to test something for a customer, and as a part of this there as a need to have a different default schema for a user. Since this isn’t something that I (or many people) do often, I wanted to make a note about how to do this.

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

    The Scenario

    A user in a database needed to access certain objects, which were going to be located in a separate schema. There was a possibility that there would be objects in the new schema and in the dbo schema with the same name, so the concern was with developers writing code that might access the wrong object.

    The Solution

    When you add a user, this is a simple parameter as part of the CREATE USER DDL. In this case, you use the DEFAULT_SCHEMA parameter. I didn’t look this up at SQL Prompt hinted me to the WITH and the parameter.

    In my case, we wanted to add a new user, which we will call APIUser and assign them to the WebAPI schema. We use this code:

    CREATE USER APIUser FOR LOGIN APIUser WITH DEFAULT_SCHEMA=WebAPI
GO

    Note a couple things. First, the schema name isn’t quoted. It’s treated as an identifier. Second, the WITH is used to add this parameter to the statement. Once we do this, if they user does not include a schema in an object reference, like the one below, they will still get data from the object in the WebAPI schema.

    SELECT * FROM location

    SQL New Blogger

    This was a minor part of something else I was doing. In this case, setting up a different scenario, but I captured this slice of code, edited the names slightly, and then pasted them in here.

    Outside of the work I was doing, the sketch of these notes took about 2 minutes, and then the entire post was < 10 minutes.

    You can do this.

  • Securing All Your Connections

    I read an interesting blog from the cyber security team at Microsoft, noting you should use TLS for your SQL Server connections. I would assume most professionals know that using TLS and secure protocols across the network is important. I would also assume few of us are willing to get real certificates for all of our SQL Servers, especially those in dev and test environments.

    Setting up a certificate for a server instance isn’t hard, but it’s also not easy. It’s also something that I don’t know how easy would be to automate in many environments. I know that you can use APIs from somewhere like Let’s Encrypt, but integrating that into a server setup process would be something. What about integrating this into instances in containers? I don’t know that I think many SQL Server organizations have DNS integrated for most of their database servers, much less asking them to also get certificates set up.

    I do think that Microsoft likely has better infrastructure in place, and better staff resources, than most of us. I can see this being something needed for Azure SQL DB and Azure MI. Heck, this might be something we want for all services that we get in the cloud. Since we don’t control much of anything other than the service, and Microsoft can automate the process of generating certificates, this makes sense.

    I think this might be something that is a good idea for production systems as well, ensuring that when a DBA or system makes a privileged connection to a database server in production, the person or process is sure of which database server is at the other end of the connection. I don’t know that I’ve seen an exploit in the wild where someone impersonates a prod server and captures credentials in some man-in-the-middle attack, but I wouldn’t be surprised if it happens.

    Do you think you also ought to avoid trusting the server certificate? I don’t think this is practical in many orgs, but I’d be curious what you think today.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • Validating Password Expiration

    I would guess that the majority of instances I’ve had to manage in my career were those that I didn’t initially install and configure. I’ve inherited more instances than I would bother to count, and I often need to double-check what’s been done in the past. As noted in the series on new jobs from Tracy and Josephine, there are a lot of settings to check and adjust to meet your standards.

    While backups are often my first priority, security is second. I usually want to know who the sysadmins are and ensure systems are patched and configured to reduce the attack surface area. There is one other security check that I think I haven’t always been overly concerned about checking: password expiration.

    There was a post from Steve Stedman recently that mentioned the way to alter logins and ensure they have CHECK_EXPIRATION set ot on, which ensures that passwords expire and need to get changed. This is especially important for sysadmins. I try to ensure those accounts in that role are secured with AD, but there have been times when SQL accounts are used. Usually, I disable sa, but I’ve seen other accounts, especially those used by monitoring systems who seem to think sysadmin is required. It’s not.

    I don’t know that I’ve run queries to check the value in the is_expiration_checked column is appropriately set. If it’s not, then Steve’s post above will help you change those logins. That’s a handy script to have set up and use to ensure that all logins have this set. In fact, this is one of those areas where new logins could be created by junior administrators and not set the option. Perhaps this is something you want to run on a regular basis, perhaps weekly, to ensure that if any new SQL logins are created, they are done so with the password expiration set.

    Ideally, no one would ever create logins without expiration set, but sometimes things happen. I’ve seen monitoring systems set up with sysadmin privileges and passwords that never expire. A surefire way to dramatically increase the risk to your database systems. It would be better to have a known, consistent process for setting up accounts. Some companies have specific scripts, or snippets, that administrators use when tickets are filed. One customer of mine had even linked a script to a Slack command in a sysadmin channel. Only admins could use this channel, but they could use Slack to kick off scripts to create logins, add roles, and force password changes.

    No matter how you choose to handle security at a process level, it is important to include monitoring and remediation for issues. Mistakes will get made, settings altered, and exceptions approved. Sometimes we can fix things, sometimes we cannot, but knowing what our environment looks like and where we have potential issues is important not only for getting the work complete but getting the approvals to make changes that ensure better security. My recommendation is that you ensure you have a way to regularly check your systems, automatically fix issues where appropriate, and report on those that need additional approvals.

    Steve Jones

  • The Public Perception of Security Issues

    It’s my fault.

    That’s what I think if there is a security incident with my employer that involves the database. It’s almost my first thought when I hear about issues at other organizations, thinking a technical person is at fault. Since I’ve been a developer and administrator, and I know how complex systems are, I usually stop myself and try to learn more before I assign blame.

    The public and your customers also think that it’s just your fault. At least, that’s what I see and hear from friends. Non-technical people are very quick to assign blame and get upset. They can’t understand why some companies get breached and others don’t.  To them, it’s because the staff or management are lazy and haven’t done a good job keeping their systems secure.

    However, even my technical friends get upset. I’ve had more than a few of them chastise an organization for getting breached when they themselves haven’t always kept up to date on patches. I mean, how many of you are sure every SQL Server you have is at the latest CU level? How quickly do you patch? Are you sure your firewall people haven’t accidentally misconfigured a rule for port 1433?

    Anyone can get breached, as noted in this article. However, a good response can set you apart, and I wish that more management and technical people would be prepared now for a data loss incident, a ransomware attack, or really any security issue that might occur in the future.

    It’s easy to panic and make rash decisions. The best time to draft your response is now, when you have a clear head and no pressure. Have a few people start to game out how to react, what words and message to send, and who will take responsibility for communicating with customers. It’s worth a little exercise to discuss some possible responses to events and at least have the outline of a plan.

    And no matter what, be sure you have a copy of the plan air-gapped from your network. On a few flash drives, saved to a separate OneDrive/Google Drive/Dropbox account, or even printed out. The last think you need is for all of your work to be inaccessible because of something like ransomware encryption.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.