November 20, 2014 at 8:27 am
Hello -
Should a SQL DBA know users passwords when using SQL Server authentication?
Years ago, I had 2 managers that a DBA should NEVER know the password of an end-user. The reason was liability - a DBA could be blamed for logging with another password and compromising data.
Today, a DBA in my group doesn't understand the reasoning and has experiencing storing the passwords for end-users.
Also, developers are asking for their old passwords, telling me their passwords, showing it in the web.config, etc.
I can see how it would be easier to keep track of the passwords myself, but is it correct practice?
I'm ready to bring it up again with new management but wanted to ask if anyone on this forum had experience in this area they could share.
Thanks
Dave
November 20, 2014 at 8:36 am
DBAs should NEVER store passwords of the end-user. There is no good reason for that. What purpose does a DBA have to store user passwords? Sure a DBA may have to create a SQL Login for an end-user, but there is a reason to require a password change.
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
November 20, 2014 at 8:44 am
My experience is that nobody knows anyone else's password. Period. End of story. It is a fire-able offense if this is violated. As a developer, it would be a lot easier if I could log in as a user and run the application I am writing as them. But, I can't and don't want to.
It is a matter of security. If one is subject to HIPAA, SAR-BOX, or other government regulations, certain violations could land one in jail.
Knowing other user's passwords should never be allowed under any circumstances.
Tom
November 20, 2014 at 8:49 am
NJDave (11/20/2014)
Should a SQL DBA know users passwords when using SQL Server authentication?
Absolutely not.
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
November 20, 2014 at 8:50 am
GilaMonster (11/20/2014)
NJDave (11/20/2014)
Should a SQL DBA know users passwords when using SQL Server authentication?Absolutely not.
Agree!!!
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
November 20, 2014 at 8:53 am
Thank you for all of the replies - is there an exception if the sql server authentication is only being used by an application?
I expect the answer is "no" but want to be thorough.
Thanks
Dave
November 20, 2014 at 8:55 am
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.
November 20, 2014 at 8:58 am
NJDave (11/20/2014)
Thank you for all of the replies - is there an exception if the sql server authentication is only being used by an application?I expect the answer is "no" but want to be thorough.
Thanks
Dave
That should be treated the same. In addition, that is essentially like a service account and how you would treat the service account password. No single user should have those passwords or the entire password should not be held in a single location (or by a single person). That also means that connection strings should not be stored in clear text and should not contain passwords if possible.
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
November 20, 2014 at 9:14 am
It depends...
I never keep track of end users passwords. Also, we have very few users that log in using SQL Server authentication. They're very few, but they forget their passwords very often. In this case, you just need to reset their password and you're done.
Obviously, I have a password safe to keep track of all the sa passwords across the various instances. The login is disabled, but it can come handy at times. In the same password safe I keep all the encryption keys for SSRS and their passwords.
I keep track of the password for some APPLICATION logins. Yes, we do have applications that require SQL Server authentication and won't work with Windows authentication. Usually it's web applications that run on non-windows machines.
Sometimes the application needs to be installed on a new server and nobody knows the password. If I keep track of it, I can give it to the sysadmins without resetting the password and affecting the existing installations or creating new logins for the exact same purpose.
As you already said, this kind of password is usually stored in clear text in a config file (don't get me started...), so keeping it in a password safe just saves time: the sysadmins would find it out anyway (much later).
I know many will disagree with this, and I disagree with myself in the first place :-), but this is the reality at some places, as ugly as it can be.
Regarding compromising data, if you're the DBA you already have access to all the data, so this is a bit of a moot point IMHO. Also, you can impersonate any other user with EXECUTE AS LOGIN.
-- Gianluca Sartori
November 20, 2014 at 9:48 am
spaghettidba (11/20/2014)
Regarding compromising data, if you're the DBA you already have access to all the data, so this is a bit of a moot point IMHO. Also, you can impersonate any other user with EXECUTE AS LOGIN.
Exactly. DBAs have access to the data. Exec as Login should be used for certain cases imho. If a DBA is to use that method or even login as the user with their password and then change data - then that DBA should be fired. That is questionable behavior.
But if using the execute as method to test functionality - that is a different story.;-)
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
November 20, 2014 at 9:55 am
SQLRNNR (11/20/2014)
spaghettidba (11/20/2014)
Regarding compromising data, if you're the DBA you already have access to all the data, so this is a bit of a moot point IMHO. Also, you can impersonate any other user with EXECUTE AS LOGIN.
Exactly. DBAs have access to the data. Exec as Login should be used for certain cases imho. If a DBA is to use that method or even login as the user with their password and then change data - then that DBA should be fired. That is questionable behavior.
But if using the execute as method to test functionality - that is a different story.;-)
In this case, auditing is your friend. The instances that hold sensitive data should be audited and the DBA should not be able to access the audit file location.
With system admins it's a bit tougher, because when you're administrator on the machine nothing can stop you from gaining sysadmin privileges on the instance and do whatever you want, without leaving a trace.
-- Gianluca Sartori
November 20, 2014 at 9:55 am
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 20, 2014 at 10:03 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.
Make sure that they do SQL security audits. I have brought up major SQL security issues to ISO auditors and they didn't include them in their report.
It's almost like some people are just in it to take a check and tell you everything is sunshine and daisies. :w00t:
November 20, 2014 at 10:09 am
sqldriver (11/20/2014)
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.
Make sure that they do SQL security audits. I have brought up major SQL security issues to ISO auditors and they didn't include them in their report.
It's almost like some people are just in it to take a check and tell you everything is sunshine and daisies. :w00t:
IMHO, ISO auditors are the wrong people to do this. You need someone much more hard-core-security for the very reasons you mention.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 20, 2014 at 10:21 am
Jeff Moden (11/20/2014)
sqldriver (11/20/2014)
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.
Make sure that they do SQL security audits. I have brought up major SQL security issues to ISO auditors and they didn't include them in their report.
It's almost like some people are just in it to take a check and tell you everything is sunshine and daisies. :w00t:
IMHO, ISO auditors are the wrong people to do this. You need someone much more hard-core-security for the very reasons you mention.
But do they give you a cool emblem to put in your email signature? Hm?
Viewing 15 posts - 1 through 15 (of 65 total)
You must be logged in to reply to this topic. Login to reply