T-SQL Tuesday #73 Naughty or Nice?

TSQL2sDay150x150_388014A5This month’s topic for T-SQL Tuesday is hosted by Bradley Ball (blog | twitter) with the subject of server environments.

As you work with SQL Server look around you. Is your environment Naughty or Nice? If it is Naughty what’s wrong with it? What would you do to fix it? Do you have a scrooge that is giving you the Christmas chills? Perhaps you have servers of past, present, and future haunting you. Maybe you are looking at SQL Server 2016 like some bright shining star in the east.

I won’t discuss my current environment, but I do have a frightening story of SQL past.

At a previous employer, I worked in a hastily built environment that never had the benefit of a DBA or Database Architect during setup.  The company started small, so originally they used Microsoft Access to house all their data. At some point, the tiny IT department migrated all the data over to a SQL Server database, but they still ran an Access front end.  The migration was done by people who were not concerned about normalization.  The goal was to move the data, structure was a tertiary concern at best. Many sins were committed in that database, and as the years went by and the data grew, the sins became more apparent.

Querying became a nightmare.  The number of nested functions, within nested functions, within nested stored procedures, was exhausting. I remember finding a procedure that had 14 levels of nested objects. I had to keep notes of object names as I delved in, trying to understand what was happening in that query. I’m still not certain I understood it all.

The real problem though, was that every user also had Excel reports that were direct connections to the database. The wait times were high from all the external hits to SQL Server, but the connection strings…oh the connections strings…

Every single user connected to the database via the SA account. Which was not renamed.  At least the password (which was from the beginning of time) was moderately complex.  However, each user could display the connection string to the database in plain text with a bit of poking around. There was no limit to the mayhem they could commit.

Since every user was connecting as SA, the only identification possible was by IP address. And since everyone always locks their computer in small company… it was not the best method for identifying a nefarious user.

I did try to get the SA password changed and the account locked down, but it was an arduous process. The old database was delicate, but even more unknown were all the individual reports and local connection interfaces created over the years. Interrupting business was frowned on more than the unlikely possibility of a user misusing the SA account. A few changes got made, but until the database and user interface was rebuilt from the ground up, nothing was actually solved.

Security is a major concern, and something like a global server admin account should always be kept tightly controlled, no matter the company size or trust level.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s