Removing Weak Security from SQL Server

Question

Removing Weak Security from SQL Server

Steve Jones - SSC Editor

SSC Guru

Points: 736212
More actions
August 25, 2023 at 12:00 am

#4263745

Comments posted to this topic are about the item Removing Weak Security from SQL Server
August 25, 2023 at 3:50 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Eric M Russell SSC Guru Points: 125561 More actions · Answer 1

Password encrypting backup files can actually be useful in some scenarios, like for organizations that copy their backups to cloud storage or removable media, so why not just fix the implementation rather than remove the feature?

Another gripe is that SQL Server Express edition has never supported TDE or encrypted backups. Security features should be fully supported on all editions of a database that are typically used in a production environment. For example, we actually have Express edition installed at 1,000+ point of sale locations and rely on a 3rd party backup solution just for the encryption.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

Eric M Russell wrote:

For example, we actually have Express edition installed at 1,000+ point of sale locations and rely on a 3rd party backup solution just for the encryption.

Now THAT sounds like a boat load of interesting stuff, Eric! Are you saying that you backup each of those sites on a regular basis? That's incredible.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eric M Russell SSC Guru Points: 125561 More actions · Answer 3

Jeff Moden wrote:

Eric M Russell wrote:
For example, we actually have Express edition installed at 1,000+ point of sale locations and rely on a 3rd party backup solution just for the encryption.
Now THAT sounds like a boat load of interesting stuff, Eric! Are you saying that you backup each of those sites on a regular basis? That's incredible.

So, each POS database is replicated back to a cluster of partitioned database in the datacenter. But for the purpose of quick disaster recovery, full backups at each physical store are taken daily and differential on hourly basis. Of course Express Edition doesn't have SQL Agent, so the backups are scheduled using a process we developed in-house. It works, and when it doesn't, it's usually something that field technical support can address. From a DBA perspective, store backups are something we only occasionally get involved with.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

I think early on the decision to not encrypt Express backups was sales to try and get people to use Standard. Personally, I wish they had a low=cost license for Express+, maybe $100/instance, that made it look like Standard with the CPU/size limits. Then encryption could and should be added.

Express is free, and not really aimed at production, so I get why it's crippled, but these days, security is important. I'm not sure MS is helping us here by limiting many of those features.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 5

For higher volume stores whose database can't fit within 10GB limit, we will deploy Standard Edition. Obviously Azure SQL would have advantages over on-sight deployments, but the issue is that we can't count on reliable internet connectivity many areas.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

frederico_fonseca SSCoach Points: 16154 More actions · Answer 6

Eric M Russell wrote:

For higher volume stores whose database can't fit within 10GB limit, we will deploy Standard Edition. Obviously Azure SQL would have advantages over on-sight deployments, but the issue is that we can't count on reliable internet connectivity many areas.

and then there are the "smart" companies - the limit of 10GB is per database - no limit in having 100 databases all with a single 10GB table on it. know of 2 software houses that did this to save clients costs.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 7

frederico_fonseca wrote:

Eric M Russell wrote:
For higher volume stores whose database can't fit within 10GB limit, we will deploy Standard Edition. Obviously Azure SQL would have advantages over on-sight deployments, but the issue is that we can't count on reliable internet connectivity many areas.
and then there are the "smart" companies - the limit of 10GB is per database - no limit in having 100 databases all with a single 10GB table on it. know of 2 software houses that did this to save clients costs.

So, how practical vertical partitioning across multiple databases would be depends on the application, and in this case it's a multi-user OLTP database. For a POS application that performs a lot concurrent reads and writes, it needs to have sub-second latency. Express Edition also a limit of 1 GB memory and 4 cores for which there is no work-around, so it would struggle scaling past 10 GB even if the per database storage cap were not there.

There is a historical data retention policy for the POS databases (payed off accounts, inactive customers, logging, etc.), so anything like reporting and dashboards point to the consolidated replica servers hosted in the corporate data center.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho