Voice of the DBA

Author: way0utwest

Connection String Confusion in SSMS
Recently I was trying to use a connection string to connect in SSMS. There are some tools that have a connection string available as an output, including some Redgate tools. ADS lets me paste in a connection string. Can I do this in Management Studio (SSMS).

Yes, but be careful.

Getting a Connection String

There are lots of ways to get a connection string. You can build one, or use a site like https://www.connectionstrings.com/sql-server/ I tend to get them for applications, as I work more with app developers.

In any case, I’ll use this as my string:
```
Server=Aristotle;Database=Sandbox;Trusted_Connection=True;
```
If I open SSMs, I get a connection dialog like this one. I see the server, but if I wanted a specific database, I’d have to go to the second tab.

I could also go to the last tab, the Additional Connection Properties, and paste my string in there.

I press Connect and that works great.

If I open a new Query Window, I’m connected to the Sandbox database.

The Problem

Let’s change the connection. I’ll press the icon to the left of the database name in the image above. Then I’ll go to the second tab and pick a database. In this case, I’ve selected the Westwind database.

If I click Connect, I see this:

The connection string in the individual parameters overrides the selection here. If this were a day later, I might remember I’d put a string in the Additional Connection Parameters. I rarely use this and when this happened, I couldn’t figure out why this wasn’t working.

I thought this also happened with changing the main dialog and the server name, but this appears fixed. At least on SSMS 20.1, the additional connection parameters are linked with any saved systems I have on the first tab, so if I change servers in the drop down, the additional connection parameters link to the last entry for that server.

On my laptop, which has 20.2, I’ll enter this as a connection string in the last tab:

In this case, I have multiple container instances running on different ports. This instance is on port 41433. On the main tab of the connection dialog, I see this:

What happens when I press Connect? I get to this server:

The default port is SQL Server 2022, but the additional connection properties overrode the front screen in this case.

Summary

I’ve never had this problem in 30+ years of SQL Server work until this summer. However, it’s the first time I’ve really been focused on using connection strings more often than just entering values in the dialog. I only noticed this as I had a deployment going to one instance, but SSMS kept connecting to the other one and I didn’t realize this.

I think it’s OK to have conflicting values in locations, but it wasn’t clear to me that these values override others. This is documented on the MSLearn site, but it’s easy to miss this.

Hence this blog.
September 25, 2024
AI Is Great and Tech is Failing

On a recent weekend, I got a text from my bank that they had declined a charge to one of my business accounts. I called them back and they let me know there had been a couple of weird charges on the account that their AI system detected. This seems to happen every year or two so I wasn’t overly worried. I cancelled the card and ordered a new one.

A day later, my wife got a call about our credit card with the same issue. She cancelled the card and got new ones ordered. However, I use that card to travel and I had a trip booked. Suddenly I was without a credit. Luckily, we have another card for my wife’s business that I could use. I called the bank and had a card expedited, but the situation created some stress. In fact, I panic-bought an RFID-shielded wallet. I’ve resisted for years, using an older, large wallet me daughter bought for me one Father’s Day that always reminds me of her. The timing across a few cards was weird, and I suspect my wallet got scanned somewhere and both card numbers were stolen.

A few things. First, be careful with the new tap cards, as they can be scanned and read from a distance, albeit a short one. Second, having a spare payment method might be nice in this age of non-cash transactions. Third, why is technology failing with new cards?

I lost a card last year and knew it was gone. There were no charges, but I couldn’t find it and needed a replacement, so I cancelled it and ordered a new one. In minutes the digital cards on my phone (and watch) had been replaced. I had new numbers and could transact business.

Why would this be different? The banks arguably have better knowledge of my digital wallets, and replacing those is much easier than relying on a snail mail server and the time it takes to deliver cards. In my rural area, we regularly have reports of stolen mail, with thieves targeting credit cards and physical checks sent by snail mail.

