May 3, 2011 at 9:09 pm
Comments posted to this topic are about the item Running as SysAdmin
May 3, 2011 at 9:16 pm
Given to 8 consultants with RDP access from outside the network.
Then when I changed the pass after your possible attack warning last week I got an earfull from the network admin... even if nothing was lost, security tightened AND a real threat eliminated.
Oh ya and this is the SA password for the ERP db. You know the one with all the personnal info including bank accounts.
May 3, 2011 at 9:38 pm
I had an instance once where we were being pushed to deploy a small website internally with the SA password in clear text in the page and hence clearly visible to anyone who decided to view the code of the page. They could not see the issue as it was only a litlte page to look at some asset information, even when explaining to them the implications of using SA let alone putting it in plain sight.
Needless to say after a short chat with network security that little website got quietly dropped.
May 3, 2011 at 10:22 pm
I think it most often signals lack of effort or desire to do it differently. It seems like the most effective method to get this changed is to just change the password, have things break and then make them change the way they do things.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 4, 2011 at 12:28 am
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").
But you may as well shoot yourself in the head if any of your users or developers need to do actual work beyond simply selecting tables/executing procedures and you're not allowed to grant sysadmin privileges... enjoy hours searching through BOL for the exact set of permissions meant to allow the SQL Profiler to run (hint: you'll be hard pressed to find any descriptions of exactly which combination of permissions are required to do any kind of statement in SQL Server or its add-on components).
I personally believe that the people who think they know SQL Server security actually just know one or two of the most common cases and (after learning through massive, painful trial and error) applying them. I'd love to throw you onto a real world database or two and see how you fare getting a problem resolved (and I'd even let you access that useless POS BOL). But maybe I'm wrong and you're a genius, in which case please write a book.
Until then, security systems that can't be easily explained and understood are going to be misused so that the case of "most permissions possible" applies; it's time people took MS to task over this instead of kissing butt. Rant over.
May 4, 2011 at 1:49 am
I found a webserver connecting to a back end database using SQL Server authentication, whereby the account used in the web site config file was the SA account. When i questioned the vendors onsite engineer it became very obvious that they did not understand SQL Server security or how to apply it (via the use of appropriate least permissions). Turns out the account only needed to read write a few tables and most of this was done through stored procedures anyway.
The issue is, as we know, datareader and datawriter do not give permissions on SP's so in a vain effort to "Make things work" they just check the Sysadmin box 🙂
Also seen situations where they check every single role for a user, again no understanding that by checking just Sysadmin grants all anyaway
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 4, 2011 at 2:12 am
I agree about the lack of documentation. For in house development I ask for stored procs to be used throughout and have a script which grants execute on all stored procs to a given sql login. That works fine for most things but then I get one team of developers who want sysadmin permissions but don't understand what it's all about.
I ended up being ordered to give them sysadmin as they played their bosses against mine but what they actually needed was control of ssis packages and sql agent jobs and the roles in msdb didn't do the complete job as they couldn't change certain things or work on each others' items. We spent ages researching and trying to give the permissions they needed but they ended up with sysadmin on their server which houses both their development and live databases (our other systems have dev, test and live servers seperated). I have raised a lot of caveats about that server as they now can change anything unwittingly.
May 4, 2011 at 2:20 am
Linked SQL Servers. All the linked servers authenticate with SA, so anyone with access to one SQL Server has sysadmin rights to others.
Some of the devs know this......
May 4, 2011 at 2:22 am
Should someone tell Sony? 🙂
May 4, 2011 at 4:10 am
Well let's start first with a "why".
Usually the DB is last in the chain of layers (or first, depending on the perspective :D).
It's a common implementation for Authentification/Authorization to affect the behavior and look of the application on the other end; usually before a message gets to DAL you already have been in a front end that showed/let you do only thigs that you're authorized for, and hit a BL that already stopped you to perform operations that you're not authorized to do and then and only then the DAL operations are performed. Secondly, under some circumsances, one can or cannot save something to DB - the domain objectual model is not the same with the relational model.
Statement: A good separation of layers always hides the subsequent layers to the frontmost layers, and the layers applications impersonates different users.
Basically that means that you have an authorization (usually checked by the BL and Business Facade) over the application content itself, rather than over each piece of infrastructure of the application.
This is a common approach, and quite a good one.
So, if you're doing it the right way, in order for someone (user or not) to break the security of the SQL server it's almost the same with breaking the security of the (phisical) layers deployment; therefore the security of the machines involved in the layers chain. Well, if that is breaked, SQL security will not be a problem, it may be your last concern...
On the other hand, I saw pretty stupid things, from passing connection strings to invisible textboxes in webapps - more or less protected by some cryptography - to two tier applications (sometimes configured with clear text conection stings to acces the DB with sa) where the user was blocked only by the application to access the full database.
May 4, 2011 at 5:00 am
I agree that used to its fullest SQL Server security is very confusing, and I can certainly understand some people not being willing or able to make full use of it, but I think the big issue really is the mass of low hanging fruit that could very easily be resolved.
Working primarily in the web arena over the years I've lost count of the number of instances where I've seen exposed passwords in website code. Trying to get it through to developers, especially 3rd party ones, why at the very least passwords should only be stored in the global.asa / web.config files is always a challenge.
The worst I've seen though was from a company that needed to install an application on a clients server. They insisted that they needed to have the SA password for the server. We patiently explained that 1) that would happen shortly after hell froze over, and 2) we'd already provided them with a specific account with the necessary permissions. They naturally insisted that it would only work with the SA password (read they'd probably only ever tried it with the SA password), to which we explained that perhaps they should at least try it with the supplied details, and that if it really was that half-arsed then we didn't want it anywhere near one of our servers. After much arguing (thankfully the client had enough faith in us to just go with our advice), they tried it and what do you know it worked. What's worrying in a way is that they'd obviously installed that on lots of other systems, and presumably we must have been the first and only people to put their foot down and refuse to just give them what they asked for without question.
Perhaps that's part of the problem, all too often it's easier to just complain to management that you're being obstructed by the DBA than to learn what's required. As a DBA there's a limit to how much you can argue with management about how things are implemented, especially when doing things properly will cost money and cause delays, and all too often it's those things that matter to managers.
May 4, 2011 at 5:04 am
Horatiu Ripa (5/4/2011)On the other hand, I saw pretty stupid things, from passing connection strings to invisible textboxes in webapps - more or less protected by some cryptography - to two tier applications (sometimes configured with clear text conection stings to acces the DB with sa) where the user was blocked only by the application to access the full database.
You did bring up one point that most business applications end up calling one single high-powered SQL account to "get things done"... it used to be sa and now it's the one account all your web services use.
Not that that's necessarily a bad thing.
But knowing this, and knowing there's at least half a dozen layers in SQL itself with hundreds of specific actions you can restrict... what's the point? Who's using it?
May 4, 2011 at 6:03 am
Let's detail a little bit:
- It's completely inefficient to hit the SQL server with "Select * from table1" with a user that don't have the right to browse "table1" and return to the user "You have no rights to access table1". A good application will have the "Browse something" button that triggers that action unabled/ invisible/ inexistent/ doing nothing. At the second level the BL (if you still, somehow, create the request that asks for browsing the "something") should reject the call and avoid further pull of the message.
- It's pretty common to have people which can see quantities but cannot see values (e.g. for an order/order line) but they can be columns in the same table. Of course you can have different statements for retrieving, but let's say you want to cache those values, and respond from cache, then what can you do? Cache both, with or without values?
- Obviously ANY caching cannot be controlled by SQL authorization since it's outside and before DL.
- It's pretty common to have authority to see "only mine, only belonging to my/a/several groups, all or none" rows or just the rows containing X on column Y, but that may depend on nontrivial conditions - not just belonging or not to a group. Of course, you can workaround something in SQL but it's way harder than to have it directly from code and issue the exact statement to SQL.
- A fine grained authorization at application level can block one to update some fields while enabling you to edit others, I don't think you can cover this with SQL
- The very meaning (content) of some entities can depend on authorization and even the relations between tables that creates them can be different.
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.
May 4, 2011 at 6:12 am
Adrian Chapman (5/4/2011)
Linked SQL Servers. All the linked servers authenticate with SA, so anyone with access to one SQL Server has sysadmin rights to others.Some of the devs know this......
I'll do you one better. Not only do the linked servers connect with sa but the sql server service was a domain admin. I showed my boss that a user with no permissions other than what public gives, a user could get a list of linked servers create a user with sa, do a dir *.* of the file system and then sent the encryption key for Exchange to an external email account.
May 4, 2011 at 7:08 am
Least privilage is actually east to setup. First create a role that grants exec permission on the stored procedures that the application needs access to, add then add the application's login accounts as members of that role in the database.
Seperating the duties of application developer and database developer makes maintaining a least privilage envrionment a lot easier. LINQ and ad-hoc SQL makes it more difficult.
The application and users don't own the database, the DBA and organization own the database. Ask for what you need, and then you will receive.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 95 total)
You must be logged in to reply to this topic. Login to reply