Running as SysAdmin

Question

Running as SysAdmin

Viewing 15 posts - 31 through 45 (of 95 total)

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

Steve Jones - SSC Editor SSC Guru Points: 734522 More actions · Answer 1

www.ssrstips.com (5/4/2011)
Forgive me in advance for a bit of a rant but by raising lack of knowledge about SQL Server security and then failing to offer any solution (other than saying there should be a course) you've touched a raw nerve in me! The problem is that SQL Server security is overly and uselessly complex and there's a lack of any current books on how SQL Server security works (let alone teaching by example rather than waffling on philosophically).
Logins and users are easy enough to understand, but throw in schemas, roles, owners, and the possibility of having users/group domain and machine name logins, and it begins to get messy (we'll ignore for the moment the SQL add-on components like reporting/analysis services which might be on a different server, and how that security works, and also ignore file-level NTFS security on the SQL server itself, which hopefully will "just work").

I'd actually disagree somewhat with this. I do think the documentation is poorly done, and an afterthought on the product. However the vast, vast, majority of SQL Server databases can have easy security. Create a role, add permissions to the role for tables/views/functions/procs.

It's that simple. You can ignore schemas and owners. You can put everything under one schema (dbo) and ignore any security there. Where this breaks down is so many developers and DBAs use SSMS to create a table or a proc and then when they have issues, they just run everything as "sa" to make it easy on themselves. Personally when you create/alter an object, there ought to be a security roles dialog on the designer, or one that pops up so you can easily assign security then. If you do things with T-SQL, it's easier to do, but not foolproof. Users/logins whether windows or SQL are just simple adds to a role. Doing anything else and you are unnecessarily shooting yourself in the foot.

No "normal" application needs to run Profiler/trace, or alter schemas. BCP was a big hole in previous versions, but that's better done now.

I would agree that the overall security structure when you get to many admin tasks is confusing, and it's not well designed from an ergonomic standpoint. It's as flexible as it can be, but there is so little help in debugging issues or explaining how the permissions work that it is complex.

Craig A. Silvis Ten Centuries Points: 1038 More actions · Answer 2

We had one vendor for a major application hard code the SA password in the application instead of creating correct security via a special user id/role etc.

The DBA team didn't know that and reset the SA password which caused the app to stop working.

We tried resetting it back but the App didn't like it for some reason.

Worst part of the story was that it took the vendor 5 hours to get back to us with instructions on how to change the password in the App.

jaldridge SSC Journeyman Points: 99 More actions · Answer 3

I saw a linked server setup with no explicit logins mapped and the "For a login not defined in the list above, connections will:" be mapped to sa!!!! All I could say was wow... In their defense they didn't have anyone with any sort of dba skills and just "needed it to work". 😀

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Worst setup ever...

I once saw an environment where every application and then some (from SQL Server to custom windows services to vendor products) ran as Domain Administrator (not local admin, domain admin).

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Rose Bud Hall of Fame Points: 3147 More actions · Answer 5

True story: Blank SA password, long-time developer knows this, long-time developer laid off, delay in revoking long-time developer's Remote Desktop privileges and network login.

No, nothing happened. Long-time developer had impeccable ethics. I still do.

Dream Weaver Ten Centuries Points: 1104 More actions · Answer 6

I know some people involved in one that might possibly win the award for the worst ever.

The scene: development of an on-line pharmacy. Users would be entering personal identification information, medical information. HIPAA protections required.

The main character: a Fortune 500 company doing the development, mostly in-house.

Secondary character: a very large communications vendor

The good guys: the development team

The bad guys: their management

The plot: main character buys a Customer Relationship Management (CRM) system from the communications vendor. The only piece not developed in house. The CRM system runs on SQL 2000.

Which thickens: the CRM product has its login hard-coded into the app, and the vendor won't change it. The login: sa, blank password. The good guys try their best to put the brakes on the project, pointing out the even if the system doesn't get hacked from outside, HIPAA-protected information will be available to anyone in the company who can discover default instances of SQL Server. The villians press forward anyway, launching the site without ever reporting any concerns to their superiors.

Several years later, under new management, the site is still live, still unprotected, the CRM product is no longer supported by the vendor. The good guys report the issue again during a security audit. New management pushes concerns up to senior-level management but it never gets to the executive level. The server gets put behind a firewall to at least attempt to protect it from insiders.

The end: a couple years later the online pharmacy is finally shut down. No one knows whether any information was ever compromised. Probably not, but that's the point: who knows?

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

A simple Google search can reveal page after page of web.config files located (perhaps inadvertently) in public web folders and contain a ConnectionString with uid="SA" and PW.

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

Peter Trast SSCarpal Tunnel Points: 4392 More actions · Answer 8

Eric M Russell (5/4/2011)
A simple Google search can reveal page after page of web.config files located (perhaps inadvertently) in public web folders and contain a ConnectionString with uid="SA" and PW.

shhhhhhhh..... security by obscurity at work here...

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems

Eric M Russell SSC Guru Points: 125523 More actions · Answer 9

Peter Trast (5/4/2011)
Eric M Russell (5/4/2011)
A simple Google search can reveal page after page of web.config files located (perhaps inadvertently) in public web folders and contain a ConnectionString with uid="SA" and PW.
shhhhhhhh..... security by obscurity at work here...

I'd call it hiding in plain sight.

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

Peter Trast SSCarpal Tunnel Points: 4392 More actions · Answer 10

Eric M Russell (5/4/2011)
Peter Trast (5/4/2011)
Eric M Russell (5/4/2011)
A simple Google search can reveal page after page of web.config files located (perhaps inadvertently) in public web folders and contain a ConnectionString with uid="SA" and PW.
shhhhhhhh..... security by obscurity at work here...
I'd call it hiding in plain sight.

I would call it an oxymoron...

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems

Cody Konior SSCarpal Tunnel Points: 4827 More actions · Answer 11

Steve Jones - SSC Editor (5/4/2011)I'd actually disagree somewhat with this. I do think the documentation is poorly done, and an afterthought on the product. However the vast, vast, majority of SQL Server databases can have easy security. Create a role, add permissions to the role for tables/views/functions/procs.

I agree with what you've said, so we're largely on the same page. It's not too hard to go for a default install and some basic roles, which is why some people say they "get" sql server security.

But once requirements get beyond something simple, that's when it falls down. As a report writer on an in-development application, my requirements are actually pretty heavy. We don't have a dedicated DBA, but the management is security conscious, so I actually get locked out of what I need to do my job and have nowhere to turn. Sigh.

I would agree that the overall security structure when you get to many admin tasks is confusing, and it's not well designed from an ergonomic standpoint. It's as flexible as it can be, but there is so little help in debugging issues or explaining how the permissions work that it is complex.

I think this is where I'm mainly disgruntled at.

But I'm having a look at that Wrox book mentioned earlier in the thread, maybe it will clear everything up for me...

LutzM SSC Guru Points: 107049 More actions · Answer 12

Ninja's_RGR'us (5/4/2011)
kim.etcheson (5/4/2011)
We bought a vendor package where the vendor hardcoded the db logon and password in the application that we couldn't change on the database or the app wouldn't work. They swore the id had to be assigned the sys admin privilege. Then the password that they hardcoded was readily available in all their system install documentation on the internet.
Hack fest!!!
Wow that is bad.
Have you actually tried denying permissions to that login just to see if it really broke the app?

You're not talking about a huge german company with an S at the beginning and at the end and a "MEN almost in the middle"?

To add to what's already mentioned: this specific software form that specific vendor even had xp_cmdshell enabled by default and login permissions from the network. Fortunately, there is a (more or less) easy fix: the next software version is much more secure...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Nadrek SSC-Insane Points: 20039 More actions · Answer 13

Horatiu Ripa (5/4/2011)
Let's detail a little bit:
...
At the end of the day I may say:
- when SQL is deep in the militarized zone, beneath multiple layers and security levels, the application DB access configuration is hidden, you have a complex but good authentication/authorization at application level, then if someone breaks all the security levels up to SQL means that you're completely hacked and the SQL security is of very little use. Then why bother?
- when SQL is "close" to front end, or somehow exposed to users than SQL security is a must.
...

Any SQL Server that a web server uses is one concatenated query away from SQL Injection.

Any SQL Server that the aforementioned SQL server has a linked server connection to is... the same distance away from SQL Injection.

It's always possible that human error(s) create a linked server.

Steve Jones - SSC Editor SSC Guru Points: 734522 More actions · Answer 14

www.ssrstips.com (5/4/2011)
I would agree that the overall security structure when you get to many admin tasks is confusing, and it's not well designed from an ergonomic standpoint. It's as flexible as it can be, but there is so little help in debugging issues or explaining how the permissions work that it is complex.
I think this is where I'm mainly disgruntled at.
But I'm having a look at that Wrox book mentioned earlier in the thread, maybe it will clear everything up for me...

I doubt it, but let me know if it does. Haven't read that one.

I tried to simplify some of this for people, but at a high (300-400) level, it's hard. And documentation is for crap, especially for SSRS and the subsystems. You have my sympathy there.

sqlstad SSCrazy Points: 2484 More actions · Answer 15

I usually have to educate colleagues about the downside of giving sysadmin to service accounts.

The response I get the most is that the business is pushing forward and time is getting tight to setup privileges the right way.

It looks good at the moment but when you work in the banking world you get regular audits which will then point out these kind of setups.

I've had external developers say they needed the sysadmin rights because the application wouldn't work without it.

My question to that is always if they can explain why.

As long as I know why the service account needs it I can show that to the auditors and there would be valid reason to it.

But that's not the case in most situations and external and internal developers just can't tell me why.

As soon as that happens I pull the brake because this could be harmful to the system and/or the databases.

I don't like the fact of service accounts having sysadmin privileges but as long as there is a very good reason behind it and the password is not just p@ssw0rd 😉

Still the security system of SQL Server seems very easy but this is only when you don't use schema's, database roles and all other kinds of combinations. It can get very complicated and I've had my share of investigations to get somebody the right privilege without giving them the old GOD role in the system.