This was a minor issue in my life, and I am fortunate I have other ways to manage payments in this minor crisis. I was (and am) happy that AI systems are often detecting fraud. I haven’t had any fake charges go through in a decade and almost every real charge is approved, even with my crazy travel schedule. However, I’m disappointed in technology in this case.

Many organizations are engaged in a digital transformation. They’re hiring software developers and trying to take advantage of all the data they have to improve services and efficiency. Security, service, and spending would be served better with a little technology improvement here.

Steve Jones

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

Note, podcasts are only available for a limited time online.

September 25, 2024
Small Data SF 2024

I have often made an effort to attend conferences in the past to grow my career. Even today, when I speak at a conference, I’ll try to go to a few sessions and learn something, but I can be distracted. It’s rare I focus on just learning stuff without other responsibilities.

I’m at Small Data today and tomorrow doing precisely that. I’m just an attendee, and today is a couple of workshops for me with a variety of talks tomorrow. It’s a real career growth opportunity for me and I’m excited. I’m doing the Data Warehousing and Design workshops today and sitting in sessions all day tomorrow.

I can’t remember where I heard of this conference, but when I saw the manifesto and sessions, I was intrigued. There were tentative plans this week for me to do a customer visit, but when that got delayed, I jumped on the opportunity to visit San Fran and learn something.

As I’ve worked with a lot of customers, I see the value of small data sets providing lots of agility for teams, while also allowing them to get work done, as long as the data sets are relevant and representative. That’s a big part of me pushing the Subsetter at Redgate.

In this case, the conference focuses more on analytics and AI, and likely more developers than DBAs, but I think it’s a chance to get different perspectives, maybe learn a few things, and perhaps get others to see my data viewpoint in the hallways discussions.

September 23, 2024
No Backdoors

Every once in awhile I hear about someone in law enforcement sure that tech people can build in a safe, secure way for data to be unencrypted by the company or vendor. The latest appears to be from Australia, where the Security Intelligence Organization wants tech companies to build this into products.

Backdoors never work. Anytime an encryption key is stored, it could be stolen. We see this all the time. Keys are just data, and companies lose data all the time. At scale. Governments are certainly not immune from this. One of the reasons that Azure allows a BYOK (bring your own key) for encryption mechanisms is that many organizations don’t want to trust Microsoft to store their keys. I’m guessing Microsoft doesn’t want the liability, either.

Many of the data protections an organization might implement are outside the scope of data professionals, but we certainly have responsibilities in this area. We should certainly manage our keys appropriately, and my recommendation for many people is that they have a separate repo and pipeline for the deployment of privileged objects, such as encryption keys. Your organization also ought to have a process (and test it) for key rotation. Keys expire relatively infrequently and the person who last rotated the key might not be available when you realize this process needs to be completed.

It’s especially important everyone knows how this works for those emergency situations where something expires and none of you realize there’s a problem until a client calls.

There are plenty of other security mechanisms we ought to be using. Secure your backup files and limit access to shares or even processes that move these files around. In general, limit access to those who need access to everything. Using organizational groups is the best way to do this, along with regular audits to ensure that those who change jobs are removed from groups that aren’t needed. I haven’t seen any organization that has a good process for knowing what positions need what access. I often only see people given new access for a role change, giving them the new roles that match some other employee. This is usually done without any previous access removed when it is no longer needed. The most senior people often have the most access, not because they need it, but because they keep getting new roles.

Managing security with roles too granularly can become a nightmare, though ensure you use roles everywhere you can while trying to limit the numbers of roles. For most databases, we are giving access to nothing or everything, but there are reasons to limit access for certain data. You might consider two roles by default in most places: one for privileged users and another for everyone. Easy to ensure new objects get grants to one or both roles and let access be managed by role membership.

Keep it simple, but keep it secure.

Steve Jones

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

Note, podcasts are only available for a limited time online.

September 23, 2024