May 4, 2011 at 7:14 am
www.ssrstips.com (5/4/2011)
Forgive me in advance for a bit of a rant ...Rant over.
I am not sure if I agree with you or not on the points you made about how difficult it is, nor whether there is sufficient help available.
That said, I certainly agree with your attitude! I don't think this issue is limited to SQL either, it seems to be pervasive across the industry. Even simple things aren't documented. Why? Maybe it is a result of all of the outsourcing being done, and people are relying on their unique knowledge to stay employed. Who knows. I can name vendors whose products have deliberately incorrect documentation for the purpose of forcing the customer to pay them to complete upgrades, so I don't think this is an issue of individuals either.
Dave
May 4, 2011 at 7:17 am
The book I found with the best explanation of SQL Security is:
WROX - Beginning SQL Server 2005 Administration
Chapter 6 - SQL Server 2005 Security
Page 175
Does anyone know of any excellent resources, especially online?
May 4, 2011 at 7:19 am
I have never been "put on the carpet" for security matters. I have been "put on the carpet" for getting things finished on time. Security takes time. I've had vendors who install their app and need "the sa password". Of course no forewarning of their coming. Since I have no idea what the app is doing I give them a temporary acct/password. I tell them its temporary. They install and leave. I remove the login and stuff breaks. Do I demand things be sorted out and spend days with a broken app and mad end users, or do I reinstall the login in 1 minute and things work and I am a hero ? The real world is a beach.
May 4, 2011 at 7:19 am
The worst I have seen is the all too common blank sa password, with the added benefit of providing instructions to end users that documents how to create a data source to the system. Not enough, how about the instructions are kept in a file with full control from authenticated users. Better - we published these instructions, and a the information on where they are kept, on a site where employees can read organizational updates each day.
The thing that makes it really cool is at the time every system had a blank password, every person in our department had the domain admin password, and we were running applications and services for various products using the domain admin! It was really fun when someone quit - fix every system you can think of (nothing documented of course) and be careful rebooting anything in the future because we always find another system using the domain admin that wasn't changed the last time.
Dave
May 4, 2011 at 7:27 am
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.
May 4, 2011 at 7:34 am
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?
May 4, 2011 at 7:58 am
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
Because unless the only wire from the Database Server is the one to the Application Server then your applications's authentication is useless. Anyone can hit port 1433 and log in if they can find a password, if the password they can find is to an account that has sysadmin then you have much larger problems than if the account is least permissions.
I can't control the network routing, or the Domain account permissions. I can control the SQL Server permissions.
--
JimFive
May 4, 2011 at 8:05 am
When I originally arrived to my current work encoutered most web apps using the SA account. But that was not the scary thing...
We had a ton of users querying the DB from Access, most of their ODBCs were using SA...
And of course, SA password was blank! to make life easier for our fellow users :w00t:
May 4, 2011 at 8:09 am
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.
Occasionally we all have to manage some database application developed by a 3rd party (perhaps even another corporate division) and we have little control or knowledge over it's architecture. Whenever a support tech claims that an application account "must" have SA provilages just to operate it's daily functions, what that usually means (even if they don't know for sure themselves) is that the account needs permission to kick off jobs, execute bulk insert, or truncate/create/drop staging tables.
One thing you can try is to create a non-SYSADMIN account and add it to the db_datareader, db_datawriter, and db_ddladmin roles. If that doesn't work, then make them the database owner. The only server level membership they may need is SQLAgentUserRole for kicking off jobs and perhaps VIEW DATABASE STATE for querying DMVs and system tables.
A poorly designed CRM or accounting application may run roughshod over it's own database, but there is totally no reason for it to require admin privilages over your server. You can also use SQL Profiler to trace what exactly what the account is doing.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 4, 2011 at 8:09 am
When i started all developers had sysadmin to run Profiler and to pull/store profiler results on a db the server..atleast those were the reasons given. Further reasoning...we will drop the db when we don't need it so that you guys (DBAs) don't run into space issues.. :))
May 4, 2011 at 8:17 am
I've seen a lot of cases of SA bad-use applications. Starting with leaving it with a blank password or use it in ODBC's connections.
Let us remember that SysAdmin is a backdoor-breackTheGlass-InCaseOfEmergency user. So we don´t want to prostitute it. Instead of that, les back to the most basic theory: Create specific users for specific tasks, with specific scope.
It's true that the lack of documentation is a limitation, but i think that -for the most common task (80% of them)- we could apply a simply logic to solve any problem avoiding the use of SA login.
After all, ensure a secure environment is one of our primary tasks.
Mario Iturralde
May 4, 2011 at 8:24 am
Eric M Russell (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.Occasionally we all have to manage some database application developed by a 3rd party (perhaps even another corporate division) and we have little control or knowledge over it's architecture. Whenever a support tech claims that an application account "must" have SA provilages just to operate it's daily functions, what that usually means (even if they don't know for sure themselves) is that the account needs permission to kick off jobs, execute bulk insert, or truncate/create/drop staging tables.
One thing you can try is to create a non-SYSADMIN account and add it to the db_datareader, db_datawriter, and db_ddladmin roles. If that doesn't work, then make them the database owner. The only server level membership they may need is SQLAgentUserRole for kicking off jobs and perhaps VIEW DATABASE STATE for querying DMVs and system tables.
A poorly designed CRM or accounting application may run roughshod over it's own database, but there is totally no reason for it to require admin privilages over your server. You can also use SQL Profiler to trace what exactly what the account is doing.
On the development and QA servers, the only servers where I'm sometimes a DBA, I will grant application developers db_datareader, view schema, and view server state permission. It's beneficial for development and QA to have no restrictions about exploring and troubleshooting the data, schemas, and running profiler traces. Honestly I'd rather not be pestered with constant query requests. However, the application account still has least privillage (only exec permission on app procedures), and the developer accounts are not carried over to the QA and Production environments.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 4, 2011 at 8:25 am
A professional environment would have documented standards for access ideally by the DBA team with management approval. A good DBA team will not ask a developer to dig into BOL and come up with what permissions are needed for running profiler or anything similar. Further restricting permissions is rarely possible without management approval which is all the more reason to document it. In most small shops liberal use of sysadmin happens with management approval and is often an issue around control-freak(ism) than anything technical.
May 4, 2011 at 9:17 am
I have a potential customer who wants to migrate his SQL 2000 environment from an "old" server to a new server keeping the SQL 2000 environment because of the custom application that rings their register but has never been updated. Without my asking, they sent me the credentials for the old and new server. This is a credit card processing application that uses SA and has a blank password... BLANK! :w00t: It's nice to be trusted but yikes!
Fortunately (???) the new server has "better" security, SA with a password that is... well, let me just say weak and most of you would guess it in 10 minutes or less.
Time for that best practices talk... the one I do several times a month for some strange reason...
"When will they ever learn... when will they eeeee-ver learn?"
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
May 4, 2011 at 9:42 am
Steve, I think you’re being too nice. I’m much more blunt about this. It comes down to plain negligence and laziness. I know this is going to ruffle some feathers but so be it. My personal information is stored in SQL Server all over the world. Though I don’t think about it on a daily basis I want to know that DBAs out there are doing everything in their power to protect my data. Just as I’m sure they would want from other DBAs managing their data. Doctors have the Hippocratic oath maybe we need to have a DBA oath. I would put data security management at the top of the list. Remember, ignorance is not a defense! I could go on but I leave it at this.
Viewing 15 posts - 16 through 30 (of 95 total)
You must be logged in to reply to this topic. Login to reply