November 20, 2014 at 10:49 am
Jeff Moden (11/20/2014)
NJDave (11/20/2014)
Today, a DBA in my group doesn't understand the reasoning and has experiencing storing the passwords for end-users.
Sounds mean but my suggestion is that anyone that doesn't understand isn't actually a "DBA".
Also, developers are asking for their old passwords, telling me their passwords, showing it in the web.config, etc.
All bad. Really bad. The company should get a professional security audit done and then impliment just about everything they suggest (if not everything). The company would also seriously benefit by having someone come in and teach some mandatory-attendance security. And it all should be done immediately to avoid becoming something like the next Home Depot, etc.
Throughout this entire thread there's no mention of Dev vs production. Is there no separate environment at all? I could see some leeway in Dev, assuming it's walled off (i.e. different securinty in dev than anywhere as one possible scneario)- but if you don't even have that kind of isolation, most of these other controls don't mean a whole lot (sure it would be nice for the DBA not to know the dev passwords, but hey if the devs have the ability to modify stuff, even code, in production, knowing their passwords is likely the least of your issues.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 20, 2014 at 11:36 am
There is a seperate dev & production area. The login is for an application using SQL Server authentication in a web.config file for the application to connect.
Thank you for sharing
Dave
November 20, 2014 at 2:02 pm
No, DBAs should not know and definitely not store end user passwords, period.
App passwords are a different matter, and it can depend on the app. But stored passwords should always be encrypted, and the decrypt key known by as few people possible, period. In a true emergency, one of those people can make it available to others as needed, then the password changed after the emergency has passed.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 20, 2014 at 3:16 pm
NJDave (11/20/2014)
There is a seperate dev & production area. The login is for an application using SQL Server authentication in a web.config file for the application to connect.Thank you for sharing
Dave
Then these are all essentially service accounts. Your "wild west" issue is limited to Dev, which can be mitigated in the deployment process. Ours is set up to not deploy to the web.config automatically and to keep the "real" web.config out of the automatic deployment stream: used ID's (if they're even needed) are encrypted and set up by folks other than the devs. At that point, as long as they are not the same in both environments I don't know that you have a major issue.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 20, 2014 at 3:30 pm
ZZartin (11/20/2014)
There's a difference between a service account login used by an application to connect, which a dba should know as the dba is likely the one who will need to configure that login and a user login.
The DBA doesn't need to know the password of such accounts to maintain those same accounts.
I'll also say that such accounts should also be Windows Logins and use Windows Authentication rather than SQL SERVER Authentication, just like any other user.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 20, 2014 at 3:37 pm
Jeff Moden (11/20/2014)
ZZartin (11/20/2014)
There's a difference between a service account login used by an application to connect, which a dba should know as the dba is likely the one who will need to configure that login and a user login.The DBA doesn't need to know the password of such accounts to maintain those same accounts.
I'll also say that such accounts should also be Windows Logins and use Windows Authentication rather than SQL SERVER Authentication, just like any other user.
Not always possible. Some applications are (still) written so as to require a native SQL login to function.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 21, 2014 at 6:10 am
The query tool that our developers use do not allow for the use of Windows accounts to query, only SQL Server authenticated IDs.
I guess I fail to see the huge security risk knowing Suzies password... as a DBA I have 'sa' rights to begin with so anything Suzie does I can do... right?
November 21, 2014 at 7:00 am
Markus (11/21/2014)
The query tool that our developers use do not allow for the use of Windows accounts to query, only SQL Server authenticated IDs.I guess I fail to see the huge security risk knowing Suzies password... as a DBA I have 'sa' rights to begin with so anything Suzie does I can do... right?
I think it's not a real security concern, but it's rather a "temptation" you shouldn't have.
I'll try to explain it with an example.
Let's say that you're the DBA and one of your databases contains payroll information. You've been asking for a raise for years and your boss keeps telling you that there's a global economic stagnation going on and you can't have your raise and nobody else can. Access to the payroll database is audited and you know it. You also cannot access the audit location.
If you knew the password for one of the employees in the HR department, wouldn't it be tempting to impersonate him and check if your boss is telling the truth or somebody else got his raise despite the economic crisis? Come on, it's a 10 seconds query... aren't you tempted? Nobody will ever know...
See? You don't know the password means no temptation. Period.
However, it must be said that there are thousands ways to do what you shoudln't be doing.
For instance you could:
1) restore a copy of the database somewhere else where auditing is not active
2) change the login's password, use it and then revert to the original password using the hash
3) fool the audits with fake IP/hostnames
4) "forget" to fix a security hole and use it to attack the database
5) inject code in stored procedures to have the information you need delivered to you by unaware legitimate users
6) gain Administrator rights and delete your traces
...
I'll stop here: I don't want to be blamed for suggesting too many bad ideas 🙂
I'm sure you get the point.
-- Gianluca Sartori
November 21, 2014 at 7:09 am
Markus (11/21/2014)
The query tool that our developers use do not allow for the use of Windows accounts to query, only SQL Server authenticated IDs.I guess I fail to see the huge security risk knowing Suzies password... as a DBA I have 'sa' rights to begin with so anything Suzie does I can do... right?
In generally it's just bad practice to know end user's personal passwords, the most you should ever have to do with an end user password is set it to a temporary password with the must change on next login box checked.
Think about it, if you know their SQL password what other potential passwords do you know? Their windows password(and what kind of mischief can you get into with that especially if your company uses other windows authentication programs)? The password they use for other systems either in the company or personally?
It should be an actionable offense by both parties the DBA and the person offering their personal passwords to the DBA.
November 21, 2014 at 7:35 am
It is fair to say the concern that started this thread terrifies me.
In our environment, we don't give users SQL logins. We use windows authentication. SQL Logins are reserved for things like Linked Servers and the login for an older application. All Dev work uses an environment-specific "App account" windows login for its permissions.
ZZartin (11/21/2014)
Markus (11/21/2014)
The query tool that our developers use do not allow for the use of Windows accounts to query, only SQL Server authenticated IDs.I guess I fail to see the huge security risk knowing Suzies password... as a DBA I have 'sa' rights to begin with so anything Suzie does I can do... right?
In generally it's just bad practice to know end user's personal passwords, the most you should ever have to do with an end user password is set it to a temporary password with the must change on next login box checked.
Think about it, if you know their SQL password what other potential passwords do you know? Their windows password(and what kind of mischief can you get into with that especially if your company uses other windows authentication programs)? The password they use for other systems either in the company or personally?
To pile in on this point. Suzie happens to be one of those people who uses the same password for every single account she has. So yes, knowing Suzie's password not only means you can pretend to be her to hide your own activities, but it means you could log into her PC and get access to servers or accounts you don't normally have access to. If you knew her Yahoo or Facebook account, you could access those, etc.
Or worse, Suzie gets pissed and does something nasty with her login. But then, because you know what her password is, she blames you for logging into the system with her account. And, for whatever reason, auditing didn't pick up which machine the login came from or you happened to be around her desk at the time of the login...
Do I really need to sketch out the rest of this scenario? ... Damn. I think I just came up with another story idea.
November 21, 2014 at 7:49 am
Brandie Tarvin (11/21/2014)
Damn. I think I just came up with another story idea.
Thanks Brandie! We really needed the fantasy of a writer here 🙂
"The creepy DBA" - out soon in libraries near you 😀
-- Gianluca Sartori
November 21, 2014 at 7:59 am
ScottPletcher (11/20/2014)
Jeff Moden (11/20/2014)
ZZartin (11/20/2014)
There's a difference between a service account login used by an application to connect, which a dba should know as the dba is likely the one who will need to configure that login and a user login.The DBA doesn't need to know the password of such accounts to maintain those same accounts.
I'll also say that such accounts should also be Windows Logins and use Windows Authentication rather than SQL SERVER Authentication, just like any other user.
Not always possible. Some applications are (still) written so as to require a native SQL login to function.
Every application I support uses SQL logins, and they all require at minimum the dbcreator role assigned.
November 21, 2014 at 8:02 am
sqldriver (11/21/2014)
Every application I support uses SQL logins, and they all require at minimum the dbcreator role assigned.
Yikes! What kind of application spawns its own databases? And how many databases does that mean you end up maintaining?
November 21, 2014 at 8:03 am
Our main system was just upgraded from SQL2000 to 2008R2 in May. There was no way to have the user change their password in SQL2000 and/or make it conform to the Network policy for passwords. I guess it is time to change how we handle user logins. Our SOX audit has never questioned the change password on first login for SQL2005 and newer. I guess I will implement that for all new IDs going forward as well.
November 21, 2014 at 8:07 am
Markus (11/21/2014)
Our main system was just upgraded from SQL2000 to 2008R2 in May. There was no way to have the user change their password in SQL2000 and/or make it conform to the Network policy for passwords. I guess it is time to change how we handle user logins. Our SOX audit has never questioned the change password on first login for SQL2005 and newer. I guess I will implement that for all new IDs going forward as well.
You can also go back and implement that on the older imported IDs (if you haven't already).
Viewing 15 posts - 16 through 30 (of 65 total)
You must be logged in to reply to this topic. Login to reply